小弟第一次接触数据库,但不知如何在 VC 下通过 ADO 连接 SQL SERVER 2000数据库,请各位大侠不吝赐教,能多详细请多详细....
解决方案 »
- 线程中通过Ole调用Excel出现错误
- 为什么lstrcpy()不能拷贝数据呢?只拷了第一个字符
- 请问我要通过网络采X光在X光下做手术采用什么压缩方法
- 为什么别人给我分之后,我的分数没有增加呢?
- 代理服务器和客户端连接成功以后,怎么发的数据客户端收不到?
- windows内存问题!
- 如何读取txt文本中既有逗号,又有空格的数据?
- InstallShield Profession6.22, 如何调用外部执行程序,有无象VC的CreateProcess()API ?
- 用过CJ60Lib的请看过来,大家一起讨论讨论
- 网易的聊天室不错啊,如何用ActiveX实现呢
- 怎么快速的去看懂1个软件的开发?
- 静态文本框变透明,灰化的edit框竟然也跟着变了
先搜索下吧,也有ado类
(1)引入ADO库定义文件
(2)初始化OLE/COM库
(3)用Connection对象连接数据库
(4)利用建立好的连接,通过Connection、Command对象执行SQL命令,或利用Recordset对象取得结果记录集进行查询、处理。
(5)使用完毕后关闭连接释放对象。
可以参考以下的类
用以下的类试一试:
引用msado15.tlh.H 文件
class CMyDataBase
{
public:
int GetRecordCount(int& nCount);
bool ExecuteSQL(CString& strSQL);
void InitDBParam(CString sHost, CString sDBName, CString sUID, CString sPWD);
void CloseDB();
bool OpenDB();
bool IsConnectedValid();
CMyDataBase();
virtual ~CMyDataBase();
bool GetMTmsg(CMTMsg& Mtmsg);
bool SetMtmsgFlag(int iId, int iStatus);
bool MoveMtmsgToBak(int iId);private:
void dmpCOMerr(_com_error &e, int line, char *file, bool traceOnly);
bool m_bConn;
void TrimStr(CString &sStr);// 数据库参数
CString m_sDBHost; //数据库服务器名
CString m_sDBName; //数据库名
CString m_sUID; //数据库用户名
CString m_sPWD; //数据库密码CString m_sConn;
_ConnectionPtrm_pConn;};cpp文件
#include "stdafx.h"
#include "MyDataBase.h"
#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif/*===========================================================================
*常用的宏
===========================================================================*/inline void TESTHR(HRESULT x) {if FAILED(x) _com_issue_error(x);};
void PrintProviderError(_ConnectionPtr pConnection);
void PrintComError(_com_error &e);
//设置存储过程参数
#define PutPARA(pCom, name, value) (pCom)->Parameters->GetItem(_variant_t(name))->PutValue(value)
//取得存储过程参数
#define GetPARA(pCom, name) (pCom)->Parameters->GetItem(_variant_t(name))->Value
//取记录中指定字段参数
#define GetVALUE(mRs ,name) (mRs)->Fields->Item[(_variant_t(name))]->Value
//取记录中指定字段参数,并转换为CString
#define GetSTRVALUE(mRs, name) CString((mRs)->Fields->Item[(_variant_t(name))]->Value.bstrVal).GetBuffer(0)#define RSGetVALUE(x) (_bstr_t(pRs->GetCollect(x)))
#define DMPcomERR(e) dmpCOMerr(e,__LINE__,__FILE__, true);
//#define CREATEiNSTANCE(sp, riid) { HRESULT _hr = sp.CreateInstance(__uuidof(riid)); if (FAILED(_hr)) _com_issue_error(_hr); }
//========================================================================#include "io.h"//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////CMyDataBase::CMyDataBase()
{
m_bConn = false;
}CMyDataBase::~CMyDataBase()
{
CloseDB();
}
void CMyDataBase::dmpCOMerr(_com_error &e, int line, char *file, bool traceOnly)
{
CString ErrMsg;
ErrMsg.Format(" Line: %d File %s", line, file);_bstr_t bstrSource(e.Source());
_bstr_t bs = _bstr_t(" Error: ") + _bstr_t(e.Error()) + _bstr_t(" Msg: ")
+ _bstr_t(e.ErrorMessage()) + _bstr_t(" Description: ")
+ _bstr_t(e.Description()) + _bstr_t(ErrMsg) ;if(!traceOnly)
MessageBox(0,bs,bstrSource, MB_OK);ErrMsg.Format("%s %s \n", (char *) bs, (char *) bstrSource);}bool CMyDataBase::IsConnectedValid()
{
if (!m_bConn)
return false;try
{
m_pConn->Execute(L"select top 1 * from sysobjects", NULL, adOptionUnspecified);
}
catch (_com_error&)
{
m_bConn = false;
return false;
}catch(...){}return true;
}bool CMyDataBase::OpenDB()
{
if (m_bConn)
return true;try
{
CoInitialize(NULL);m_pConn.CreateInstance(__uuidof(Connection));
m_pConn->ConnectionString = _bstr_t(m_sConn);
m_pConn->Open("", "", "", adConnectUnspecified);
m_bConn = true;}
catch (_com_error& e)
{
m_bConn = false;
//DMPcomERR(e);`
return false;
}
catch (...)
{
return false;
}return true;
}void CMyDataBase::CloseDB()
{
if (m_bConn)
{
try
{
m_pConn->Close();
}
catch (...)
{
}m_bConn = false;
}CoUninitialize();
}void CMyDataBase::TrimStr(CString &sStr)
{
sStr.TrimLeft();
sStr.TrimRight();
}void CMyDataBase::InitDBParam(CString sHost, CString sDBName, CString sUID, CString sPWD)
{
m_sDBHost = sHost;
m_sDBName = sDBName;
m_sUID = sUID;
m_sPWD = sPWD;
m_sConn.Format("driver={sql server};server=%s;Database=%s;UID=%s;PWD=%s;",
m_sDBHost, m_sDBName, m_sUID, m_sPWD);
}bool CMyDataBase::ExecuteSQL(CString& strSQL)
{
if (!OpenDB())
return false;
try
{
_CommandPtr pCmd;
_RecordsetPtr pRs;TESTHR(pCmd.CreateInstance(__uuidof(Command)));
TESTHR(pRs.CreateInstance(__uuidof(Recordset)));pCmd->ActiveConnection = m_pConn;
pCmd->CommandText = _bstr_t(strSQL);
pCmd->CommandType = adCmdText;
pCmd->Parameters->Refresh();pRs = pCmd->Execute(NULL, NULL, adCmdText);if (pRs->GetState() != adStateClosed)
pRs->Close();pRs.Release();
pCmd.Release();}
catch (_com_error& e)
{
m_bConn = false;
DMPcomERR(e);#ifdef _DEBUG
OutputDebugString(strSQL);
#endifreturn false;
}
catch(...)
{
#ifdef _DEBUG
OutputDebugString(strSQL);
#endif
return false;
}return true;
}
bool CMyDataBase::GetMTmsg(CMTMsg& Mtmsg)
{
Mtmsg.Reset();if (!OpenDB())
return false;CString strSQL;try
{
_CommandPtr pCmd;
_RecordsetPtr pRs;TESTHR(pCmd.CreateInstance(__uuidof(Command)));
TESTHR(pRs.CreateInstance(__uuidof(Recordset)));strSQL.Format("%s", "select top 1 * from sms_send where status = 0 ");pCmd->ActiveConnection = m_pConn;
pCmd->CommandText = _bstr_t(strSQL);
pCmd->CommandType = adCmdText;
pCmd->Parameters->Refresh();pRs = pCmd->Execute(NULL, NULL, adCmdText);if (!pRs->adoEOF)
{
Mtmsg.m_iId = atoi((LPTSTR)RSGetVALUE(L"Id"));
Mtmsg.m_strMobile = (LPTSTR)RSGetVALUE(L"Mobile");
Mtmsg.m_strContent = (LPTSTR)RSGetVALUE(L"content");
Mtmsg.m_strEId = (LPTSTR)RSGetVALUE(L"Eid");
pRs->MoveNext();
}if (pRs->GetState() != adStateClosed)
pRs->Close();pRs.Release();
pCmd.Release();}
catch (_com_error& e)
{
m_bConn = false;
DMPcomERR(e);
#ifdef _DEBUG
OutputDebugString(strSQL);
#endif
return false;
}
catch(...)
{
//LOG();
#ifdef _DEBUG
OutputDebugString(strSQL);
#endif
return false;
}return true;
}
bool CMyDataBase::SetMtmsgFlag(int iId, int iStatus)
{
CString strSQL;strSQL.Format("update sms_send set status = %d where id = %d", iStatus, iId);return ExecuteSQL( strSQL );
}bool CMyDataBase::MoveMtmsgToBak(int iId)
{
CString strSQL;strSQL.Format("insert into sms_Already_send select * from sms_send where id = %d", iId); if(ExecuteSQL( strSQL ))
{
strSQL.Format("delete sms_send where id = %d", iId);
return ExecuteSQL( strSQL );
}return false;
}
------
执行SQL参看:ExecuteSQL
取数据参考:GetMTmsg
_RecordsetPtr m_pRecord;*.cppAfxOleInit()m_strSQL = "driver={SQL Server};";
m_strSQL = m_strSQL + "Server=";
m_strSQL = m_strSQL+ m_Server +";database=" + m_Db +";uid=";
m_strSQL = m_strSQL +m_Usr+";pwd="+m_Pwd;char * SQL= (LPSTR)(LPCTSTR)m_strSQL;try {
hr = m_pConnection.CreateInstance("ADODB.Connection");///创建Connection对象
hr = m_pConnection->Open(SQL,"","",adModeUnknown);
}
catch(_com_error e)///捕捉异常
{
CString errormessage;
errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
}
#import "C:\program files\common files\System\ado\msado15.dll" no_namespace \
rename("EOF","EndOfFile") \
rename("LockTypeEnum","newLockTypeEnum")\
rename("DataTypeEnum","newDataTypeEnum")\
rename("FieldAttributeEnum","newFieldAttributeEnum")\
rename("EditModeEnum","newEditModeEnum")\
rename("RecordStatusEnum","newRecordStatusEnum")\
rename("ParameterDirectionEnum","newParameterDirectionEnum")
如果你的系统不是安装在C盘的话就把#import 后面的C改成系统所有的盘
(2).在C***App类的
public:下加入
_RecordsetPtr m_pADOSet;
bool ADOExecute(_RecordsetPtr &ADOSet, _variant_t &strSQL);
在private:下加入_ConnectionPtr ADOConn;
在class C***App : public CWinApp
{
...
};之后#endif之前加入extern C***App theApp; (3)在BOOL C***App::InitInstance()函数中Enable3dControls(); // Call this when linking to MFC statically这一行下面加入
if( FAILED(::CoInitialize(NULL)) )
{
AfxMessageBox("ADO Init failed");
return false;
}
try
{
ADOConn.CreateInstance(__uuidof(Connection));
ADOConn->Open("DSN=OBDC数据源;Provider=MSDASQL","用户","密码", adConnectUnspecified);//这一行要自已修改
}
catch(_com_error &e)
{
CString err;
err.Format("%s", (char*)(e.Description()) );
AfxMessageBox(err);
}
catch(...)
{
AfxMessageBox("Unknown Error...");
}
m_pADOSet.CreateInstance(__uuidof(Recordset));
并在文件最后加上如下代码:
bool C***App::ADOExecute(_RecordsetPtr &ADOSet, _variant_t &strSQL)
{
if ( ADOSet->State == adStateOpen) ADOSet->Close();
try
{
ADOSet->Open(strSQL, ADOConn.GetInterfacePtr(), adOpenStatic, adLockOptimistic, adCmdUnknown);
return true;
}
catch(_com_error &e)
{
CString err;
err.Format("ADO Error: %s",(char*)e.Description());
AfxMessageBox(err);
return false;
}
}
最后就可以在登录时执行SQL语句了,比如用户为CString strUser, 密码是CString strPwd;数据库表是user_table(user_id, user_name, user_pwd)则
_variant_t strQuery, Holder;
strQuery = "select * from user_table where user_name='"+strUser +"' and user_pwd='"+ strPwd +"'";
theApp.ADOExecute(theApp.m_pADOSet, strQuery);
int iCount = theApp.m_pADOSet->GetRecordCount();
if ( 0==iCount )
{
AfxMessageBox(_T("密码错误"), MB_ICONEXCLAMATION);
return;
}
else
{
AfxMessageBox(_T("登录成功"), MB_ICONEXCLAMATION);
}
#include <icrsint.h>
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF") 在APP()里加上一行:
AfxOleInit();在主界面的初始化对话框上加上这一段:
_ConnectionPtr m_pConnection;
HRESULT hr;
try
{
hr = m_pConnection.CreateInstance("ADODB.Connection");///创建Connection对象
if(SUCCEEDED(hr))
{
m_pConnection->ConnectionTimeout = 5;///设置超时时间为5秒
if(m_pConnection->State)
m_pConnection->Close(); ///如果已经打开了连接则关闭它
hr = m_pConnection->Open("driver={SQL Server};Server=127.0.0.1;DATABASE=ZHOUBAO;UID=sa;PWD=1234","","",adModeUnknown);///连接数据库
///上面一句中连接字串中的Provider是针对ACCESS2000环境的,对于ACCESS97,需要改为:Provider=Microsoft.Jet.OLEDB.3.51;
}
}
catch(_com_error e)///捕捉异常
{
CString errormessage;
errormessage.Format("连接数据库失败!\r\n错误信息:%s",e.ErrorMessage());
AfxMessageBox(errormessage);///显示错误信息
} 请问,这样做,行不行,好不好?
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF","adoEOF")::CoInitialize(NULL);//初始化COM
HRESULT hr=m_pConnection.CreateInstance("ADODB.Connection");
if(SUCCEEDED(hr))
{
try
{
// 打开本地SQL库
//m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Data.mdb;Jet OLEDB:Database Password=","","",adModeUnknown);(accesss数据库,Data.mdb与工程在同一目录下)
m_pConnection->Open("driver={SQL Server};Server=127.0.0.1;DATABASE=netmanage;UID=;PWD=","","",adModeUnknown);
return true;
}
catch(_com_error e)//COM错误取得,当执行COM功能的时候,如果出错,可以捕捉到_com_error的异常
{
CString strComError;
strComError.Format("错误编号: %08lx\n错误信息: %s\n错误源: %s\n错误描述: %s",
e.Error(), // 错误编号
e.ErrorMessage(), // 错误信息
(LPCSTR) e.Source(), // 错误源
(LPCSTR) e.Description()); // 错误描述
::MessageBox(NULL,strComError,"错误",MB_ICONEXCLAMATION);
return FALSE;
}
}