MFC中如何用ADO连接SQL Server 2005
VC++ ADO连接数据库
第一步:(以下语句插入相应的文件中,ADOConn.h,可能代码中已经有了)
#import"C:\Program Files\Common Files\System\ado\msado15.dll"no_namespace rename("EOF","adoEOF")rename("BOF","adoBOF")
第二步:
在工程中增加以下两个文件(ADOConn.h, ADOConn.ccp),增加方法如下,在FILEVIEW中的Source Files,点右键,选择添加文件到工程目录.选择ALL FILES,将以上两个文件选中. 文件可以自建,自建代码附后,也可以在网上下载.
以下是程序的关键的连接代码,我在测试过程中这部分的代码一定要写对.服务器的IP地址:我们公司是192.168.3.4,数据库的名称,以下红色部分,填自己的信息
BOOL CADOConn::OnInitDBConnect()
{
CString strConnection;
strConnection = "Provider=SQLOLEDB.1;Server=" + theApp.m_strServerApp + ";Database=DB_TestDatabase";
// theApp.m_strServerApp:192.168.3.4
::CoInitialize(NULL);
try
{
m_pConnection.CreateInstance(__uuidof(Connection));///////////////////////////////////////////////////////////////////////
#if !defined(AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_)
#define AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_
#include
//#include
//#include
#if _MSC_VER > 1000
#pragma once
#endif // _MSC_VER > 1000
#include "sqlext.h"
#import "c:Program FilesCommon FilesSystemadomsado15.dll"no_namespace rename("EOF","adoEOF")rename("BOF","adoBOF")
class CADOConn
...{
public:
CADOConn();
virtual ~CADOConn();
public:
//ADODB::_ConnectionPtr m_pConnect
//_ConnectionPtr m_pConnection;
//_RecordsetPtr m_pRecordset;
public:
int FindRecordSet(CString m_sql,CString strField,CString str);
int DeleteRecordSet(CString m_sql);
//初始化——连接数据库
static void OnInitDBConnect();
//执行查询
static _RecordsetPtr& GetRecordSet(_bstr_t bstrSQL);
//Insetr Update _variant_t
static BOOL ExecuteSQL(_bstr_t bstrSQL);
void ExitConnect();
//获得表长
int GetRecordCount(CString strTable, int &nCount, CString strCondition = _T(""));
//sql语句,待查属性列,查询结果
static int SelectRecordSet(CString m_sql,CString strField,CString &strResult);
//修改属性值
int UpdateRecordSet(CString m_sql, CString strField, CString str);
int InsertRecord(CString strTable, int nCount, CString *strFields, CString *strValue,
CString strkeyfield = _T(""), CString strkey = _T(""));
};
#endif // !defined(AFX_ADOCONN1_H__E411A6A3_90BA_4C07_8BC5_5F48FFF4DB07__INCLUDED_)
// ADOConn.cpp: implementation of the CADOConn class.
//
//////////////////////////////////////////////////////////////////////
#include "stdafx.h"
#include "MyClient.h"
#include "ADOConn.h"
//#include "ExamTest.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
_ConnectionPtr m_pConnection;
_RecordsetPtr m_pRecordset;
extern CMyClientApp theApp;
////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
CADOConn::CADOConn()
...{
}
CADOConn::~CADOConn()
...{
}
void CADOConn::OnInitDBConnect()
...{
CString strUser;
CString strPwd;
CString strservername;
CString strdbname;
CString strConnection;
strUser = theApp.m_strDBUserApp;
strPwd = theApp.m_strDBPwdApp;
strConnection = "Provider=SQLOLEDB.1;Server=" + theApp.m_strServerApp+ ";Database=TroubleCheck";
::CoInitialize(NULL);
try
...{
m_pConnection.CreateInstance(__uuidof(Connection));//******************
_bstr_t strConnect = (_bstr_t)strConnection;
m_pConnection->Open(strConnect,(_bstr_t)strUser,(_bstr_t)strPwd,adModeUnknown);//****************
theApp.isDBOK = TRUE;
}
catch(_com_error e)
...{
//AfxMessageBox(e.Description()+"系统退出!");
//exit(0);//***********************************
AfxMessageBox("数据库连接失败，请重新配置连接数据库!");
::SendMessage(theApp.hwndmainDlg,WM_SETDBSERVER,0,0);
}
}
_RecordsetPtr& CADOConn::GetRecordSet(_bstr_t bstrSQL)
...{
try
...{
if (m_pConnection == NULL)
...{
OnInitDBConnect();
}
m_pRecordset.CreateInstance(_uuidof(Recordset));
m_pRecordset->Open(bstrSQL,(_variant_t)m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
}
catch (_com_error e)
...{
AfxMessageBox(e.Description()+"系统退出！");
exit(0);
//AfxMessageBox("2");
}
return m_pRecordset;
// _variant_t var;
}
BOOL CADOConn::ExecuteSQL(_bstr_t bstrSQL)//************************
...{
try
...{
if (m_pConnection == NULL)
...{
//AfxMessageBox("自动连接!");
OnInitDBConnect();
}
m_pConnection->Execute(bstrSQL ,NULL,adCmdText);
}
catch (_com_error e)
...{
AfxMessageBox(e.Description()+"系统退出！");
exit(0);
}
return true;
}
void CADOConn::ExitConnect()
...{
if (m_pRecordset != NULL)
...{
m_pRecordset->Close();
m_pConnection->Close();
::CoUninitialize();
}
}
int CADOConn::SelectRecordSet(CString m_sql,CString strField,CString &strResult)
...{
_variant_t varTemp;
_RecordsetPtr m_pRecord = NULL;
try
...{
m_pRecord = GetRecordSet((_bstr_t)m_sql);
if (m_pRecord != NULL)
...{
if (!m_pRecord->adoBOF)
...{
m_pRecord->MoveFirst();
}
while(!m_pRecord->adoEOF)
...{
varTemp = m_pRecord->GetCollect((_bstr_t)strField);
if (varTemp.vt == VT_NULL)
...{
strResult = _T("");
}
strResult = (LPCSTR)_bstr_t(varTemp);
//strResult.TrimRight();
m_pRecord->MoveNext();
}
}
else
return 0;
}
catch (...)
...{
AfxMessageBox("读取失败，请检查数据库!");
return 0;
}
return 1;
}
ADOConn.cpp
int CADOConn::UpdateRecordSet(CString m_sql, CString strField, CString str)
...{
_variant_t varTemp;
_RecordsetPtr m_pRecord = NULL;
try
...{
m_pRecord = GetRecordSet((_bstr_t)m_sql);
if (m_pRecord!=NULL)
...{
if (!m_pRecord->adoBOF)
...{
m_pRecord->MoveFirst();
}
while(!m_pRecord->adoEOF)
...{
m_pRecord->PutCollect((_bstr_t)strField,_variant_t(str));
m_pRecord->Update();
m_pRecord->MoveNext();
}
}
}
catch (...)
...{
AfxMessageBox("读取失败，请检查数据库!");
return 0;
}
return 1;
}
int CADOConn::FindRecordSet(CString m_sql, CString strField, CString str)
...{
_variant_t varTemp;
_RecordsetPtr m_pRecord = NULL;
CString strTemp;
try
...{
m_pRecord = GetRecordSet((_bstr_t)m_sql);
if (m_pRecord!=NULL)
...{
if (!m_pRecord->adoBOF)
...{
m_pRecord->MoveFirst();
}
while(!m_pRecord->adoEOF)
...{
varTemp = m_pRecord->GetCollect((_bstr_t)strField);
if (varTemp.vt == VT_NULL)
...{
strTemp = _T("");
}
strTemp = (LPCSTR)_bstr_t(varTemp);
strTemp.TrimRight();
if (!strcmp(strTemp,str))
return 1;
m_pRecord->MoveNext();
}
}
}
catch (...)
...{
AfxMessageBox("读取失败，请检查数据库!");
return 0;
}
return 1;
}
int CADOConn::DeleteRecordSet(CString m_sql)
...{
if(!ExecuteSQL(_bstr_t(m_sql)))
...{
return 0;
}
else
...{
AfxMessageBox("删除成功");
return 1;
}
}
int CADOConn::GetRecordCount(CString strTable, int &nCount, CString strCondition)
...{
_RecordsetPtr m_pRecord = NULL;
int nCounter = 0;
CString m_sql;
m_sql = "select * from " + strTable + strCondition;
try
...{
m_pRecord = GetRecordSet((_bstr_t)m_sql);
if (m_pRecord!=NULL)
...{
if (!m_pRecord->adoBOF)
...{
m_pRecord->MoveFirst();
}
while(!m_pRecord->adoEOF)
...{
m_pRecord->MoveNext();
nCounter++;
}
}
nCount = nCounter;
}
catch (...)
...{
AfxMessageBox("数据库错误");
return 0;
}
return 1;
}
//return -1:主键冲突/////////////////注意m_Storedata=VT_NULL的情况，要做处理
//strTable:表名
//nCount:属性列数
//strFields:属性列名
//strValue:属性列值
//strKeyfield:主键名
//strKey:主键属性值
int CADOConn::InsertRecord(CString strTable, int nCount, CString *strFields,
CString *strValue,CString strKeyfield, CString strKey)
...{
CString m_sql;
_RecordsetPtr m_pRecord;
_variant_t m_Storedata;
try
...{
if (strKeyfield != "")
...{
m_sql = "select * from " + strTable;
m_pRecord = GetRecordSet((_bstr_t)m_sql);
if (m_pRecord!=NULL)
...{
if (!m_pRecord->adoBOF)
...{
m_pRecord->MoveFirst();
}
while (!m_pRecord->adoEOF)
...{
m_Storedata = m_pRecord->GetCollect((_bstr_t)strKeyfield);
if ((LPCSTR)_bstr_t(m_Storedata)==strKey)
...{
return 0;
}
m_pRecord->MoveNext();
}
}
}
m_sql = "insert into " + strTable + " (";
for (int i=0; i
...{
m_sql += strFields[i] + ",";
}
m_sql += strFields[nCount-1] + ") ";
m_sql += "values (";
for (i=0; i
...{
m_sql += strValue[i] + ",";
}
m_sql += strValue[nCount-1] + ") ";
ExecuteSQL((_bstr_t)m_sql);
}
catch (...)
...{
AfxMessageBox("数据库错误");
return 0;
}
return 1;
}