dbmanager.cpp

VC++一个数据库的开发

// DBManager.cpp: implementation of the CDBManager class.
//
//////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "AdoDB.h"
#include "DBManager.h"

#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif

//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
CDBManager::CDBManager(DB_Type dt/*=_Access*/)
{
	m_dbType=dt;
	m_pConnection=NULL;
}

CDBManager::~CDBManager()
{

}

BOOL CDBManager::_RealConnectAccess(StConnPar* pConnPar)
{
	CString strConn;
	//根据参数构造连接字符串
	strConn.Format(_T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s"),
		pConnPar->szFilePath);
	if(m_pConnection)
	{
		m_strErrMsg=_DB_ALREALY_OPEN_;
		return FALSE;
	}
	m_pConnection.CreateInstance(__uuidof(Connection));
	try
	{
		//打开数据库
		m_pConnection->Open((_bstr_t)strConn,
			pConnPar->szUser,
			pConnPar->szPassword,
			adModeUnknown);
	}
	catch (_com_error e)
	{
		m_strErrMsg=(LPSTR)e.Description();
		m_pConnection=NULL;
		return FALSE;
	}
	return TRUE;
}

BOOL CDBManager::_RealConnectDB2(StConnPar* pConnPar)
{
	m_strErrMsg=_UNSUPPORT_FUNCTION_;
	return FALSE;
}

BOOL CDBManager::_RealConnectMSSQL(StConnPar* pConnPar)
{
	m_strErrMsg=_UNSUPPORT_FUNCTION_;
	return FALSE;
}

BOOL CDBManager::_RealConnectMYSQL(StConnPar* pConnPar)
{
	m_strErrMsg=_UNSUPPORT_FUNCTION_;
	return FALSE;
}

BOOL CDBManager::_RealConnectOracle(StConnPar* pConnPar)
{
	m_strErrMsg=_UNSUPPORT_FUNCTION_;
	return FALSE;
}

BOOL CDBManager::Open(StConnPar* pConnPar)
{
	if(m_pConnection)
	{
		m_strErrMsg=_DB_ALREALY_OPEN_;
		return FALSE;
	}
	switch(m_dbType)
	{
	//目前只支持Access数据库
	case _Access:
		return _RealConnectAccess(pConnPar);
		break;
	default:
		m_strErrMsg=_UNSUPPORT_FUNCTION_;
		return FALSE;
	}
}

BOOL CDBManager::RunSQLCommand(CString strSQL)
{
	_CommandPtr pCommand;
	//创建Command对象
	pCommand.CreateInstance(__uuidof(Command));
	try
	{
		//设置Command关联的连接
		pCommand->ActiveConnection=m_pConnection;
		//设置Command类型
		pCommand->CommandType=adCmdText;
		//设置Command关联命令字符串
		pCommand->CommandText=(LPCTSTR)strSQL;
		
		//执行命令
		pCommand->Execute(NULL,NULL,adCmdUnknown);
	}
	catch (_com_error e)
	{
		m_strErrMsg=(LPSTR)e.Description();
		pCommand=NULL;
		return FALSE;
	}
	return TRUE;
}

BOOL CDBManager::DeleteRecord(LONG lID)
{
	CString strSQLCommand;
	//构造SQL语句字符串
	strSQLCommand.Format(_T("delete from talk_info where id=%d"),lID);
	return RunSQLCommand(strSQLCommand);
}

BOOL CDBManager::UpdateRecord(StClientTalkInfo& st)
{
	CString strSQLCommand;
	//更新数据
	if(st.lID>0)
	{
		strSQLCommand.Format(_T("Update talk_info set client_name='%s',\
		phone_number='%s',\
		talk_time='%s',\
		message='%s' where id= %d"),
		st.szClientName,
		st.szPhoneNumber,
		st.dtTime.Format(),
		st.szMessage,
		st.lID);
	}
	//插入新数据
	else
	{
		strSQLCommand.Format(_T("insert into talk_info(client_name,phone_number,talk_time,message)\
values(\
'%s','%s','%s','%s')"),
			st.szClientName,
			st.szPhoneNumber,
			st.dtTime.Format(),
			st.szMessage);
	}

	return RunSQLCommand(strSQLCommand);
}

BOOL CDBManager::GetTalkInformations(TalkInfoSet& ts,LPCTSTR lpCommandText)
{
	//错误处理
	if(m_pConnection==NULL || m_pConnection->State!=adStateOpen)
	{
		m_strErrMsg=_DB_NOT_OPEN_;
		return FALSE;
	}

	//清空数组
	ts.RemoveAll();

	_RecordsetPtr pRecordset;
	_variant_t var;
	LPCTSTR lpString=NULL;
	if(lpCommandText)
	{
		lpString=lpCommandText;
	}
	else
	{
		//如果lpCommandText为NULL则返回所有记录
		lpString=_T("select * from talk_info");
	}

	//创建RecordSet对象
	pRecordset.CreateInstance((__uuidof(Recordset)));
	try
	{
		pRecordset->Open(lpString,
						m_pConnection.GetInterfacePtr(),
						adOpenDynamic,
						adLockOptimistic,
						adCmdText);
	}
	catch (_com_error e)
	{
		m_strErrMsg=(LPSTR)e.Description();
		pRecordset=NULL;
		return FALSE;
	}
	
	//保存记录
	while (!pRecordset->adoEOF)
	{
		StClientTalkInfo st;
		//获取ID
		var=pRecordset->GetCollect(_T("id"));
		if (var.vt!=VT_NULL)
		{
			st.lID=(LONG)var;
		}
		
		//获取客户姓名
		var=pRecordset->GetCollect(_T("client_name"));
		if (var.vt!=VT_NULL)
		{
			sprintf(st.szClientName,(LPCTSTR)(_bstr_t)var);
		}

		//获取客户电话号码
		var=pRecordset->GetCollect(_T("phone_number"));
		if (var.vt!=VT_NULL)
		{
			sprintf(st.szPhoneNumber,(LPCTSTR)(_bstr_t)var);
		}

		//获取通话日期
		var=pRecordset->GetCollect(_T("talk_time"));
		if (var.vt!=VT_NULL)
		{
			COleDateTime dt(var);
			st.dtTime=dt;
		}

		//获取通话主要内容
		var=pRecordset->GetCollect(_T("message"));
		if (var.vt!=VT_NULL)
		{
			sprintf(st.szMessage,(LPCTSTR)(_bstr_t)var);
		}

		ts.Add(st);

		pRecordset->MoveNext();
	}

	//关闭RecordSet对象
	if (pRecordset)
	{
        if (pRecordset->State == adStateOpen)
		{
            pRecordset->Close();
			pRecordset=NULL;
		}
	}
	return TRUE;
}

void CDBManager::CloseAdoObjects()
{
	if(m_pConnection)
	{
		if(m_pConnection->State == adStateOpen)
		{
			m_pConnection->Close();
			m_pConnection=NULL;
		}
	}
}

void CDBManager::Close()
{
	switch(m_dbType)
	{
		//关闭ADO对象
		case _Access:
			CloseAdoObjects();
			break;
		default:
			break;
	}
}