MFC连接ACCESS数据库心得

由于写个小程序，自己封装了一下，做了一个辅助类。和大家分享一下~

DataBaseHelper.h

#pragma once
#include <map>
#include <list>
#include "ValueObject.h"
using namespace std;
#import "C:\Program Files\Common Files\System\ADO\msado15.dll"  rename("EOF","adoEOF"), rename("BOF","adoBOF") 
using namespace ADODB;
typedef list<ValueObject*> VALUEOBJECT_LIST;
typedef list<CString> CSTRING_LIST;

class DataBaseHelper
{
public:
	virtual ~DataBaseHelper();
	static DataBaseHelper* GetInstance();
	static CString formatToSql(CString sql);
	static int CStringParseInt(CString numStr);
	static void ReleaseValueObjectList(VALUEOBJECT_LIST list);
	static void ReleaseCStringList(CSTRING_LIST list);
	ValueObject* findBySql(ValueObject* vo,CString sql);
	VALUEOBJECT_LIST findListBySql(ValueObject* vo,CString sql);
	BOOL updateBySql(CString sql);
	BOOL deleteBySql(CString sql);
	BOOL insertBySql(CString sql);
	BOOL initADOConn();
	BOOL excuteSQL(CString sql);
	void unInitADOConn();
private:
	DataBaseHelper();
	_ConnectionPtr  pConnection; 
	_RecordsetPtr   pRecordset;
	_CommandPtr     pCommand;
	HRESULT hr;
	BOOL bSuccess;
	void printError(CString msg);
	void printLog(CString msg);
};

DataBaseHelper.cpp

#include "StdAfx.h"
#include "DataBaseHelper.h"

DataBaseHelper::DataBaseHelper()
{
	pConnection = NULL;
	pRecordset = NULL;
	pCommand = NULL;
}
DataBaseHelper::~DataBaseHelper()
{
	unInitADOConn();
}
//初始化数据库连接
BOOL DataBaseHelper::initADOConn()
{
	bSuccess = TRUE;
	if(NULL == pConnection)
	{
		::CoInitializeEx(NULL,COINIT_APARTMENTTHREADED);    
		//得到当前程序路径
		CString    sPath;   
		GetModuleFileName(NULL,sPath.GetBufferSetLength(MAX_PATH+1),MAX_PATH);   
		sPath.ReleaseBuffer    ();   
		int    nPos;   
		nPos=sPath.ReverseFind('\\');   
		sPath=sPath.Left(nPos);   
		CString conStr_ = L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sPath + "\\data\\data.mdb;Jet OLEDB:Database Password=zch";
		_bstr_t conStr = conStr_;
		try
		{
			hr = pConnection.CreateInstance(__uuidof(Connection));///创建Connection实例
			if(SUCCEEDED(hr))
			{
				hr = pConnection->Open(conStr,"","",adModeUnknown);
			}
			else
			{
				bSuccess = FALSE;
			}
		}
		catch(_com_error e)
		{
			//连接失败
			bSuccess = FALSE;
			CString errormessage;
			errormessage.Format(e.Description());
			printError(errormessage);
		}
	}
	return bSuccess;
}
//反初始化数据库连接
void DataBaseHelper::unInitADOConn()
{
	if(pRecordset!=NULL)
		pRecordset->Close();
	if(pConnection!=NULL)
		pConnection->Close();
	::CoUninitialize();
}
//单例模式，获取实例
DataBaseHelper* DataBaseHelper::GetInstance()
{
	static DataBaseHelper* dbHelper = NULL;

	if(!dbHelper)
	{
		dbHelper = new DataBaseHelper();
	}
	return dbHelper;

}
//根据主键查询 返回vo
ValueObject* DataBaseHelper::findBySql(ValueObject* vo,CString sql)
{
	try
	{
		hr = pRecordset.CreateInstance(__uuidof(ADODB::Recordset));
		if(SUCCEEDED(hr)){
			excuteSQL(sql);
			_variant_t roader;
			STRING_MAP fields = vo->getFields();
			if(!pRecordset->adoEOF)
			for(STRING_MAP::iterator it = fields.begin(); it != fields.end(); it++)
			{
				roader = pRecordset->GetCollect(it->first.GetString());
				if(roader.vt != VT_NULL)
				{
					vo->setCString(it->first,roader);
				}
			}
		}
	}
	catch(_com_error e)
	{
		//连接失败
		CString errormessage;
		errormessage.Format(e.Description());
		printError(errormessage);
	}
	return vo;
}
//根据sql查询list
VALUEOBJECT_LIST DataBaseHelper::findListBySql(ValueObject* vo,CString sql)
{
	VALUEOBJECT_LIST list;
	try
	{
		hr = pRecordset.CreateInstance(__uuidof(ADODB::Recordset));
		if(SUCCEEDED(hr)){
			excuteSQL(sql);
			_variant_t roader;
			STRING_MAP fields = vo->getFields();
			while(!pRecordset->adoEOF)
			{
				ValueObject* temVo = new ValueObject(vo->getModelName());
				for(STRING_MAP::iterator it = fields.begin(); it != fields.end(); it++)
				{
					roader = pRecordset->GetCollect(it->first.GetString());
					if(roader.vt != VT_NULL)
					{
						temVo->setCString(it->first,roader);
					}
				}
				list.push_back(temVo);
				pRecordset->MoveNext();
			}
		}
	}
	catch(_com_error e)
	{
		//连接失败
		CString errormessage;
		errormessage.Format(e.Description());
		printError(errormessage);
	}
	return list;
}

BOOL DataBaseHelper::excuteSQL(CString sql)
{
	BOOL bFlag = TRUE;
	if(bSuccess)
	{
		_bstr_t sqlToExecute = sql;
		printLog(L"SQL:"+sql);
		try
		{
			pRecordset = pConnection->Execute(sqlToExecute,NULL,adCmdText);
			bFlag = TRUE;
		}
		catch(_com_error e)
		{
			//连接失败
			bFlag = FALSE;
			CString errormessage;
			errormessage.Format(e.Description());
			printError(errormessage);
		}
	}
	return bFlag;
}

//文本格式转换。
//单引号
CString DataBaseHelper::formatToSql(CString sql)
{
	sql.Replace(L"'",L"''");
	return sql;
}
//String转int
int DataBaseHelper::CStringParseInt(CString numStr)
{
	char *ch = (char*)numStr.GetBuffer(numStr.GetLength());
	int num = atoi(ch);
	numStr.ReleaseBuffer();
	return num;
}
//释放list的资源
void DataBaseHelper::ReleaseValueObjectList(VALUEOBJECT_LIST list)
{
	while (!list.empty())
	{
		ValueObject* p =list.front();
		delete p;
		list.pop_front();

	}
}
//释放list的资源
void DataBaseHelper::ReleaseCStringList(CSTRING_LIST list)
{
	while (!list.empty())
	{
		list.pop_front();

	}
}
void DataBaseHelper::printError(CString msg)
{
	TRACE1("ERROR: %s\n",msg);
}
void DataBaseHelper::printLog(CString msg)
{
	TRACE1("Log: %s\n",msg);
}

这里用到了一个值对象，类似web开发中的DTO

ValueObject.h

#pragma once
#include <map>
using namespace std;
typedef map<CString,CString> STRING_MAP;
class ValueObject
{
public:
	ValueObject(CString modelName);
	virtual ~ValueObject();

	STRING_MAP getFields();
	void setCString(CString field,CString value);
	CString getCString(CString field);
	CString getModelName();
private:
	STRING_MAP fields;
	CString modelName;
};

ValueObject.cpp

#include "StdAfx.h"
#include "ValueObject.h"

ValueObject::ValueObject(CString modelName)
{
	//以后实现
	this->modelName = modelName;
}

ValueObject::~ValueObject()
{
}

STRING_MAP ValueObject::getFields()
{
	return this->fields;
}
//往map中设置值，如果存在则覆盖field的value，不存在则insert
void ValueObject::setCString(CString field,CString value)
{
	STRING_MAP::iterator it= this->fields.find(field);
	if(it != fields.end()) {
		it->second = value;
	}else
	{
		this->fields.insert(STRING_MAP::value_type(field,value));
	}
	
}
//从map中取出字段的值，如果字段不存在则返回空字符串
CString ValueObject::getCString(CString field)
{
	CString result = L"";
	STRING_MAP::iterator it= this->fields.find(field);
	if(it != fields.end()) {
		result = it->second;
	}
	return result;
}
CString ValueObject::getModelName()
{
	return this->modelName;
}

使用的时候：

insert、update、delete都是用excuteSQL去执行，返回成功与否。

select时有两种用法，一种是根据主键查询，返回一条记录：

BOOL findProjectByID(CString id)
{
	ValueObject* project = new ValueObject(L"PROJECT");
	project->setCString(L"ID",L"");
	project->setCString(L"PNAME",L"");
	project->setCString(L"REMARK",L"");
	CString sql = L"SELECT ID,PNAME,REMARK FROM PROJECT WHERE ID="+id;
	ValueObject* temVo = dbHelper->findBySql(project,sql);
	m_id =temVo->getCString(L"ID");
	m_pname = temVo->getCString(L"PNAME");
	m_remark = temVo->getCString(L"REMARK");
	UpdateData(FALSE);
	delete project;
	return TRUE;
}

第二种是，查询返回N条记录：

void loadPlan(CString id)
{
	ValueObject *vo = new ValueObject(L"PLAN");
	vo->setCString(L"ID",L"");
	vo->setCString(L"PNAME",L"");
	VALUEOBJECT_LIST list = dbHelper->findListBySql(vo,L"SELECT ID,PNAME FROM PLAN WHERE PROJECT_ID="+id);
	//遍历list取数据
	for(VALUEOBJECT_LIST::iterator plist = list.begin(); plist != list.end(); plist++)
	{
		ValueObject* temVo = *plist;
		CString str = temVo->getCString(L"ID");
		
	}
	DataBaseHelper::ReleaseValueObjectList(list);
	delete vo;
}

写的粗糙，请见谅，但解决了一些问题，用起来也比直接用ADO方便一些。有问题可以联系我和我交流~