封装一个ado的数据库操作

vc数据库基本操作

         对于vc连接数据库其实也很简单，vc深入详解中讲解的已经很清除了:

         首先要先在头文件(stdafx.h)中加入(masdo15.dll拷贝到工程目录下)：

#import "msado15.dll" no_namespace rename("EOF","rsEOF")

查询与更新：

{CoInitialize(NULL);//初始化组件_ConnectionPtr pConn(__uuidof(Connection));_RecordsetPtr  pRst(__uuidof(Recordset));pConn->ConnectionString = "provider=SQLOLEDB; Server=127.0.0.1; DataBase=数据库名字; uid=sa; pwd=数据库密码";//访问的属性和方法，连接字串try{pConn->Open("", "", "", adConnectUnspecified);//打开数据库的连接}catch(_com_error &e){MessageBox(e.ErrorMessage());return;}char sql[50]="select * from userTable";char rest[10];pRst = pConn->Execute(sql, NULL, adCmdText);//查询时候这样返回记录while(!pRst->rsEOF){strcpy(rest,(_bstr_t)pRst->GetCollect("sName"));MessageBox(rest);pRst->MoveNext();}//更新代码pConn->Execute(sql, NULL, adCmdText)//更新时候不需要返回值pRst->Close();pConn->Close();pRst.Release();pConn.Release();CoUninitialize();//卸载组件}

其实上面的语句执行也应该放到try{}catch()中，这里只是尽量简化操作，记录主体而已。

如果在实际应用中，上面的操作有点复杂，特别是众多try,catch.下面是我封装了一下这些操作的一个示例：

1.首先在stdafx中加入如下语句：

#import "msado15.dll" no_namespace rename("EOF","rsEOF")#include "DBsqlServer.h"extern CDBsqlServer*g_pDB;

在程序初始化时候

::CoInitialize(NULL);//加载组件
if( !g_pDB->Initialize("127.0.0.1", "数据库密码") )
return FALSE;

//在程序退出时候卸载组件

::CoUninitialize();

2.查找与更新操作如下：

CSqlStatement *stmt = g_pDB->ExecuteSQL(strSQL);if( stmt != NULL && stmt->NextRow() )//如果查询多个while(stmt->NextRow()){nCount = stmt->ValueInt(“字段名”);//获取整形值str = stmt->ValueString(“字段名”);//获取字符串值nCount = stmt->ValueInt();//如果查询count值时候}delete stmt;//别忘了delete

封装的也很简单，代码如下：

.h

/************************************************************************//* 数据库封装类                                                         *//* 1.CSalStatement用于取值*//* 2.CDBsqlServer操作数据库*//* 3.cpp文件中有个g_pDB全局变量，用这个来操作数据库*//* 4.如果查询语句别忘了delete掉返回的结果集*//* 5.(其实CSqlStatement重载'='也可以做成局部变量的形势，以后再处理吧)*//* 用法:*//* 1.stdafx.h或其它头文件下*//*#import "msado15.dll" no_namespace rename("EOF","rsEOF")*//*extern CDBsqlServer*g_pDB;*//* 2.程序初始化时候*//*::CoInitialize(NULL);*//*g_pDB->Initialize(dbName, dbPsw);*//* 3.查询时候*//*CSqlStatement *stmt = g_pDB->ExecuteSQL(strSQL);*//*if( stmt != NULL && stmt->NextRow() )*//*nCount = stmt->ValueInt();*//*delete stmt;*//* 执行不返回结果的语句时:*//*g_pDB->UpdateSQL( strSQL );*//* 4.程序退出时候别忘了: ::CoUninitialize();*//************************************************************************/#pragma onceclass CSqlStatement{public:CSqlStatement(CString sql);~CSqlStatement();friend class CDBsqlServer;BOOL NextRow();longValueInt(CString  value = "");CStringValueString(CString value);protected:BOOLExecutesql();protected:_RecordsetPtr m_recordset;BOOLm_bFirst;CString m_strSQL;};class CDBsqlServer  {public:BOOLInitialize(CString sDBname, CString sDBpsw);BOOLUpdateSQL(const CString strSQL);CSqlStatement* ExecuteSQL(const CString strSQL);static CDBsqlServer *GetDBsqlServer();protected:CDBsqlServer();virtual ~CDBsqlServer();BOOLConnectDB(CString sDBname, CString sDBpsw);public:static _ConnectionPtr m_pConnection;private:static CDBsqlServer *m_pDBsql;};

.cpp

#include "stdafx.h"#include "DBsqlServer.h"CDBsqlServer *CDBsqlServer::m_pDBsql = NULL; _ConnectionPtr CDBsqlServer::m_pConnection = NULL;CDBsqlServer* g_pDB = CDBsqlServer::GetDBsqlServer();//用这个全局变量来进行数据库操作CDBsqlServer::CDBsqlServer(){}CDBsqlServer::~CDBsqlServer(){m_pConnection->Close();m_pConnection.Release();}CDBsqlServer* CDBsqlServer::GetDBsqlServer(){if(NULL == m_pDBsql)return new CDBsqlServer;return m_pDBsql;}BOOL CDBsqlServer::Initialize(CString sDBname, CString sDBpsw){if ( (_T("") == sDBpsw) || (_T("")==sDBpsw) ){return FALSE;}return ConnectDB(sDBname, sDBpsw);}BOOL CDBsqlServer::UpdateSQL(const CString strSQL){try   {_variant_t   vr; m_pConnection->Execute((_bstr_t)strSQL,&vr,adCmdText);}   catch(_com_error& e)   {AfxMessageBox(e.ErrorMessage());return FALSE;} return TRUE;}BOOL CDBsqlServer::ConnectDB(CString sDBname, CString sDBpsw){try                 {m_pConnection.CreateInstance( __uuidof(Connection) );m_pConnection->PutConnectionTimeout((long)10);//10秒超时CString  sConnect;sConnect.Format(_T("provider=SQLOLEDB; Server=%s; DataBase=ManModelDB; uid=sa; pwd=%s"), sDBname.GetBuffer(0), sDBpsw.GetBuffer(0));m_pConnection->ConnectionString = (_bstr_t)sConnect.GetBuffer(0);m_pConnection->Open("", "", "", adConnectUnspecified);//打开数据库的连接}catch(_com_error e){MessageBox(NULL, e.ErrorMessage(), _T("连接数据库失败"), 0);return FALSE;} return TRUE;}CSqlStatement* CDBsqlServer::ExecuteSQL(const CString strSQL){return new CSqlStatement(strSQL);//获得这个指针后别忘了delete掉}////////////////////////////////////////////////////////////////////////////取值类CSqlStatement::CSqlStatement(CString sql){m_recordset = NULL;m_bFirst = TRUE;m_strSQL = sql;}CSqlStatement::~CSqlStatement(){if(m_recordset != NULL){try{m_recordset->Close();m_recordset.Release();}catch(...){}}}BOOL CSqlStatement::Executesql(){m_recordset.CreateInstance(__uuidof(Recordset));try{m_recordset = CDBsqlServer::m_pConnection->Execute((_bstr_t)m_strSQL,NULL,adCmdText);}catch(_com_error e)//捕捉异常{MessageBox(NULL, e.ErrorMessage(), "", 0);return FALSE;}if( (!m_recordset->rsEOF) && (NULL!=m_recordset) && (!m_recordset->BOF) ) return TRUE;return FALSE;}BOOL CSqlStatement::NextRow(){if( m_bFirst ){m_bFirst = FALSE;return Executesql();}m_recordset->MoveNext();if( (!m_recordset->rsEOF) && (NULL!=m_recordset) && (!m_recordset->BOF) ) return TRUE;return FALSE;}long CSqlStatement::ValueInt(CString value){long nCount = 0;_variant_t var = m_recordset->GetCollect(value.GetBuffer(0));if( VT_NULL != var.vt ){if ( (strcmp("", value)) == 0 )//select count时候用此来取值{nCount = var.lVal;}else{nCount = atol((char*)_bstr_t(var));}}return nCount;}CString CSqlStatement::ValueString(CString value){_variant_t var;var = m_recordset->GetCollect(value.GetBuffer(0));if( VT_NULL != var.vt){return CString( (char*)_bstr_t(var) );}else{return "";}}

不知道是否有纰漏，还请大侠赐教....还有，这里使用了静态变量，所以值运行连接一个。