MFC通过ODBC连接Mysql程序

  1. 云栖社区>
  2. 博客>
  3. 正文

MFC通过ODBC连接Mysql程序

狂师 浏览1106
展开阅读全文

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函数中添加如下语句

  

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

 

1
m_show()函数中的内容完全可以全部放在OnInitDialog()中,但考虑到后面操作数据库时,每次都要写同样的操作,故将其写一函数中
View Code

 

 
 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添加响应函数

  

View Code

 

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

  

View Code

 

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

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 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的没改)

View Code

 

 
 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 }
 

到这里这个MFC连接mysql的程序基本完成。

技术改变世界! --狂诗绝剑

网友评论

登录后评论
0/500
评论
狂师
+ 关注