VC简单备忘录项目(3)--封装ADO

注意在stdafx.h中加入#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename ("EOF", "adoEOF")

/**Author:Fly*Desc:字符串操作类，待进一步扩充*/#pragma once#include <vector>#include <list>#include <string>using namespace std;namespace FlyUtil{class CFlyStringUtil{public:static CString ToCString(char* pchar);static CString ToCString(BYTE btValue);static CString ToCString(int iValue);static CString ToCString(unsigned int iValue);static CString ToCString(long lValue);static CString ToCString(unsigned long lValue);static CString ToCString(float fltValue);static CString ToCString(double dblValue);static CString ToCString(const COleDateTime& time);static CString ToCString(const _variant_t& var);static char* ToPChar(CString str);static void split(CString sourceString, CString, CStringArray&);static void split(CString sourceString, CString, vector<CString>&);static void split(string sourceString, string, vector<string>&);static void split(CString sourceString, CString, list<CString>&);static void split(string sourceString, string, list<string>&);static CString GetFileTitleFromFileName(CString);static CString GetExtendNameFromFileName(CString);};__inline char* CFlyStringUtil::ToPChar(CString str){return str.GetBuffer(0);}__inline CString CFlyStringUtil::ToCString (char* pchar){pchar[strlen(pchar)]='\0';CString str(pchar);return str;}__inline CString CFlyStringUtil::ToCString(BYTE btValue){CString strValue;strValue.Format(_T("%d"), btValue);return strValue;}__inline CString CFlyStringUtil::ToCString(int iValue){CString strValue;strValue.Format(_T("%d"), iValue);return strValue;}__inline CString CFlyStringUtil::ToCString(unsigned int iValue){CString strValue;strValue.Format(_T("%u"), iValue);return strValue;}__inline CString CFlyStringUtil::ToCString(long lValue){CString strValue;strValue.Format(_T("%d"), lValue);return strValue;}__inline CString CFlyStringUtil::ToCString(unsigned long lValue){CString strValue;strValue.Format(_T("%u"), lValue);return strValue;}__inline CString CFlyStringUtil::ToCString(float fltValue){CString strValue;strValue.Format(_T("%f"), fltValue);return strValue;}__inline CString CFlyStringUtil::ToCString(double dblValue){CString strValue;strValue.Format(_T("%f"), dblValue);return strValue;}__inline CString CFlyStringUtil::ToCString(const COleDateTime &time){CString strValue;strValue = time.Format(_T("%Y-%m-%d %H:%M:%S"));return strValue;}__inline CString CFlyStringUtil::ToCString(const _variant_t &var){CString strValue;switch (var.vt){case VT_BSTR: //字符串case VT_LPSTR:case VT_LPWSTR:strValue = (LPCTSTR)(_bstr_t)var;break;case VT_I1:   //无符号字符case VT_UI1:strValue.Format(_T("%d"), var.bVal);break;case VT_I2:   //短整型strValue.Format(_T("%d"), var.iVal);break;case VT_UI2:   //无符号短整型strValue.Format(_T("%u"), var.uiVal);break;case VT_INT: //整型strValue.Format(_T("%d"), var.intVal);break;case VT_I4:   //整型case VT_I8:   //长整型strValue.Format(_T("%d"), var.lVal);break;case VT_UINT:   //无符号整型strValue.Format(_T("%d"), var.uintVal);break;case VT_UI4:    //无符号整型case VT_UI8:    //无符号长整型strValue.Format(_T("%d"), var.ulVal);break;case VT_VOID:strValue.Format(_T("%08x"), var.byref);break;case VT_R4:   //浮点型strValue.Format(_T("%f"), var.fltVal);break;case VT_R8:   //双精度型strValue.Format(_T("%f"), var.dblVal);break;case VT_DECIMAL: //小数strValue.Format(_T("%f"), (double)var);break;case VT_CY:{COleCurrency cy = var.cyVal;strValue = cy.Format();}break;case VT_BLOB:case VT_BLOB_OBJECT:case 0x2011:strValue = _T("[BLOB]");break;case VT_BOOL:   //布尔型  strValue = var.boolVal ? _T("TRUE") : _T("FALSE");break;case VT_DATE: //日期型{DATE dt = var.date;COleDateTime da = COleDateTime(dt);strValue = da.Format(_T("%Y-%m-%d %H:%M:%S"));}break;case VT_NULL://NULL值case VT_EMPTY:   //空strValue = _T("");break;case VT_UNKNOWN:   //未知类型default:strValue = _T("VT_UNKNOW");break;}return strValue;}__inline void CFlyStringUtil::split(CString sourceString, CString pattern, CStringArray& strContainer){int iPos = sourceString.Find(pattern), iStart = 0;CString strElement;while (iPos != -1){strElement=sourceString.Mid(iStart, iPos - iStart);strContainer.Add(strElement);iStart = iPos + pattern.GetLength();iPos = sourceString.Find(pattern, iStart);}//如果最后一个元素不为空则加入最后一个元素(视最后有无pattern)CString laststrs=sourceString.Mid(iStart);if (!laststrs.IsEmpty())strContainer.Add(sourceString.Mid(iStart));}__inline void CFlyStringUtil::split(CString sourceString, CString pattern, vector<CString>& strContainer){int iPos = sourceString.Find(pattern), iStart = 0;CString strElement;while (iPos != -1){strElement=sourceString.Mid(iStart, iPos - iStart);strContainer.push_back(strElement);iStart = iPos + pattern.GetLength();iPos = sourceString.Find(pattern, iStart);}//如果最后一个元素不为空则加入最后一个元素(视最后有无pattern)CString laststrs=sourceString.Mid(iStart);if (!laststrs.IsEmpty())strContainer.push_back(sourceString.Mid(iStart));}__inline void CFlyStringUtil::split(string sourceString, string pattern, vector<string>& strContainer){int iPos = sourceString.find(pattern), iStart = 0;string strElement;while (iPos != -1){strElement=sourceString.substr(iStart, iPos - iStart);strContainer.push_back(strElement);iStart = iPos + pattern.size();iPos = sourceString.find(pattern, iStart);}//如果最后一个元素不为空则加入最后一个元素(视最后有无pattern)string laststrs=sourceString.substr(iStart);if (!laststrs.empty())strContainer.push_back(sourceString.substr(iStart));}__inline void CFlyStringUtil::split(CString sourceString, CString pattern, list<CString>& strContainer){int iPos = sourceString.Find(pattern), iStart = 0;CString strElement;while (iPos != -1){strElement=sourceString.Mid(iStart, iPos - iStart);strContainer.push_back(strElement);iStart = iPos + pattern.GetLength();iPos = sourceString.Find(pattern, iStart);}//如果最后一个元素不为空则加入最后一个元素(视最后有无pattern)CString laststrs=sourceString.Mid(iStart);if (!laststrs.IsEmpty())strContainer.push_back(sourceString.Mid(iStart));}__inline void CFlyStringUtil::split(string sourceString, string pattern, list<string>& strContainer){int iPos = sourceString.find(pattern), iStart = 0;string strElement;while (iPos != -1){strElement=sourceString.substr(iStart, iPos - iStart);strContainer.push_back(strElement);iStart = iPos + pattern.size();iPos = sourceString.find(pattern, iStart);}//如果最后一个元素不为空则加入最后一个元素(视最后有无pattern)string laststrs=sourceString.substr(iStart);if (!laststrs.empty())strContainer.push_back(sourceString.substr(iStart));}__inline CString CFlyStringUtil::GetFileTitleFromFileName(CString FileName){int pos;pos=FileName.ReverseFind('\\');if(pos==-1)        pos=FileName.ReverseFind('/');if(pos==-1)return "";CString FileTitle=FileName.Right(FileName.GetLength()-1-pos);return FileTitle;}__inline CString CFlyStringUtil::GetExtendNameFromFileName(CString FileName){int pos=FileName.ReverseFind('.');if(pos==-1)return "";    CString ExtendName=FileName.Right(FileName.GetLength()-pos-1);return ExtendName;}}// end namespace

 

/**Author:Fly*Desc:ADO连接数据库操作，待进一步扩充*/#pragma once#include "StdAfx.h"#include "FlyUtil.h"#if !defined _CATCH_ERROR_#define _CATCH_ERROR_           \catch(_com_error e) \{             \   CString strComError;       \   strComError.Format("错误编号: %08lx\n错误信息: %s\n错误源: %s\n错误描述: %s", \        e.Error(),                  \        e.ErrorMessage(),           \        (LPCSTR) e.Source(),        \        (LPCSTR) e.Description()); \   ::MessageBox(NULL,strComError,"错误",MB_ICONEXCLAMATION); \}#endifnamespace FlyADO{class WorkSheet{public:CString Name;long UsedRowsCount;long UsedColumnsCount;CString** Cells;CString** GetCells(){return Cells;}};class WorkBook{public:CString Name;WorkSheet* pWorkSheets;};//////////////////////////////////////////////////////Begin For CADO///////////////////////////////////////////////////////////////class CADO{public:CADO(void);virtual ~CADO(void);_ConnectionPtrm_pConnection;//添加一个指向Connection对象的指针_RecordsetPtrm_pRecordset;//添加一个指向Recordset对象的指针_variant_tm_RecordsAffected;//执行Execute影响的行数virtual void CreateConnection(CString dbPath,CString userName,CString passWord,CString HDR="yes",CString IMEX="0")=0;_RecordsetPtr& GetRecordSet(_bstr_t bstrSQL,CursorTypeEnum ct=adOpenDynamic);BOOL ExecuteSQL(_bstr_t bstrSQL);void CloseConnection();void CloseRecordSet();void GetFieldNames(CString DBTableName,CStringArray& FieldNameArray);};__inline CADO::CADO(void){::CoInitialize(NULL);m_pConnection=NULL;m_pRecordset=NULL;m_pConnection.CreateInstance("ADODB.Connection");m_pRecordset.CreateInstance("ADODB.Recordset");}__inline CADO::~CADO(void){CloseRecordSet();CloseConnection();m_pRecordset.Release();m_pConnection.Release();m_pConnection=NULL;m_pRecordset=NULL;::CoUninitialize();}__inline _RecordsetPtr& CADO::GetRecordSet(_bstr_t bstrSQL,CursorTypeEnum ct){try{if(m_pConnection->GetState()!=adStateOpen)  throw "m_pConnection is not init";if(m_pRecordset->GetState()==adStateOpen)m_pRecordset->Close();m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),ct,adLockOptimistic, //adLockBatchOptimistic,//adLockOptimistic,adCmdText);}_CATCH_ERROR_return m_pRecordset;}__inline BOOL CADO::ExecuteSQL(_bstr_t bstrSQL){try{//是否已连接数据库if(m_pConnection==NULL)      throw "m_pConnection is not init";m_pRecordset=m_pConnection->Execute(bstrSQL,&m_RecordsAffected,adCmdText);return true;}_CATCH_ERROR_return false;}__inline void CADO::GetFieldNames(CString DBTableName,CStringArray& FieldNameArray){_bstr_t sql="SELECT TOP 1 * FROM "+DBTableName;//AfxMessageBox(sql);GetRecordSet(sql,adOpenKeyset);int fieldCount=0;if(!m_pRecordset->adoEOF){fieldCount=m_pRecordset->GetFields()->GetCount();for(int m=0;m<fieldCount;m++){FieldNameArray.Add(m_pRecordset->GetFields()->GetItem(m)->GetName());}}}__inline void CADO::CloseConnection(){//关闭记录集和连接try{if(m_pConnection->GetState()==adStateOpen)m_pConnection->Close();}_CATCH_ERROR_}__inline void CADO::CloseRecordSet(){try{if(m_pRecordset->GetState()==adStateOpen)m_pRecordset->Close();}_CATCH_ERROR_}///////////////////////////////////////////////////////////////////End for CADO////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////Begin For CADOAccess////////////////////////////////////////////class CADOAccess:public CADO{public:CADOAccess(void){}~CADOAccess(void){}virtual void CreateConnection(CString dbPath,CString userName="",CString passWord="",CString HDR="yes",CString IMEX="0"){try{if(m_pConnection->GetState()==adStateOpen)m_pConnection->Close();_bstr_t strConnect="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+dbPath+";Persist Security Info=False;Jet OLEDB:Database Password="+passWord+";";m_pConnection->Open(strConnect,"","",adModeUnknown);}_CATCH_ERROR_}};//////////////////////////////////////////////////////////////////////End For CADOAccess/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////Begin For CADOSQLServer////////////////////////////////////////////class CADOSQLServer:public CADO{public:CADOSQLServer(void){}~CADOSQLServer(void){}virtual void CreateConnection(CString dbName,CString serverAddr="127.0.0.1",CString port="1433",CString userName="",CString passWord="",CString HDR="yes",CString IMEX="0"){try{if(m_pConnection->GetState()==adStateOpen)m_pConnection->Close();_bstr_t strConnect="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" +userName+ ";Password=" +passWord+ ";Data Source=" +serverAddr+","+port+ ";";if(!dbName.IsEmpty())strConnect=strConnect+"Initial Catalog=" +dbName+";";m_pConnection->Open(strConnect,"","",adModeUnknown);}_CATCH_ERROR_}};//////////////////////////////////////////////////////////////////////End For CADOSQLServer/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////Begin For CADOOracle////////////////////////////////////////////class CADOOracle:public CADO{public:CADOOracle(void){}~CADOOracle(void){}virtual void CreateConnection(CString dbName,CString serverAddr="127.0.0.1",CString port="1521",CString userName="",CString passWord="",CString HDR="yes",CString IMEX="0"){try{if(m_pConnection->GetState()==adStateOpen)m_pConnection->Close();_bstr_t strConnect="Provider=MSDAORA.1;Persist Security Info=False;User ID=" +userName+ ";Password=" +passWord+ ";Data Source=" +serverAddr+","+port+ ";";if(!dbName.IsEmpty())strConnect=strConnect+"Initial Catalog=" +dbName+";";m_pConnection->Open(strConnect,"","",adModeUnknown);}_CATCH_ERROR_}};//////////////////////////////////////////////////////////////////////End For CADOOracle/////////////////////////////////////////////////////////////////////////////////////////////////////////Begin For CADOExcel/////////////////////////////////////////////////////////////////////class CADOExcel:public CADO{public:CADOExcel(void){}~CADOExcel(void){}virtual void CreateConnection(CString dbPath,CString userName="",CString passWord="",CString HDR="yes",CString IMEX="0");void GetSheetNames(CStringArray& tableNames);void GetFieldNames(CString DBTableName,CStringArray& FieldNameArray);void GetWorkSheet(CString sheetName,WorkSheet& mySheet);void GetWorkSheet(int sheetIdx,WorkSheet &mySheet);void ReleaseSheet(WorkSheet& ws);};__inline void CADOExcel::CreateConnection(CString dbPath,CString userName,CString passWord,CString HDR,CString IMEX){try{CString extName=FlyUtil::CFlyStringUtil::GetExtendNameFromFileName(dbPath);_bstr_t strConnect;if(extName=="xlsx")strConnect="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+dbPath+";Extended Properties=\"Excel 12.0 Xml;HDR="+HDR+"\"";elsestrConnect="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+dbPath+";Extended Properties=\"Excel 8.0;HDR="+HDR+"\"";if(m_pConnection->GetState()==adStateOpen)m_pConnection->Close();m_pConnection->Open(strConnect,"","",adModeUnknown);}_CATCH_ERROR_}__inline void CADOExcel::GetSheetNames(CStringArray& tableNames){try{if(m_pConnection->GetState()!=adStateOpen)  throw "m_pConnection is not init";_RecordsetPtr pRstSchema = NULL;pRstSchema = m_pConnection->OpenSchema(adSchemaTables);_bstr_t table_name;//// 循环读取excel文件中的所有表的名字while(!pRstSchema->adoEOF){ table_name = pRstSchema->Fields->GetItem("TABLE_NAME")->Value; tableNames.Add((LPCSTR)table_name); pRstSchema->MoveNext();  } pRstSchema->Close(); pRstSchema.Release(); pRstSchema=NULL;}_CATCH_ERROR_}__inline void CADOExcel::GetWorkSheet(int sheetIdx,WorkSheet &mySheet){CStringArray tableNames;GetSheetNames(tableNames);CString sheetName=tableNames.GetAt(sheetIdx);GetWorkSheet(sheetName,mySheet);}__inline void CADOExcel::GetWorkSheet(CString sheetName,WorkSheet &mySheet){try{sheetName=sheetName.Find("{1}quot;)!=-1?sheetName:sheetName+"{1}quot;;GetRecordSet((_bstr_t)("SELECT * FROM ["+sheetName+"]"),adOpenKeyset);//ExecuteSQL((_bstr_t)("SELECT * FROM ["+sheetName+"]"));mySheet.UsedRowsCount=m_pRecordset->GetRecordCount();//如果用Execute得到的结果可能会是-1mySheet.UsedColumnsCount=m_pRecordset->GetFields()->GetCount();/*CString str;str.Format("rows=%d,columns=%d",mySheet.UsedRowsCount,mySheet.UsedColumnsCount);AfxMessageBox(str);*/long i=0;mySheet.Cells=new CString*[mySheet.UsedRowsCount];while(!m_pRecordset->adoEOF){mySheet.Cells[i]=new CString[mySheet.UsedColumnsCount];for(long j=0;j<mySheet.UsedColumnsCount;j++)mySheet.Cells[i][j]=FlyUtil::CFlyStringUtil::ToCString(m_pRecordset->GetCollect(j));m_pRecordset->MoveNext();i++;}}_CATCH_ERROR_}__inline void CADOExcel::GetFieldNames(CString DBTableName,CStringArray& FieldNameArray){FieldNameArray.RemoveAll();DBTableName=DBTableName.Find("{1}quot;)!=-1?DBTableName:DBTableName+"{1}quot;;_bstr_t sql="SELECT * FROM ["+DBTableName+"]";//AfxMessageBox(sql);CString tmpstr;try{//ExecuteSQL(sql);GetRecordSet(sql,adOpenKeyset);int fieldCount=0;//_variant_t var;if(!m_pRecordset->adoEOF){fieldCount=m_pRecordset->GetFields()->GetCount();//CString str;//str.Format("fieldcount:%d",fieldCount);//AfxMessageBox(str);for(long m=0;m<fieldCount;m++){//var = m_pRecordset->Fields->GetItem(m)->GetName();tmpstr=(LPCSTR)(m_pRecordset->GetFields()->GetItem(m)->GetName());FieldNameArray.Add(tmpstr);}}}_CATCH_ERROR_}__inline void CADOExcel::ReleaseSheet(WorkSheet& ws){if(ws.UsedRowsCount>0&&ws.UsedColumnsCount>0)for(int i=0;i<ws.UsedRowsCount;i++)delete[] ws.Cells[i];if(ws.UsedRowsCount>0) delete[] ws.Cells;}//////////////////////////////////////////////////////////////////End For CADOExcel////////////////////////////////////////////////}//end namespace/*Add to stdafx.h#pragma warning指令关闭警告该指令允许有选择性的修改编译器的警告消息的行为指令格式如下:#pragma warning( warning-specifier : warning-number-list [; warning-specifier : warning-number-list...]#pragma warning( push[ ,n ] )#pragma warning( pop )主要用到的警告表示有如下几个:once:只显示一次(警告/错误等)消息default:重置编译器的警告行为到默认状态1,2,3,4:四个警告级别disable:禁止指定的警告信息error:将指定的警告信息作为错误报告如果大家对上面的解释不是很理解,可以参考一下下面的例子及说明 #pragma warning( disable : 4507 34; once : 4385; error : 164 )   等价于：   #pragma warning(disable:4507 34) // 不显示4507和34号警告信息   #pragma warning(once:4385) // 4385号警告信息仅报告一次   #pragma warning(error:164) // 把164号警告信息作为一个错误。   同时这个pragma warning 也支持如下格式：   #pragma warning( push [ ,n ] )   #pragma warning( pop )   这里n代表一个警告等级(1---4)。   #pragma warning( push )保存所有警告信息的现有的警告状态。   #pragma warning( push, n)保存所有警告信息的现有的警告状态，并且把全局警告   等级设定为n。   #pragma warning( pop )向栈中弹出最后一个警告信息，在入栈和出栈之间所作的   一切改动取消。例如：   #pragma warning( push )   #pragma warning( disable : 4705 )   #pragma warning( disable : 4706 )   #pragma warning( disable : 4707 )   #pragma warning( pop )在这段代码的最后，重新保存所有的警告信息(包括4705，4706和4707)在使用标准C++进行编程的时候经常会得到很多的警告信息,而这些警告信息都是不必要的提示,所以我们可以使用#pragma warning(disable:4786)来禁止该类型的警告在vc中使用ADO的时候也会得到不必要的警告信息,这个时候我们可以通过#pragma warning(disable:4146)来消除该类型的警告信息*/