经过这个礼拜的折腾，先是安装Mysql，学习下Mysql基本语句操作，学习下MFC操作，通过ODBC连接Mysql，参考各种网上的实例程序，
加上自己的不断调试，修改，终于将这一可对数据库进行基本操作的程序写完（由于还是新手，，其中肯定有很多不完善的地方，
希望各位高手帮忙指出。）
下面记录下程序：
安装建立mysql，连接ODBC以及通过MFC拖控件这些过程网上很多，就不写了，说一下我的mysql中有database mytest 内有表格mytable，如下：
1、先通过vs2005建立一个MFC工程，vs自动生成文件如图：
2、打开stdafx.h头文件，添加如下语句
#include <odbcinst.h>
#include "afxdb.h"
这两个头文件应该是定义了后面用到的数据库操作类CDataBase、CRecodSet .etc的。
3、剩下的基本都是在ODBCTestDlg.cpp文件操作
打开数据库进行数据显示：
在OnInitDialog函数中添加如下语句
1 m_list.InsertColumn(1, _T("Name"), LVCFMT_LEFT,80);//在Listctrl控件上添加列，与数据库table对应。
2 m_list.InsertColumn(2, _T("Sex"), LVCFMT_LEFT,80);
3 m_list.InsertColumn(3, _T("Grade"), LVCFMT_LEFT,80);
4 CString cmdStr = _T("SELECT * FROM mytable order by Name Desc");
5 m_show(cmdStr);
|
1
|
m_show()函数中的内容完全可以全部放在OnInitDialog()中，但考虑到后面操作数据库时，每次都要写同样的操作，故将其写一函数中 |
1 // get data from database and show them in list control
2 void CODBCTestDlg::m_show(CString cmdStr)
3 {
4 m_list.DeleteAllItems();
5 CDatabase db;
6 db.Open(NULL,FALSE,FALSE,L"ODBC;DSN=mydb;UID=root;PWD=123456");
7 CRecordset rs( &db );
8 rs.Open( CRecordset::forwardOnly, (L"%s", cmdStr));
9 //short nFields = rs.GetODBCFieldCount();
10 while(!rs.IsEOF())
11 {
12
13 CString varName;
14 rs.GetFieldValue(L"Name", varName);
15 m_list.InsertItem(0, varName);
16 CString varSex;
17 rs.GetFieldValue(L"Sex", varSex);
18 m_list.SetItemText(0, 1, varSex);
19 CString varGrade;
20 rs.GetFieldValue(L"Grade", varGrade);
21 m_list.SetItemText(0, 2, varGrade);
22 rs.MoveNext();
23 }
24 m_list.SetExtendedStyle(LVS_EX_FLATSB | LVS_EX_FULLROWSELECT | LVS_EX_GRIDLINES); // | LVS_SINGLESEL);
25 rs.Close();
26 db.Close();
27 }
|
1
|
这样已经可以在ListCtrl中显示数据库中得内容了。 |
4、操作数据库
a、点击ListCtrl在Edit control中显示对应内容，为ListCtrl添加响应函数
1 void CODBCTestDlg::OnLvnItemchangedList1(NMHDR *pNMHDR, LRESULT *pResult)
2 {
3 LPNMLISTVIEW pNMLV = reinterpret_cast<LPNMLISTVIEW>(pNMHDR);
4 // TODO: Add your control notification handler code here
5
6 #if 0
7 LPNMITEMACTIVATE lpNMItemActivate = (LPNMITEMACTIVATE)pNMHDR;
8 if (lpNMItemActivate != NULL)
9 {
10 nItem = lpNMItemActivate->iItem;
11 }
12 LV_ITEM lvitem = {0};
13 lvitem.iItem =nItem;
14 lvitem.iSubItem = 0;
15 lvitem.mask = LVIF_TEXT | LVIF_IMAGE | LVIF_PARAM;
16 m_list.GetItem(&lvitem);
17 m_control_edit_name.SetWindowText(lvitem.pszText);
18 #endif
19 int nIndex;
20 CString name, sex, grade;
21 //nIndex = m_list.GetSelectionMark();
22 nIndex = m_list.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
23 if(-1 == nIndex)
24 nIndex = 0;
25 name = m_list.GetItemText(nIndex, 0);
26 sex = m_list.GetItemText(nIndex, 1);
27 grade = m_list.GetItemText(nIndex, 2);
28 m_control_edit_name.SetWindowText(name);
29 m_control_edit_sex.SetWindowText(sex);
30 m_control_edit_grade.SetWindowText(grade);
31
32 *pResult = 0;
33 }
b、在mysql 中add数据函数
1 void CODBCTestDlg::OnBnClickedAdd()
2 {
3 // TODO: Add your control notification handler code here
4 CDatabase db;
5 db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=mydb;UID=root;PWD=123456"));
6 CString str, Name, Sex, Grade;
7 m_control_edit_name.GetWindowText(Name);
8 m_control_edit_sex.GetWindowText(Sex);
9 m_control_edit_grade.GetWindowText(Grade);
10 //str = L"insert into mytable values('" + Name +L"','" + Sex +L"','" + Grade + L"')";
11 str.Format(L"insert into mytable values('%s','%s','%s')", Name, Sex, Grade);
12 db.ExecuteSQL(str);
13 db.Close();
14 CString cmdStr = _T("SELECT * FROM mytable order by name Desc");
15 m_show(cmdStr);
16 }
c、在mysql中alter数据函数
1 void CODBCTestDlg::OnBnClickedAlter()
2 {
3 // TODO: 在此添加控件通知处理程序代码
4 int nIndex;
5 CString name, sex, grade, newName, newSex, newGrade;
6 CString dataToAlter;
7 CDatabase db;
8 db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=mydb;UID=root;PWD="));
9 nIndex = m_list.GetSelectionMark();
10 if(-1 == nIndex)
11 {
12 AfxMessageBox(L"select a row data!");
13 }
14 else
15 {
16 name = m_list.GetItemText(nIndex, 0);
17 sex = m_list.GetItemText(nIndex, 1);
18 grade = m_list.GetItemText(nIndex, 2);
19 }
20 m_control_edit_name.GetWindowText(newName);
21 m_control_edit_sex.GetWindowText(newSex);
22 m_control_edit_grade.GetWindowText(newGrade);
23 if(newName.IsEmpty() || newSex.IsEmpty() || newGrade.IsEmpty())
24 {
25 AfxMessageBox(L"Name,Sex,Grade all cannot be empty");
26 }
27 dataToAlter.Format(L"update mytable set Name='%s', Sex='%s', Grade='%s'\
28 where Name='%s' and Sex='%s' and Grade='%s'",\
29 newName, newSex, newGrade, name, sex, grade);
30 db.ExecuteSQL(dataToAlter);
31 db.Close();
32 CString cmdStr = _T("select * from mytable order by Name Desc");
33 m_show(cmdStr);
34 }
d、在mysql中delete数据函数
1 void CODBCTestDlg::OnBnClickedDelete()
2 {
3 // TODO: Add your control notification handler code here
4 int nIndex;
5 CString name, sex, grade, dataToDel;
6 CDatabase db;
7 db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=mydb;UID=root;PWD=123456"));
8 nIndex = m_list.GetNextItem(-1, LVNI_ALL | LVNI_SELECTED);
9 //if(-1 == nIndex)
10 // nIndex = 0;
11 if(-1 != nIndex)
12 {
13 name = m_list.GetItemText(nIndex, 0);
14 sex = m_list.GetItemText(nIndex, 1);
15 grade = m_list.GetItemText(nIndex, 2);
16 }
17 else
18 {
19 m_control_edit_name.GetWindowText(name);
20 m_control_edit_sex.GetWindowText(sex);
21 m_control_edit_grade.GetWindowText(grade);
22 }
23
24 dataToDel.Format(L"delete from mytable where Name='%s' and Sex='%s'", name, sex);
25 db.ExecuteSQL(dataToDel);
26 db.Close();
27 CString cmdStr = _T("select * from mytable order by Name Desc");
28 m_show(cmdStr);
29
30 }
e、在mysql中select数据函数（当时写find的没改）
1 void CODBCTestDlg::OnBnClickedFind()
2 {
3 // TODO: Add your control notification handler code here
4 CDatabase db;
5 db.Open(NULL, FALSE, FALSE, _T("ODBC;DSN=mydb;UID=root;PWD=123456"));
6 CString Name, str;
7 m_control_edit_name.GetWindowText(Name);
8 str.Format(_T("select * from mytable where Name= '%s' Desc"), Name);
9 db.ExecuteSQL(str);
10 db.Close();
11 m_show(str);
12 }