好用的 C++ 数据库操作 Ado封装类库

//libSqlAdo.h



#pragma once
#import "C:\\Program Files\\Common Files\\system\\ado\\msado15.dll" no_namespace rename("EOF","EndOfFile") rename("BOF","BeginOfFile")

class SQLCommand;
class SQLRecordset;
class SQLConnection;

#include <memory>
#include <vector>

typedef std::shared_ptr<SQLCommand> SQLCommandPtr;
typedef std::shared_ptr<SQLRecordset> SQLRecordsetPtr;

#define TIMEOUT_SECOND  10

class SQLRecordset
{
	friend class SQLCommand;
	_RecordsetPtr m_pRecordset;
	SQLRecordset(_RecordsetPtr pRecordset);
	
public:
	virtual ~SQLRecordset();

	bool IsEOF();
	bool MoveNext();

	virtual int GetFieldCount() const;
	virtual CString GetFieldName(int nIndex) const;

	virtual int GetInt(const CString& strFieldName) const;
	virtual unsigned int GetUint(const CString& strFieldName) const;

	virtual __int64 GetInt64(const CString& strFieldName) const;
	virtual unsigned __int64 GetUint64(const CString& strFieldName) const;

	virtual bool GetBool(const CString& strFieldName) const;
	virtual float GetFloat(const CString& strFieldName) const;
	virtual double GetDouble(const CString& strFieldName) const;
	virtual CString GetString(const CString& strFieldName) const;

	typedef std::vector<unsigned char> SQLBinaryVector;
	virtual bool GetBinary(const CString& strFieldName, SQLBinaryVector& vData) const;

protected:
	virtual _variant_t GetCollect(int nIndex) const;
	virtual _variant_t GetCollect(const CString& strFieldName) const;
};

class SQLCommand
{
	friend class SQLConnection;
	_CommandPtr m_pCommand;
	SQLCommand(_ConnectionPtr pConnection, DWORD dwTimtoutSeconds=TIMEOUT_SECOND);
	
public:
	virtual ~SQLCommand();
	virtual SQLRecordsetPtr Execute(const CString& strSql);
};

class SQLConnection
{
	friend class SQLCommand;
	_ConnectionPtr m_pConnection;
	static UINT s_nConnectionCount;
public:
	SQLConnection();
	virtual ~SQLConnection();

	virtual bool Open(const CString& strProvider, const CString& strDataSource,
		const CString& strDataBase, const CString& strUser, const CString& strPassword, DWORD dwTimeoutSeconds=TIMEOUT_SECOND);

	virtual bool Close();

	SQLCommandPtr GetCommand(DWORD dwTimeoutSeconds=TIMEOUT_SECOND);
};









//libSqlAdo.cpp

#include "stdafx.h"
#include "AdoSql.h"

static _bstr_t TStringToBString(const CString& str)
{
	CStringA strTemp;

#ifdef UNICODE
	USES_CONVERSION;
	strTemp = W2A(str);
#else
	strTemp = str;
#endif

	return _com_util::ConvertStringToBSTR(strTemp.GetString());
}

static CString BStringToTString(const _bstr_t& str)
{
	CString strRet;
	CStringA strTemp = _com_util::ConvertBSTRToString(str);

#ifdef UNICODE
	USES_CONVERSION;
	strRet = A2W(str);
#else
	strRet = strTemp;
#endif

	return strRet;
}

//
//SQLRecordset
//
SQLRecordset::SQLRecordset(_RecordsetPtr pRecordset)
	: m_pRecordset(pRecordset)
{
}

SQLRecordset::~SQLRecordset()
{
	try
	{
		if (m_pRecordset)
		{
			if(adStateOpen == m_pRecordset->GetState()) 
			{
				m_pRecordset->Close();
				m_pRecordset = nullptr;
			}
		}
	}
	catch(_com_error err)
	{
		LPCTSTR lpErr = err.ErrorMessage();
		int ii = 0;
	}
}

bool SQLRecordset::IsEOF()
{
	if (m_pRecordset)
	{
		return m_pRecordset->EndOfFile ? true : false;
	}

	return true;
}

bool SQLRecordset::MoveNext()
{
	if (m_pRecordset)
	{
		try
		{
			HRESULT hr = m_pRecordset->MoveNext();
			return SUCCEEDED(hr);
		}
		catch(_com_error err)
		{
		}
	}

	return false;
}

int SQLRecordset::GetFieldCount() const
{
	if (m_pRecordset)
	{
		return (int)m_pRecordset->GetFields()->GetCount();
	}

	return 0;
}

CString SQLRecordset::GetFieldName(int nIndex) const
{
	if (m_pRecordset)
	{
		FieldsPtr pFields = m_pRecordset->GetFields();

		if (pFields)
		{
			int nFieldCount = pFields->GetCount();

			if (nIndex < nFieldCount)
			{
				FieldPtr pField = pFields->GetItem(variant_t(long(nIndex)));

				if (pField)
				{
					if (char* pszName = _com_util::ConvertBSTRToString(pField->GetName()))
					{
						CString strName(pszName);
						delete[] pszName;
						return strName;
					}
				}
			}
		}
	}

	return _T("");
}

_variant_t SQLRecordset::GetCollect(int nIndex) const
{
	return m_pRecordset->GetCollect(_variant_t(nIndex));
}

_variant_t SQLRecordset::GetCollect(const CString& strFieldName) const
{
	return m_pRecordset->GetCollect(_variant_t(strFieldName));
}

int SQLRecordset::GetInt(const CString& strFieldName) const
{
	return GetCollect(strFieldName).intVal;
}

unsigned int SQLRecordset::GetUint(const CString& strFieldName) const
{
	return GetCollect(strFieldName).uintVal;
}


__int64 SQLRecordset::GetInt64(const CString& strFieldName) const
{
	return (unsigned __int64)GetCollect(strFieldName).llVal;
}

unsigned __int64 SQLRecordset::GetUint64(const CString& strFieldName) const
{
	return (unsigned __int64)GetCollect(strFieldName).ullVal;
}


bool SQLRecordset::GetBool(const CString& strFieldName) const
{
	return GetCollect(strFieldName).boolVal ? true : false;
}

float SQLRecordset::GetFloat(const CString& strFieldName) const
{
	return GetCollect(strFieldName).fltVal;
}

double SQLRecordset::GetDouble(const CString& strFieldName) const
{
	return GetCollect(strFieldName).dblVal;
}

CString SQLRecordset::GetString(const CString& strFieldName) const
{
	return BStringToTString(GetCollect(strFieldName).bstrVal);
}

bool SQLRecordset::GetBinary(const CString& strFieldName, SQLBinaryVector& vData) const
{
	_variant_t vtValue = GetCollect(strFieldName);

	if (vtValue.vt == 0x2011)
	{
		if (vtValue.parray)
		{
			unsigned long nDataLen = vtValue.parray->rgsabound[0].cElements;

			if (nDataLen > 0)
			{
				unsigned char* pData = (unsigned char*)vtValue.parray->pvData;
				vData.assign(pData, pData + nDataLen);
			}

			return true;
		}
	}
	else
	{
		ASSERT(false);
	}

	return false;
}


//
//SQLCommand
//

SQLCommand::SQLCommand(_ConnectionPtr pConnection, DWORD dwTimtoutSeconds)
{
	try
	{
		m_pCommand.CreateInstance(__uuidof(Command));
		m_pCommand->ActiveConnection = pConnection;
		m_pCommand->CommandTimeout = dwTimtoutSeconds;
	}
	catch(...)
	{
		ASSERT(0);
	}

}

SQLCommand::~SQLCommand()
{
}

SQLRecordsetPtr SQLCommand::Execute(const CString& strSql)
{
	try
	{
		if (m_pCommand)
		{
			_variant_t RecordsAffected;
			m_pCommand->CommandText = TStringToBString(strSql);
			_RecordsetPtr pRecordset = m_pCommand->Execute(NULL, NULL, adCmdText);
			return SQLRecordsetPtr(new SQLRecordset(pRecordset));
		}
	}
	catch(_com_error err)
	{
		CString str;
		str.Format(_T("Execute SQL(%s) exception(%s)"), strSql, err.ErrorMessage());
		AfxMessageBox(str);
	}

	ASSERT(0);
	return SQLRecordsetPtr(new SQLRecordset(nullptr));
}

//
//SQLConnection
//
UINT SQLConnection::s_nConnectionCount = 0;

SQLConnection::SQLConnection()
{
	m_pConnection = nullptr;

	if (1 == ::InterlockedIncrement(&s_nConnectionCount))
	{
		::CoInitialize(NULL);
	}
}

SQLConnection::~SQLConnection()
{
	if (::InterlockedDecrement(&s_nConnectionCount))
	{
		::CoUninitialize();
	}
}

bool SQLConnection::Open(const CString& strProvider, const CString& strDataSource, 
	const CString& strDataBase, const CString& strUser, const CString& strPassword, DWORD dwTimeoutSeconds)
{
	CString strConnection;
	strConnection.Format(_T("Provider=%s;Persist Security Info=true;DataSource=%s;Initial Catalog=%s;User ID=%s;Password=%s;Connect Timeout=%u")
		, strProvider, strDataSource, strDataBase, strUser, strPassword, dwTimeoutSeconds);

	try
	{
		if (m_pConnection)
		{
			Close();
		}

		HRESULT hr = m_pConnection.CreateInstance(__uuidof(Connection));

		if (SUCCEEDED(hr))
		{
			hr = m_pConnection->Open(TStringToBString(strConnection), _T(""), _T(""), adModeUnknown);

			if (FAILED(hr))
			{
				CString str;
				str.Format(_T("Connect Database failed(%s)"), strConnection);
				AfxMessageBox(str);
				return false;
			}

			return SUCCEEDED(hr);
		}
	}
	catch(_com_error err)
	{
		CString str;
		str.Format(_T("Connect Database exception(%s)"), err.ErrorMessage());
		AfxMessageBox(str);
	}

	return false;
}

bool SQLConnection::Close()
{
	if (m_pConnection)
	{
		try
		{
			if (adStateClosed != m_pConnection->GetState())
			{
				HRESULT hr = m_pConnection->Close();
				m_pConnection = nullptr;
				return SUCCEEDED(hr);
			}
			else
			{
				ASSERT(false);
				m_pConnection = nullptr;
			}
		}
		catch(_com_error err)
		{
			CString str;
			str.Format(_T("Close Database exception(%s)"), err.ErrorMessage());
			AfxMessageBox(str);
		}
	}

	return false;
}

SQLCommandPtr SQLConnection::GetCommand(DWORD dwTimeoutSeconds)
{
	return SQLCommandPtr(new SQLCommand(m_pConnection, dwTimeoutSeconds));
}