工程在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:liulei
note:使用的时候需要初始化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;
}