?? adodatabase.cpp
字號:
/*羅家兄弟 2004－05－12*/
#include "stdafx.h"
#include "AdoDatabase.h"
int CAllFieldValue::nFieldCount=5 ;
CAdoDatabase::CAdoDatabase()
{
m_bOpen=FALSE ;
szOld="'" ;
szNew ="''" ;
hInstance = NULL ;
}
CAdoDatabase::~CAdoDatabase()
{
}
//斷開連接
void CAdoDatabase::DisconnectionDB()
{
if(m_bOpen)
{
m_CmdExecute.ReleaseCommand() ; //必須調用
m_CmdQuery.ReleaseCommand() ; //必須調用
m_Session.Close() ;
m_DataSource.Close() ;
m_bOpen=FALSE ;
}
if(hInstance !=NULL)
{//釋放句柄
::FreeLibrary(hInstance) ;
hInstance = NULL ;
}
}
//連接數據庫
BOOL CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::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 CAdoDatabase::NextMatchingRecord()
{
hr=m_CmdQuery.MoveNext() ;
if(hr!=S_OK)
{//移動失敗
m_CmdQuery.Close() ;
return FALSE ;
}else
{//移動成功
MyGetColumnValue() ;
}
return TRUE ;
}
void CAdoDatabase::StartTran()
{
if(!m_bOpen)
return ;
m_Session.StartTransaction() ;
}
void CAdoDatabase::CommitTran()
{
if(!m_bOpen)
return ;
m_Session.Commit() ;
}
void CAdoDatabase::RollbackTran()
{
if(!m_bOpen)
return ;
m_Session.Abort() ;
}
//取得執行錯誤
void CAdoDatabase::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);
// Free the buffer.
LocalFree( lpMsgBuf );
//FreeLibrary(hModule);
}
}
//轉換DB_NUMERIC 為標注格式 XXXXX.XXX
void CAdoDatabase::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 CAdoDatabase::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 ;
}
}
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -