vs2008 MFC访问Access 2010数据库

时间：2020-12-24 13:14:15

    MSDN给出了各种语言访问Access 2010的详细介绍：点击打开链接。下图列出了各种语言访问Access 2010的接口。

vs2008 MFC访问Access 2010数据库

我在VS2008下使用MFC下的ODBC相关接口访问Access 2010数据库。

Demo中使用MFC访问数据库accsess2010数据库AccessDemo.accdb下有student表，表中有用户编号、电话号码、姓名、省份四列信息，表中有包含这四列信息的多个记录。
MFC下使用CDatabase、CRecordset、CDBException三个类来访问数据库。其中CDatabase主要用来打开和关闭数据库以及执行SQL指令；CRecordset用来执行查询、删除、添加记录；CDBException用来处理异常信息。
1.使用CDatabase、CRecordset、CDBException需要包含头文件：
#include "afxwin.h"
2.打开数据库
CDatabase db;
BOOL result = TRUE;
LPCTSTR lpszConnect = 
    _T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\AccessDemo.accdb;PWD=asdfg;");
    TRY
    {
        result = db.OpenEx(lpszConnect, CDatabase::openExclusive|CDatabase::noOdbcDialog);
    }
    catch(CDBException * e){
MessageBox(e->m_strError);
    }
3.关闭数据库
    TRY
    {
        db.Close();
    }
    catch(CDBException * e){
MessageBox(e->m_strError);
    }
4.插入一个新的记录，这里使用CDatabase.ExecuteSQL()来执行SQL语句插入记录到表中。
    UpdateData(TRUE);
    CString insert,temp;
    insert=_T("insert into student(用户编号,电话号码,姓名,省份) values(");
    temp.Format(_T("%d"),m_idlint);
    insert+=temp+_T(",\'")+m_phonenumstr+_T("\',\'")+m_namestr+_T("\',\'")+m_provincestr+_T("\')");
    try{
    db.ExecuteSQL(insert);
    }
    catch(CDBException *e){
    MessageBox(e->m_strError);
    }
5.使用CRecordset查询记录符合条件的记录。
UpdateData(TRUE);
BOOL result = TRUE;
CDBVariant var;
CString value;
CString query =_T("SELECT student.[用户编号], student.[电话号码],student.[姓名], student.[省份] FROM student where student.[电话号码]='");
query+=m_phonenumstr+_T("'");
MessageBox(query);
CRecordset rs(&db); 
try{
result = rs.Open(CRecordset::dynaset, query, CRecordset::none);
if(TRUE==result){
CODBCFieldInfo fInfo; 
short sFieldCount = rs.GetODBCFieldCount();
int rowCount = 0;
while (!rs.IsEOF())
{
for (short column = 0; column < sFieldCount; column++)
{
rs.GetFieldValue(column, var);
switch (var.m_dwType)
{
case DBVT_STRING:
value.Format(_T("%s"), var.m_pstring->GetBuffer(var.m_pstring->GetLength()));
break;
case DBVT_ASTRING:
value.Format(_T("%s"), var.m_pstringA->GetBuffer(var.m_pstringA->GetLength()));
break;
case DBVT_WSTRING:
value.Format(_T("%s"), var.m_pstringW->GetBuffer(var.m_pstringW->GetLength()));
break;
case DBVT_LONG:
value.Format(_T("%ld"), var.m_lVal);
break;
case DBVT_SHORT:
value.Format(_T("%d"), var.m_iVal);
break;
default:
value = _T("");
}
switch(column){
case 0:
m_idlint=var.m_lVal;
break;
case 1:
m_phonenumstr=*var.m_pstring;
break;
case 2:
m_namestr=*var.m_pstring;
break;
case 3:
m_provincestr=*var.m_pstring;
break;
}
}
rowCount++;
rs.MoveNext();
}
}
}
catch(CDBException * e){
MessageBox(e->m_strError);
}
UpdateData(FALSE);

数据库AccessDemo.accdb中的表student数据如下所示：

vs2008 MFC访问Access 2010数据库

Demo主界面如下所示：

vs2008 MFC访问Access 2010数据库

打开数据库并查询student表如下所示：

vs2008 MFC访问Access 2010数据库

插入一条新的数据如下所示：

vs2008 MFC访问Access 2010数据库

插入新的记录后的数据库AccessDemo.accdb中的表student数据如下所示：

vs2008 MFC访问Access 2010数据库

主要代码如下所示：

AccessDemoDlg.h：

// AccessDemoDlg.h : header file
//

#pragma once
//数据库操作类的头文件
#include <afxdb.h>
#include "afxcmn.h"
#include "afxwin.h"

// CAccessDemoDlg dialog
class CAccessDemoDlg : public CDialog
{
// Construction
public:
	CAccessDemoDlg(CWnd* pParent = NULL);	// standard constructor

// Dialog Data
	enum { IDD = IDD_ACCESSDEMO_DIALOG };

	protected:
	virtual void DoDataExchange(CDataExchange* pDX);	// DDX/DDV support


// Implementation
protected:
	HICON m_hIcon;

	// Generated message map functions
	virtual BOOL OnInitDialog();
	afx_msg void OnSysCommand(UINT nID, LPARAM lParam);
	afx_msg void OnPaint();
	afx_msg HCURSOR OnQueryDragIcon();
	DECLARE_MESSAGE_MAP()
public:
	afx_msg void OnBnClickedOpenButton();
private:
    CDatabase db;
	CListCtrl m_accesslist;
	CButton m_openbutton;
	CButton m_insertbutton;
	CButton m_querybutton;
	CEdit m_idedit;
	CEdit m_phonenumedit;
	CEdit m_nameedit;
	CEdit m_provinceedit;
	CStatic m_idstatic;
	CStatic m_phonenumstatic;
	CStatic m_namestatic;
	CStatic m_provincestatic;
	long m_idlint;
	CString m_phonenumstr;
	CString m_namestr;
	CString m_provincestr;
	void InitCtrl();
	CRect rect;
public:
	afx_msg void OnDestroy();
	afx_msg void OnBnClickedInsertButton();

	afx_msg void OnBnClickedQueryButton();


};
AccessDemoDlg.cpp： 

// AccessDemoDlg.cpp : implementation file
//

#include "stdafx.h"
#include "AccessDemo.h"
#include "AccessDemoDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#endif


// CAboutDlg dialog used for App About

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	enum { IDD = IDD_ABOUTBOX };

	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support

// Implementation
protected:
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
END_MESSAGE_MAP()


// CAccessDemoDlg dialog




CAccessDemoDlg::CAccessDemoDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CAccessDemoDlg::IDD, pParent)
	, m_idlint(0)
	, m_phonenumstr(_T(""))
	, m_namestr(_T(""))
	, m_provincestr(_T(""))
{
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CAccessDemoDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_ACCESS_LIST, m_accesslist);
	DDX_Control(pDX, IDC_OPEN_BUTTON, m_openbutton);
	DDX_Control(pDX, IDC_INSERT_BUTTON, m_insertbutton);
	DDX_Control(pDX, IDC_QUERY_BUTTON, m_querybutton);
	DDX_Control(pDX, IDC_ID_EDIT, m_idedit);
	DDX_Control(pDX, IDC_PHONENUM_EDIT, m_phonenumedit);
	DDX_Control(pDX, IDC_EDIT3, m_nameedit);
	DDX_Control(pDX, IDC_PROVINCE_EDIT, m_provinceedit);
	DDX_Control(pDX, IDC_ID_STATIC, m_idstatic);
	DDX_Control(pDX, IDC_PHONENUM_STATIC, m_phonenumstatic);
	DDX_Control(pDX, IDC_NAME_STATIC, m_namestatic);
	DDX_Control(pDX, IDC_PROVINCE_STATIC, m_provincestatic);
	DDX_Text(pDX, IDC_ID_EDIT, m_idlint);
	DDX_Text(pDX, IDC_PHONENUM_EDIT, m_phonenumstr);
	DDX_Text(pDX, IDC_NAME_EDIT, m_namestr);
	DDX_Text(pDX, IDC_PROVINCE_EDIT, m_provincestr);
}

BEGIN_MESSAGE_MAP(CAccessDemoDlg, CDialog)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	//}}AFX_MSG_MAP
	ON_BN_CLICKED(IDC_OPEN_BUTTON, &CAccessDemoDlg::OnBnClickedOpenButton)
	ON_WM_DESTROY()
	ON_BN_CLICKED(IDC_INSERT_BUTTON, &CAccessDemoDlg::OnBnClickedInsertButton)
	ON_BN_CLICKED(IDC_QUERY_BUTTON, &CAccessDemoDlg::OnBnClickedQueryButton)
END_MESSAGE_MAP()


// CAccessDemoDlg message handlers

BOOL CAccessDemoDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon

	// TODO: Add extra initialization here
	InitCtrl();

	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CAccessDemoDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CAccessDemoDlg::OnPaint()
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, reinterpret_cast<WPARAM>(dc.GetSafeHdc()), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this function to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CAccessDemoDlg::OnQueryDragIcon()
{
	return static_cast<HCURSOR>(m_hIcon);
}

void CAccessDemoDlg::OnBnClickedOpenButton()
{
	// TODO: Add your control notification handler code here
	BOOL result = TRUE;
	CString cmpstr=_T("请打开数据库"),temp;
	LV_COLUMN lvcolumn;
	LV_ITEM lvitem;
	TCHAR szConnect[1000]=_T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=");
	TCHAR currentdirectory[MAX_PATH];
	::GetModuleFileNameW(NULL,temp.GetBuffer(MAX_PATH+1),MAX_PATH);
	temp.ReleaseBuffer();
	temp=temp.Left(temp.ReverseFind(_T('\\')));
	wcscpy(currentdirectory,temp.GetBuffer(temp.GetLength()));
	temp.ReleaseBuffer();
	wcscat(currentdirectory,_T("\\AccessDemo.accdb;PWD=asdfg;"));
	wcscat(szConnect,currentdirectory);
	try{
		TCHAR buttonname[255]={_T('0'),};
		m_openbutton.GetWindowTextW(buttonname,255);
		if(cmpstr.Compare(buttonname)==0){
			result = db.OpenEx(szConnect,CDatabase::openExclusive|CDatabase::noOdbcDialog);//CDatabase::openReadOnly|CDatabase::noOdbcDialog);
			if (TRUE == result)
			{
				m_openbutton.SetWindowTextW(_T("请关闭数据库"));
				LPCTSTR query =_T("SELECT student.[用户编号], student.[电话号码],student.[姓名], student.[省份] FROM student ORDER BY student.[用户编号] ASC;");
					//DESC;");
				CRecordset rs(&db); 
				result = rs.Open(CRecordset::dynaset, query, CRecordset::none);
				if(TRUE==result){
					CODBCFieldInfo fInfo; 
					short sFieldCount = rs.GetODBCFieldCount();
					//删除列表控件原来所有行
					m_accesslist.DeleteAllItems();
					//删除列表控件原来所有列
					while(m_accesslist.GetHeaderCtrl()->GetItemCount()>0){
						m_accesslist.DeleteColumn(m_accesslist.GetHeaderCtrl()->GetItemCount()-1);
					}
					m_accesslist.SetRedraw(TRUE);
					m_accesslist.Invalidate();
					m_accesslist.UpdateWindow();
					//获取数据库中表列信息
					if (sFieldCount > 0)
					{
						for (short column = 0; column < sFieldCount; column++)
						{
							CODBCFieldInfo fInfo;
							rs.GetODBCFieldInfo(column, fInfo);
							//MessageBox(fInfo.m_strName);
							lvcolumn.mask=LVCF_FMT|LVCF_TEXT|LVCF_WIDTH;
							lvcolumn.fmt=LVCFMT_LEFT;
							lvcolumn.pszText=fInfo.m_strName.GetBuffer(fInfo.m_strName.GetLength());
							lvcolumn.iSubItem=column;
							lvcolumn.cx=rect.Width()/sFieldCount;
							fInfo.m_strName.ReleaseBuffer();
							m_accesslist.InsertColumn(column,&lvcolumn);

						}
					}
					CDBVariant var;
					CString value;
					int rowCount = 0;
					int iactualitem;
					//获取数据库中表行信息
					while (!rs.IsEOF())
					{
						for (short column = 0; column < sFieldCount; column++)
						{
							rs.GetFieldValue(column, var);
							switch (var.m_dwType)
							{
								case DBVT_STRING:
									value.Format(_T("%s"), var.m_pstring->GetBuffer(var.m_pstring->GetLength()));
									break;
								case DBVT_ASTRING:
									value.Format(_T("%s"), var.m_pstringA->GetBuffer(var.m_pstringA->GetLength()));
									break;
								case DBVT_WSTRING:
									value.Format(_T("%s"), var.m_pstringW->GetBuffer(var.m_pstringW->GetLength()));
									break;
								case DBVT_LONG:
									value.Format(_T("%ld"), var.m_lVal);
									break;
								case DBVT_SHORT:
									value.Format(_T("%d"), var.m_iVal);
									break;
								default:
									value = _T("");
							}
							if(column==0){
								lvitem.mask=LVIF_TEXT;
								lvitem.iItem=rowCount;
								lvitem.iSubItem=column;
								lvitem.pszText=value.GetBuffer(value.GetLength());
								value.ReleaseBuffer();
								lvitem.cchTextMax=value.GetLength();
								m_accesslist.GetItem(&lvitem);
								lvitem.iItem=m_accesslist.GetItemCount();
								iactualitem=m_accesslist.InsertItem(&lvitem);
							}else{
								lvitem.mask=LVIF_TEXT;
								lvitem.iItem=rowCount;
								lvitem.iSubItem=column;
								lvitem.pszText=value.GetBuffer(value.GetLength());
								value.ReleaseBuffer();
								lvitem.cchTextMax=value.GetLength();
								lvitem.iItem=iactualitem;
								m_accesslist.SetItem(&lvitem);
							}
						}
						rowCount++;
						rs.MoveNext();
					}
				}
				m_insertbutton.EnableWindow(TRUE);
				m_querybutton.EnableWindow(TRUE);
			}
			else{
				MessageBox(_T("打开数据库失败"));
				m_insertbutton.EnableWindow(FALSE);
				m_querybutton.EnableWindow(FALSE);

			}
		}
		else{
			db.Close();
			m_openbutton.SetWindowTextW(_T("请打开数据库"));
			m_insertbutton.EnableWindow(FALSE);
			m_querybutton.EnableWindow(FALSE);

		}
	}
	catch(CDBException * e){
		MessageBox(e->m_strError);
	}
}

void CAccessDemoDlg::InitCtrl(){

	CFont nFont ,* nOldFont;
	nFont.CreateFont(20,15,0,0,0,FALSE,FALSE,0,0,0,0,0,0,_TEXT("宋体"));//创建字体 
	m_accesslist.SetFont(&nFont);
	m_openbutton.SetFont(&nFont);
	m_insertbutton.SetFont(&nFont);
	m_querybutton.SetFont(&nFont);
	m_idedit.SetFont(&nFont);
	m_phonenumedit.SetFont(&nFont);
	m_nameedit.SetFont(&nFont);
	m_provinceedit.SetFont(&nFont);
	m_idstatic.SetFont(&nFont);
	m_phonenumstatic.SetFont(&nFont);
	m_namestatic.SetFont(&nFont);
	m_provincestatic.SetFont(&nFont);
	m_openbutton.SetWindowTextW(_T("请打开数据库"));
	m_insertbutton.SetWindowTextW(_T("插入记录到数据库"));
	m_querybutton.SetWindowTextW(_T("根据手机号码查询"));
	m_idstatic.SetWindowTextW(_T("用户编号"));
	m_phonenumstatic.SetWindowTextW(_T("电话号码"));
	m_namestatic.SetWindowTextW(_T("姓名"));
	m_provincestatic.SetWindowTextW(_T("省份"));
	m_insertbutton.EnableWindow(FALSE);
	m_querybutton.EnableWindow(FALSE);
	DWORD dwstyle=m_accesslist.GetExtendedStyle();
	//dwstyle|=(LVS_EX_ONECLICKACTIVATE|LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES|LVS_EX_TRACKSELECT|LVS_EDITLABELS|LVS_NOLABELWRAP);
	dwstyle|=LVS_EX_FULLROWSELECT;
	m_accesslist.SetExtendedStyle(dwstyle);
	m_accesslist.GetWindowRect(&rect);
}
void CAccessDemoDlg::OnDestroy()
{
	CDialog::OnDestroy();

	// TODO: Add your message handler code here
	CString cmpstr=_T("请关闭数据库");
	TCHAR buttonname[255]={_T('0'),};
	m_openbutton.GetWindowTextW(buttonname,255);
	if(cmpstr.Compare(buttonname)==0){
		db.Close();
	}
}

//插入一个记录
void CAccessDemoDlg::OnBnClickedInsertButton()
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	CString insert,temp;
	insert=_T("insert into student(用户编号,电话号码,姓名,省份) values(");
	temp.Format(_T("%d"),m_idlint);
	insert+=temp+_T(",\'")+m_phonenumstr+_T("\',\'")+m_namestr+_T("\',\'")+m_provincestr+_T("\')");
	try{
	db.ExecuteSQL(insert);
	}
	catch(CDBException *e){
		MessageBox(e->m_strError);
	}
}
//根据手机号码查询记录
void CAccessDemoDlg::OnBnClickedQueryButton()
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	BOOL result = TRUE;
	CDBVariant var;
	CString value;
	CString query =_T("SELECT student.[用户编号], student.[电话号码],student.[姓名], student.[省份] FROM student where student.[电话号码]='");
	query+=m_phonenumstr+_T("'");
	MessageBox(query);
	CRecordset rs(&db); 
	try{
		result = rs.Open(CRecordset::dynaset, query, CRecordset::none);
		if(TRUE==result){
			CODBCFieldInfo fInfo; 
			short sFieldCount = rs.GetODBCFieldCount();
			int rowCount = 0;
			while (!rs.IsEOF())
			{
				for (short column = 0; column < sFieldCount; column++)
				{
					rs.GetFieldValue(column, var);
					switch (var.m_dwType)
					{
						case DBVT_STRING:
							value.Format(_T("%s"), var.m_pstring->GetBuffer(var.m_pstring->GetLength()));
							break;
						case DBVT_ASTRING:
							value.Format(_T("%s"), var.m_pstringA->GetBuffer(var.m_pstringA->GetLength()));
							break;
						case DBVT_WSTRING:
							value.Format(_T("%s"), var.m_pstringW->GetBuffer(var.m_pstringW->GetLength()));
							break;
						case DBVT_LONG:
							value.Format(_T("%ld"), var.m_lVal);
							break;
						case DBVT_SHORT:
							value.Format(_T("%d"), var.m_iVal);
							break;
						default:
							value = _T("");
					}
					switch(column){
						case 0:
							m_idlint=var.m_lVal;
							break;
						case 1:
							m_phonenumstr=*var.m_pstring;
							break;
						case 2:
							m_namestr=*var.m_pstring;
							break;
						case 3:
							m_provincestr=*var.m_pstring;
							break;
					}
				}
				rowCount++;
				rs.MoveNext();
			}
		}
	}
	catch(CDBException * e){
		MessageBox(e->m_strError);
	}
	UpdateData(FALSE);
}




标签：

相关文章