?? adodb.cpp
字號:
// DB.cpp : 實現文件
//
#include "stdafx.h"
#include "afx.h"
#include "ADODB.h"
// CDB
IMPLEMENT_DYNCREATE(CADOConnection, CObject)
CADOConnection::CADOConnection()
{
::CoInitialize(NULL);
HRESULT hr;
try
{
hr=this->m_pConnection.CreateInstance("ADODB.Connection");
if(SUCCEEDED(hr))
{
return;
}
}
catch(_com_error e)///捕捉異常
{
CString errormessage;
errormessage.Format("連接數據庫失敗!\r\n錯誤信息:%s",e.ErrorMessage());
AfxMessageBox(errormessage);///顯示錯誤信息
}
}
CADOConnection::~CADOConnection()
{
//if (this->m_pConnection->State
if (this->IsOpen())
{
this->m_pConnection->Close();
//this->m_pConnection->Release();
this->m_pConnection=NULL;
}
::CoUninitialize();
}
bool CADOConnection::ConDB(CString DbName,CString Password,int DBType)
{
//HRESULT hr;
CString constr;
//獲取連接字符串
DbName.TrimRight();
ASSERT(DbName!="");
if (DBType==0)
{
constr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+DbName+";Persist Security Info=False;";
Password.TrimRight();
if(Password!="")
{
constr=constr+"Jet OLEDB:Database Password="+Password+";";
}
}
else
{
}
return this->Open(constr);
}
bool CADOConnection::Open(LPCTSTR lpstrConnection)
{
HRESULT hr = S_OK;
if(IsOpen())
Close();
if(strcmp(lpstrConnection, _T("")) != 0)
m_strConnection = lpstrConnection;
ASSERT(!m_strConnection.IsEmpty());
try
{
hr = m_pConnection->Open(_bstr_t(m_strConnection), "", "", NULL);
return hr == S_OK;
}
catch(_com_error &e)
{
CString errormessage;
errormessage.Format("連接數據庫失敗!\r\n錯誤信息:%s",e.ErrorMessage());
AfxMessageBox(errormessage);///顯示錯誤信息
}
return false;
}
bool CADOConnection::Execute(LPCTSTR lpstrExec)
{
ASSERT(m_pConnection != NULL);
ASSERT(strcmp(lpstrExec, _T("")) != 0);
try
{
m_pConnection->Execute(_bstr_t(lpstrExec), NULL, adExecuteNoRecords);
}
catch(_com_error &e)
{
CString errormessage;
errormessage.Format("連接數據庫失敗!\r\n錯誤信息:%s",e.ErrorMessage());
AfxMessageBox(errormessage);///顯示錯誤信息
}
return true;
}
bool CADOConnection::IsOpen()
{
if(m_pConnection )
return m_pConnection->GetState() != adStateClosed;
return false;
}
void CADOConnection::Close()
{
if(IsOpen())
m_pConnection->Close();
}
// CDB 成員函數
//CADODataset
IMPLEMENT_DYNCREATE(CADODataset, CObject)
CADODataset::CADODataset()
{
//m_pRecordset.CreateInstance("ADODB.Recordset");
m_pRecordset = NULL;
m_pCmd = NULL;
m_strQuery = _T("");
m_pRecordset.CreateInstance(__uuidof(Recordset));
m_pCmd.CreateInstance(__uuidof(Command));
m_nEditStatus = CADODataset::dbEditNone;
m_nSearchDirection = CADODataset::searchForward;
}
CADODataset::~CADODataset()
{
Close();
m_pRecordset.Release();
m_pCmd.Release();
m_pRecordset = NULL;
m_pCmd = NULL;
m_strQuery = _T("");
m_nEditStatus = dbEditNone;
}
_RecordsetPtr CADODataset::OpenSql(LPCTSTR TblName,LPCTSTR FldList,LPCTSTR Constr,LPCSTR OrderBy)
{
//ASSERT(this->m_pAdocon->m_pConnection!=NULL);
ASSERT(this->m_pConnection!=NULL);
char sqlstr[256];
strcpy(sqlstr,"select ");
strcat(sqlstr,FldList); strcat(sqlstr," from ");
strcat(sqlstr,TblName);strcat(sqlstr," ");
strcat(sqlstr,Constr);strcat(sqlstr," ");strcat(sqlstr,OrderBy);
if (IsOpen())
{
this->m_pRecordset->Close();
}
m_pRecordset->Open((_variant_t) sqlstr,
this->m_pConnection.GetInterfacePtr(),
adOpenStatic,
adLockOptimistic,
adCmdText);
return m_pRecordset;
}
bool CADODataset::ExecSql(LPCSTR SqlStr)
{
ASSERT(this->m_pConnection!=NULL);
ASSERT(this->m_pCmd!=NULL);
m_pCmd->ActiveConnection = this->m_pConnection;
m_pCmd->CommandText = _bstr_t(SqlStr);
m_pCmd->CommandType = adCmdText;
m_pCmd->Execute(NULL, NULL, adCmdText);
return true;
}
_RecordsetPtr CADODataset::ExecStoreProc(LPCSTR SqlProc)
{
ASSERT(this->m_pConnection!=NULL);
ASSERT(this->m_pCmd!=NULL);
m_pCmd->ActiveConnection = this->m_pConnection;
m_pCmd->CommandText = _bstr_t(SqlProc);
m_pCmd->CommandType = adCmdStoredProc;
this->m_pRecordset=m_pCmd->Execute(NULL, NULL, adCmdText);
return this->m_pRecordset;
}
void CADODataset::FillList(CListCtrl &ListCtrl1)
{
if(this->IsOpen())
{
ListCtrl1.SetRedraw(false);
this->ClearList(ListCtrl1);
this->FillColumn(ListCtrl1);
this->FillContent(ListCtrl1);
ListCtrl1.SetRedraw(true);
ListCtrl1.Invalidate();
}
else
{
AfxMessageBox("記錄集沒有打開！");
}
}
void CADODataset::ClearList(CListCtrl &ListCtrl1)
{
ListCtrl1.DeleteAllItems ();
int nColumnCount = ListCtrl1.GetHeaderCtrl()->GetItemCount();
// Delete all of the columns.
for (int i=0;i < nColumnCount;i++)
{
ListCtrl1.DeleteColumn (0);
}
}
void CADODataset::FillColumn(CListCtrl &ListCtrl1)
{
int fieldCount=m_pRecordset->Fields ->Count;
int fieldLength = 0;
FieldPtr m_fieldCtl;
for(int i=0;i<fieldCount;i++)
{
m_fieldCtl = m_pRecordset->Fields ->GetItem(long(i));
fieldLength = m_fieldCtl->DefinedSize*10;
if (fieldLength >100) //when field is very long then trim it
{
fieldLength = 100;
}
else if (fieldLength < 50)
{
fieldLength = 50;
}
else
{
fieldLength = 80;
}
/*
if (fieldLength < m_fieldCtl->Name.length()*12)
{
fieldLength = m_fieldCtl->Name.length()*12;
}
*/
ListCtrl1.InsertColumn(i,m_fieldCtl->Name,LVCFMT_LEFT,fieldLength);
}
}
void CADODataset::FillContent(CListCtrl &ListCtrl1)
{
int nItem = 0;
FieldPtr m_fieldCtl;
int fieldCount=m_pRecordset->Fields ->Count;
_variant_t varValue;
_bstr_t bstrValue;
while(!m_pRecordset->adoEOF)
{
m_fieldCtl = m_pRecordset->Fields ->GetItem(long(0));
varValue = m_fieldCtl->Value;
if (varValue.vt == VT_NULL)
{
bstrValue = "";
}
else if (varValue.vt==VT_BOOL)
{
if(varValue.boolVal==-1)
{bstrValue="是";}
else
{bstrValue="否";}
}
else
{
bstrValue=varValue;
}
nItem=ListCtrl1.InsertItem(0xffff,bstrValue); //fisrt value
for(int i=1;i<fieldCount;i++) //next all value
{
m_fieldCtl = m_pRecordset->Fields ->GetItem(long(i));
varValue = m_fieldCtl->Value;
if (varValue.vt == VT_NULL)
{
bstrValue = "";
}
else if (varValue.vt==VT_BOOL)
{
if(varValue.boolVal==-1)//-1 true
{bstrValue="是";}
else
{bstrValue="否";}
}
else
{
bstrValue=varValue;
}
ListCtrl1.SetItem(nItem,i,1,bstrValue,NULL,0,0,0);
}
m_pRecordset->MoveNext ();
}
}
bool CADODataset::Open(_ConnectionPtr mpdb, LPCTSTR lpstrExec, int nOption)
{
Close();
if(strcmp(lpstrExec, _T("")) != 0)
m_strQuery = lpstrExec;
ASSERT(!m_strQuery.IsEmpty());
m_strQuery.TrimLeft();
BOOL bIsSelect =(m_strQuery.Mid(0, strlen("Select ")).CompareNoCase("select ") == 0);
try
{
m_pRecordset->CursorLocation = adUseClient;
if(bIsSelect || nOption == openQuery)
m_pRecordset->Open((LPCSTR)m_strQuery, _variant_t((IDispatch*)mpdb, true),
adOpenStatic, adLockOptimistic, adCmdUnknown);
else if(nOption == openTable)
m_pRecordset->Open((LPCSTR)m_strQuery, _variant_t((IDispatch*)mpdb, true),
adOpenDynamic, adLockOptimistic, adCmdTable);
else if(nOption == openStoredProc)
{
m_pCmd->ActiveConnection = mpdb;
m_pCmd->CommandText = _bstr_t(m_strQuery);
m_pCmd->CommandType = adCmdStoredProc;
m_pRecordset = m_pCmd->Execute(NULL, NULL, adCmdText);
}
else
{
TRACE( "Unknown parameter. %d", nOption);
return false;
}
}
catch(_com_error &e)
{
dump_com_error(e);
return false;
}
return m_pRecordset != NULL;
}
bool CADODataset::Open(LPCTSTR lpstrExec, int nOption)
{
ASSERT(this->m_pConnection != NULL);
return Open(this->m_pConnection, lpstrExec, nOption);
}
bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, double& dbValue)
double val = (double)NULL;
_variant_t vtFld;
vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
if(vtFld.vt != VT_NULL)
val = vtFld.dblVal;
dbValue = val;
return true;
}
bool CADODataset::GetFieldValue(int nIndex, double& dbValue)
{
double val = (double)NULL;
_variant_t vtFld;
_variant_t vtIndex;
vtIndex.vt = VT_I2;
vtIndex.iVal = nIndex;
vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
if(vtFld.vt != VT_NULL)
val = vtFld.dblVal;
dbValue = val;
return true;
}
bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, long& lValue)
{
long val = (long)NULL;
_variant_t vtFld;
vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
if(vtFld.vt != VT_NULL)
val = vtFld.lVal;
lValue = val;
return true;
}
bool CADODataset::GetFieldValue(int nIndex, long& lValue)
{
long val = (long)NULL;
_variant_t vtFld;
_variant_t vtIndex;
vtIndex.vt = VT_I2;
vtIndex.iVal = nIndex;
vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
if(vtFld.vt != VT_NULL)
val = vtFld.lVal;
lValue = val;
return true;
}
bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, int& nValue)
{
int val = NULL;
_variant_t vtFld;
vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
switch(vtFld.vt)
{
case VT_I2:
val = vtFld.iVal;
break;
case VT_I4:
val = vtFld.iVal;
break;
case VT_BOOL:
val = vtFld.boolVal;
break;
case VT_NULL:
val=0;
break;
case VT_EMPTY:
val=0;
break;
case VT_BSTR:
try
{
val=atoi((char*)vtFld.bstrVal);
break;
}
catch( char* str )
{
AfxMessageBox(str);
return 0;
};
default:
nValue = 0;
return false;
}
nValue = val;
return true;
}
bool CADODataset::GetFieldValue(int nIndex, int& nValue)
{
int val = (int)NULL;
_variant_t vtFld;
_variant_t vtIndex;
vtIndex.vt = VT_I2;
vtIndex.iVal = nIndex;
vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
switch(vtFld.vt)
{
case VT_I2:
val = vtFld.iVal;
break;
case VT_I4:
val = vtFld.iVal;
break;
case VT_NULL:
val = 0;
break;
case VT_BOOL:
val = vtFld.boolVal;
break;
case VT_EMPTY:
val = 0;
break;
default:
return false;
}
nValue = val;
return true;
}
bool CADODataset::GetFieldValue(LPCTSTR lpFieldName, CString& strValue)
{
CString str = _T("");
_variant_t vtFld;
vtFld = m_pRecordset->Fields->GetItem(lpFieldName)->Value;
switch(vtFld.vt)
{
case VT_BSTR:
str = vtFld.bstrVal;
break;
case VT_DATE:
{
COleDateTime dt(vtFld);
str = dt.Format("%Y-%m-%d %H:%M:%S");
}
break;
case VT_EMPTY:
case VT_NULL:
str="";
break;
case VT_I1:
str.Format("%d",vtFld.intVal);
break;
case VT_I2:
str.Format("%d",vtFld.iVal);
break;
case VT_I4:
str.Format("%d",vtFld.intVal);
break;
case VT_BOOL:
if(vtFld.boolVal==-1)
{str="TRUE";}
else
{str="FALSE";}
break;
case VT_DECIMAL:
str.Format("%f",vtFld.decVal);
break;
default:
strValue.Empty();
return false;
}
strValue = str;
return true;
}
bool CADODataset::GetFieldValue(int nIndex, CString& strValue)
{
CString str = _T("");
_variant_t vtFld;
_variant_t vtIndex;
vtIndex.vt = VT_I2;
vtIndex.iVal = nIndex;
vtFld = m_pRecordset->Fields->GetItem(vtIndex)->Value;
switch(vtFld.vt)
{
case VT_BSTR:
str = vtFld.bstrVal;
break;
case VT_DATE:
{
COleDateTime dt(vtFld);
str = dt.Format("%Y-%m-%d %H:%M:%S");
}
break;
case VT_EMPTY:
str="";
break;
case VT_NULL:
str="";
break;
case VT_I1:
str.Format("%d",vtFld.intVal);
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -