利用Native Client OLEDB 11 高效率地对SQL SERVER 进行查询和插入操作

前言:

鄙司原始用的都是ADO来访问数据库,而我现在着手的项目是从我的GPS历史数据库中,取出历时数据的一个接口,一个DLL.用ADO写完之后,测试下来,平均4000条的数据,需要 180 毫秒左右. 包括数据包的排序.领导说有点慢.他是用C#写的.

而后我查找了资料,决定用Native Client OLEDB来试一试. 测试之后,效果还不错.取4000条的数据,只花了50毫秒左右,足足快了三倍.当然这也包括了排序.

鉴于此外加OLEDB资料很少,把我的代码分享出来.

WO 对 OLEDB 进行了简单的封装. 以下是项目的OLEDB部分代码.

//NativeClientOLEDB.h

  1 #pragma once
  2 
  3 #include <oledb.h>
  4 #include <oledberr.h>
  5 #include <stdio.h>
  6 #include <stddef.h>   // for offsetof
  7 
  8 #include <msdaguid.h>
  9 #include <msdasql.h>
 10 #include <msdasc.h>
 11 #include <sqlncli.h>
 12 #include <string>
 13 //#include "C:\Program Files\Microsoft SQL Server\110\SDK\Include\sqlncli.h"
 14 
 15 #ifndef _AUTHOR_NAME
 16 #define _AUTHOR_NAME        "沈春钟"
 17 #define _CREATE_DATE        "2016-05-18"
 18 #define _LIBRARY_TITLE        "NativeClientOLEDB"
 19 #define _LIBRARY_DESC        "对NaTiveClientOLEDB的封装"
 20 #define _MAINTAIN_CONTACT    "SamRichard@live.cn"
 21 #define _MAX_COL_NUM        100
 22 #endif
 23 
 24 #pragma comment(lib, "sqlncli11.lib")
 25 
 26 
 27 // @type UWORD    | 2 byte unsigned integer.
 28 typedef unsigned short UWORD;
 29 
 30 // @type SDWORD   | 4 byte signed integer.
 31 typedef signed long SDWORD;
 32 
 33 using namespace std;
 34 
 35 //委托表数据结构体
 36 #pragma pack(push, 1)
 37 class Data
 38 {
 39     //SDWORD SOID_len;   // Length of data (not space allocated).
 40     //DWORD SOID_status;   // Status of column.
 41     //int SOID_value;
 42 };
 43 #pragma pack(pop)
 44 
 45 //// How to lay out each column in memory.
 46 //struct COLUMNDATA {
 47 //    SDWORD idLen;   // Length of data (not space allocated).
 48 //    DWORD idStatus;   // Status of column.
 49 //    int id;   // Store data here as a variant.
 50 //    SDWORD dateLen;
 51 //    DWORD dateStatus;
 52 //    char date[21];
 53 //};
 54 
 55 class CNativeClientOLEDB
 56 {
 57 public:
 58     CNativeClientOLEDB(void);
 59     ~CNativeClientOLEDB(void);
 60 
 61 public:
 62     //初始化连接
 63     HRESULT Init(CStringW strDataSource, CStringW strUserID, CStringW strPassWD, CStringW strCataLOG);
 64     //不建议用此方式初始化
 65     HRESULT Init(CStringW strConnectionString);
 66     //释放连接
 67     void UnInit();
 68 
 69 protected:
 70     //函数申明
 71     virtual void set_bindings();
 72     //设置每列数据列的类型
 73     virtual void SetColType(void);
 74     //设置每列数据列的长度
 75     virtual void SetColLen(void);
 76 
 77     //bind one by one
 78     void set_bind(DBBINDING &binding, int col, DBBYTEOFFSET len_offset, DBBYTEOFFSET status_offset, DBBYTEOFFSET value_offset, DBLENGTH len, DBTYPE type);
 79 
 80     void DumpErrorInfo(IUnknown* pObjectWithError, REFIID IID_InterfaceWithError);
 81 
 82     //HRESULT FastInsertData();
 83     //快速查询数据
 84     HRESULT FastQueryData(CStringW strSql,ULONG& nColNum, IRowset** pIRowset,IAccessor** pIAccessor, HACCESSOR& hAccessor);
 85     //释放RowSet和IAccessor
 86     HRESULT ReleaseRowsetAndIAccessor(IRowset** pIRowset,IAccessor** pIAccessor,const HACCESSOR& hAccessor);
 87     //快速插入数据
 88     HRESULT FastInsertData(CStringW strTable, ULONG& nColNum, IRowsetFastLoad** pIRowsetFastLoad, IAccessor** pIAccessor, HACCESSOR& hAccessor);
 89     //释放RowsetFast和IAccessor
 90     HRESULT ReleaseRowsetFastLoadAndIAccessor(IRowsetFastLoad** pIRowset,IAccessor** pIAccessor,const HACCESSOR& hAccessor);
 91 
 92 private:
 93 
 94     // Given an ICommand pointer, properties, and query, a rowsetpointer is returned.
 95     HRESULT DBInitAndConnect(DBPROPSET* rgPropertySets, ULONG ulcPropCount, CLSID clsidProv);
 96 
 97     // Use to set properties and execute a given query.
 98     HRESULT ExecuteQuery(IDBCreateCommand* pIDBCreateCommand,
 99         WCHAR* pwszQuery,
100         DBPROPSET* rgPropertySets,
101         ULONG ulcPropCount,
102         LONG* pcRowsAffected,
103         IRowset** ppIRowset,
104         BOOL fSuccessOnly = TRUE);
105 
106     // Use to set up options for call to IDBInitialize::Initialize.
107     void SetupOption(DBPROPID PropID, WCHAR *wszVal, DBPROP * pDBProp);
108 
109     // Sets fastload property on/off for session.
110     HRESULT SetFastLoadProperty(BOOL fSet);
111 
112 public:
113     //创建会话和命令
114     HRESULT CreateSessionAndCommand();
115     //释放会话和命令
116     HRESULT ReleaseSessionAndCommand();
117     //设置模式和创建会话
118     HRESULT SetPropertiesAndCreateSessionAndIOpenRowset(BOOL bFast = TRUE);
119     //释放会话
120     HRESULT ReleaseSessionIOpenRowset();
121 
122 private:
123     //初始化层
124     IMalloc* m_pIMalloc;
125     IDataInitialize* m_pIDataInitialize;
126     IDBInitialize* m_pIDBInitialize;
127     // OLE initialized?
128     BOOL m_bInitialized;
129     //回话和命令层
130     IDBCreateSession* m_pIDBSession;
131     IOpenRowset* m_pIOpenRowset;
132     ICommand* m_pICommand;
133     IDBCreateCommand* m_pIDBCreateCommand;
134     ICommandText* m_pICommandText;
135     DBID TableID;
136 
137 protected:
138     //binding数组
139     DBBINDING m_bindings[_MAX_COL_NUM];
140 
141     //委托表每列的数据类型
142     DBTYPEENUM col_type[_MAX_COL_NUM];
143 
144     //委托表每列的数据长度
145     DBBYTEOFFSET col_len[_MAX_COL_NUM];
146 };
147 
148

View Code

//NativeClientOLEDB.cpp

  1 #include "StdAfx.h"
  2 #include "NativeClientOLEDB.h"
  3 #include <time.h>
  4 #include <Base64.h>
  5 
  6 #define COUNT  10000
  7 #define ROW_SIZE  1000
  8 
  9 #define COLUMN_ALIGNVAL 8
 10 #define ROUND_UP(Size, Amount)(((DWORD)(Size) + ((Amount)-1)) & ~((Amount)-1))
 11 
 12 const UWORD g_cOPTION = 4;
 13 const UWORD MAXPROPERTIES = 5;
 14 const ULONG DEFAULT_CBMAXLENGTH = 20;
 15 
 16 
 17 CNativeClientOLEDB::CNativeClientOLEDB(void)
 18 {
 19     m_pIMalloc = NULL;
 20     m_pIDBInitialize = NULL;
 21     m_bInitialized = FALSE;
 22     m_pIDBSession = NULL;
 23     m_pIOpenRowset  = NULL;
 24     m_pICommand = NULL;
 25     m_pIDBCreateCommand = NULL;
 26     m_pICommandText = NULL;
 27     TableID.uName.pwszName = NULL;
 28     m_pIDataInitialize = NULL;
 29     SetColType();
 30     SetColLen();
 31 }
 32 
 33 CNativeClientOLEDB::~CNativeClientOLEDB(void)
 34 {
 35     ReleaseSessionAndCommand();
 36     UnInit();
 37 }
 38 
 39 HRESULT CNativeClientOLEDB::Init(CStringW strDataSource, CStringW strUserID, CStringW strPassWD, CStringW strCataLOG)
 40 {
 41     HRESULT hr = NOERROR;
 42     // One property set for initializing.
 43     DBPROPSET rgPropertySets[1];
 44     // Properties within above property set.
 45     DBPROP rgDBProperties[g_cOPTION];
 46     do 
 47     {
 48         // Basic initialization.
 49         if (FAILED(CoInitialize(NULL)))
 50             break;
 51         else
 52             m_bInitialized = TRUE;
 53 
 54         hr = CoGetMalloc(MEMCTX_TASK, &m_pIMalloc);
 55         if ((!m_pIMalloc) || FAILED(hr))
 56             break;
 57 
 58         // Set up property set for call to IDBInitialize in CreateSessionCommand.
 59         rgPropertySets[0].rgProperties = rgDBProperties;
 60         rgPropertySets[0].cProperties = g_cOPTION;
 61         rgPropertySets[0].guidPropertySet = DBPROPSET_DBINIT;
 62 
 63         SetupOption(DBPROP_INIT_DATASOURCE, (WCHAR *)(LPCWSTR)strDataSource, &rgDBProperties[0]);
 64         SetupOption(DBPROP_INIT_CATALOG, (WCHAR *)(LPCWSTR)strCataLOG, &rgDBProperties[1]);
 65         SetupOption(DBPROP_AUTH_USERID, (WCHAR *)(LPCWSTR)strUserID, &rgDBProperties[2]);
 66         SetupOption(DBPROP_AUTH_PASSWORD, (WCHAR *)(LPCWSTR)strPassWD, &rgDBProperties[3]);
 67 
 68         //if (S_OK != (hr = DBInitAndConnect(rgPropertySets, 1, SQLNCLI_CLSID)))
 69         if (S_OK != (hr = DBInitAndConnect(rgPropertySets, 1, CLSID_SQLNCLI11)))
 70             break;
 71         return S_OK;
 72     } while (0);
 73 
 74     return hr;
 75 }
 76 
 77 HRESULT CNativeClientOLEDB::Init(CStringW strConnectionString)
 78 {
 79     HRESULT hr  = S_OK;
 80     do 
 81     {
 82 
 83         if (FAILED(CoInitialize(NULL)))
 84             break;
 85         else
 86             m_bInitialized = TRUE;
 87 
 88         hr = CoCreateInstance(
 89             CLSID_MSDAINITIALIZE,
 90             NULL,
 91             CLSCTX_INPROC_SERVER,
 92             IID_IDataInitialize,
 93             reinterpret_cast<LPVOID *>(&m_pIDataInitialize));
 94         if (!SUCCEEDED(hr))
 95             break;
 96 
 97 
 98         hr = m_pIDataInitialize->GetDataSource(
 99             NULL,
100             CLSCTX_INPROC_SERVER,
101             strConnectionString,            
102             IID_IDBInitialize,
103             reinterpret_cast<IUnknown **>(&m_pIDBInitialize));
104 
105         if (!SUCCEEDED(hr))
106             break;
107 
108         if (!SUCCEEDED(hr = m_pIDBInitialize->Initialize())) {
109             printf("Call to initialize failed.\n");
110             break;
111         }
112 
113         return S_OK;
114 
115     } while (0);
116 
117     UnInit();
118     return hr;
119 }
120 
121 void CNativeClientOLEDB::UnInit()
122 {
123     HRESULT hr = NOERROR;
124 
125     //ReleaseSessionAndCommand();
126     //ReleaseSessionIOpenRowset();
127 
128     if (m_pIMalloc){
129         m_pIMalloc->Release();
130         m_pIMalloc = NULL;
131     }
132 
133     if (m_pIDBInitialize) {
134         hr = m_pIDBInitialize->Uninitialize();
135         m_pIDBInitialize = NULL;
136         if (FAILED(hr))
137             printf("Uninitialize failed\n");
138     }
139 
140     if (m_bInitialized){
141         CoUninitialize();
142         m_bInitialized = FALSE;
143     }
144 
145     if (SUCCEEDED(hr))
146         printf("Test completed successfully.\n\n");
147     else
148         printf("Test failed.\n\n");
149 
150 }
151 
152 void CNativeClientOLEDB::SetColType(void)
153 {
154 
155 }
156 
157 void CNativeClientOLEDB::SetColLen(void)
158 {
159     
160 }
161 
162 void CNativeClientOLEDB::set_bindings()
163 {
164 
165 }
166 
167 void CNativeClientOLEDB::set_bind(DBBINDING &binding, int col, DBBYTEOFFSET len_offset, DBBYTEOFFSET status_offset, DBBYTEOFFSET value_offset, DBLENGTH len, DBTYPE type)
168 {
169     binding.dwPart = DBPART_VALUE | DBPART_LENGTH | DBPART_STATUS;
170     binding.iOrdinal = col;
171     binding.pTypeInfo = NULL;
172     binding.obValue = value_offset;
173     binding.obLength = len_offset;
174     binding.obStatus = status_offset;
175     binding.cbMaxLen = len;   // Size of varchar column.
176     binding.pTypeInfo = NULL;
177     binding.pObject = NULL;
178     binding.pBindExt = NULL;
179     binding.dwFlags = 0;
180     binding.eParamIO = DBPARAMIO_NOTPARAM;
181     binding.dwMemOwner = DBMEMOWNER_CLIENTOWNED;
182     binding.bPrecision = 0;
183     binding.bScale = 0;
184     binding.wType = type;
185 }
186 
187 HRESULT CNativeClientOLEDB::DBInitAndConnect(DBPROPSET* rgPropertySets, ULONG ulcPropCount, CLSID clsidProv)
188 {
189     HRESULT hr = NOERROR;
190     IDBProperties* pIDBProperties = NULL;
191     UWORD i = 0, j = 0;   // indexes.
192 
193     if (ulcPropCount && !rgPropertySets) {
194         hr = E_INVALIDARG;
195         return hr;
196     }
197 
198     if (S_OK != (hr = CoCreateInstance(clsidProv,
199         NULL, CLSCTX_INPROC_SERVER,
200         IID_IDBInitialize,
201         (void **)&m_pIDBInitialize))){
202             goto CLEANUP;
203     }
204 
205     if (S_OK != (hr = m_pIDBInitialize->QueryInterface(IID_IDBProperties,
206         (void **)&pIDBProperties)))
207         goto CLEANUP;
208 
209     if (S_OK != (hr = pIDBProperties->SetProperties(ulcPropCount, rgPropertySets))){
210         goto CLEANUP;
211     }
212     
213     if (S_OK != (hr = m_pIDBInitialize->Initialize())) {
214 
215         printf("Call to initialize failed.\n");
216         goto CLEANUP;
217     }
218 
219 CLEANUP:
220     if (pIDBProperties)
221         pIDBProperties->Release();
222 
223     for (i = 0; i < ulcPropCount; i++)
224         for (j = 0; j < rgPropertySets[i].cProperties; j++)
225             VariantClear(&(rgPropertySets[i].rgProperties[j]).vValue);
226     return hr;
227 }
228 
229 void CNativeClientOLEDB::DumpErrorInfo(IUnknown* pObjectWithError, REFIID IID_InterfaceWithError)
230 {
231     // Interfaces used in the example.
232     IErrorInfo*             pIErrorInfoAll = NULL;
233     IErrorInfo*             pIErrorInfoRecord = NULL;
234     IErrorRecords*          pIErrorRecords = NULL;
235     ISupportErrorInfo*      pISupportErrorInfo = NULL;
236     ISQLErrorInfo*          pISQLErrorInfo = NULL;
237     ISQLServerErrorInfo*    pISQLServerErrorInfo = NULL;
238 
239     // Number of error records.
240     ULONG                   nRecs;
241     ULONG                   nRec;
242 
243     // Basic error information from GetBasicErrorInfo.
244     ERRORINFO               errorinfo;
245 
246     // IErrorInfo values.
247     BSTR                    bstrDescription;
248     BSTR                    bstrSource;
249 
250     // ISQLErrorInfo parameters.
251     BSTR                    bstrSQLSTATE;
252     LONG                    lNativeError;
253 
254     // ISQLServerErrorInfo parameter pointers.
255     SSERRORINFO*            pSSErrorInfo = NULL;
256     OLECHAR*                pSSErrorStrings = NULL;
257 
258     // Hard-code an American English locale for the example.
259     DWORD                   MYLOCALEID = 0x0409;
260 
261     // Only ask for error information if the interface supports
262     // it.
263     if (FAILED(pObjectWithError->QueryInterface(IID_ISupportErrorInfo,
264         (void**)&pISupportErrorInfo)))
265     {
266         wprintf_s(L"SupportErrorErrorInfo interface not supported");
267         return;
268     }
269     if (FAILED(pISupportErrorInfo->
270         InterfaceSupportsErrorInfo(IID_InterfaceWithError)))
271     {
272         wprintf_s(L"InterfaceWithError interface not supported");
273         return;
274     }
275 
276     // Do not test the return of GetErrorInfo. It can succeed and return
277     // a NULL pointer in pIErrorInfoAll. Simply test the pointer.
278     GetErrorInfo(0, &pIErrorInfoAll);
279 
280     if (pIErrorInfoAll != NULL)
281     {
282         // Test to see if it's a valid OLE DB IErrorInfo interface 
283         // exposing a list of records.
284         if (SUCCEEDED(pIErrorInfoAll->QueryInterface(IID_IErrorRecords,
285             (void**)&pIErrorRecords)))
286         {
287             pIErrorRecords->GetRecordCount(&nRecs);
288 
289             // Within each record, retrieve information from each
290             // of the defined interfaces.
291             for (nRec = 0; nRec < nRecs; nRec++)
292             {
293                 // From IErrorRecords, get the HRESULT and a reference
294                 // to the ISQLErrorInfo interface.
295                 pIErrorRecords->GetBasicErrorInfo(nRec, &errorinfo);
296                 pIErrorRecords->GetCustomErrorObject(nRec,
297                     IID_ISQLErrorInfo, (IUnknown**)&pISQLErrorInfo);
298 
299                 // Display the HRESULT, then use the ISQLErrorInfo.
300                 wprintf_s(L"HRESULT:\t%#X\n", errorinfo.hrError);
301 
302                 if (pISQLErrorInfo != NULL)
303                 {
304                     pISQLErrorInfo->GetSQLInfo(&bstrSQLSTATE,
305                         &lNativeError);
306 
307                     // Display the SQLSTATE and native error values.
308                     wprintf_s(L"SQLSTATE:\t%s\nNative Error:\t%ld\n",
309                         bstrSQLSTATE, lNativeError);
310 
311                     // SysFree BSTR references.
312                     SysFreeString(bstrSQLSTATE);
313 
314                     // Get the ISQLServerErrorInfo interface from
315                     // ISQLErrorInfo before releasing the reference.
316                     pISQLErrorInfo->QueryInterface(
317                         IID_ISQLServerErrorInfo,
318                         (void**)&pISQLServerErrorInfo);
319 
320                     pISQLErrorInfo->Release();
321                 }
322 
323                 // Test to ensure the reference is valid, then
324                 // get error information from ISQLServerErrorInfo.
325                 if (pISQLServerErrorInfo != NULL)
326                 {
327                     pISQLServerErrorInfo->GetErrorInfo(&pSSErrorInfo,
328                         &pSSErrorStrings);
329 
330                     // ISQLServerErrorInfo::GetErrorInfo succeeds
331                     // even when it has nothing to return. Test the
332                     // pointers before using.
333                     if (pSSErrorInfo)
334                     {
335                         // Display the state and severity from the
336                         // returned information. The error message comes
337                         // from IErrorInfo::GetDescription.
338                         wprintf_s(L"Error state:\t%d\nSeverity:\t%d\n",
339                             pSSErrorInfo->bState,
340                             pSSErrorInfo->bClass);
341 
342                         // IMalloc::Free needed to release references
343                         // on returned values. For the example, assume
344                         // the g_pIMalloc pointer is valid.
345                         m_pIMalloc->Free(pSSErrorStrings);
346                         m_pIMalloc->Free(pSSErrorInfo);
347                     }
348 
349                     pISQLServerErrorInfo->Release();
350                 }
351 
352                 if (SUCCEEDED(pIErrorRecords->GetErrorInfo(nRec,
353                     MYLOCALEID, &pIErrorInfoRecord)))
354                 {
355                     // Get the source and description (error message)
356                     // from the record's IErrorInfo.
357                     pIErrorInfoRecord->GetSource(&bstrSource);
358                     pIErrorInfoRecord->GetDescription(&bstrDescription);
359 
360                     if (bstrSource != NULL)
361                     {
362                         wprintf_s(L"Source:\t\t%s\n", bstrSource);
363                         SysFreeString(bstrSource);
364                     }
365                     if (bstrDescription != NULL)
366                     {
367                         wprintf_s(L"Error message:\t%s\n",
368                             bstrDescription);
369                         SysFreeString(bstrDescription);
370                     }
371 
372                     pIErrorInfoRecord->Release();
373                 }
374             }
375 
376             pIErrorRecords->Release();
377         }
378         else
379         {
380             // IErrorInfo is valid; get the source and
381             // description to see what it is.
382             pIErrorInfoAll->GetSource(&bstrSource);
383             pIErrorInfoAll->GetDescription(&bstrDescription);
384 
385             if (bstrSource != NULL)
386             {
387                 wprintf_s(L"Source:\t\t%s\n", bstrSource);
388                 SysFreeString(bstrSource);
389             }
390             if (bstrDescription != NULL)
391             {
392                 wprintf_s(L"Error message:\t%s\n", bstrDescription);
393                 SysFreeString(bstrDescription);
394             }
395         }
396 
397         pIErrorInfoAll->Release();
398     }
399     else
400     {
401         wprintf_s(L"GetErrorInfo failed.");
402     }
403 
404     pISupportErrorInfo->Release();
405 
406     return;
407 }
408 
409 void CNativeClientOLEDB::SetupOption(DBPROPID PropID, WCHAR *wszVal, DBPROP * pDBProp)
410 {
411     pDBProp->dwPropertyID = PropID;
412     pDBProp->dwOptions = DBPROPOPTIONS_REQUIRED;
413     pDBProp->colid = DB_NULLID;
414     pDBProp->vValue.vt = VT_BSTR;
415     pDBProp->vValue.bstrVal = SysAllocStringLen(wszVal, wcslen(wszVal));
416 }
417 
418 HRESULT CNativeClientOLEDB::SetFastLoadProperty(BOOL fSet)
419 {
420     HRESULT hr = S_OK;
421     IDBProperties* pIDBProps = NULL;
422     DBPROP rgProps[1];
423     DBPROPSET PropSet;
424 
425     VariantInit(&rgProps[0].vValue);
426 
427     rgProps[0].dwOptions = DBPROPOPTIONS_REQUIRED;
428     rgProps[0].colid = DB_NULLID;
429     rgProps[0].vValue.vt = VT_BOOL;
430     rgProps[0].dwPropertyID = SSPROP_ENABLEFASTLOAD;
431 
432     if (fSet == TRUE)
433         rgProps[0].vValue.boolVal = VARIANT_TRUE;
434     else
435         rgProps[0].vValue.boolVal = VARIANT_FALSE;
436 
437     PropSet.rgProperties = rgProps;
438     PropSet.cProperties = 1;
439     PropSet.guidPropertySet = DBPROPSET_SQLSERVERDATASOURCE;
440 
441     if (SUCCEEDED(hr = m_pIDBInitialize->QueryInterface(IID_IDBProperties, (LPVOID *)&pIDBProps)))
442         hr = pIDBProps->SetProperties(1, &PropSet);
443 
444     VariantClear(&rgProps[0].vValue);
445 
446     if (pIDBProps)
447         pIDBProps->Release();
448 
449     return hr;
450 }
451 
452 HRESULT CNativeClientOLEDB::FastQueryData(CStringW strSql,ULONG& nColNum,IRowset** pIRowset,IAccessor** pIAccessor, HACCESSOR& hAccessor)
453 {
454 
455     HRESULT hr = E_FAIL;
456     if (NULL != *pIRowset || NULL != *pIAccessor || strSql.IsEmpty() || NULL == m_pICommandText || NULL == m_pICommand || nColNum <= 0){
457         return hr;
458     }
459     do 
460     {
461         if (FAILED(hr = m_pICommandText->SetCommandText(DBGUID_DBSQL, strSql))){
462             break;
463         }
464 
465         LONG lAffected;
466         if (FAILED(hr = m_pICommand->Execute(NULL, IID_IRowset, NULL, &lAffected, (IUnknown **)pIRowset))){
467             break;
468         }
469 
470         if (FAILED(hr = (*pIRowset)->QueryInterface(IID_IAccessor, (void **)pIAccessor))){
471             break;
472         }
473 
474         set_bindings();
475 
476         DBBINDSTATUS dbs[_MAX_COL_NUM] = { 0 };
477         if (FAILED(hr = (*pIAccessor)->CreateAccessor(DBACCESSOR_ROWDATA, (DBCOUNTITEM)nColNum, m_bindings, sizeof(Data),&hAccessor, dbs))){
478             //dbs是绑定状态
479             break;
480         }
481         
482         return S_OK;
483     } while (0);
484     
485     ReleaseRowsetAndIAccessor(pIRowset, pIAccessor, hAccessor);
486 }
487 
488 
489 HRESULT CNativeClientOLEDB::FastInsertData(CStringW strTable, ULONG& nColNum, IRowsetFastLoad** pIRowsetFastLoad, IAccessor** pIAccessor, HACCESSOR& hAccessor)
490 {
491     HRESULT hr = E_FAIL;
492     TableID.uName.pwszName = NULL;
493     TableID.eKind = DBKIND_NAME;
494     TableID.uName.pwszName = new WCHAR[strTable.GetLength() + 2];
495     wcsncpy_s(TableID.uName.pwszName, strTable.GetLength() + 2, strTable, strTable.GetLength() + 1);
496     TableID.uName.pwszName[strTable.GetLength() + 1] = (WCHAR)NULL;
497 
498     do 
499     {
500         // Get IRowsetFastLoad initialized to use the test table.
501         if (FAILED(hr =    m_pIOpenRowset->OpenRowset(NULL, &TableID, NULL, IID_IRowsetFastLoad, 0, NULL, (LPUNKNOWN *)pIRowsetFastLoad))){
502             break;
503         }
504 
505         set_bindings();
506         DBBINDSTATUS bds[_MAX_COL_NUM] = { 0 };
507         if (FAILED(hr =    (*pIRowsetFastLoad)->QueryInterface(IID_IAccessor, (void **)pIAccessor))){
508             break;
509         }
510 
511         if (FAILED(hr = (*pIAccessor)->CreateAccessor(DBACCESSOR_ROWDATA, (DBCOUNTITEM)nColNum, m_bindings, (ROUND_UP(sizeof(Data), COLUMN_ALIGNVAL)), &hAccessor, bds))){
512             //dbs是绑定状态
513             break;
514         }
515 
516         return S_OK;
517     } while (0);
518 
519     ReleaseRowsetFastLoadAndIAccessor(pIRowsetFastLoad, pIAccessor, hAccessor);
520     return E_FAIL;
521 }
522 
523 HRESULT CNativeClientOLEDB::ReleaseRowsetAndIAccessor(IRowset** pIRowset,IAccessor** pIAccessor,const HACCESSOR& hAccessor)
524 {
525     HRESULT hr = S_OK;
526     if (*pIRowset){
527         (*pIRowset)->Release();
528         (*pIRowset) = NULL;
529     }
530 
531     if (*pIAccessor && hAccessor){
532         if (FAILED((*pIAccessor)->ReleaseAccessor(hAccessor, NULL))){
533             hr = E_FAIL;
534         }
535         (*pIAccessor)->Release();
536         *pIAccessor = NULL;
537     }
538 
539     return hr;
540 }
541 
542 HRESULT CNativeClientOLEDB::ReleaseRowsetFastLoadAndIAccessor(IRowsetFastLoad** pIRowset,IAccessor** pIAccessor,const HACCESSOR& hAccessor)
543 {
544 
545     HRESULT hr = S_OK;
546     if (FAILED(hr = SetFastLoadProperty(FALSE)))
547         printf("SetFastLoadProperty(FALSE) failed with %x", hr);
548 
549     if (*pIAccessor && hAccessor)
550         if (FAILED((*pIAccessor)->ReleaseAccessor(hAccessor, NULL)))
551             hr = E_FAIL;
552 
553     if (*pIAccessor){
554         (*pIAccessor)->Release();
555         *pIAccessor = NULL;
556     }
557 
558     if (*pIRowset){
559         (*pIRowset)->Release();
560         (*pIRowset) = NULL;
561     }
562 
563     if (TableID.uName.pwszName)
564         delete[]TableID.uName.pwszName;
565 
566     return hr;
567 }
568 
569 HRESULT CNativeClientOLEDB::CreateSessionAndCommand()
570 {
571     HRESULT hr = E_FAIL;
572     if (NULL == m_pIDBInitialize || m_pIDBSession || m_pIOpenRowset || m_pIDBCreateCommand || m_pICommand || m_pICommandText){
573         return hr;
574     }
575 
576     do 
577     {
578         if (FAILED(hr =
579             m_pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&m_pIDBSession)))
580             break;
581 
582         if (FAILED(hr =
583             m_pIDBSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown **)&m_pIOpenRowset )))
584             break;
585 
586         if (FAILED(hr = m_pIOpenRowset ->QueryInterface(IID_IDBCreateCommand, (void **)&m_pIDBCreateCommand))){
587             break;
588         }
589 
590         if (FAILED(hr =    m_pIDBCreateCommand->CreateCommand(NULL, IID_ICommand, (IUnknown **)&m_pICommand))){
591                 break;
592         }
593 
594         if (FAILED(hr = m_pICommand->QueryInterface(&m_pICommandText))){
595             break;
596         }
597 
598         return S_OK;
599 
600     } while (0);
601     
602     ReleaseSessionAndCommand();
603     return hr;
604 }
605 
606 HRESULT CNativeClientOLEDB::ReleaseSessionAndCommand()
607 {
608     HRESULT hr = S_OK;
609     if (m_pICommandText){
610         m_pICommandText->Release();
611         m_pICommandText = NULL;
612     }
613 
614     if (m_pICommand){
615         m_pICommand->Release();
616         m_pICommand = NULL;
617     }
618 
619     if (m_pIDBCreateCommand){
620         m_pIDBCreateCommand->Release();
621         m_pIDBCreateCommand = NULL;
622     }
623 
624     if (m_pIOpenRowset){
625         m_pIOpenRowset->Release();
626         m_pIOpenRowset = NULL;
627     }
628 
629     if (m_pIDBSession){
630         m_pIDBSession->Release();
631         m_pIDBSession = NULL;
632     }
633 
634     return S_OK;
635 }
636 
637 HRESULT CNativeClientOLEDB::SetPropertiesAndCreateSessionAndIOpenRowset(BOOL bFast /*= TRUE*/)
638 {
639     HRESULT hr;
640     if (NULL == m_pIDBInitialize){
641         return E_FAIL;
642     }
643     do 
644     {
645         if (FAILED(hr = SetFastLoadProperty(bFast)))
646             break;
647 
648         if (FAILED(hr =
649             m_pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&m_pIDBSession)))
650             break;
651 
652         if (FAILED(hr =
653             m_pIDBSession->CreateSession(NULL, IID_IOpenRowset, (IUnknown **)&m_pIOpenRowset)))
654             break;
655 
656         return S_OK;
657     } while (0);
658     // Get the fastload pointer.
659     
660     ReleaseSessionIOpenRowset();
661     return E_FAIL;
662 }
663 
664 HRESULT CNativeClientOLEDB::ReleaseSessionIOpenRowset()
665 {
666     if (m_pIDBSession){
667         m_pIDBSession->Release();
668         m_pIDBSession = NULL;
669     }
670 
671     if (m_pIOpenRowset){
672         m_pIOpenRowset->Release();
673         m_pIOpenRowset = NULL;
674     }
675     return S_OK;
676 }

View Code

以上两个文件,基本覆盖了创建\连接\释放等等的过程.下面对某些函数进行讲解一下;

1. 调用CoInitialize 来初始化控件.因为它是基于com组件的.

2. 通过调用 CoCreateInstance 方法来创建数据源对象的实例。

每个 OLE DB 访问接口都具有一个唯一的类标识符 (CLSID)。对于 SQL Server Native Client OLE DB 访问接口，类标识符为 CLSID_SQLNCLI10。也可以使用符号 SQLNCLI_CLSID，该符号将解析为您引用的 sqlncli.h 中使用的 SQL Server Native Client OLE DB 访问接口。

数据源对象公开了 IDBProperties 接口，使用者使用该接口提供基本的身份验证信息，如服务器名、数据库名、用户 ID 和密码。可调用IDBProperties::SetProperties 方法设置这些属性。

3. 调用 IDBProperties::Initialize 接口来建立与数据源的连接.

4. 在建立与某一数据源的连接后，使用者调用 IDBCreateSession::CreateSession 方法以创建一个会话。该会话充当命令、行集或事务工厂。

5. IOpenRowset::OpenRowset 方法打开并返回一个行集，该行集包括来自单个基表或索引的所有行。

6. 执行IDBCreateCommand::CreateCommand 方法，以便为 ICommandText 接口创建一个命令对象和请求。

7. 利用 ICommandText::SetCommandText 指定要执行的命令。

当然在我的代码中有 set_bindings(); 这个函数是用来把数据库中的类型与OLEDB中的数据类型进行一个绑定. 可以参照:

https://msdn.microsoft.com/zh-cn/library/ms130984.aspx

8. 调用 Execute 命令用于执行该命令。

9. 从IRowset中取出数据等等操作.

10. 调用ReleaseRowsetAndIAccessor 来释放RowSet和IAccessor

11. 调用 ReleaseSessionAndCommand 来释放会话和命令

12. 最后调用UnInit 来释放连接.

上面是基于底层封装的数据源操作部分.下面给出与数据相关的操作的类.

//HistoryDataOLEDB.h

  1 #pragma once
  2 #include <OLEDB/NativeClientOLEDB.h>
  3 
  4 #pragma pack(push, 1)
  5 class HistoryData : 
  6     public Data{
  7 public:
  8     /*
  9     SDWORD SOID_len;   // Length of data (not space allocated).
 10     DWORD SOID_status;   // Status of column.
 11     int SOID_value;
 12 
 13     SDWORD TerminalID_len;   // Length of data (not space allocated).
 14     DWORD TerminalID_status;   // Status of column.
 15     int TerminalID_value;
 16     */
 17     SDWORD Longitude_len;   // Length of data (not space allocated).
 18     DWORD Longitude_status;   // Status of column.
 19     int Longitude_value;
 20 
 21     SDWORD Latitude_len;   // Length of data (not space allocated).
 22     DWORD Latitude_status;   // Status of column.
 23     int Latitude_value;
 24 
 25     SDWORD Speed_len;   // Length of data (not space allocated).
 26     DWORD Speed_status;   // Status of column.
 27     WORD Speed_value;
 28 
 29     //
 30 
 31     SDWORD Direction_len;   // Length of data (not space allocated).
 32     DWORD Direction_status;   // Status of column.
 33     WORD Direction_value;
 34 
 35     SDWORD Height_len;   // Length of data (not space allocated).
 36     DWORD Height_status;   // Status of column.
 37     short Height_value;
 38 
 39     SDWORD Lock_len;   // Length of data (not space allocated).
 40     DWORD Lock_status;   // Status of column.
 41     BYTE Lock_value;
 42 
 43 
 44     SDWORD Gpstime_len;   // Length of data (not space allocated).
 45     DWORD Gpstime_status;   // Status of column.
 46     char Gpstime_value[30];
 47 
 48     SDWORD InStatus_len;   // Length of data (not space allocated).
 49     DWORD InStatus_status;   // Status of column.
 50     short InStatus_value;
 51 
 52     //
 53     SDWORD TmlStatus_len;   // Length of data (not space allocated).
 54     DWORD TmlStatus_status;   // Status of column.
 55     short TmlStatus_value;
 56 
 57     SDWORD WarnFlag_len;   // Length of data (not space allocated).
 58     DWORD WarnFlag_status;   // Status of column.
 59     short WarnFlag_value;
 60 
 61     SDWORD Resver2_len;   // Length of data (not space allocated).
 62     DWORD Resver2_status;   // Status of column.
 63     short Resver2_value;
 64 
 65     SDWORD Alldis_len;   // Length of data (not space allocated).
 66     DWORD Alldis_status;   // Status of column.
 67     int Alldis_value;
 68 
 69     SDWORD Temperature_len;   // Length of data (not space allocated).
 70     DWORD Temperature_status;   // Status of column.
 71     int Temperature_value;
 72 
 73     //
 74     SDWORD Oil_len;   // Length of data (not space allocated).
 75     DWORD Oil_status;   // Status of column.
 76     short Oil_value;
 77 
 78     SDWORD Smsid1_len;   // Length of data (not space allocated).
 79     DWORD Smaid1_status;   // Status of column.
 80     short Smaid1_value;
 81 
 82     SDWORD Smaid2_len;   // Length of data (not space allocated).
 83     DWORD Smaid2_status;   // Status of column.
 84     short Smaid2_value;
 85     /*
 86     SDWORD Longitude2_len;   // Length of data (not space allocated).
 87     DWORD Longitude2_status;   // Status of column.
 88     int Longitude2_value;
 89 
 90     SDWORD Latitude2_len;   // Length of data (not space allocated).
 91     DWORD Latitude2_status;   // Status of column.
 92     int Latitude2_value;
 93     */
 94     //
 95     SDWORD AffixData_len;   // Length of data (not space allocated).
 96     DWORD AffixData_status;   // Status of column.
 97     char AffixData_value[2000];
 98 };
 99 #pragma pack(pop)
100 
101 class CHistoryDataOLEDB :
102     public CNativeClientOLEDB
103 {
104 public:
105     CHistoryDataOLEDB(void);
106     ~CHistoryDataOLEDB(void);
107 
108     virtual void SetColType(void);
109 
110     virtual void SetColLen(void);
111 
112     virtual void set_bindings();
113 
114     void Lock()        {    cs.Lock();        };
115     void Unlock()    {    cs.Unlock();    };
116 
117 public:
118     //快速查询数据
119     LPBYTE FastQueryData(CStringW strSql,ULONG nColNum, int* nLen, int* nCount);
120     //快速插入数据
121     BOOL FastInsertData(const CStringW strTable, HistoryData* const pData, const int nDataNum);
122 
123 private:
124     CCriticalSection cs;
125 };

View Code

//HistoryDataOLEDB.cpp

  1 #include "StdAfx.h"
  2 #include "HistoryDataOLEDB.h"
  3 #include "HistoryDefine.h"
  4 #include "Base64.h"
  5 
  6 CHistoryDataOLEDB::CHistoryDataOLEDB(void)
  7 {
  8     SetColLen();
  9     SetColType();
 10 }
 11 
 12 
 13 CHistoryDataOLEDB::~CHistoryDataOLEDB(void)
 14 {
 15 }
 16 
 17 void CHistoryDataOLEDB::SetColType(void)
 18 {
 19     col_type[0] = DBTYPE_I4;
 20     col_type[1] = DBTYPE_I4;
 21     col_type[2] = DBTYPE_I4;
 22     col_type[3] = DBTYPE_I4;
 23     col_type[4] = DBTYPE_I2;
 24 
 25     col_type[5] = DBTYPE_I2;
 26     col_type[6] = DBTYPE_I2;
 27     col_type[7] = DBTYPE_UI1;
 28     col_type[8] = DBTYPE_STR;
 29     col_type[9] = DBTYPE_I2;
 30 
 31     col_type[10] = DBTYPE_I2;
 32     col_type[11] = DBTYPE_I2;
 33     col_type[12] = DBTYPE_I2;
 34     col_type[13] = DBTYPE_I4;
 35     col_type[14] = DBTYPE_I4;
 36 
 37     col_type[15] = DBTYPE_I2;
 38     col_type[16] = DBTYPE_I2;
 39     col_type[17] = DBTYPE_I2;
 40     col_type[18] = DBTYPE_I4;
 41     col_type[19] = DBTYPE_I4;
 42 
 43     col_type[20] = DBTYPE_STR;
 44 }
 45 
 46 void CHistoryDataOLEDB::SetColLen(void)
 47 {
 48     col_len[0] = sizeof(int);
 49     col_len[1] = sizeof(int);
 50     col_len[2] = sizeof(int);
 51     col_len[3] = sizeof(int);
 52     col_len[4] = sizeof(short);
 53 
 54     col_len[5] = sizeof(short);
 55     col_len[6] = sizeof(short);
 56     col_len[7] = sizeof(char);
 57     col_len[8] = 30;
 58     col_len[9] = sizeof(short);
 59 
 60     col_len[10] = sizeof(short);
 61     col_len[11] = sizeof(short);
 62     col_len[12] = sizeof(short);
 63     col_len[13] = sizeof(int);
 64     col_len[14] = sizeof(int);
 65 
 66     col_len[15] = sizeof(short);
 67     col_len[16] = sizeof(short);
 68     col_len[17] = sizeof(short);
 69     col_len[18] = sizeof(int);
 70     col_len[19] = sizeof(int);
 71 
 72     col_len[20] = 2000;
 73 }
 74 
 75 void CHistoryDataOLEDB::set_bindings()
 76 {
 77     set_bind(m_bindings[0], 1, offsetof(HistoryData, Longitude_len), offsetof(HistoryData, Longitude_status), offsetof(HistoryData, Longitude_value), col_len[2], col_type[2]);
 78     set_bind(m_bindings[1], 2, offsetof(HistoryData, Latitude_len), offsetof(HistoryData, Latitude_status), offsetof(HistoryData, Latitude_value), col_len[3], col_type[3]);
 79     set_bind(m_bindings[2], 3, offsetof(HistoryData, Speed_len), offsetof(HistoryData, Speed_status), offsetof(HistoryData, Speed_value), col_len[4], col_type[4]);
 80 
 81     set_bind(m_bindings[3], 4, offsetof(HistoryData, Direction_len), offsetof(HistoryData, Direction_status), offsetof(HistoryData, Direction_value), col_len[5], col_type[5]);
 82     set_bind(m_bindings[4], 5, offsetof(HistoryData, Height_len), offsetof(HistoryData, Height_status), offsetof(HistoryData, Height_value), col_len[6], col_type[6]);
 83     set_bind(m_bindings[5], 6, offsetof(HistoryData, Lock_len), offsetof(HistoryData, Lock_status), offsetof(HistoryData, Lock_value), col_len[7], col_type[7]);
 84     set_bind(m_bindings[6], 7, offsetof(HistoryData, Gpstime_len), offsetof(HistoryData, Gpstime_status), offsetof(HistoryData, Gpstime_value), col_len[8], col_type[8]);
 85     set_bind(m_bindings[7], 8, offsetof(HistoryData, InStatus_len), offsetof(HistoryData, InStatus_status), offsetof(HistoryData, InStatus_value), col_len[9], col_type[9]);
 86 
 87     set_bind(m_bindings[8], 9, offsetof(HistoryData, TmlStatus_len), offsetof(HistoryData, TmlStatus_status), offsetof(HistoryData, TmlStatus_value), col_len[10], col_type[10]);
 88     set_bind(m_bindings[9], 10, offsetof(HistoryData, WarnFlag_len), offsetof(HistoryData, WarnFlag_status), offsetof(HistoryData, WarnFlag_value), col_len[11], col_type[11]);
 89     set_bind(m_bindings[10], 11, offsetof(HistoryData, Resver2_len), offsetof(HistoryData, Resver2_status), offsetof(HistoryData, Resver2_value), col_len[12], col_type[12]);
 90     set_bind(m_bindings[11], 12, offsetof(HistoryData, Alldis_len), offsetof(HistoryData, Alldis_status), offsetof(HistoryData, Alldis_value), col_len[13], col_type[13]);
 91     set_bind(m_bindings[12], 13, offsetof(HistoryData, Temperature_len), offsetof(HistoryData, Temperature_status), offsetof(HistoryData, Temperature_value), col_len[14], col_type[14]);
 92 
 93     set_bind(m_bindings[13], 14, offsetof(HistoryData, Oil_len), offsetof(HistoryData, Oil_status), offsetof(HistoryData, Oil_value), col_len[15], col_type[15]);
 94     set_bind(m_bindings[14], 15, offsetof(HistoryData, Smsid1_len), offsetof(HistoryData, Smaid1_status), offsetof(HistoryData, Smaid1_value), col_len[16], col_type[16]);
 95     set_bind(m_bindings[15], 16, offsetof(HistoryData, Smaid2_len), offsetof(HistoryData, Smaid2_status), offsetof(HistoryData, Smaid2_value), col_len[17], col_type[17]);
 96 
 97     set_bind(m_bindings[16], 17, offsetof(HistoryData, AffixData_len), offsetof(HistoryData, AffixData_status), offsetof(HistoryData, AffixData_value), col_len[20], col_type[20]);
 98 
 99 }
100 
101 LPBYTE CHistoryDataOLEDB::FastQueryData(CStringW strSql,ULONG nColNum, int* nLen, int* nCount)
102 {
103     HRESULT hr = E_FAIL;
104     HistoryData rmd;            //历史数据
105     HROW* rghRows = NULL;
106     LONG cRows = 100;        //一次读取10行
107     ULONG uRowsObtained = 0;    //实际读取的条数
108 
109     IRowset *pIRowset = NULL;
110     IAccessor* pIAccessor = NULL;
111     HACCESSOR hAccessor = DB_NULL_HACCESSOR;
112     if (S_OK != (hr = CNativeClientOLEDB::FastQueryData(strSql, nColNum, &pIRowset, &pIAccessor, hAccessor))){
113         return NULL;
114     }
115     LPBYTE lpData = new BYTE[10 * 1024 * 1024];
116     ZeroMemory(lpData, 10 * 1024 * 1024);
117     int nDataLen = 0;
118     int nDataCount = 0;
119     while (FALSE == FAILED(hr = pIRowset->GetNextRows( DB_NULL_HCHAPTER, 0, cRows, &uRowsObtained, &rghRows)))
120     {
121         int j = 0;
122         if (cRows != uRowsObtained){
123             printf("cRows != uRowsObtained.\n");
124         }
125 
126         for (int i = 0; i < uRowsObtained; ++i){
127             if (FAILED( hr = pIRowset->GetData(rghRows[i], hAccessor, &rmd )))
128             {
129                 printf("获取数据失败 %d .\r\n", i);
130                 break;
131                 //std::cout << "获取数据失败." << std::endl;
132             }
133 
134             if (rmd.AffixData_status != 0 || rmd.Alldis_status != 0 || rmd.Direction_status != 0 || rmd.Gpstime_status != 0 ||
135                 rmd.Height_status != 0 || rmd.InStatus_status != 0 || rmd.Latitude_status != 0 || rmd.Latitude_status != 0 || rmd.Lock_status != 0 ||
136                 rmd.Oil_status != 0 || rmd.Speed_status != 0 || rmd.Direction_status != 0 ||
137                 rmd.Resver2_status != 0 || rmd.Smaid1_status != 0 || rmd.Smaid2_status != 0 || rmd.Temperature_status != 0 || 
138                 rmd.WarnFlag_status != 0){
139                     ASSERT(0);
140                     break;
141             }
142 
143             HistoryGPSData gpsdata;
144             memset(&gpsdata, 0, sizeof(HistoryGPSData));
145             gpsdata.longitude = rmd.Longitude_value;
146             gpsdata.latitude = rmd.Latitude_value;
147             gpsdata.speed = rmd.Speed_value;
148             gpsdata.direction = rmd.Direction_value;
149             gpsdata.gpsHeight = rmd.Height_value;
150             gpsdata.byLock = rmd.Lock_value;
151             //TRACE("%s\n", rmd.Gpstime_value);
152             SYSTEMTIME sysGps;
153             sscanf(rmd.Gpstime_value, "%d-%d-%d %d:%d:%d", &sysGps.wYear, &sysGps.wMonth, &sysGps.wDay, &sysGps.wHour, &sysGps.wMinute, &sysGps.wSecond);
154             gpsdata.gpstm[0] = sysGps.wYear - 2000;
155             gpsdata.gpstm[1] = sysGps.wMonth;
156             gpsdata.gpstm[2] = sysGps.wDay;
157             gpsdata.gpstm[3] = sysGps.wHour;
158             gpsdata.gpstm[4] = sysGps.wMinute;
159             gpsdata.gpstm[5] = sysGps.wSecond;
160             int nInStatus = rmd.InStatus_value;
161             gpsdata.InStatus1 = nInStatus & 0xFF;
162             gpsdata.InStatus2 = (nInStatus >> 8) & 0xFF;
163             int nTmlStatus = rmd.TmlStatus_value;
164             gpsdata.TmlStatus1 = nTmlStatus & 0xFF;
165             gpsdata.TmlStatus2 = (nTmlStatus >> 8) & 0xFF;
166             int nWarnFlag = rmd.WarnFlag_value;
167             gpsdata.WarnFlag1 = nWarnFlag & 0xFF;
168             gpsdata.WarnFlag2 = (nWarnFlag >> 8) & 0xFF;
169             int resver2 = rmd.Resver2_value;
170             gpsdata.extflag = resver2 &0xFF;
171             gpsdata.netid = (resver2 >> 8) & 0xFF;
172             gpsdata.alldis = rmd.Alldis_value;
173             int nTemperatue = rmd.Temperature_value;
174             memcpy(gpsdata.temperture, &nTemperatue, 4);
175             gpsdata.oil = rmd.Oil_value;
176             gpsdata.cellid = rmd.Smaid2_value;
177             gpsdata.lacid = rmd.Smaid1_value;
178             CString strAffixData = rmd.AffixData_value;
179 
180             gpsdata.longitude = htonl(gpsdata.longitude);
181             gpsdata.latitude = htonl(gpsdata.latitude);
182             gpsdata.speed = htons(gpsdata.speed);
183             gpsdata.direction = htons(gpsdata.direction);
184             gpsdata.gpsHeight = htons(gpsdata.gpsHeight);
185             gpsdata.alldis = htonl(gpsdata.alldis);
186             gpsdata.oil = htons(gpsdata.oil);
187             gpsdata.cellid = htons(gpsdata.cellid);
188             gpsdata.lacid = htons(gpsdata.lacid);
189 
190             nDataCount++;
191             memcpy(lpData + nDataLen, &gpsdata, sizeof(HistoryGPSData));
192             nDataLen += sizeof(HistoryGPSData);
193 
194             //写入扩展数据
195             if(!strAffixData.IsEmpty())
196             {
197                 BYTE bAffixData[4096] = {0};
198                 int nSrcLen = strAffixData.GetLength();
199                 LPBYTE pSrcData = (LPBYTE)strAffixData.GetBuffer();
200                 int nAffixLen = base64_decode(bAffixData, pSrcData, nSrcLen);
201                 strAffixData.ReleaseBuffer();
202                 if(nAffixLen > 2)
203                 {
204                     int nTmpLen = nAffixLen - 2;
205                     bAffixData[0] = (nTmpLen >> 8) & 0xFF;
206                     bAffixData[1] = nTmpLen & 0xFF;
207                     memcpy(lpData + nDataLen, bAffixData, nAffixLen);
208                     nDataLen += nAffixLen;
209                 }
210             }
211         }
212 
213         if (uRowsObtained){
214             pIRowset->ReleaseRows(uRowsObtained, rghRows, NULL, NULL, NULL);
215         }
216         //释放
217         CoTaskMemFree(rghRows);
218         rghRows = NULL;
219 
220         if (hr == DB_S_ENDOFROWSET){
221             printf("数据已经全部读取完成共计:%d 条.", nDataCount);
222             break;
223         }
224     }
225 
226     //释放 IRowSet 和 IAccessor
227     ReleaseRowsetAndIAccessor(&pIRowset, &pIAccessor, hAccessor);
228 
229     if (0 == nDataLen){
230         delete[] lpData;
231         lpData = NULL;
232         nDataCount = 0;
233         nDataLen = 0;
234     }
235     *nLen = nDataLen;
236     *nCount = nDataCount;
237     return lpData;
238 }
239 
240 BOOL CHistoryDataOLEDB::FastInsertData(const CStringW strTable, HistoryData* const pData, const int nDataNum)
241 {
242     if (NULL == pData || 0 == nDataNum || strTable.IsEmpty()){
243         return TRUE;
244     }
245 
246     HRESULT hr = NOERROR;
247     IRowsetFastLoad * pIRowsetFastLoad = NULL;
248     IAccessor* pIAccessor = NULL;
249     HACCESSOR hAccessor = 0;
250     ULONG nColNum = 21;
251     if (S_OK != (hr = CNativeClientOLEDB::FastInsertData(strTable, nColNum, &pIRowsetFastLoad, &pIAccessor, hAccessor))){
252         return FALSE;
253     }
254     BOOL bRet = TRUE;
255     for (int i = 0; i < nDataNum; i++){
256         if (FAILED(hr = pIRowsetFastLoad->InsertRow(hAccessor, &pData[i])))
257         {
258 
259             DumpErrorInfo(pIRowsetFastLoad, IID_ISQLServerErrorInfo);
260             bRet = TRUE;
261             break;
262         }
263     }
264     //全部插入之后,提交一次
265     if (FAILED(hr = pIRowsetFastLoad->Commit(TRUE)))
266     {
267         DumpErrorInfo(pIRowsetFastLoad, IID_ISQLServerErrorInfo);
268         bRet = TRUE;
269         printf("Error on IRFL::Commit\n");
270     }
271     
272     ReleaseRowsetFastLoadAndIAccessor(&pIRowsetFastLoad, &pIAccessor, hAccessor);
273     if (bRet){
274         return FALSE;
275     } else {
276         return TRUE;
277     }
278 }

View Code

这个类继承于前面的类,用于对历史数据表的读取与插入. 以下我简单地说明一下:

1. 重写SetColLen() 来设置每列数据的长度.用于绑定

2. 重写SetColType() 来设置每列数据的类别.用于绑定

3. 重写 set_bindings() 来进行绑定.

4. FastQueryData 函数是快速查询数据的接口.

调用父类的FastQueryData来取得数据.利用GetNextRows从IRowset中取得数据.

5. 释放 IRowSet 和 IAccessor

6. 数据都是以数据流的方式查询返回和插入.

到此为止,基本OLEDB 操作算是结束了.

后续. 我写了个数据池来存储数据源的多个连接.其中也利用了信号量来保证了多线程同步的问题.

//CNativeClientOLEDBPool.h

  1 #pragma once
  2 
  3 #include <list>
  4 using namespace std;
  5 
  6 template <class T>
  7 class CNativeClientOLEDBPool
  8 {
  9 public:
 10     CNativeClientOLEDBPool(void){
 11         m_hSemaphore = NULL;
 12         m_nMaxCount = 1;
 13     }
 14     ~CNativeClientOLEDBPool(void){
 15         Clear();
 16     }
 17     //static CNativeClientOLEDBPool* Instance() { return &m_Instance; }
 18 
 19     //nCount连接数
 20     HRESULT Init(CStringW strDataSource, CStringW strUserID, CStringW strPassWD, CStringW strCataLOG, int nCount = 1){
 21         HRESULT hr = S_OK;
 22         ASSERT(nCount >= 1);
 23         m_hSemaphore = ::CreateSemaphore(NULL, nCount, 0x7FFFFFFF, NULL);
 24 
 25         m_cs.Lock();
 26         m_nMaxCount = nCount;
 27         m_strDataSource = strDataSource;
 28         m_strUserID = strUserID;
 29         m_strPassWD = strPassWD;
 30         m_strCataLOG = strCataLOG;
 31 
 32         for(int i=0; i<m_nMaxCount; i++){
 33             T* pConn = new T;
 34             if (S_OK == (hr = pConn->Init(m_strDataSource, m_strUserID, m_strPassWD, m_strCataLOG))){
 35                 m_pConnList.push_back(pConn);
 36             } else {
 37                 delete pConn;
 38                 break;
 39             }
 40         }
 41         if(m_pConnList.size() != m_nMaxCount){
 42             ASSERT(0);
 43         } 
 44         m_cs.Unlock();
 45         return hr;
 46     }
 47 
 48     void Clear(){
 49         if(m_hSemaphore)
 50         {
 51             ::CloseHandle(m_hSemaphore);
 52             m_hSemaphore = NULL;
 53         }
 54 
 55         m_cs.Lock();
 56 
 57         m_nMaxCount = 0;
 58         list<T*>::iterator it = m_pConnList.begin();
 59         for(; it != m_pConnList.end(); it++)
 60         {
 61             T* pConn = *it;
 62             if(pConn)
 63             {
 64                 pConn->UnInit();
 65                 delete pConn;
 66             }
 67         }
 68         m_pConnList.clear();
 69 
 70         m_cs.Unlock();
 71     }
 72 
 73     T* GetConnection(){
 74         T* pConn = NULL;
 75         if(::WaitForSingleObject(m_hSemaphore, INFINITE) != WAIT_OBJECT_0)
 76             return pConn;
 77 
 78         m_cs.Lock();
 79         if(!m_pConnList.empty())
 80         {
 81             pConn = m_pConnList.front();
 82             m_pConnList.pop_front();
 83         }
 84         m_cs.Unlock();
 85 
 86         if(pConn == NULL)
 87         {
 88             //create new database connection
 89             pConn = new T();
 90             HRESULT hr = NOERROR;
 91             if(S_OK != (hr = pConn->Init(m_strDataSource, m_strUserID, m_strPassWD, m_strCataLOG))) {
 92                 delete pConn;
 93                 pConn = NULL;
 94             }
 95         }
 96         return pConn;
 97     }
 98     void FreeConnection(T* pConn){
 99         ASSERT(pConn != NULL);
100 
101         BOOL bSemaphore = FALSE;
102 
103         m_cs.Lock();
104         if(m_pConnList.size() >= m_nMaxCount)
105         {
106             pConn->UnInit();
107             delete pConn;
108         }
109         else
110         {
111             m_pConnList.push_back(pConn);
112             bSemaphore = TRUE;
113         }
114         m_cs.Unlock();
115 
116         if(bSemaphore)
117         {
118             ::ReleaseSemaphore(m_hSemaphore, 1, NULL);
119         }
120     }
121 
122 public:
123     BOOL ReOpenConnection(T* pConn){
124         return FALSE;
125     }
126 
127     BOOL VerifyConnection(T* pConn){
128         return TRUE;
129     }
130 
131 protected:
132     int m_nMaxCount;
133     CStringW m_strDataSource;
134     CStringW m_strUserID;
135     CStringW m_strPassWD;
136     CStringW m_strCataLOG;
137     CCriticalSection m_cs;
138     list<T*> m_pConnList;
139 
140     HANDLE m_hSemaphore;
141     //static CNativeClientOLEDBPool m_Instance;
142 };
143 
144  
145 
146  
147 
148 #pragma once
149 
150 #include <list>
151 using namespace std;
152 
153 template <class T>
154 class CNativeClientOLEDBPool
155 {
156 public:
157     CNativeClientOLEDBPool(void){
158         m_hSemaphore = NULL;
159         m_nMaxCount = 1;
160     }
161     ~CNativeClientOLEDBPool(void){
162         Clear();
163     }
164     //static CNativeClientOLEDBPool* Instance() { return &m_Instance; }
165 
166     //nCount连接数
167     HRESULT Init(CStringW strDataSource, CStringW strUserID, CStringW strPassWD, CStringW strCataLOG, int nCount = 1){
168         HRESULT hr = S_OK;
169         ASSERT(nCount >= 1);
170         m_hSemaphore = ::CreateSemaphore(NULL, nCount, 0x7FFFFFFF, NULL);
171 
172         m_cs.Lock();
173         m_nMaxCount = nCount;
174         m_strDataSource = strDataSource;
175         m_strUserID = strUserID;
176         m_strPassWD = strPassWD;
177         m_strCataLOG = strCataLOG;
178 
179         for(int i=0; i<m_nMaxCount; i++){
180             T* pConn = new T;
181             if (S_OK == (hr = pConn->Init(m_strDataSource, m_strUserID, m_strPassWD, m_strCataLOG))){
182                 m_pConnList.push_back(pConn);
183             } else {
184                 delete pConn;
185                 break;
186             }
187         }
188         if(m_pConnList.size() != m_nMaxCount){
189             ASSERT(0);
190         } 
191         m_cs.Unlock();
192         return hr;
193     }
194 
195     void Clear(){
196         if(m_hSemaphore)
197         {
198             ::CloseHandle(m_hSemaphore);
199             m_hSemaphore = NULL;
200         }
201 
202         m_cs.Lock();
203 
204         m_nMaxCount = 0;
205         list<T*>::iterator it = m_pConnList.begin();
206         for(; it != m_pConnList.end(); it++)
207         {
208             T* pConn = *it;
209             if(pConn)
210             {
211                 pConn->UnInit();
212                 delete pConn;
213             }
214         }
215         m_pConnList.clear();
216 
217         m_cs.Unlock();
218     }
219 
220     T* GetConnection(){
221         T* pConn = NULL;
222         if(::WaitForSingleObject(m_hSemaphore, INFINITE) != WAIT_OBJECT_0)
223             return pConn;
224 
225         m_cs.Lock();
226         if(!m_pConnList.empty())
227         {
228             pConn = m_pConnList.front();
229             m_pConnList.pop_front();
230         }
231         m_cs.Unlock();
232 
233         if(pConn == NULL)
234         {
235             //create new database connection
236             pConn = new T();
237             HRESULT hr = NOERROR;
238             if(S_OK != (hr = pConn->Init(m_strDataSource, m_strUserID, m_strPassWD, m_strCataLOG))) {
239                 delete pConn;
240                 pConn = NULL;
241             }
242         }
243         return pConn;
244     }
245     void FreeConnection(T* pConn){
246         ASSERT(pConn != NULL);
247 
248         BOOL bSemaphore = FALSE;
249 
250         m_cs.Lock();
251         if(m_pConnList.size() >= m_nMaxCount)
252         {
253             pConn->UnInit();
254             delete pConn;
255         }
256         else
257         {
258             m_pConnList.push_back(pConn);
259             bSemaphore = TRUE;
260         }
261         m_cs.Unlock();
262 
263         if(bSemaphore)
264         {
265             ::ReleaseSemaphore(m_hSemaphore, 1, NULL);
266         }
267     }
268 
269 public:
270     BOOL ReOpenConnection(T* pConn){
271         return FALSE;
272     }
273 
274     BOOL VerifyConnection(T* pConn){
275         return TRUE;
276     }
277 
278 protected:
279     int m_nMaxCount;
280     CStringW m_strDataSource;
281     CStringW m_strUserID;
282     CStringW m_strPassWD;
283     CStringW m_strCataLOG;
284     CCriticalSection m_cs;
285     list<T*> m_pConnList;
286 
287     HANDLE m_hSemaphore;
288     //static CNativeClientOLEDBPool m_Instance;
289 };

View Code

CNativeClientOLEDBPool.cpp 为空

查询数据的时候,就把会话创建出来,执行语句之后(可多次),释放会话.

pDB->CreateSessionAndCommand();
DWORD dwTick = ::GetTickCount();
m_lpData = pDB->FastQueryData(strSql, 17, &m_nDataLen, &m_nCount);
DWORD dwTick2 = ::GetTickCount();
pDB->ReleaseSessionAndCommand();

实测: 确实比ADO的要快三倍以上.虽然比ADO访问上复杂一点.但你要懂了,也觉得很简单.

以上的封装只是针对某张表需要进行高速访问和插入来的.

查询的话.每张表都要写相对应的数据绑定. 当然你也可以在外层调用,自行绑定.哈哈~

后续:

后来我也进行了ODBC API的数据访问测试,效率跟OLEDB相差不多.

需要代码的话,我也可以贴出来.

目前微软已经不对OLEDB进行艮新了.但7年之内还是保持支持.

后续都会采用ODBC来访问数据库.

参考资料:

微软官方MSDN:https://msdn.microsoft.com/zh-cn/library/ms131687(v=sql.120).aspx

frank_liuxing博客:http://blog.csdn.net/frank_liuxing/article/details/43231233

我淋着雨博客:http://www.cnblogs.com/smartstone/archive/2006/04/23/383002.html

感谢这两位大牛.

posted on 2016-06-01 16:22 Sam.Richard 阅读(2637) 评论(2) 编辑收藏举报

刷新页面返回顶部

利用Native Client OLEDB 11 高效率地对SQL SERVER 进行查询和插入操作

导航

公告