数据库操作的封装

 暑期写了一个数据库类,封装了数据库的各种操作.特发表在此处,与人分享.如有疑问,欢迎提出.

//ADOConn.h #pragma once #import "C:/Program Files/Common Files/System/ado/msado15.dll" no_namespace rename("EOF","adoEOF") rename("BOF","adoBOF") #include<vector> using namespace std; namespace Global{ static vector<CString>Select_Stack(0); //用来保存结果集 //返回个数,并结果保存在Select_Stack中 static int ExecuteSelect(CString cmd,char **title,int n); //仅仅返回结果集的个数 static int ExecuteSelectEx(CString cmd); }; class ADOConn { public: _ConnectionPtr pConnection; _RecordsetPtr pRecordset; _CommandPtr pCommand; public: ADOConn(); ~ADOConn(); bool Connect(); bool DisConnect(); bool ExecuteSQL(_bstr_t bstrSQL); //执行SQL操作，返回是否正确执行 static BOOL ExecuteCmd(CString cmd); }; int Global::ExecuteSelect(CString cmd,char ** title,int n){ ADOConn adoConn; Global::Select_Stack.resize(0); if(!adoConn.Connect()) return 0; if(!(adoConn.pConnection->State&adStateOpen)) return 0; try{ adoConn.pRecordset=adoConn.pConnection->Execute(_bstr_t(cmd),NULL,adCmdText); }catch(_com_error e){ return 0; } if(adoConn.pRecordset->adoBOF&&adoConn.pRecordset->adoEOF) return 0; adoConn.pRecordset->MoveFirst(); int m=0; CString result(""); while(!adoConn.pRecordset->adoEOF){ for(int i=0;i<n;i++){ result.Empty(); _variant_t varValue=adoConn.pRecordset->GetCollect(_variant_t(title[i])); if(varValue.vt!=VT_NULL) result=varValue.bstrVal; Global::Select_Stack.push_back(result); } m++; adoConn.pRecordset->MoveNext(); } return m; } int Global::ExecuteSelectEx(CString cmd){ ADOConn adoConn; if(!adoConn.Connect()) return 0; if(!(adoConn.pConnection->State&adStateOpen)) return 0; try{ adoConn.pRecordset=adoConn.pConnection->Execute(_bstr_t(cmd),NULL,adCmdText); }catch(_com_error e){ return 0; } if(adoConn.pRecordset->adoBOF&&adoConn.pRecordset->adoEOF) return 0; adoConn.pRecordset->MoveFirst(); int m=0; while(!adoConn.pRecordset->adoEOF){ m++; adoConn.pRecordset->MoveNext(); } return m; }

 

//ADOConn.cpp #include"StdAfx.h" #include"ADOConn.h" #define DATABASE "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=StaffTraining;Data Source=" ADOConn::ADOConn(){ if (!SUCCEEDED(pConnection.CreateInstance(__uuidof(Connection)))) { pConnection = NULL; TRACE(_T("Database CreateInstance failed")); exit(0); } if (!SUCCEEDED(pRecordset.CreateInstance(__uuidof(Recordset)))) { pRecordset = NULL; TRACE(_T("Recordset CreateInstance Failed!")); exit(0); } if(!SUCCEEDED(pCommand.CreateInstance(__uuidof(Command)))) exit(0); } bool ADOConn::Connect(){ CString path=CString(DATABASE); if(pConnection->State&adStateOpen) DisConnect(); try{ if(!SUCCEEDED(pConnection->Open(_bstr_t(path),"","",adConnectUnspecified))){ ::AfxMessageBox(CString("数据库连接失败，无法打开")); return false; } pCommand->ActiveConnection=pConnection; return true; }catch(_com_error & e){ ::AfxMessageBox(CString("数据库连接失败，无法打开")); } return false; } bool ADOConn::DisConnect(){ if (pConnection->State&adStateOpen) return TRUE; //关闭数据库连接 try{ return SUCCEEDED(pConnection->Close()); } catch (_com_error& e){ ::AfxMessageBox(e.ErrorMessage()); return FALSE; } } ADOConn::~ADOConn(){ DisConnect(); } bool ADOConn::ExecuteSQL(_bstr_t bstrSQL){ if(!(pConnection->State&adStateOpen)) return false; try{ pConnection->Execute(bstrSQL,NULL,adCmdText); return true; }catch(_com_error & e){ ::AfxMessageBox(e.ErrorMessage()); return false; } } BOOL ADOConn::ExecuteCmd(CString cmd){ ADOConn tempt; if(!tempt.Connect()) return FALSE; if(!tempt.ExecuteSQL(_bstr_t(cmd))) return FALSE; return TRUE; }

使用方法:

第一步.在MFC程序的InitInstance方法中添加以下代码

if(!SUCCEEDED(::CoInitialize(NULL))){ ::AfxMessageBox("初始化com库失败!"); exit(0); }

第二步.将ADOConn.cpp中的DATABASE宏修改为你所对应的数据库,SQLOLEDB.1表示SQL Server,Initial Catalog=数据库名.具体情况,请搜索百度或Google

这样就可以使用该类了.

第三步.在你要使用的地方加上该类#include"ADOConn.h"

然后就可以使用了

比如插入操作

CString sql="INSERT INTO 员工 VALUES('000001','胡某','158******')"; ADOConn::ExecuteCmd(sql); //这样就在我的员工表中插入了一条信息

其他的非SELECT操作都可以像上面那样完成.

比如SELECT操作

CString sql("SELECT 工号,姓名 FROM 员工"); char * title[2]={"工号","姓名"}; Global::ExecuteSelect(sql,title,2); //执行以上语句的集合保存在Global::SelectStack中. //用最简单的显示方法如下: CString s(""); for(int i=0;i<Global::SelectStack.size();i++) s+=Global::SelectStack.at(i); ::AfxMessageBox(s);

需要注意的是ExecuteSelect只返回字符串,所以类似这样的语句"SELECT COUNT(*) 数量" 应该为"SELECT CONVERT(varchar(10),COUNT(*)) 数量"