`
阿尔萨斯
  • 浏览: 4108044 次
社区版块
存档分类
最新评论

Using OLE DB Consumer Templates

 
阅读更多

Using OLE DB Consumer Templates
CComBSTR LoadUDL(const CComBSTR& udlPath)
{
CComPtr<IDataInitialize> spDataInitialize;
HRESULT hr = spDataInitialize.CoCreateInstance( __uuidof(MSDAINITIALIZE));
CComHeapPtr<OLECHAR> conString;
if (SUCCEEDED(hr))
{
hr=spDataInitialize->LoadStringFromStorage(udlPath, &conString);
if(hr!=S_OK)
{
cout<<"LoadStringFromStorage failed"<<endl;
return CComBSTR(L"");
}
return CComBSTR(conString);
}
cout<<"CoCreateInstance( __uuidof(MSDAINITIALIZE)) failed"<<endl;
return CComBSTR(L"");
}
CDataConnection con;
HRESULT hr=con.Open(conString);//conString can be generated by LoadUDL function
We can also connect to a database by using CDataSource::Open member function.Whats the difference between CDataConnection and CDataSource? CDataConnection is a useful class for creating clients because it encapsulates necessary objects (data source and session) and some of the work you need to do when connecting to a data source.

//Popup a data link property dialog to connect database
CDataSource source;
HRESULT hr=source.Open();
CComBSTR conStr;
source.GetInitializationString(&conStr,true);
CComBSTR GetDatabaseProviderName(const CDataSource& ds)
{
CComVariant var;
HRESULT hr = ds.GetProperty( DBPROPSET_DATASOURCEINFO, DBPROP_PROVIDERNAME, &var );
if(hr==S_OK)
{
return var.bstrVal;
}
return CComBSTR(L"");
}
HRESULT GetTableNames(const CDataConnection& con,vector<CComBSTR>& names)
{
if(!names.empty())
{
names.clear();
}
CTables ts;
HRESULT hr=ts.Open(con.m_session,NULL,NULL,NULL,_T("TABLE"));
if( FAILED(hr) )
{
cout<<"Unable to open the TABLES schema to get the list of available tables"<<endl;;
return hr;
}
hr = ts.MoveFirst();
while( SUCCEEDED( hr ) && hr != DB_S_ENDOFROWSET )
{
names.push_back(ts.m_szName);
hr = ts.MoveNext();
}
if(names.empty())
{
return S_FALSE;
}
else
{
return S_OK;
}
}
void GetPrimaryKeys(CDataConnection& con,vector<CString>& pkeys)
{
CPrimaryKeys key;
HRESULT hr=key.Open(con.m_session,NULL,NULL,_T("pet"));
while(S_OK==key.MoveNext())
{
pkeys.push_back(key.m_szColumnName);
}
}
HRESULT OpenByDynamicAccessor(const CDataConnection& con,const CComBSTR& sql)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
return rs.Open(con.m_session, sql, &pset );
}
HRESULT OpenByDynamicAccessor(CDataConnection& con,const CComBSTR& sql)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
//If the table has blob column,you must call this method
rs.SetBlobHandling( DBBLOBHANDLING_SKIP );
return rs.Open(con.m_session, sql, &pset );
}
Remark-----
Either DBPROP_CLIENTCURSOR or DBPROP_IRowsetScroll property is surpported,the count of columns will be equal to actual count of columns + 1.The column 0 is used for bookmark.
HRESULT GetColumnInfo(CDataConnection& con,const CComBSTR& sql)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);
b=rs.SetBlobHandling( DBBLOBHANDLING_SKIP );
HRESULT hr=rs.Open(con, sql, &pset );
if(hr!=S_OK)
{
return hr;
}
DBTYPE dbtype;
for( DBORDINAL col = 1; col <= rs.GetColumnCount(); col++ )
{
hr=rs.GetColumnType( col, &dbtype );
if( col > 1 )
printf( ", " );
printf( "%S [%d]", rs.GetColumnName( col ), dbtype );
}
DBORDINAL count;
DBCOLUMNINFO* pInfo=NULL;
OLECHAR* pbuffer=NULL;
CComPtr<IColumnsInfo> spColumnsInfo;
rs.m_spRowset->QueryInterface(&spColumnsInfo);
if (SUCCEEDED(hr))
{
hr = spColumnsInfo->GetColumnInfo(&count, &pInfo, &pbuffer);
if(hr!=S_OK)
{
return hr;
}
}
CComPtr<IMalloc> spMalloc;
hr=CoGetMalloc(1,&spMalloc);
spMalloc->Free(pInfo);
spMalloc->Free(pbuffer);
}
HRESULT GetRowsetSize(CDataConnection& con,const CComBSTR& sql,unsigned long& size)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);
b=rs.SetBlobHandling( DBBLOBHANDLING_SKIP );
HRESULT hr=rs.Open(con, sql, &pset );
if(hr!=S_OK)
{
return hr;
}
return rs.GetApproximatePosition(NULL,NULL,&size);
}
HRESULT ExecuteSQLStatement(CDataConnection& con,const CComBSTR& sql)
{
CCommand<CNoAccessor,CNoRowset> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
return rs.Open(con.m_session, sql, &pset );
}
If we want to make our db_table attribute class provide the ability of updating its fields’s values,we should pay attention to thus points:
1)Implementing the inner codes
void GetRowsetProperties(CDBPropSet* pPropSet)
{
pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_IRowsetUpdate,true,DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_CHANGE);
}
2)The codes of client
#include "stdafx.h"
#include "sender.h"
int _tmain(int argc, _TCHAR* argv[])
{
::CoInitialize(NULL);
Csender s;
s.OpenAll();
HRESULT hr=s.MoveFirst();
s.m_com_num=11;
s.SetData();
hr=s.UpdateAll();
::CoUninitialize();
return 0;
}
SetData() member function must be called.
Please refer to the below url:
CTable_1 table;
HRESULT hr=table.OpenAll();
hr=table.MoveFirst();
table.m_a=5;
table.m_dwaStatus=DBSTATUS_S_OK;
table.m_dwaLength=4;
hr=table.Insert();
Please refer to the below url:
Please see the arctile of MSDN named as Using Multiple Accessors on a Rowset
Definition----A saved placeholder value that identifies a row in a rowset and can later be used by the consumer to retrieve that particular row.
void GetBookMark(CDataConnection& con,const CComBSTR& sql)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
bool b=pset.AddProperty(DBPROP_CLIENTCURSOR,true,DBPROPOPTIONS_REQUIRED);
rs.SetBlobHandling( DBBLOBHANDLING_SKIP );
HRESULT hr=rs.Open(con.m_session, sql, &pset );
DBORDINAL count=rs.GetColumnCount();
CBookmark<> firstmark;
rs.MoveFirst();
char* pV=static_cast<char*>(rs.GetValue(1));
hr=rs.GetBookmark(&firstmark);
while(S_OK==rs.MoveNext())
{
CBookmark<> bookmark;
hr=rs.GetBookmark(&bookmark);
BYTE* p=bookmark.GetBuffer();
}
hr=rs.MoveToBookmark(firstmark);
pV=static_cast<char*>(rs.GetValue(1));
}
The bookmark retrieved through upon codes is numeric bookmark,the value is equalt to the index of current row.We can also use SetBookmark memeber function set the current bookmarks value.
void GetXMLData(CDataConnection& con,const CComBSTR& sql)
{
CCommand<CXMLAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
HRESULT hr=rs.Open(con.m_session, sql, &pset );
CStringW str;
while(S_OK==rs.MoveNext())
{
rs.GetXMLRowData(str,true);
}
}
void ConnectDatabase(LPTSTR conString)
{
CComBSTR str(L"Provider=MSDASQL.1;Persist Security Info=True;");
str+=conString;
CDataConnection con;
HRESULT hr=con.Open(str);
if(hr!=S_OK)
{
CDBErrorInfo info;
LCID lcid = ::GetUserDefaultLCID();
ULONG records;
hr=info.GetErrorRecords(con.m_source.m_spInit,__uuidof(IDBInitialize),&records);
CComBSTR errorMsg,source;
hr=info.GetAllErrorInfo(0,lcid,&errorMsg,&source);
string s("Failed connect to database: ");
s+=CW2A(errorMsg.m_str);
throw CTXDatabaseExcep(hr,s);
}
}
void ThrowDBException(const string& context)
{
CDBErrorInfo info;
LCID lcid = ::GetUserDefaultLCID();
ULONG records;
HRESULT hr=info.GetErrorRecords(m_con.m_source.m_spInit,__uuidof(IDBInitialize),&records);
if(hr!=S_OK)
{
throw std::runtime_error("CDBErrorInfo::GetErrorRecords faield");
}
CComBSTR errorMsg,source;
hr=info.GetAllErrorInfo(0,lcid,&errorMsg,&source);
if(hr!=S_OK)
{
throw std::runtime_error("CDBErrorInfo::GetAllErrorInfo faield");
}
string s=context+" : ";
s+=CW2A(errorMsg.m_str);
throw std::exception(s.c_str());
}
How can I use native client?Just setting the udl property,and using native client provider.
When I fetch 10,000 records ,and each record has 118 columns,Native Client provider is faster than OLE DB provider.That’s my test function.
DWORD PerformanceTest(CDataConnection& con,const CComBSTR& sql)
{
DWORD x=GetTickCount();
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
bool b=pset.AddProperty(DBPROP_CLIENTCURSOR,true,DBPROPOPTIONS_REQUIRED);
rs.SetBlobHandling( DBBLOBHANDLING_SKIP );
rs.Open(con.m_session, sql, &pset );
while(S_OK==rs.MoveNext())
{}
return GetTickCount()-x;
}
OLE DB provider always spend 2,000 million seconds at least,and Native Client always spend less than 1,790 million seconds.
CREATE PROCEDURE test AS
delete from carInfo2
GO
HRESULT CallStoredProcedureNoParameterNoResult(CDataConnection& con,const CComBSTR& procName)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset;
return rs.Open(con.m_session, procName, &pset );
}
CREATE PROCEDURE test2AS
select * from carInfo2
GO
HRESULT CallStoredProcedureNoParameter(CDataConnection& con,const CComBSTR& procName)
{
CCommand<CDynamicAccessor> rs;
CDBPropSet pset( DBPROPSET_ROWSET );
bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);
b=pset.AddProperty(DBPROP_CLIENTCURSOR,true,DBPROPOPTIONS_OPTIONAL);
HRESULT hr=rs.Open(con.m_session, procName, &pset );
if(S_OK==hr)
{
DBCOUNTITEM count(0);
hr=rs.GetApproximatePosition(0,0,&count);
return hr;
}
else
{
return hr;
}
}
CREATE PROCEDURE test3 @id int
AS
delete from carInfo2
where carID=@id
GO
HRESULT CallStoredProcedureNoRowset(CDataConnection& con,const int parameter)
{
CCommand<CDynamicParameterAccessor,CNoRowset> rs;
HRESULT hr = rs.Create(con.m_session,"exec test3 ?");
hr=rs.Prepare();
void* p;
rs.BindParameters(&rs.m_hParameterAccessor,rs.m_spCommand,&p);
bool r=rs.SetParam<int>(1,&parameter);
return rs.Open(NULL,NULL,0);
}
CREATE PROCEDURE test4 @id int
as
select * from carInfo2
select * from carInfo2
where carID=@id
GO
HRESULT CallStoredProcedure(CDataConnection& con,const int parameter)
{
CCommand<CDynamicParameterAccessor,CRowset> rs;
HRESULT hr = rs.Create(con.m_session,"exec test4 ?");
hr=rs.Prepare();
void* p;
rs.BindParameters(&rs.m_hParameterAccessor,rs.m_spCommand,&p);
bool r=rs.SetParam<int>(1,&parameter);
CDBPropSet pset( DBPROPSET_ROWSET );
bool b=pset.AddProperty( DBPROP_ISequentialStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_IStream, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty( DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL );
b=pset.AddProperty(DBPROP_IRowsetScroll,true,DBPROPOPTIONS_OPTIONAL);
b=pset.AddProperty(DBPROP_IRowsetUpdate,true,DBPROPOPTIONS_OPTIONAL);
b=pset.AddProperty(DBPROP_IRowsetChange, true, DBPROPOPTIONS_OPTIONAL);
hr=rs.Open(&pset);
hr=rs.MoveFirst();
DBORDINAL l=1;
int* pv=(int*)rs.GetValue(l);
return hr;
}
分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics