网上关于ADO的使用方法很多,这边我个人就整理出一个使用ADO的方法的具体步骤:1、用#import引入ADO库文件在stdafx.h文件中添加#import "c:\program files\common files\system\ado\msado15.dll"no_namespaces rename("EOF" adoEOF")
2、 数据库连接,创建CDBConnection,代码如下:
classCDBConnection
{
public:
//数据库是否已连接
BOOL m_Actived;
//关闭连接
voidClose();
//打开连接
BOOL Open(CString CnnStr);
//ADO的连接对象指针
_ConnectionPtr m_pConn;
CDBConnection();
virtual~CDBConnection();
};
CDBConnection::CDBConnection()
{
//创建连接对象
m_pConn.CreateInstance("ADODB.Connection");
m_Actived=FALSE;
}
CDBConnection::~CDBConnection()
{
//释放连接对象
m_pConn.Release();
}
BOOL CDBConnection::Open(CString CnnStr)
{
try
{
m_pConn->Open(_bstr_t(CnnStr),"","", adConnectUnspecified);
m_Actived=TRUE;
returnTRUE;
}
catch(_com_error&e)
{
returnFALSE;
}
}
voidCDBConnection::Close()
{
if(m_Actived)
{
m_pConn->Close();
m_Actived=FALSE;
}
}
相关函数:
1)HRESULT Connection15::Open ( _bstr_t ConnectionString, _bstr_t UserID, _bstr_t Password, long Options )ConnectionString
例子:"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strCommandLine +
"WhMgrDB.mdb;Persist Security Info=False"。其中strCommandLine
为数据库的地址,WhMgrDB.mdb为所要用到的数据库。
UserID和Password分别为建立连接时所使用的用户名和密码
Options:
决定是使用同步方式(adConnectUnspecified默认)还是异步方式(adAsyncConnext)打开数据库。当使用同步方式打开数据
库时,只有打开完成,Open方法才返回;而是用异步方式打开数据库时,Open方法在建立连接完成之前就返回。
3、 通用数据访问模块的实现
classCDataSet
{
protected:
//打开查询
BOOL Open(CString SQL);
public:
//删除当前记录
voidDelete();
//更新记录
voidUpdate();
//将Field列的值设置为Value
voidSetAsString(CString Field, CString Value);
//关闭查询
voidClose();
//加载数据
virtualBOOL LoadData();
//得到Field列的值
CString GetAsString(CString FieldName);
//是否在第一条记录之前
BOOL IsBOF();
//是否在最后一条记录之后
BOOL IsEOF();
//移动到上一条记录
voidMovePrevious();
//移动到下一条记录
voidMoveNext();
//移动到最后一条记录
voidMoveLast();
//移动到第一条记录
voidMoveFirst();
//数据库连接对象
CDBConnection*m_cnn;
CDataSet();
virtual~CDataSet();
private:
BOOL m_Actived;
_RecordsetPtr m_pRs;
};
CDataSet::CDataSet()
{
m_pRs.CreateInstance("ADODB.RecordSet");
m_Actived=FALSE;
}
CDataSet::~CDataSet()
{
m_pRs.Release();
}
BOOL CDataSet::Open(CString SQL)
{
ASSERT(m_cnn);
ASSERT(m_cnn->m_Actived);
try
{
m_pRs->Open(_variant_t(SQL), _variant_t(m_cnn->m_pConn,true), adOpenStatic, adLockOptimistic, adCmdText);
m_Actived=TRUE;
returnTRUE;
}
catch(_com_error&e)
{
returnFALSE;
}
}
voidCDataSet::MoveFirst()
{
m_pRs->MoveFirst();
}
voidCDataSet::MoveLast()
{
m_pRs->MoveLast();
}
voidCDataSet::MoveNext()
{
m_pRs->MoveNext();
}
voidCDataSet::MovePrevious()
{
m_pRs->MovePrevious();
}
BOOL CDataSet::IsEOF()
{
returnm_pRs->EndOfFile;
}
BOOL CDataSet::IsBOF()
{
returnm_pRs->BOF;
}
CString CDataSet::GetAsString(CString FieldName)
{
ASSERT(!IsBOF()&&!IsEOF());
_variant_t vValue=m_pRs->Fields->Item[_variant_t(FieldName)]->Value;
//如果为空值则返回空
if((V_VT(&vValue)==VT_NULL)||(V_VT(&vValue)==VT_EMPTY))
{
return"";
}
return_com_util::ConvertBSTRToString(_bstr_t(vValue));
}
BOOL CDataSet::LoadData()
{
returnFALSE;
}
voidCDataSet::Close()
{
if(m_Actived)
{
m_pRs->Close();
}
}
voidCDataSet::SetAsString(CString Field, CString Value)
{
ASSERT(!IsBOF()&&!IsEOF());
m_pRs->Fields->Item[_variant_t(Field)]->Value=_variant_t(Value);
}
voidCDataSet::Update()
{
m_pRs->Update();
}
voidCDataSet::Delete()
{
m_pRs->Delete(adAffectCurrent);
}
相关函数:1)HRESULT Recordset15::Open (
const _variant_t & Source, const _variant_t & ActiveConnection,
enum CursorTypeEnum CursorType, enum LockTypeEnum LockType, long Options
)
Source:变体型,计算Command对象的变量名、SQL语句、表名、存储过程调用或持久Recordset文件名。
ActiveConnection:计算有效地Connection对象变量名或字符串,包含ConnectionString参数。
CursorType:确定提供者打开Recordset时应该使用的游标类型。
LockType:adLockReadOnly(默
认值,只读)、adLockPessimistic(保守式锁定)、adLockOptimistic(开放式锁定,只有使用Update时才锁定记
录)、adLockBatchOptimistic(开放式批更新)
Options:有adCmdText、adCmdTable、adCmdTableDirect和adCmdStoredProc等。
2)_variant_t vValue = m_pRs->Fields->Item[_variant_t(FieldName)]->Value;
_com_util::ConvertBSTRToString(_bstr_t(vValue));
m_pRs->Fields->Item[_variant_t(Field)]->Value = _variant_t(Value);
4、通用数据命令模块的实现
classCDBCommand
{
public:
//数据库连接对象
CDBConnection*m_cnn;
//执行SQL
BOOL ExecuteSQL(CString SQL);
CDBCommand();
virtual~CDBCommand();
};
CDBCommand::CDBCommand()
{
}
CDBCommand::~CDBCommand()
{
}
BOOL CDBCommand::ExecuteSQL(CString SQL)
{
ASSERT(m_cnn);
ASSERT(m_cnn->m_Actived);
try
{
m_cnn->m_pConn->Execute(_bstr_t(SQL), NULL, adCmdText);
returnTRUE;
}
catch(_com_error&e)
{
returnFALSE;
}
}
1)_RecordsetPtr Connection15::Execute ( _bstr_t CommandText, VARIANT * RecordsAffected, long Options )
CommandText:设置或返回包含提供者的命令。
RecordsAffected:可选,提供者向其返回操作所影响的记录书目。
Options:有adCmdText、adCmdTable、adCmdTableDirect和adCmdStoredProc等。5、借用表命令类——CBorrowCommand(继承了CDBCommand)
#include"DBCommand.h"
classCBorrowCommand :publicCDBCommand
{
public:
//进行借用
//返回值为借用号
CString DoBorrow();
//各项借用信息
CString m_MaterialNum;
CString m_Count;
CString m_Department;
CString m_Use;
CString m_State;
CString m_Jsr;
CString m_Lqr;
CBorrowCommand();
virtual~CBorrowCommand();
private:
//得到当前最大借用号
intGetMaxBorrowID();
};
CBorrowCommand::CBorrowCommand()
{
}
CBorrowCommand::~CBorrowCommand()
{
}
intCBorrowCommand::GetMaxBorrowID()
{
_RecordsetPtr pRs("ADODB.RecordSet");
pRs->Open(_variant_t("Select Max(ID) as MAXID From tblBorrow"), _variant_t(m_cnn->m_pConn,true), adOpenStatic, adLockOptimistic, adCmdText);
intnResult;
_variant_t vValue=pRs->Fields->Item[_variant_t("MAXID")]->Value;
//如果不为空,返回最大值;否则返回0
if(V_VT(&vValue)!=VT_NULL)
{
nResult=atoi(_bstr_t(vValue));
}
else
{
nResult=0;
}
pRs->Close();
returnnResult;
}
CString CBorrowCommand::DoBorrow()
{
//得到当前时间
CString strData;
CTime time=CTime::GetCurrentTime();
strData.Format("%d-%d-%d %d:%d:%d",
time.GetYear(),
time.GetMonth(),
time.GetDay(),
time.GetHour(),
time.GetMinute(),
time.GetSecond());
CString strSQL;
//得到新的借用ID
intnMaxID=GetMaxBorrowID();
nMaxID++;
CString strNewID;
strNewID.Format("%d", nMaxID);
strSQL="Insert into tblBorrow([ID], [MaterialNum], [Count], [Department], [Data], [Use], [State], [Jsr], [Lqr]) Values("
+strNewID+","
+m_MaterialNum+","
+m_Count+", \""
+m_Department+"\", #"
+strData+"#, \""
+m_Use+"\", \""
+m_State+"\", \""
+m_Jsr+"\", \""
+m_Lqr+"\")";
if(ExecuteSQL(strSQL))
{
returnstrNewID;
}
else
{
return"";
}
}
6、借用表访问类——CBorrowDataSet(继承了CDataSet)
#include"DataSet.h"
classCBorrowDataSet :publicCDataSet
{
public:
CString GetID();
CString GetMaterialNum();
voidSetCount(CString Value);
CString GetCount();
CString GetUse();
CString GetLqr();
CString GetState();
CString GetData();
CString GetDepartment();
CString GetJsr();
BOOL LoadDataByID(CString ID);
BOOL LoadData();
CBorrowDataSet();
virtual~CBorrowDataSet();
};
CBorrowDataSet::CBorrowDataSet()
{
}
CBorrowDataSet::~CBorrowDataSet()
{
}
BOOL CBorrowDataSet::LoadData()
{
returnOpen("Select * From tblBorrow");
}
BOOL CBorrowDataSet::LoadDataByID(CString ID)
{
returnOpen("Select * From tblBorrow Where ID ="+ID);
}
CString CBorrowDataSet::GetCount()
{
returnGetAsString("Count");
}
voidCBorrowDataSet::SetCount(CString Value)
{
SetAsString("Count", Value);
}
CString CBorrowDataSet::GetMaterialNum()
{
returnGetAsString("MaterialNum");
}
CString CBorrowDataSet::GetID()
{
returnGetAsString("ID");
}
CString CBorrowDataSet::GetJsr()
{
returnGetAsString("Jsr");
}
CString CBorrowDataSet::GetDepartment()
{
returnGetAsString("Department");
}
CString CBorrowDataSet::GetData()
{
returnGetAsString("Data");
}
CString CBorrowDataSet::GetState()
{
returnGetAsString("State");
}
CString CBorrowDataSet::GetLqr()
{
returnGetAsString("Lqr");
}
CString CBorrowDataSet::GetUse()
{
returnGetAsString("Use");
}
7、用户信息表访问类——CUserInfoDataSet(继承了CDataSet)
#include"DataSet.h"
classCUserInfoDataSet :publicCDataSet
{
public:
//得到密码
CString GetPassword();
//根据用户名加载数据
BOOL LoadData(CString UserName);
CUserInfoDataSet();
virtual~CUserInfoDataSet();
};
CUserInfoDataSet::CUserInfoDataSet()
{
}
CUserInfoDataSet::~CUserInfoDataSet()
{
}
BOOL CUserInfoDataSet::LoadData(CString UserName)
{
returnOpen("Select * From UserInfo Where Username like \""+ UserName +"\"");
}
CString CUserInfoDataSet::GetPassword()
{
returnGetAsString("Password");
}
8、主程序的初始化
classCWhMgrApp :publicCWinApp
{
public:
CDBConnection*m_pConn;
CWhMgrApp();
//Overrides
//ClassWizard generated virtual function overrides
//{{AFX_VIRTUAL(CWhMgrApp)
public:
virtualBOOL InitInstance();
virtualintExitInstance();
//}}AFX_VIRTUAL
//Implementation
//{{AFX_MSG(CWhMgrApp)
//NOTE - the ClassWizard will add and remove member functions here.
//DO NOT EDIT what you see in these blocks of generated code !
//}}AFX_MSG
DECLARE_MESSAGE_MAP()
};
CWhMgrApp::CWhMgrApp()
{
//TODO: add construction code here,
//Place all significant initialization in InitInstance
}
/**//
//The one and only CWhMgrApp object
CWhMgrApp theApp;
/**//
//CWhMgrApp initialization
BOOL CWhMgrApp::InitInstance()
{
//初始化Com
::CoInitialize(NULL);
//初始化数据库连接
m_pConn=newCDBConnection;
//得到当前程序所在的文件夹
CString strCommandLine=::GetCommandLine();
inti;
intnLen=strCommandLine.GetLength();
for(i=nLen-1; i>=0; i--)
{
CHAR a=strCommandLine.GetAt(i);
if(a=='\\')
{
break;
}
}
strCommandLine.ReleaseBuffer(i+1);
strCommandLine.Delete(0,1);
//打开数据库连接,数据库地址为:当前程序所在文件夹\WhMgrDB.mdb
if(!m_pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strCommandLine+"WhMgrDB.mdb;Persist Security Info=False"))
{
::AfxMessageBox("打开数据库失败!");
returnFALSE;
}
//用户登录
BOOL bLogined;
CLoginDlg loginDlg;
CUserInfoDataSet dsUserInfo;
dsUserInfo.m_cnn=m_pConn;
bLogined=FALSE;
//给3次登录机会
for(i=0; i<3; i++)
{
if(loginDlg.DoModal()==IDOK)
{
dsUserInfo.LoadData(loginDlg.m_strUserName);
if(!dsUserInfo.IsEOF())
{
if(dsUserInfo.GetPassword()==loginDlg.m_strPassword)
{
bLogined=TRUE;
break;
}
}
else
{
::AfxMessageBox("用户名或者密码不正确,请重试!");
}
dsUserInfo.Close();
}
else
{
break;
}
}
if(!bLogined)
{
returnFALSE;
}
AfxEnableControlContainer();
//Standard initialization
//If you are not using these features and wish to reduce the size
//of your final executable, you should remove from the following
//the specific initialization routines you do not need.
#ifdef _AFXDLL
Enable3dControls();//Call this when using MFC in a shared DLL
#else
Enable3dControlsStatic();//Call this when linking to MFC statically
#endif
CWhMgrDlg dlg;
//给dlg的m_pConn成员变量赋值
dlg.m_pConn=m_pConn;
m_pMainWnd=&dlg;
intnResponse=dlg.DoModal();
if(nResponse==IDOK)
{
//TODO: Place code here to handle when the dialog is
//dismissed with OK
}
elseif(nResponse==IDCANCEL)
{
//TODO: Place code here to handle when the dialog is
//dismissed with Cancel
}
//Since the dialog has been closed, return FALSE so that we exit the
//application, rather than start the application's message pump.
returnFALSE;
}
intCWhMgrApp::ExitInstance()
{
m_pConn->Close();
delete m_pConn;
::CoUninitialize();
returnCWinApp::ExitInstance();
}
1 )::CoInitialize(NULL);和::CoUninitialize();:ADO库是一组COM动态库,这意味应用程序在调用ADO前,必须初始化OLE/COM库环境。在MFC应用程序里,一个比较好的方法是在应用程序主类的InitInstance成员函数里初始化OLE/COM库环境。也可用
if(!AfxOleInit())//这就是初始化COM库{AfxMessageBox(“OLE初始化出错!”);return FALSE;}
2)得到当前程序所在的文件夹:
CString strCommandLine = ::GetCommandLine();
int i;
int nLen = strCommandLine.GetLength();
for(i = nLen - 1; i >= 0; i--)
{
CHAR a = strCommandLine.GetAt(i);
if(a == '\\')
{
break;
}
}
strCommandLine.ReleaseBuffer(i + 1);
strCommandLine.Delete(0, 1);
9、归还的实现
classCOutDialog :publicCDialog
{
//Construction
public:
CDBConnection*m_pConn;
CString m_strMaterialNum;
COutDialog(CWnd*pParent=NULL);//standard constructor
//Dialog Data
//{{AFX_DATA(COutDialog)
enum
{ IDD=IDD_OUT_DIALOG };
CStatic m_Unit;
CComboBox m_MaterialNum;
BOOL m_bNeedReturn;
CString m_strCount;
CString m_strDepartment;
CString m_strJsr;
CString m_strLqr;
CString m_strState;
CString m_strUse;
//}}AFX_DATA
//Overrides
//ClassWizard generated virtual function overrides
//{{AFX_VIRTUAL(COutDialog)
protected:
virtualvoidDoDataExchange(CDataExchange*pDX);//DDX/DDV support
//}}AFX_VIRTUAL
//Implementation
protected:
//Generated message map functions
//{{AFX_MSG(COutDialog)
virtualvoidOnOK();
afx_msgvoidOnSelchangeComboMaterialnum();
//}}AFX_MSG
DECLARE_MESSAGE_MAP()
};
COutDialog::COutDialog(CWnd*pParent/**//*=NULL*/)
: CDialog(COutDialog::IDD, pParent)
{
//{{AFX_DATA_INIT(COutDialog)
m_bNeedReturn=FALSE;
m_strCount=_T("");
m_strDepartment=_T("");
m_strJsr=_T("");
m_strLqr=_T("");
m_strState=_T("");
m_strUse=_T("");
//}}AFX_DATA_INIT
}
voidCOutDialog::DoDataExchange(CDataExchange*pDX)
{
CDialog::DoDataExchange(pDX);
//{{AFX_DATA_MAP(COutDialog)
DDX_Control(pDX, IDC_STATIC_UNIT, m_Unit);
DDX_Control(pDX, IDC_COMBO_MATERIALNUM, m_MaterialNum);
DDX_Check(pDX, IDC_CHECK_NEEDRETURN, m_bNeedReturn);
DDX_Text(pDX, IDC_EDIT_COUNT, m_strCount);
DDX_Text(pDX, IDC_EDIT_DEPARTMENT, m_strDepartment);
DDX_Text(pDX, IDC_EDIT_JSR, m_strJsr);
DDX_Text(pDX, IDC_EDIT_LQR, m_strLqr);
DDX_Text(pDX, IDC_EDIT_STATE, m_strState);
DDX_Text(pDX, IDC_EDIT_USE, m_strUse);
//}}AFX_DATA_MAP
CMaterialInfoDataSet dsMaterialInfo;
dsMaterialInfo.m_cnn=m_pConn;
if(!dsMaterialInfo.LoadData())
{
::AfxMessageBox("加载物资信息失败!");
}
else
{
while(!dsMaterialInfo.IsEOF())
{
m_MaterialNum.AddString(dsMaterialInfo.GetMaterialNum());
dsMaterialInfo.MoveNext();
}
dsMaterialInfo.Close();
}
if(m_MaterialNum.GetCount()>0)
{
m_MaterialNum.SetCurSel(0);
OnSelchangeComboMaterialnum();
}
}
BEGIN_MESSAGE_MAP(COutDialog, CDialog)
//{{AFX_MSG_MAP(COutDialog)
ON_CBN_SELCHANGE(IDC_COMBO_MATERIALNUM, OnSelchangeComboMaterialnum)
//}}AFX_MSG_MAP
END_MESSAGE_MAP()
/**//
//COutDialog message handlers
voidCOutDialog::OnOK()
{
intnIndex=m_MaterialNum.GetCurSel();
if(nIndex>=0)
{
m_MaterialNum.GetLBText(nIndex, m_strMaterialNum);
}
else
{
m_strMaterialNum="0";
}
CDialog::OnOK();
}
voidCOutDialog::OnSelchangeComboMaterialnum()
{
intnIndex=m_MaterialNum.GetCurSel();
if(nIndex>=0)
{
CMaterialInfoDataSet dsMaterialInfo;
dsMaterialInfo.m_cnn=m_pConn;
CString strNum;
m_MaterialNum.GetLBText(nIndex, strNum);
dsMaterialInfo.LoadDataByNum(strNum);
if(!dsMaterialInfo.IsEOF())
{
m_Unit.SetWindowText(dsMaterialInfo.GetUnit());
}
dsMaterialInfo.Close();
}
}
1)给部分程序不仅给出了关于借出表的实现,还参有出库的实现(关于出库的出库表命令类和出库表访问类这边没介绍,与借出表的差不多)。如有疑问可以联系我。
这次利用一个工程中的一部分代码对VC中ADO做了比较详细的介绍,如有错误希望给与指正!!!!!!!!!!