通过小练习掌握MFC知识点之起步篇-MFC与数据库的交互

数据库tangentDemo中的表employee

1.             编程实现增加、删除、修改employee中的数据，并把结果显示在list中

2.             编程实现查询出Title为sales，TitleofCourtesy为MS的所有职员的FirstName, LastName，Title，TitleofCourtesy并把结果显示到list中

ADO方式访问Access数据库，引用封装好的数据库操作类（点我下载点我下载TGAdoKernel.zip）。

最终效果如下：

代码预览：

Exercise6Dlg.h

protected://初始化ListCtrlvoid InitListCtrl();//步骤1：初始化数据库连接    BOOL InitAdoConn(CUIIADORXServer &dbServer, LPCTSTR lpPath);//刷新CListCtrl的内容，从数据库读入数据void RefreshData(const CString& strSql = _T("select * from Employee"));  protected:HICON m_hIcon;CListCtrl m_lstCtrl;CUIIADORXServer m_dbServer;int m_nSel;CButton m_btnDel;CButton m_btnMod;

Exercise6Dlg.cpp

void CExercise6Dlg::InitListCtrl(){m_lstCtrl.DeleteAllItems();CRect rect;m_lstCtrl.GetWindowRect(rect);m_lstCtrl.SetExtendedStyle(LVS_EX_GRIDLINES | LVS_EX_FULLROWSELECT | LVS_EX_DOUBLEBUFFER);m_lstCtrl.InsertColumn(0, _T("ID"),LVCFMT_LEFT, (int)(rect.Width() * 0.13));m_lstCtrl.InsertColumn(1, _T("FirstName"),LVCFMT_LEFT, (int)(rect.Width() * 0.25));m_lstCtrl.InsertColumn(2, _T("LastName"),LVCFMT_LEFT, (int)(rect.Width() * 0.25));m_lstCtrl.InsertColumn(3, _T("Title"),LVCFMT_LEFT, (int)(rect.Width() * 0.2));m_lstCtrl.InsertColumn(4, _T("TitleCourt"),LVCFMT_LEFT, (int)(rect.Width() * 0.17));RefreshData();}BOOL CExercise6Dlg::InitAdoConn(CUIIADORXServer &dbServer, LPCTSTR lpPath ){if (NULL == lpPath)return FALSE;if (_tcsicmp(dbServer.GetDatabase(), lpPath) != 0){CString csConnection;csConnection.Format(_T("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;UID='';PWD=''"), lpPath);if (!dbServer.InitADOServer(csConnection)){CString csReport;csReport.Format(_T("%s %s"), lpPath, _T("数据库连接初始化失败!"));AfxMessageBox(csReport);return FALSE;}dbServer.SetDatabase(lpPath);}return TRUE;}void CExercise6Dlg::RefreshData(const CString& strSql){m_lstCtrl.DeleteAllItems();//读取数据库中的信息CUIIValueTable uiivTable;m_dbServer.GetSelData(strSql,uiivTable);//显示到ListCtrl上uiivTable.ShowToListCtrl(&m_lstCtrl);}void CExercise6Dlg::OnBnClickedBtnIns(){int nCount = m_lstCtrl.GetItemCount();SEmployeeInfo emp;CInsModDlg dlg(FALSE,&emp);dlg.DoModal();dlg.GetEmployeeInfo(emp);CString strSql;strSql = _T("insert into Employee(FirstName,LastName,Title,TitleofCourtesy) values('")+ emp.strFirstname + _T("','") + emp.strLastname + _T("','")+ emp.strTitle + _T("','") + emp.strTitlecourt + _T("')");m_dbServer.ExecuteSQL(strSql);RefreshData();//刷新ListCtrl的内容}void CExercise6Dlg::OnBnClickedBtnDel(){if (IDYES == MessageBox(_T("确定删除吗？\n删除后不可恢复"),NULL,MB_YESNO | MB_ICONWARNING)){CString strID = m_lstCtrl.GetItemText(m_nSel,0);CString strSql;strSql.Format(_T("delete from Employee where ID = %d"),_ttoi(strID));m_dbServer.ExecuteSQL(strSql);RefreshData();int nState = m_nSel;if ( m_nSel == m_lstCtrl.GetItemCount())//删除末尾行时，高亮选择项在nState = m_nSel - 1;elsenState = m_nSel;m_lstCtrl.SetItemState(nState, LVIS_FOCUSED|LVIS_SELECTED, LVIS_FOCUSED|LVIS_SELECTED);m_lstCtrl.Invalidate(); //没有此句有问题，有此句LISTCTRL控件会闪烁}}void CExercise6Dlg::OnBnClickedBtnMod(){SEmployeeInfo emp;emp.strFirstname = m_lstCtrl.GetItemText(m_nSel,1);emp.strLastname = m_lstCtrl.GetItemText(m_nSel,2);emp.strTitle = m_lstCtrl.GetItemText(m_nSel,3);emp.strTitlecourt = m_lstCtrl.GetItemText(m_nSel,4);CInsModDlg modDlg(TRUE,&emp);modDlg.DoModal();emp.Clear();modDlg.GetEmployeeInfo(emp);CString strID;strID = m_lstCtrl.GetItemText(m_nSel,0);int nID = _ttoi(strID);CString strSql;strSql.Format(_T("update Employee set FirstName = '%s',LastName = '%s',\ Title = '%s', TitleofCourtesy = '%s' where ID = %d"), emp.strFirstname,emp.strLastname,emp.strTitle,emp.strTitlecourt,nID);m_dbServer.ExecuteSQL(strSql);RefreshData();}void CExercise6Dlg::OnBnClickedBtnQuery(){    CString strTitle = _T("sales");CString strTitleCourt = _T("ms");CString strSql;strSql.Format(_T("select * from Employee where Title = '%s' and TitleofCourtesy = '%s'"), strTitle, strTitleCourt);RefreshData(strSql);}void CExercise6Dlg::OnNMClickList(NMHDR *pNMHDR, LRESULT *pResult){// TODO: 在此添加控件通知处理程序代码*pResult = 0;LPNMITEMACTIVATE pNMItem = reinterpret_cast<LPNMITEMACTIVATE>(pNMHDR);if (-1 == pNMItem->iItem)//当前没有选中行   //if (m_lstCtrl.GetSelectedCount() <= 0){m_btnDel.EnableWindow(FALSE);m_btnMod.EnableWindow(FALSE);}else{m_btnDel.EnableWindow(TRUE);m_btnMod.EnableWindow(TRUE);m_nSel = pNMItem->iItem;}}void CExercise6Dlg::OnNMCustomdrawList(NMHDR *pNMHDR, LRESULT *pResult){NMLVCUSTOMDRAW* pLVCD = reinterpret_cast<NMLVCUSTOMDRAW*>( pNMHDR );*pResult = CDRF_DODEFAULT;if ( CDDS_PREPAINT == pLVCD->nmcd.dwDrawStage ){*pResult = CDRF_NOTIFYITEMDRAW;}else if ( CDDS_ITEMPREPAINT == pLVCD->nmcd.dwDrawStage ){*pResult = CDRF_NOTIFYSUBITEMDRAW;}else if ( (CDDS_ITEMPREPAINT | CDDS_SUBITEM) == pLVCD->nmcd.dwDrawStage ){COLORREF clrNewTextColor, clrNewBkColor;int nItem = static_cast<int>( pLVCD->nmcd.dwItemSpec );POSITION pos = m_lstCtrl.GetFirstSelectedItemPosition();int index = m_lstCtrl.GetNextSelectedItem(pos);if (index == nItem)//如果要刷新的项为当前选择的项，则将文字设为白色，背景色设为蓝色{clrNewTextColor = RGB(255,255,255);        //Set the text to whiteclrNewBkColor = RGB(49,106,197);        //Set the background color to blue}else{clrNewTextColor = RGB(0,0,0);clrNewBkColor = RGB(255,255,255);}//else if (nItem % 2) //奇偶行隔行变色//{//clrNewTextColor = RGB(0,0,0);        //set the text black//clrNewBkColor = RGB(200,200,220);    //leave the background color white//}//else//{//clrNewTextColor = RGB(0,0,0);        //set the text black//clrNewBkColor = RGB(255,255,255);    //leave the background color white//}pLVCD->clrText = clrNewTextColor;pLVCD->clrTextBk = clrNewBkColor;*pResult = CDRF_DODEFAULT;}}

CInsModDlg.h

struct SEmployeeInfo{CString strFirstname;CString strLastname;CString strTitle;CString strTitlecourt;SEmployeeInfo(){Clear();}//运算符重载SEmployeeInfo& operator= (const SEmployeeInfo& emp){// if (this = &emp)// return *this;strFirstname = emp.strFirstname;strLastname = emp.strLastname;strTitle = emp.strTitle;strTitlecourt = emp.strTitlecourt;return *this;}void Clear(){strFirstname.Empty();strLastname.Empty();strTitle.Empty();strTitlecourt.Empty();}};//获取用户输入的雇员信息void GetEmployeeInfo(SEmployeeInfo &employeeInfo){employeeInfo = m_employeeInfo;}

CInsModDlg.cpp

void CInsModDlg::OnBnClickedOk(){// TODO: 在此添加控件通知处理程序代码    m_edtFirname.GetWindowText(m_employeeInfo.strFirstname);m_edtLastname.GetWindowText(m_employeeInfo.strLastname);m_edtTitle.GetWindowText(m_employeeInfo.strTitle);m_edtTitleC.GetWindowText(m_employeeInfo.strTitlecourt);bool bFirname = m_employeeInfo.strFirstname.IsEmpty();bool bLastname = m_employeeInfo.strLastname.IsEmpty();bool bTitle = m_employeeInfo.strTitle.IsEmpty();bool bTitleC = m_employeeInfo.strTitlecourt.IsEmpty();if (!bFirname && !bLastname && !bTitle && !bTitleC)//全部不为空{OnOK();        //OnBnClickedBtnClr}else{if (bFirname){MessageBox(_T("Firstname不能为空"));m_edtFirname.SetFocus();}else if (bLastname){MessageBox(_T("Lastname不能为空"));m_edtLastname.SetFocus();}else if (bTitle){MessageBox(_T("Title不能为空"));m_edtLastname.SetFocus();}else if (bTitleC){MessageBox(_T("TitleofCourtesy不能为空"));m_edtLastname.SetFocus();}}}

源码地址：http://pan.baidu.com/share/link?shareid=478825&uk=805795666