OLE_DB 是基于COM接口，其访问速度，效率是最高的，ADO是基于OLE_DB的，在
效率方面是在OLE_DB之下，ODBC更不用提了，（别拍砖啊！）。但是OLE_DB相关的
资料甚少，我最进认真学习了一下OLE_DB,现已封装成一个类，现公开出来，有不足
之处，高手们多多指教。
这是类的头文件：－－－－－－－－－－－－－－－－－－
#if !defined(AFX_ADODATABASE_H__CC00630B_09FF_483D_81AB_7DC19E3EA26A__INCLUDED_)
#define AFX_ADODATABASE_H__CC00630B_09FF_483D_81AB_7DC19E3EA26A__INCLUDED_
#if _MSC_VER > 1000
#pragma once
#endif
#define MAX_FIELDS 255 /*一个表中最大字段数 */
#define MAX_FIELDS_LEN 30 /*字段名字最大字节数 */
#define MAX_FIELDS_DATA_LEN 280 /*字符字段中字符的最大字节数 */
class COLEDBDatabase
{
public:
void MyGetColumnValue();
void ConvertNumericToChar();
void RollbackTran();
void CommitTran();
void StartTran();
BOOL NextMatchingRecord();
int SelectMatchingRecords(char *szWhere);
BOOL ExecuteQuery(char *szQuery);
BOOL UpdateRecord(char *szWhere);
void Quote(char* sText);
BOOL AddRecord();
BOOL ExecuteSQL(char *szSQL);
BOOL ConnectDB2(char *szServerName, char *szDatabase, char *szUid, char *szPwd);
BOOL ConnectDB1(char* szDNS,char* szUid,char* szPwd);
BOOL ConnectDB(CDBPropSet dbPropSet);
void DisconnectionDB();
CAdoDatabase();
virtual ~CAdoDatabase();
/*-----------置换用到----------------------------- */
string szOld ,szNew ;
int nPos ,nGet ;
BOOL bReplace ;
/*-------------OLE DB 操作---------------------------*/
char m_Provider[20] ; /*数据库提供着 MSDASQL SQLOLEDB */
CDataSource m_DataSource ; /*数据源句柄 */
CSession m_Session ; /*会话句柄 */
CCommand <CNoAccessor,CNoRowset> m_CmdExecute ; /*公共执行SQL句柄【注，没有行集返回】 */
HINSTANCE hInstance ; /*取得错误的句柄 */
CCommand<CDynamicAccessor,CRowset> m_CmdQuery;
/*------------------ 列信息----------------*/
DBSTATUS dbStatus ; /*用于判断该列是否为空*/
DBCOLUMNINFO *m_pColsInfo; /*列信息*/
ULONG m_nColCount; /*列总数*/
LPOLESTR m_pStrBuffer; /**/
unsigned long nColType ; /*列类型*/
LONG dbLong ; /*用于取 整形列*/
DB_NUMERIC dbNumeric ; /*用于取 Numeric列*/
char szNumeric[20] ; /*用于辅助取 Numeric*/
unsigned int nNumericLen ; /*用于取 Numeric 列*/
__int64 dbInt64 ; /*用于取 Numeric列 只用*/
/*--------------------时间、日期处理---------------------*/
DBTIME dbTime ; /*时间 20:12:20 */
DBDATE dbDate ; /*日期 2003-10-31 */
DBTIMESTAMP dbTimeStamp; /*时间戳 2003-10-31 20:12:20 */
BOOL m_bOpen ; /*是否打开 */
char m_szExecute[1024*3]; /*执行语句 */
char m_szErrMsg[1024] ; /*错误信息*/
HRESULT hr;
int ff ;
string szText1 ;
/*-------------临时绑定表--------------------*/
char m_szTableName[255] ; /*临时绑定表名称*/
int m_nFields; /*临时绑定表字段的总数*/
char m_sFieldName[MAX_FIELDS] [MAX_FIELDS_LEN]; /*临时绑定表字段名字*/
char m_sFieldValue[MAX_FIELDS][MAX_FIELDS_DATA_LEN]; /*临时绑定表字段值*/
int m_bNumeric[MAX_FIELDS] ; /*临时绑定表字段是否为数值类型 1 int 2 varchar 3 numeric 4 date */
protected:
void GetExecError();
};
#endif
以下是类的实现文件 －－－－－－－－－－－－－－－－－－－－－－－－－－
/*罗家兄弟 2004－05－12*/
#include "stdafx.h"
#include "OLEDBDatabase.h"
COLEDBDatabase::COLEDBDatabase()
{
m_bOpen=FALSE ;
szOld="'" ;
szNew ="''" ;
hInstance = NULL ;
}
COLEDBDatabase::~COLEDBDatabase()
{
}
/*断开连接 */
void COLEDBDatabase::DisconnectionDB()
{
if(m_bOpen)
{
m_CmdExecute.ReleaseCommand() ; file://必须调用 */
m_CmdQuery.ReleaseCommand() ; file://必须调用 */
m_Session.Close() ;
m_DataSource.Close() ;
m_bOpen=FALSE ;
}
if(hInstance !=NULL)
{/*释放句柄 */
::FreeLibrary(hInstance) ;
hInstance = NULL ;
}
}
/*连接数据库 */
BOOL COLEDBDatabase::ConnectDB(CDBPropSet dbPropSet)
{
if(m_bOpen)
DisconnectionDB();
hInstance = ::LoadLibrary("MSDAERR.DLL") ;
hr=m_DataSource.Open(m_Provider,&dbPropSet) ;
if(FAILED(hr))
goto end ;
hr=m_Session.Open(m_DataSource) ;
if(FAILED(hr))
goto end ;
m_bOpen = TRUE ;
return TRUE ;
end:
{
m_bOpen=FALSE ;
GetExecError() ;
return FALSE ;
}
}
/*连接数据库1
szDNS -> ODBC 的DNS
szUid -> 用户名
szPwd -> 密码
*/
BOOL COLEDBDatabase::ConnectDB1(char *szDNS, char *szUid, char *szPwd)
{
CDBPropSet dbinit(DBPROPSET_DBINIT);
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, szDNS);
dbinit.AddProperty(DBPROP_AUTH_USERID, szUid);
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, szPwd);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
strcpy(m_Provider,"MSDASQL") ;
return ConnectDB(dbinit) ;
}
/*连接数据库
说明：szServerName -> 是机器名字 或者IP地址
szUid -> 用户名
szPwd -> 密码
*/
BOOL COLEDBDatabase::ConnectDB2(char *szServerName, char *szDatabase, char *szUid, char *szPwd)
{
CDBPropSet dbinit(DBPROPSET_DBINIT);
strcpy(m_Provider,"SQLOLEDB") ;
dbinit.AddProperty(DBPROP_INIT_DATASOURCE, szServerName);
dbinit.AddProperty(DBPROP_INIT_CATALOG, szDatabase);
dbinit.AddProperty(DBPROP_AUTH_USERID, szUid);
dbinit.AddProperty(DBPROP_AUTH_PASSWORD, szPwd);
dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
return ConnectDB(dbinit) ;
}
/*执行一句SQL语句*/
BOOL COLEDBDatabase::ExecuteSQL(char *szSQL)
{
if(!m_bOpen)
return FALSE ;
hr=m_CmdExecute.Open(m_Session,szSQL) ;
if(FAILED(hr))
{
GetExecError() ;
m_CmdExecute.Close() ;
m_CmdExecute.ReleaseCommand() ;
return FALSE ;
}
m_CmdExecute.Close() ;
m_CmdExecute.ReleaseCommand() ;
return TRUE ;
}
/*增加一条记录 */
BOOL COLEDBDatabase::AddRecord()
{
if(!m_bOpen)
return FALSE ;
if(m_nFields<=0)
return FALSE ;
ff = 0 ;
sprintf(m_szExecute,"Insert into %s(",m_szTableName) ;
for(int i=0;i<m_nFields;i++)
{
if(ff>0)
strcat(m_szExecute,",") ;
strcat(m_szExecute,m_sFieldName[i]) ;
ff ++ ;
}
strcat(m_szExecute,") values(") ;
ff=0 ;
for(i=0;i<m_nFields;i++)
{
if(ff>0)
strcat(m_szExecute,",") ;
if(m_bNumeric[i]==1 || m_bNumeric[i]==3)
strcat(m_szExecute,m_sFieldValue[i]) ;
else
{
strcat(m_szExecute,"'") ;
Quote(m_sFieldValue[i]) ;
strcat(m_szExecute,m_sFieldValue[i]) ;
strcat(m_szExecute,"'") ;
}
ff ++ ;
}
strcat(m_szExecute,")") ;
return ExecuteSQL(m_szExecute) ;
}
/*置换 ' 为 '' 以防出错 建议在外部处理*/
void COLEDBDatabase::Quote(char* sText)
{
szText1=sText;
bReplace = FALSE ;
nPos = 0 ; nGet = 0 ;
while(nGet!=-1)
{
nGet = szText1.find(szOld,nPos) ;
if(nGet>0)
{
szText1.replace(nGet,szOld.length(),szNew) ;
nPos = nGet + 2 ;
bReplace = TRUE ;
}
}
if(bReplace)
strcpy(sText,szText1.c_str()) ;
}
/*修改一记录
把数据设置好，带入修改条件，执行即可
*/
BOOL COLEDBDatabase::UpdateRecord(char *szWhere)
{
if(!m_bOpen)
return FALSE ;
if(m_nFields<=0)
return FALSE ;
sprintf(m_szExecute,"Update %s Set ",m_szTableName) ;
ff=0 ;
for(int i=0;i<m_nFields;i++)
{
if(ff>0)
strcat(m_szExecute,",") ;
strcat(m_szExecute,m_sFieldName[i]) ;
strcat(m_szExecute,"=") ;
if(m_bNumeric[i]==1 || m_bNumeric[i]==3)
strcat(m_szExecute,m_sFieldValue[i]) ;
else
{
strcat(m_szExecute,"'") ;
Quote(m_sFieldValue[i]) ; /*进行保护*/
strcat(m_szExecute,m_sFieldValue[i]) ;
strcat(m_szExecute,"'") ;
}
ff ++ ;
}
strcat(m_szExecute," where ") ;
strcat(m_szExecute,szWhere) ;
return ExecuteSQL(m_szExecute) ;
}
/*执行查询*/
BOOL COLEDBDatabase::ExecuteQuery(char *szQuery)
{
if(!m_bOpen)
return FALSE ;
hr=m_CmdQuery.Open(m_Session,szQuery) ;
if(FAILED(hr))
{/*出错处理*/
GetExecError() ;
m_CmdQuery.Close() ;
return FALSE ;
}
return TRUE ;
}
/* 查找记录
参数：Where 后面的条件即可
返回值 :
1 ->有符合条件的值
0 ->没有符合条件的值
-1 ->出错
*/
int COLEDBDatabase::SelectMatchingRecords(char *szWhere)
{
if(!m_bOpen)
return FALSE ;
if(m_nFields<=0)
return FALSE ;
sprintf(m_szExecute,"Select ") ;
ff=0 ;
for(int i=0;i<m_nFields;i++)
{
if(ff>0)
strcat(m_szExecute,",") ;
strcat(m_szExecute,m_sFieldName[i]) ;
ff ++ ;
}
strcat(m_szExecute," From ") ;
strcat(m_szExecute,m_szTableName) ;
strcat(m_szExecute," Where ") ;
strcat(m_szExecute,szWhere) ;
if(!ExecuteQuery(m_szExecute))
return -1 ;
hr = m_CmdQuery.MoveFirst() ;
if(hr!=S_OK)
{/*没有记录*/
m_CmdQuery.Close() ;
return 0 ;
}else
{/*有记录*/
MyGetColumnValue() ;
}
return 1 ;
}
/*下一条记录*/
BOOL COLEDBDatabase::NextMatchingRecord()
{
hr=m_CmdQuery.MoveNext() ;
if(hr!=S_OK)
{/*移动失败*/
m_CmdQuery.Close() ;
return FALSE ;
}else
{/*移动成功*/
MyGetColumnValue() ;
}
return TRUE ;
}
void COLEDBDatabase::StartTran()
{
if(!m_bOpen)
return ;
m_Session.StartTransaction() ;
}
void COLEDBDatabase::CommitTran()
{
if(!m_bOpen)
return ;
m_Session.Commit() ;
}
void COLEDBDatabase::RollbackTran()
{
if(!m_bOpen)
return ;
m_Session.Abort() ;
}
/*取得执行错误*/
void COLEDBDatabase::GetExecError()
{
LPVOID lpMsgBuf;
if(hInstance)
{
long l = FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER | FORMAT_MESSAGE_FROM_HMODULE |
FORMAT_MESSAGE_FROM_SYSTEM, hInstance, hr, MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf, 0, NULL);
lstrcpy(m_szErrMsg, (LPTSTR)lpMsgBuf);
LocalFree( lpMsgBuf );
}
}
/*转换DB_NUMERIC 为标注格式 XXXXX.XXX */
void COLEDBDatabase::ConvertNumericToChar()
{
memcpy((char*)&dbInt64,dbNumeric.val,sizeof(__int64)) ;
memset(szNumeric,0x00,sizeof(szNumeric)) ;
_i64toa(dbInt64,szNumeric,10) ;
nNumericLen = strlen(szNumeric) ;
if(dbNumeric.scale>0)
{
memmove(szNumeric+(nNumericLen-dbNumeric.scale+1),szNumeric+(nNumericLen-dbNumeric.scale),dbNumeric.scale) ;
szNumeric[nNumericLen-dbNumeric.scale]='.' ;
}
if(dbNumeric.sign==0)
{
memmove(szNumeric+1,szNumeric,strlen(szNumeric)) ;
szNumeric[0]='-' ;
}
}
/*取得每一列的值 */
void COLEDBDatabase::MyGetColumnValue()
{
m_CmdQuery.GetColumnInfo(&m_nColCount,&m_pColsInfo,&m_pStrBuffer) ;
for(int i=0;i<m_nColCount;i++)
{
nColType = m_pColsInfo[i].wType ;
m_CmdQuery.GetStatus(i+1,&dbStatus) ;
if(dbStatus==DBSTATUS_S_ISNULL)
{/*该列为空*/
m_sFieldValue[i][0]=0x00 ;
continue ;
}
if(nColType==129)
{/*CHAR*/
strcpy(m_sFieldValue[i],(char*)m_CmdQuery.GetValue(i+1)) ;
}else if(nColType==133)
{/*DATE*/
memcpy((char*)&dbDate,(char*)m_CmdQuery.GetValue(i+1),sizeof(DBDATE)) ;
sprintf(m_sFieldValue[i],"%d-%.2d-%.2d",dbDate.year,dbDate.month,dbDate.day) ;
}else if(nColType==134)
{/*TIME*/
memcpy((char*)&dbTime,(char*)m_CmdQuery.GetValue(i+1),sizeof(DBTIME)) ;
sprintf(m_sFieldValue[i],"%d:%.2d:%.2d",dbTime.hour,dbTime.minute,dbTime.second) ;
}else if(nColType==135)
{/*TIMESTAMP */
memcpy((char*)&dbTimeStamp,(char*)m_CmdQuery.GetValue(i+1),sizeof(DBTIMESTAMP)) ;
sprintf(m_sFieldValue[i],"%d-%.2d-%.2d %.2d:%.2d:%.2d",dbTimeStamp.year,dbTimeStamp.month,dbTimeStamp.day,dbTimeStamp.hour,dbTimeStamp.minute,dbTimeStamp.second) ;
}else if((nColType>=2 && nColType<=3) || (nColType>=18 && nColType<=21))
{/*short LONG ULONG */
memcpy((char*)&dbLong,(char*)m_CmdQuery.GetValue(i+1),sizeof(LONG)) ;
sprintf(m_sFieldValue[i],"%d",dbLong);
}else if(nColType==131)
{/*DB_NUMERIC */
memcpy((char*)&dbNumeric,(char*)m_CmdQuery.GetValue(i+1),sizeof(DB_NUMERIC)) ;
ConvertNumericToChar() ; /*进行转换*/
strcpy(m_sFieldValue[i],szNumeric) ;
}else /*空值或者未知类型 */
m_sFieldValue[i][0]=0x00 ;
}
}
