ADO连接MSSQL数据库及事务操作

时间：2010-07-14 来源：chen_yu_hust

1.在工程的Cxx.cpp(在工程中的CAApp类中)的InitInstance()函数中
/*********初始化COM库***************/
在MFC中可以用AfxOleInit();
非MFC环境中用:
CoInitialize(NULL); //关闭窗口时释放资源
CoUnInitialize(); 2.在StdAfx.h中
#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF") 3.添加成员变量 // 定义访问数据库的ADO对象
public:
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
_CommandPtr  m_pCommand;
private:
BOOL m_blnConnected; //标记是否连接成功
CString m_strSource;//存储连接字符串strConn 4.在OnInitDialog（）函数中连接数据代码：添加如下代码： // 创建ADO的数据库连接和记录集对象
HRESULT hr;
_bstr_t strConn("Provider=SQLOLEDB;Password=;"
  "Persist Security Info=True;User ID=;"
  "Initial Catalog=master;Data Source=127.0.0.1");
try
{
  hr = m_pConnection.CreateInstance(_uuidof(Connection));
  if(SUCCEEDED(hr))
  {
   m_pConnection->CursorLocation = adUseServer;
   hr = m_pConnection->Open(strConn, "", "", adConnectUnspecified);
  }
  if(SUCCEEDED(hr))
  {
   hr = m_pRecordset.CreateInstance(_uuidof(Recordset));
  }
  if(SUCCEEDED(hr))
  {
   m_blnConnected = TRUE;
  }
  else
  {
   m_blnConnected = FALSE;
  }
}
catch(_com_error &e)
{
  MessageBox(e.ErrorMessage());
  m_blnConnected = FALSE;
} if(!m_blnConnected)
{
  AfxMessageBox("ADO数据源初始化失败!");
}
else
{
  m_strSource = (const char*)strConn;
} 需要操作数据库时：
1).添加成员变量
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
2).建立连接，创建记录集
OnInitADOConn();
//UpdateData(true);
_bstr_t sql;
sql="select * from Students";//Students为数据库中的表名
m_pRecordset.CreateInstance(_uuidof(Recordset));
m_pRecordset->Open(sql,m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
3).进行你需要的操作(本例是为数据库添加一条新的记录)
try
{
m_pRecordset->AddNew();
m_pRecordset->PutCollect("编号",(_bstr_t)number);
m_pRecordset->PutCollect("姓名",(_bstr_t)name);
m_pRecordset->PutCollect("学历",(_bstr_t)culture);
m_pRecordset->Update();
}
catch(_com_error e)
{
AfxMessageBox("编号重复");
return;
}
4）.关闭记录集和连接
m_pRecordset->Close();
m_pConnection->Close();
5）.在OnDestroy()添加 CoUninitialize();//关闭com 以下代码用业执行数据相关事务操作： int i;

_bstr_t strSource("select * from uCoursesTeacher"); CString str;
_variant_t    vIndex; if(m_pRecordset->State == adStateOpen)
{
  m_pRecordset->Close();
}

m_pRecordset->LockType = adLockOptimistic;
m_pRecordset->CursorType = adOpenDynamic;
m_pRecordset->CursorLocation = adUseServer; // _bstr_t source = m_strSource;然后用source字符串作为Open函数的第二个
// 参数，也可以打开记录集。然而此时不能执行事务。必须将Connection对象的
// 指针传递给Open函数，然后才能使得事务生效。
m_pRecordset->Open(strSource, _variant_t((IDispatch*)m_pConnection,true),
  adOpenDynamic, adLockOptimistic, adCmdText);

m_pConnection->BeginTrans(); for(i =1; i<=5; i++)
{
  str.Format("%d", i);
  m_pRecordset->AddNew();   vIndex = (short)0;
  m_pRecordset->PutCollect(&vIndex, _variant_t(str + "_YOU"));   vIndex = (short)1;
  m_pRecordset->PutCollect(&vIndex, _variant_t(str + "_I"));

  vIndex = (short)2;
  m_pRecordset->PutCollect(&vIndex, _variant_t(str + "_HE"));

  m_pRecordset->Update();
} if(MessageBox("要保存所有变化吗?", NULL, MB_ICONQUESTION | MB_YESNO)
  == IDYES)
{
  m_pConnection->CommitTrans();
}
else
{
  m_pConnection->RollbackTrans();
} m_pRecordset->Close(); m_pRecordset->LockType = adLockOptimistic;
m_pRecordset->CursorType = adOpenDynamic;
m_pRecordset->CursorLocation = adUseClient;

m_pRecordset->Open(strSource, _variant_t((IDispatch*)m_pConnection,true),
  adOpenDynamic, adLockOptimistic, adCmdText); m_dbGrid.SetRefDataSource((LPUNKNOWN)m_pRecordset);