odbc连接mysql的程序代码c_MFC通过ODBC连接Mysql程序

经过这个礼拜的折腾，先是安装Mysql，学习下Mysql基本语句操作，学习下MFC操作，通过ODBC连接Mysql，参考各种网上的实例程序，

加上自己的不断调试，修改，终于将这一可对数据库进行基本操作的程序写完(由于还是新手，，其中肯定有很多不完善的地方，

希望各位高手帮忙指出。)

下面记录下程序：

安装建立mysql，连接ODBC以及通过MFC拖控件这些过程网上很多，就不写了，说一下我的mysql中有database mytest 内有表格mytable，如下：

1、先通过vs2005建立一个MFC工程，vs自动生成文件如图：

2、打开stdafx.h头文件，添加如下语句

#include

#include "afxdb.h"

这两个头文件应该是定义了后面用到的数据库操作类CDataBase、CRecodSet .etc的。

3、剩下的基本都是在ODBCTestDlg.cpp文件操作

打开数据库进行数据显示：

在OnInitDialog函数中添加如下语句

View Code

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);

View Code

1 //get data from database and show them in list control2 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 }

4、操作数据库

a、点击ListCtrl在Edit control中显示对应内容，为ListCtrl添加响应函数

View Code

1 void CODBCTestDlg::OnLvnItemchangedList1(NMHDR *pNMHDR, LRESULT *pResult)

2 {

3 LPNMLISTVIEW pNMLV = reinterpret_cast(pNMHDR);

4 //TODO: Add your control notification handler code here5

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数据函数

View Code

1 void CODBCTestDlg::OnBnClickedAdd()

2 {

3 //TODO: Add your control notification handler code here4 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数据函数

View Code

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数据函数

View Code

1 void CODBCTestDlg::OnBnClickedDelete()

2 {

3 //TODO: Add your control notification handler code here4 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的没改)

View Code

1 void CODBCTestDlg::OnBnClickedFind()

2 {

3 //TODO: Add your control notification handler code here4 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 }