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

最新推荐文章于 2021-08-15 18:44:01 发布

ajioy

最新推荐文章于 2021-08-15 18:44:01 发布

阅读量2k

点赞数

分类专栏： MFC/STL/Boost

本文链接：https://blog.csdn.net/ajioy/article/details/8807353

版权

MFC/STL/Boost 专栏收录该内容

36 篇文章 0 订阅

订阅专栏

数据库tangentDemo中的表employee

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

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

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

最终效果如下：

代码预览：

Exercise6Dlg.h

protected:
	//初始化ListCtrl
	void 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;
		else
			nState = 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 white
			clrNewBkColor = 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