ADO 操作总结
来源：互联网 发布：淘宝代运营骗局 深圳 编辑：程序博客网 时间：2024/06/06 14:02
工程在VS2013 完成
*支持基本的Sql语句
*支持存储过程（输入输出参数）
*支持事务
*支持记录集查询和遍历
*每次调用完成之后应判断返回值，如果不对，调用GetLastError查看具体错误说明
#pragma once#include <vector>#include <string>#include <atomic>#include <memory>#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")/*Ado 操作数据库 20160604 auth:liuleinote:使用的时候需要初始化com库，支持多线程安全，存储过程，事务*/#ifdef _UNICODE#define _tstring std::wstring#else#define _tstring std::string#endif//Provider=SQLOLEDB.1;Password=lladmin;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=LENOVO-PC//Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Test;Data Source=LENOVO-PC//> 仅限制一次打开一个数据库，如果打开多个需要使用多个Ado对象class CDBConMgr;/*数据库连接接口*/class IBDCon{public: virtual void AddRef() = 0; virtual void Release() = 0; virtual operator _ConnectionPtr() = 0;};/*记录集接口*/class IDBRec{public: virtual int GetFiledCout() = 0; virtual int GetRecorderCount() = 0; //> _variant_t 如果成功则vt != VT_EMPTY virtual _variant_t GetFiledValue(long nIndex) = 0; virtual _variant_t GetFiledValue(LPCTSTR szFiledName) = 0; virtual bool MoveFirst() = 0;//移动到首条记录 virtual bool MoveNext() = 0;//移动到下一条记录};/*事务接口,使用的时候应该限制作用域不能和CDBAdo对象周期相同，如果相同则需要手动Release事务对象*/class IDBTrans{public: virtual bool Begin() = 0; virtual bool Commit() = 0; virtual bool RollBack() = 0; virtual bool Exec(LPCTSTR szSql) = 0; virtual _tstring GetLastError() = 0; virtual void Release() = 0;//使用完了之后释放Connect或者限制在当前作用域{};};enum class ESqlType{ ESql_TSql,//Sql语句 ESql_StoreProc//存储过程};///> 线程安全class CDBAdo{public: CDBAdo(void); ~CDBAdo(void); //> 打开数据库,一个对象仅限打开一个数据库 //@param szConnectString 连接数据库的字符串 //@param nDefConnection 默认连接数量 //>@ret 如果成功返回TRUE 否则返回FALSE BOOL Open(__in LPCTSTR szConnectString,__in int nDefConnection = 10); //> 关闭数据库 void Close(); //> 获取错误说明 _tstring GetLastError(); //> 执行Sql //> @param szSql 需要执行的Sql语句或者存储过程 //> @param eSqlType szSql 的类型：Sql语句或者存储过程 //> @param pSqlParam 存储过程的参数，需要指定参数类型和Value //> @param nSqlParamNum 参数个数 //> @ret 返回执行之后的记录集数据 std::shared_ptr<IDBRec> Exec(__in LPCTSTR szSql,ESqlType eSqlType = ESqlType::ESql_TSql, __in _variant_t *pSqlParam = NULL, __in int nSqlParamNum = 0); //> 获取事务接口，使用完了之后需要手动调用事务的Release std::shared_ptr<IDBTrans> GetTrans();private: //> 从连接池里面取连接 std::shared_ptr<IBDCon> GetConnect();private: CRITICAL_SECTION m_csvConnect; CRITICAL_SECTION m_csStrConnnect; std::vector<CDBConMgr *> m_vConnecttion; _tstring m_strConnectString; std::atomic<_tstring> m_strErrorInfo;//错误信息};#include "StdAfx.h"#include <time.h>#include "Ado.h"///> 定义最大连接数量static const int MAX_CONNECT = 20;//////////////////////////////////////////////////////////////////////数据库连接管理/////////////////////////////////////////////////////////////////////////////////////////class CDBConMgr{public: CDBConMgr() { m_pConnect.CreateInstance(__uuidof(Connection)); ::InitializeCriticalSection(&m_cs); } ~CDBConMgr() { ///>确保该链接没有被使用才能释放 AddRef(); if (m_pConnect) { if (m_pConnect->State == adStateOpen) m_pConnect->Close(); m_pConnect.Release(); } m_pConnect = NULL; Release(); ::DeleteCriticalSection(&m_cs); } BOOL Open(LPCTSTR strConnectstring,_tstring &strerrinfo) { if (!m_pConnect) { strerrinfo = _T("Create Connect Instance Failed"); return FALSE; } try { if (SUCCEEDED(m_pConnect->Open((_bstr_t)strConnectstring, _T(""), _T(""), adModeUnknown))) { return TRUE; } } catch (...) { strerrinfo = (LPCTSTR)m_pConnect->Errors->GetItem(long(0))->Description; return FALSE; } } void AddRef() { EnterCriticalSection(&m_cs); } void Release() { LeaveCriticalSection(&m_cs); } //如果调用成功之后对象会被锁定需要调用Release BOOL IsAvailable() { if (!m_pConnect) return FALSE; return ::TryEnterCriticalSection(&m_cs); } operator _ConnectionPtr(){ return m_pConnect; } operator bool(){ return m_pConnect ? true : false; } CDBConMgr(const CDBConMgr &) = delete; CDBConMgr& operator = (const CDBConMgr&) = delete;private: _ConnectionPtr m_pConnect; CRITICAL_SECTION m_cs;};////////////////////////////////////////////////////////////////////////////数据库连接接口///////////////////////////////////////////////////////////////////////////////////////class CDBConPtr :public IBDCon{public: CDBConPtr() { m_pConnectMgr = NULL; } CDBConPtr(CDBConMgr *pConnectMgr) { m_pConnectMgr = pConnectMgr; } ~CDBConPtr(){ Release(); } operator _ConnectionPtr() { if (m_pConnectMgr == NULL) return NULL; _ConnectionPtr ptr = (*m_pConnectMgr); return ptr; } void AddRef() { if (m_pConnectMgr) m_pConnectMgr->AddRef(); } void Release() { if (m_pConnectMgr) m_pConnectMgr->Release(); }private: CDBConMgr *m_pConnectMgr;};//////////////////////////////////////////////////////////////////////////////数据库记录集操接口/////////////////////////////////////////////////////////////////////////////////class CRecordersetPtr:public IDBRec{public: CRecordersetPtr(){ m_pRecorderSet = NULL; } CRecordersetPtr(const _RecordsetPtr &pRecorderSet){ m_pRecorderSet = pRecorderSet; } ~CRecordersetPtr() { if (m_pRecorderSet) { ///> 关闭已经打开的记录集 if (m_pRecorderSet->State == adStateOpen) m_pRecorderSet->Close(); ///> 释放记录指针集引用计数 m_pRecorderSet.Release(); } } int GetFiledCout(); int GetRecorderCount(); _variant_t GetFiledValue(long nIndex); _variant_t GetFiledValue(LPCTSTR szFiledName); bool MoveFirst(); bool MoveNext();private: _RecordsetPtr m_pRecorderSet;};int CRecordersetPtr::GetFiledCout(){ try { if (!m_pRecorderSet) return 0; return m_pRecorderSet->Fields->GetCount(); } catch (...) { return 0; }}_variant_t CRecordersetPtr::GetFiledValue(long nIndex){ _variant_t vt; vt.vt = VT_EMPTY; try { if (!m_pRecorderSet) return vt; FieldPtr filed = m_pRecorderSet->Fields->GetItem(nIndex); if (!filed) return vt; return filed->GetValue(); } catch (...) { return vt; }}_variant_t CRecordersetPtr::GetFiledValue(LPCTSTR szFiledName){ _variant_t vt; vt.vt = VT_EMPTY; try { if (!m_pRecorderSet) return vt; FieldPtr filed = m_pRecorderSet->Fields->GetItem(szFiledName); if (!filed) return vt; return filed->GetValue(); } catch (...) { return vt; }}int CRecordersetPtr::GetRecorderCount(){ try { if (!m_pRecorderSet) return 0; return m_pRecorderSet->GetRecordCount(); } catch (...) { return 0; }}bool CRecordersetPtr::MoveFirst(){ try { if (!m_pRecorderSet) return false; if (m_pRecorderSet->adoEOF) return false; if (SUCCEEDED(m_pRecorderSet->MoveFirst())) return true; return false; } catch (...) { return false; }}bool CRecordersetPtr::MoveNext(){ try { if (!m_pRecorderSet) return false; if (SUCCEEDED(m_pRecorderSet->MoveNext())) { if (m_pRecorderSet->adoEOF) return false; return true; } return false; } catch (...) { return false; }}//////////////////////////////////////////////////////////////////////////////////数据库事务操接口//////////////////////////////////////////////////////////////////////////////class CDBTrans:public IDBTrans{public: CDBTrans(std::shared_ptr<IBDCon> &pConnect); ~CDBTrans(){ Release(); } virtual bool Begin(); virtual bool Commit(); virtual bool RollBack(); virtual _tstring GetLastError() { return m_strErrorInfo; }; virtual bool Exec(LPCTSTR szSql); virtual void Release();private: std::shared_ptr<IBDCon> m_pConnect; std::atomic<_tstring> m_strErrorInfo;//错误信息};CDBTrans::CDBTrans(std::shared_ptr<IBDCon> &pConnect){ m_pConnect = pConnect;}bool CDBTrans::Begin(){ if (!m_pConnect || !m_pConnect.get()) { m_strErrorInfo = _T("DB Connect Not Exist"); return false; } _ConnectionPtr conptr = (*m_pConnect); try { conptr->BeginTrans(); return true; } catch (...) { m_strErrorInfo = (LPCTSTR)conptr->Errors->GetItem(long(0))->Description; return false; }}bool CDBTrans::Commit(){ if (!m_pConnect || !m_pConnect.get()) { m_strErrorInfo = _T("DB Connect Not Exist"); return false; } _ConnectionPtr conptr = (*m_pConnect); try { conptr->CommitTrans(); return true; } catch (...) { m_strErrorInfo = (LPCTSTR)conptr->Errors->GetItem(long(0))->Description; return false; }}bool CDBTrans::RollBack(){ if (!m_pConnect || !m_pConnect.get()) { m_strErrorInfo = _T("DB Connect Not Exist"); return false; } _ConnectionPtr conptr = (*m_pConnect); try { conptr->RollbackTrans(); return true; } catch (...) { m_strErrorInfo = (LPCTSTR)conptr->Errors->GetItem(long(0))->Description; return false; }}bool CDBTrans::Exec(LPCTSTR szSql){ if (!m_pConnect || !m_pConnect.get()) { m_strErrorInfo = _T("DB Connect Not Exist"); return false; } _ConnectionPtr conptr = (*m_pConnect); try { /*_RecordsetPtr pRecord = */conptr->Execute(szSql, NULL, adCmdText); //std::shared_ptr<CRecordersetPtr> pRet(new CRecordersetPtr(pRecord)); return true; } catch (...) { m_strErrorInfo = (LPCTSTR)conptr->Errors->GetItem(long(0))->Description; return false; }}void CDBTrans::Release(){ m_pConnect = NULL;}////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////CDBAdo::CDBAdo(void){ ::InitializeCriticalSection(&m_csvConnect); ::InitializeCriticalSection(&m_csStrConnnect);}CDBAdo::~CDBAdo(void){ ::DeleteCriticalSection(&m_csvConnect); ::DeleteCriticalSection(&m_csStrConnnect);}BOOL CDBAdo::Open(LPCTSTR szConnectString,int nDefConnection){ _tstring strerrinof; try { BOOL bRet = TRUE; EnterCriticalSection(&m_csStrConnnect); if (m_strConnectString != _T("")) bRet = FALSE; else m_strConnectString = szConnectString; LeaveCriticalSection(&m_csStrConnnect); m_strErrorInfo = _T("ConnectString has been Exist"); if (!bRet) return bRet; EnterCriticalSection(&m_csvConnect); int nOldSize = m_vConnecttion.size(); if (nOldSize < nDefConnection) { m_vConnecttion.resize(nDefConnection); for (int i = nOldSize; i < nDefConnection; ++i) { m_vConnecttion[i] = new CDBConMgr; if (!(m_vConnecttion[i]->Open(szConnectString, strerrinof))) { m_strErrorInfo = strerrinof; bRet = FALSE; break; } } } if (!bRet) { for (int i = 0; i < m_vConnecttion.size(); ++i) { if (m_vConnecttion[i]) { delete m_vConnecttion[i]; m_vConnecttion[i] = NULL; } } m_vConnecttion.clear(); EnterCriticalSection(&m_csStrConnnect); m_strConnectString = _T(""); LeaveCriticalSection(&m_csStrConnnect); } LeaveCriticalSection(&m_csvConnect); return bRet; } catch (...) { m_strErrorInfo = _T("Open DB Failed"); return FALSE; }}void CDBAdo::Close(){ EnterCriticalSection(&m_csvConnect); int nSize = m_vConnecttion.size(); for (int i = 0; i < nSize;++i) { CDBConMgr *pManagerDb = m_vConnecttion[i]; delete pManagerDb; } m_vConnecttion.clear(); LeaveCriticalSection(&m_csvConnect); EnterCriticalSection(&m_csStrConnnect); m_strConnectString = _T(""); LeaveCriticalSection(&m_csStrConnnect);}_tstring CDBAdo::GetLastError(){ return m_strErrorInfo;}std::shared_ptr<IBDCon> CDBAdo::GetConnect(){ _tstring strerrinof; try { int nCurSize = 0; std::shared_ptr<IBDCon> pRet = NULL; CDBConMgr *pConnect = NULL; EnterCriticalSection(&m_csvConnect); nCurSize = m_vConnecttion.size(); for (int i = 0; i < nCurSize; ++i) { if (m_vConnecttion[i] && ((CDBConMgr*)m_vConnecttion[i])->IsAvailable()) { pConnect = m_vConnecttion[i]; break; } } LeaveCriticalSection(&m_csvConnect); if (pConnect) { pRet = std::shared_ptr<IBDCon>(new CDBConPtr(pConnect));#ifdef _DEBUG TCHAR szInfo[255] = _T(""); _stprintf(szInfo, _T("Connect addr = %d\r\n"), pConnect); OutputDebugString(szInfo);#endif return pRet; } //> 如果连接池数目小于最大连接并且当前连接没有可用的，则创建连接，否则等待 if (nCurSize < MAX_CONNECT) { EnterCriticalSection(&m_csStrConnnect); _tstring strConnctstring = m_strConnectString; LeaveCriticalSection(&m_csStrConnnect); pConnect = new CDBConMgr; if (pConnect && pConnect->Open(strConnctstring.c_str(), strerrinof)) { pConnect->AddRef(); EnterCriticalSection(&m_csvConnect); m_vConnecttion.push_back(pConnect); LeaveCriticalSection(&m_csvConnect); } else { m_strErrorInfo = strerrinof; delete pConnect; pConnect = NULL; }#ifdef _DEBUG TCHAR szInfo[255] = _T(""); _stprintf(szInfo, _T("Create Connect addr = %d\r\n"), pConnect); OutputDebugString(szInfo);#endif } else /// 随机等待一个连接 { srand((unsigned int)(time(NULL))); int nWaitIndex = rand() % nCurSize; EnterCriticalSection(&m_csvConnect); pConnect = m_vConnecttion[nWaitIndex]; ///> 一直等待，直到获取到连接为止 pConnect->AddRef(); LeaveCriticalSection(&m_csvConnect);#ifdef _DEBUG TCHAR szInfo[255] = _T(""); _stprintf(szInfo, _T("Wait Connect addr = %d\r\n"), pConnect); OutputDebugString(szInfo);#endif } if (pConnect) { pRet = std::shared_ptr<IBDCon>(new CDBConPtr(pConnect)); return pRet; } m_strErrorInfo = _T("Get DB Connect Failed"); assert(FALSE); return NULL; } catch (...) { m_strErrorInfo = _T("Get DB Connect Failed"); assert(FALSE); return NULL; }}std::shared_ptr<IDBRec> CDBAdo::Exec(__in LPCTSTR szSql, ESqlType eSqlType, __in _variant_t *pSqlParam, __in int nSqlParamNum){ ///> 创建命令对象 std::shared_ptr<IBDCon> pIConnect = GetConnect(); assert(pIConnect.get()); if (pIConnect.get() == NULL)return NULL; _ConnectionPtr prt = *pIConnect; _CommandPtr cmd; cmd.CreateInstance(__uuidof(Command)); if (!cmd) return NULL; try { //> 设置连接 cmd->ActiveConnection = prt; CursorLocationEnum eOldCursorLocation = cmd->ActiveConnection->GetCursorLocation(); //> 否则记录集获取不到个数 cmd->ActiveConnection->put_CursorLocation(adUseClient); //> 设置命令类型 CommandTypeEnum plCmdType = (eSqlType == ESqlType::ESql_StoreProc ? adCmdStoredProc : adCmdText); cmd->PutCommandType(plCmdType); //> 设置Sql语句或者存储过程名称 cmd->PutCommandText(szSql); ///> 设置参数 if (eSqlType == ESqlType::ESql_StoreProc && pSqlParam && nSqlParamNum > 0) { ///> 从数据库查询存储过程参数,由于把存储过程的返回值也当做了参数个数并且在第一个位置0，所以真正的参数从下标1开始 cmd->Parameters->Refresh(); long lParmCount = cmd->Parameters->GetCount(); ///> 设置存储过程参数,必须为long否则会崩溃 for (long i = 1; i < lParmCount && i < nSqlParamNum + 1; ++i) { //> 获取参数为输出类型,则不用赋值 //if (cmd->Parameters->GetItem(i)->GetDirection() != adParamOutput) cmd->Parameters->GetItem(i)->Value = pSqlParam[i - 1]; } } VARIANT RecordsAffected; RecordsAffected.vt = VT_INT; _RecordsetPtr pRecord = cmd->Execute(&RecordsAffected, NULL, plCmdType); ///> 执行后的参数复制给传入的参数，输出参数可用 if (eSqlType == ESqlType::ESql_StoreProc && pSqlParam && nSqlParamNum > 0) { long lParmCount = cmd->Parameters->GetCount(); ///> 设置存储过程参数,必须为long否则会崩溃 for (long i = 1; i < lParmCount && i < nSqlParamNum + 1; ++i) { pSqlParam[i - 1] = cmd->Parameters->GetItem(i)->Value; } } ///> 还原游标类型 cmd->ActiveConnection->put_CursorLocation(eOldCursorLocation); cmd.Release(); std::shared_ptr<CRecordersetPtr> pRet(new CRecordersetPtr(pRecord)); return pRet; } catch (...) { if (cmd) cmd.Release(); m_strErrorInfo = (LPCTSTR)prt->Errors->GetItem(long(0))->Description; return NULL; }}std::shared_ptr<IDBTrans> CDBAdo::GetTrans(){ std::shared_ptr<IDBTrans> pTrans(new CDBTrans(GetConnect())); return pTrans;}// testAdo.cpp : 定义控制台应用程序的入口点。//// testAdo.cpp : 定义控制台应用程序的入口点。//#include "stdafx.h"#include <thread>#include <iostream>#include "Ado.h"CDBAdo ado;void ExecSqlThread(int i){ DWORD dwTick = GetTickCount(); _variant_t sqlparam; sqlparam.vt = VT_I4; sqlparam.intVal = 0; std::shared_ptr<IDBRec> pRecorder = ado.Exec(_T("select * from TStudent;")); int n = pRecorder->GetFiledCout(); n = pRecorder->GetRecorderCount(); std::cout << "thread id = " << i << "执行完毕,耗费时间:" <<GetTickCount() - dwTick<<std::endl;}int _tmain(int argc, _TCHAR* argv[]){ ::CoInitialize(NULL); ado.Open(_T("Provider=SQLOLEDB.1;Password=lladmin;Persist Security Info=True;User ID=sa;Initial Catalog=Test;Data Source=LENOVO-PC")); std::thread thread[100]; for (int i = 0; i < 100; ++i) { thread[i] = std::thread(ExecSqlThread, i); } for (auto &it : thread) { it.join(); } //{ std::shared_ptr<IDBTrans> pTrans = ado.GetTrans(); bool bRet = true; if (pTrans->Begin()) { bRet == true ? bRet = pTrans->Exec(_T(" insert into TStudent(id,sname,nage) values('30','30','30');")):false; bRet == true ? bRet = pTrans->Exec(_T(" insert into TStudent(id,sname,nage) values('31','31','31');")) : false; if (bRet) { pTrans->Commit(); } else { pTrans->RollBack(); } } pTrans->Release(); //} ado.Close(); return 0;} 1 0
- ADO数据库操作总结
- VC++ ADO操作总结
- ADO 操作总结
- ADO.net操作数据库总结
- ado.net操作数据库总结
- ADO.net操作数据库总结
- ado.net操作数据库总结
- ado.net操作数据库总结
- ADO.net操作数据库总结
- vc使用ADO操作数据库总结
- vc使用ADO操作数据库总结 (转)
- vc使用ADO操作数据库总结
- ADO操作数据库的方法总结
- VC++ ADO、ODBC 数据库操作总结
- C#之ADO.NET操作数据库总结
- ADO.NET数据操作常见错误总结
- ADO操作
- VC+ADO下连接并操作sql2005数据库方法总结
- Windows下faster_rcnn运行问题
- Substring with Concatenation of All Words
- SQLServer2012转2008（高版本向低版本兼容）
- Nginx vs Apache
- QtQuick 技巧 3
- ADO 操作总结
- WebApi后端的List<String>前端如何发送？
- 请求数据解析完成放到数组里给cell赋值
- 关于 DrawerLayout 如何全屏显示菜单
- IEEE802.11 学习笔记
- linux fd dup 使用
- Java配置文件读取和路径设置
- windows批处理命令教程
- APP发布“扫雷”小结
