- 浏览: 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.What’s 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);
//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 bookmark’s 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,¶meter);
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,¶meter);
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;
}
相关推荐
The ATL OLE DB Provider templates only seem to support read-only rowsets and making them support updating of data isn’t as easy as you’d expect!只读数据库模板
OLEDB驱动程序大全 PostgreSQL-OleDB-Provider
OLEDB驱动程序大全 MySQL-OleDB-Provider
解决microsoft.ace.oledb.12.0连接失败所需要的驱动 完整版.zip 解决microsoft.ace.oledb.12.0连接失败所需要的驱动 完整版.zip 解决microsoft.ace.oledb.12.0连接失败所需要的驱动 完整版.zip 解决microsoft.ace....
OLE DB OLEDB ODBC ADO
OLEDB驱动程序大全 Sybase-OleDB-Provider
C#报"本地未注册Microsoft.Jet.OLEDB.4.0" 及 "未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序的解决方法
数据导入导出,OLEDB12未注册 ACE.OLEDB.12.0
SQLca.dbms= ‘OLEDB’ //接口类型 SQLca.AutoCommit = false SQLca.LogPass = logpass SQLca.LogId =logid SQLca.DBParm ="PROVIDER='SQLOLEDB', DATASOURCE='"+serverName+"'," & +"PROVIDERSTRING='Initial ...
用oledb连接数据源时,如果数据源是access的,需要access数据库引擎,系统默认是没有的,需要下载此软件安装即可。具体使用方法请参考 张立的《C#2.0 实例自学手册:通过200个例子掌握Web开发捷径》这本书中有关OLE...
Microsoft OLE DB Provider for Visual FoxPro 9.0
解决 未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序。的问题
Oracle 的OLE DB开发接口文档
C#操作Excel(OLEDB)的实例,你可以参考这个代码发挥一下,写出属于自己的作品
亲测好用,挺不错的资源,大家快来下载吧!挺有用的!需要的话可以来下载哦!Microsoft.ACE.OLEDB.12.0驱动下载,解决 未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序,权限问题
比较详细的OLE DB教程,全中文版
用于连接db2数据库的驱动,
在为SQL SERVER创建ORACLE的连接服务器时,没有ORACLE provider for OLE DB 的连接服务驱动。经网上搜索,把PDF安装说明和驱动程序一起收藏,请各位下载;
解决microsoft.ace.oledb.12.0未注册的问题,本人亲测,双击运行安装既可,确保解决你们的问题。