#include "StdAfx.h"
#include "XD90SqlCeHelper.h"
#define DEL_HR(hr) do {if ((hr) != NULL){(hr)->Release();(hr) = NULL;}} while (0);
#define DEL_MAC(hh) do {if ((hh) != NULL){CoTaskMemFree((hh));(hh) = NULL;}} while (0);
const int COLUMN_ALIGNVAL = 8; // Column alignment value
inline DWORD ROUND_UP(DWORD dwSize, DWORD dwAmount) { return ((dwSize + (dwAmount-1)) & ~(dwAmount-1));}
XD90SqlCeHelper::XD90SqlCeHelper(void)
{
SetSqlLastError("", ClearConnect);
hr = CoInitializeEx(NULL, COINIT_MULTITHREADED); //初始化COM 环境，由于OLE 依赖于COM ，使用OLE之前，先对COM 环境进行初始化，COINIT_MULTITHREADED，允许多线程。
if (hr != S_OK )
{
SetSqlLastError(_T("CoInitializeEx failed"));
return;
}
}
XD90SqlCeHelper::~XD90SqlCeHelper(void)
{
SetSqlLastError("", ClearConnect);
}
void XD90SqlCeHelper::This_Is_A_Example()
{
CString m_dbPath = _T("\\NandFlash\\SqlCeTest1\\dbtest.sdf");
CString m_table = _T("t9170");
CString m_user = _T("");
CString m_pwd = _T("123");
if (::GetFileAttributes(m_dbPath) == 0xFFFFFFFF) //不存在该数据库，则创建
{
DBPROPSET dpset[2];
DBPROP dpp[1];
DBPROP ssedpp[2];
IDBDataSourceAdmin *pAdmin = NULL;
IUnknown* pUnknownSession = NULL;
hr = CoCreateInstance(CLSID_SQLSERVERCE_3_5, NULL, CLSCTX_INPROC_SERVER, IID_IDBDataSourceAdmin, (LPVOID*)&pAdmin);
if (FAILED(hr))
{
AfxMessageBox(L"Create DataBase Error：CoCreateInstance");
DEL_HR(pAdmin);
return;
}
VariantInit(&dpp[0].vValue);
VariantInit(&ssedpp[0].vValue);
VariantInit(&ssedpp[1].vValue);
dpp[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dpp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dpp[0].vValue.vt = VT_BSTR;
dpp[0].vValue.bstrVal = SysAllocString(m_dbPath);
if(NULL == dpp[0].vValue.bstrVal)
{
AfxMessageBox(L"Create DataBase Error：dpp[0].vValue.bstrVal out of memory");
DEL_HR(pAdmin);
return;
}
ssedpp[0].dwPropertyID = DBPROP_SSCE_ENCRYPTDATABASE;
ssedpp[0].dwOptions = DBPROPOPTIONS_REQUIRED;
ssedpp[0].vValue.vt = VT_BOOL;
ssedpp[0].vValue.boolVal = VARIANT_TRUE;
// Specify the password.
ssedpp[1].dwPropertyID = DBPROP_SSCE_DBPASSWORD;
ssedpp[1].dwOptions = DBPROPOPTIONS_REQUIRED;
ssedpp[1].vValue.vt = VT_BSTR;
ssedpp[1].vValue.bstrVal = SysAllocString(m_pwd);
if(NULL == ssedpp[1].vValue.bstrVal)
{
AfxMessageBox(L"Create DataBase Error：ssedpp[1].vValue.bstrVal out of memory");
DEL_HR(pAdmin);
return;
}
dpset[0].guidPropertySet = DBPROPSET_DBINIT;
dpset[0].rgProperties = dpp;
dpset[0].cProperties = sizeof(dpp)/sizeof(dpp[0]);
dpset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT ;
dpset[1].rgProperties = ssedpp;
dpset[1].cProperties = sizeof(ssedpp)/sizeof(ssedpp[0]);
// Create and initialize the database.
hr = pAdmin->CreateDataSource(sizeof(dpset)/sizeof(dpset[0]),
dpset, NULL, IID_IUnknown, &pUnknownSession);
if(FAILED(hr))
{
AfxMessageBox(L"Create DataBase Error：CreateDataSource() failed");
DEL_HR(pAdmin);
DEL_HR(pUnknownSession);
return;
}
AfxMessageBox(L"Create DataBase OK!");
DEL_HR(pAdmin);
DEL_HR(pUnknownSession);
//连接数据库
if (!Init(m_dbPath, m_user, m_pwd))
{
AfxMessageBox(L"Create DataBase Init error：" + GetSqlLastError());
return;
}
//创建表格t9170
CString cs;
cs = _T("CREATE TABLE t9170 ");
cs += _T("(");
cs += _T("id int PRIMARY KEY, ");
cs += _T("f1 float, ");
cs += _T("content ntext, ");
cs += _T("dt datetime, ");
cs += _T("bi bigint, ");
cs += _T("name nvarchar(50)");
cs += _T(")");
ExecuteNonQuery(cs);
AfxMessageBox(L"Create table OK");
}
//连接数据库
else if (!Init(m_dbPath, m_user, m_pwd))
{
AfxMessageBox(L"Init error：" + GetSqlLastError());
return;
}
//插入一行
VARIANT vt;
ExecuteScalar(_T("select max(id) from t9170"), vt) ;
int id = V2INT(vt);
CString cs, strBuf;
id++; //获取最大ID后+1
BYTE sBuf[1024] = {0};
for (int i = 0; i < 1024; i++)
{
sBuf[i] = (BYTE)(i + 1);
}
ConvertFromBytes(strBuf, sBuf, 1024); //转换二进制数组
cs.Format(L"insert into t9170 values (%d, %f, '", id, sqrtf((float)id));
cs += strBuf;
cs += _T("', '2011-2-2 12:32:12', 111, '我阿迪是基础性 是的')");
int row = ExecuteNonQuery(cs);
if(row < 0)
{
AfxMessageBox(L"xd.ExecuteNonQuery error：" + GetSqlLastError());
return;
}
cs.Format(L"ExecuteNonQuery effect %d rows, strBuf len %d", row, strBuf.GetLength());
AfxMessageBox(cs);
strBuf.Empty();
//获取各个类型的数据
float f = 0;
double d= 0;
int i=0;
CTime tt;
vt.vt = VT_UI1 | VT_BYREF;
BYTE aa[4096] = {0x11, 0x22, 0x33, 0x44, 0x55};
vt.pbVal = aa;
CString laststr;
laststr.Format(_T(" %d"), id);
ExecuteScalar(L"select f1 from t9170 where id = " + laststr, vt);
f = V2FLOAT(vt);
ExecuteScalar(L"select f1 from t9170 where id = " + laststr, vt);
d = V2DOUBLE(vt);
ExecuteScalar(L"select max(id) from t9170", vt);
i = V2INT(vt);
ExecuteScalar(L"select max(dt) from t9170", vt);
tt = V2TIME(vt);
cs.Format(L"%02d:%02d:%02d %02d:%02d:%02d\t", tt.GetYear(), tt.GetMonth(), tt.GetDay(), tt.GetHour(), tt.GetMinute(), tt.GetSecond());
vt.pbVal = aa;
ExecuteScalar(_T("select content from t9170 where id = ") + laststr, vt, 4096);
BYTE rBuf[1024] = {0};
ConvertToBytes(vt.pbVal,rBuf, 1024); //得到二进制数组
ExecuteScalar(L"select name from t9170 where id = " + laststr, vt);
V2CSTR(cs, vt);
//查询
row = ExecuteReader(_T("select id, f1,content,dt,bi,name from t9170")) ;
if(row < 0)
{
AfxMessageBox(L"xd.ExecuteReader error：" + GetSqlLastError());
return;
}
CString name;
float f1;
CString content;
LONGLONG bi;
CTime dt;
BYTE buf[1024] = {0};
TCHAR wBuf[1024] = {0};
while(!isEOFReader())
{
memset(buf, 0, 1024);
memset(wBuf, 0, 1024);
/*GetValueInt(0, id);
GetValueFloat(1, f1);
GetValueBytes(2, buf, 1024);
GetValueTime(3, dt);
GetValueLong(4, bi);
GetValueCString(5, name);*/
GetValueInt(_T("id"), id);
GetValueFloat(_T("f1"), f1);
GetValueBytes(_T("content"), buf, 1024);
GetValueTime(_T("dt"), dt);
GetValueLong(_T("bi"), bi);
GetValueCString(_T("name"), name);
cs.Format(L"%3d\t%.2f\t%lld\t",id, f1, bi);
cs.AppendFormat(L"%02d:%02d:%02d %02d:%02d:%02d\t", dt.GetYear(), dt.GetMonth(), dt.GetDay(), dt.GetHour(), dt.GetMinute(), dt.GetSecond());
memcpy(wBuf, buf, 1023);
cs += CString(wBuf);
cs += L"\t" + name;
AfxMessageBox(cs);
MoveNext(); //下一行
}
CloseReader();
//更新数据
row = ExecuteNonQuery(_T("update t9170 set name = '' where id = ") + laststr);
cs.Format(L"ExecuteNonQuery effect %d rows", row);
AfxMessageBox(cs);
ExecuteScalar(L"select name from t9170 where id = " + laststr, vt);
V2CSTR(cs, vt);
//关闭连接
Close();
AfxMessageBox(L"Test is OK");
}
BOOL XD90SqlCeHelper::Init(CString dbPath, CString dbUser, CString dbPwd)
{
SetSqlLastError("", ClearConnect); //清空当前所有参数
//创建一个连接数据库的COM接口对象，打开数据库接口IID_IDBInitialize
HRESULT hr = CoCreateInstance(CLSID_SQLSERVERCE_3_5, NULL, CLSCTX_INPROC_SERVER, IID_IDBInitialize, (LPVOID*)&m_pInit);
if (hr != S_OK || m_pInit == NULL)
{
SetSqlLastError("CoCreateInstance failed", ClearConnect);
return FALSE;
}
DBPROP dbprop[2]; //OLEDB属性
DBPROPSET dbpropset[2]; //OLEDB属性集 ,每个属性集对应一个子项。如数据库名、密码
VariantInit(&dbprop[0].vValue);
VariantInit(&dbprop[1].vValue);//将vValue初始化为VT_EMPTY,实际定义中，VT_EMPTY= 0
//设置要访问的数据的名称 和访问方式
dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt = VT_BSTR;
dbprop[0].vValue.bstrVal = SysAllocString(dbPath);
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties = &dbprop[0];
dbpropset[0].cProperties = 1;
//设�
评论20
最新资源