ADO连接SQL SERVER2005

wuaji

　　

 实现类的过程中借助到了单件模式

#pragma once
#include "SingleTon.h"
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace  rename("EOF", "adoEOF")
class AFXADODB : public Singleton<AFXADODB>
{
public:
_ConnectionPtr m_pConnection; //数据库连接
_RecordsetPtr m_pRecordset;   //记录
_CommandPtr m_pCommand;       //命令
_variant_t value;
public:
AFXADODB(void);
~AFXADODB(void);
void Create(void);
void Destory(void);
void Insert(void);
void Alter(void);
void Delete(void);
_RecordsetPtr Search(void);
};


#include "StdAfx.h"
#include "AFXADODB.h"
AFXADODB* Singleton<AFXADODB>::m_instance = NULL;
AFXADODB::AFXADODB(void)
{
}
AFXADODB::~AFXADODB(void)
{
}
void AFXADODB::Create(void)
{
//初始化COM库
if(!AfxOleInit())
{
::AfxMessageBox(_T("OLE初始化出错!"));
}
::CoInitialize(NULL);
HRESULT hr;
hr = m_pConnection.CreateInstance("ADODB.Connection");
try
{
if(SUCCEEDED(hr))
{
_bstr_t strConnect = "Provider=SQLOLEDB;Data Source=CSE013\\SQLEXPRESS;Initial Catalog=AFX_Server;Integrated Security=SSPI";
m_pConnection->Open(strConnect,"","",adModeUnknown);
//AfxMessageBox(_T("连接成功!"));
}
else
AfxMessageBox(_T("error!"));
}
catch(_com_error e)
{
AfxMessageBox(_T("连接失败!"));
AfxMessageBox(e.ErrorMessage());
}
}
void AFXADODB::Destory(void)
{
if(m_pRecordset!=NULL)
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();
}
void AFXADODB::Insert(void)
{
m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
CString strSQL = _T("INSERT INTO F_File_Info (F_ID, F_File_MD5,F_File_Info, F_HotKey) VALUES ('1','123','456','789')");
m_pConnection->Execute((_bstr_t)strSQL, &value, adCmdText);
//AfxMessageBox(_T("添加成功！"));
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
void AFXADODB::Alter(void)
{
m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
m_pConnection->Execute("update F_File_Info set F_File_Info = F_File_Info + 's'",&value,adCmdText);
//AfxMessageBox(_T("更新成功！"));
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
void AFXADODB::Delete(void)
{
m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
m_pConnection->Execute("delete from F_File_Info where F_ID='1'",&value,adCmdText);
//AfxMessageBox(_T("删除成功！"));
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
}
_RecordsetPtr AFXADODB::Search(void)
{
m_pRecordset.CreateInstance(__uuidof(Recordset));
try
{
m_pRecordset = m_pConnection->Execute("select * from F_File_Info where F_HotKey like '%7%' union select * from F_File_Info where F_File_Info like '%7%' order by F_ID DESC",&value,adCmdText);
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}
return m_pRecordset;
}

　　使用MFC的CListCtrl显示代码

AFXADODB* DB_Client = AFXADODB::GetInstance();
DB_Client->Create();
//DB_Client->Insert();
//DB_Client->Alter();
_RecordsetPtr a = DB_Client->Search();
while(!(a->adoEOF))
{
LPCTSTR temp = (LPCTSTR)a->GetCollect("F_ID").bstrVal;
List_Download.InsertItem(0, temp);
a->MoveNext();
}