VC6下ADO数据库编程（2）--增、改、查

全局变量：

	_ConnectionPtr m_conn = NULL;
	_RecordsetPtr m_res = NULL;
	
	CString _TABLE="TimeRecording";
	CString _RECORDING_DATE="RecordingDate";
	CString _DURATION="Duration";
	CString _COMMENTS="Comments";

1． 连接数据库

BOOL CAccessDatabase::ConnectDatabase()
{
	try
	{
        ::CoInitialize(NULL);
        m_conn.CreateInstance(_uuidof(Connection));
        CString strFileName;
        strFileName = "G:\\myDatabase.mdb";     //数据库的文件名
        m_conn->Open(_bstr_t("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName),"","",adConnectUnspecified);
        m_res.CreateInstance(_uuidof(Recordset));
	}
	catch(_com_error e)     //异常检测
	{
        AfxMessageBox("数据库连接错误!",MB_ICONEXCLAMATION);
		return FALSE;
	}
	
	return TRUE;
}

2． 添加数据

BOOL CAccessDatabase::AddData(COleDateTime date, long duration)
{
	_variant_t m_resa;      //可声明为成员变量
					
	COleDateTime curtime = COleDateTime::GetCurrentTime();
	CString strDate;
	strDate=curtime.Format("%Y-%m-%d");
	CString strComments = "";
	CString strDuration;
	strDuration.Format("%ld", duration);
	CString sql="insert into "+_TABLE+"("+_RECORDING_DATE+","+_DURATION+","+_COMMENTS+") values (#"+strDate+ "#,"+strDuration+","+"'"+strComments+"'"+")";
	
	try
	{
        m_conn->Execute((_bstr_t)sql,&m_resa,adCmdText);        //执行"增"操作
	}
	catch(_com_error e)
	{
        AfxMessageBox("数据库增错误",MB_ICONEXCLAMATION);
        return false;
	}
	
	return true;
}

3． 修改数据

BOOL CAccessDatabase::UpdateData(COleDateTime date, long duration)
{
	_variant_t m_resa;      //可声明为成员变量
					
	COleDateTime curtime = COleDateTime::GetCurrentTime();
	CString strDate;
	strDate=curtime.Format("%Y-%m-%d");
	CString strDuration;
	strDuration.Format("%ld", duration);
	CString sql="update "+_TABLE+" set "+_DURATION+"="+strDuration+" where "+_RECORDING_DATE+"="+"#"+strDate+"#";


	try
	{
        m_conn->Execute((_bstr_t)sql,&m_resa,adCmdText);        //执行"更新"操作
	}
	catch(_com_error e)
	{
        AfxMessageBox("数据库更新错误",MB_ICONEXCLAMATION);
        return false;
	}


	return true;
}

4． 查询数据

long CAccessDatabase::GetDuration(COleDateTime date)
{
	_variant_t m_resa;      //可声明为成员变量


	CString strDate;
	strDate=date.Format("%Y-%m-%d");


	CString sql="select * from "+_TABLE+" where "+_RECORDING_DATE+"="+"#"+strDate+"#";
	
	try
	{
        m_res = m_conn->Execute((_bstr_t)sql,&m_resa,adCmdText);
	}
	catch(_com_error e)
	{
        AfxMessageBox("数据库查错误",MB_ICONEXCLAMATION);
        return -1;
	}
	
	try
	{
        while(!m_res->adoEOF)   //循环遍历记录
        {
			_variant_t vDuration;
			
			vDuration = m_res->GetCollect("Duration");
			long lDuration;
			if(VT_NULL != vDuration.vt )
			{
				lDuration = (long)vDuration.lVal;
			}


			return lDuration;
        }
	}
	catch(_com_error e)
	{
		AfxMessageBox("数据库查错误",MB_ICONEXCLAMATION);
		return -1;
	}


	return -1;
}

5． 关闭数据库

void CAccessDatabase::CloseDatabase()
{
	try
	{
		if (m_res != NULL && m_res->GetState() == adStateOpen)
		{
			m_res->Close(); //关闭记录集
		}
        if(m_conn != NULL)
        {
			m_conn->Close(); //关闭连接
        }


		::CoUninitialize();
	}
	catch(_com_error e)
	{
        AfxMessageBox("数据库关闭错误",MB_ICONEXCLAMATION);
	}
}

参考链接：
http://blog.youkuaiyun.com/zijinshi/article/details/2027060
http://wenku.baidu.com/view/254067c58bd63186bcebbc80.html