数据库操作实例

1.使用Navicat工具，生成数据库表格（注意字符集的设置）

 

2.新建工程，新建数据库操作类，编写数据结构

struct tSTUDENT
{
	int nId;
	CString sName;
	int nAge;
	tSTUDENT()
	{
		nId = 0;
		sName = "";
		nAge = 0;
	}
	tSTUDENT& operator = (tSTUDENT& item)
	{
		nId = item.nId;
		sName = item.sName;
		nAge = item.nAge;
		return *this;
	}
};
typedef CArray<tSTUDENT, tSTUDENT&> ArrayStudent;

3.添加数据库文件到工程

#include "include\\mysql.h"
#pragma comment(lib,"libmySQL.lib") 

4.添加数据库变量

	MYSQL				m_Mysql;
	MYSQL_RES*			m_pResults;
	MYSQL_ROW			m_Record;

5.添加数据库操作接口：初始化数据库，连接数据库，关闭数据库，数据库数据增删改查

	bool ConnectDatabase();
	bool CloseDatabase();

	tSTUDENT m_tStudent;
	bool GetStudentInfo();
	bool InsertStudent(tSTUDENT& student);
	bool DeleteStudent(int nId);
	bool UpdateStudent(int nId, CString sName);

CMySqlDatabase::CMySqlDatabase(void)
{
	mysql_init(&m_Mysql);

	m_arrStudent.RemoveAll();
}

CMySqlDatabase::~CMySqlDatabase(void)
{
}


bool CMySqlDatabase::ConnectDatabase()
{
	if(!mysql_real_connect(&m_Mysql,"localhost","root","root","test",3306,NULL,0))
	{
		int ret = mysql_errno(&m_Mysql);
		mysql_close(&m_Mysql);
		return false;
	}

	mysql_query(&m_Mysql, "SET NAMES 'gb2312'");//加上这句就能解决乱码

	return true;
}

bool CMySqlDatabase::CloseDatabase()
{
	mysql_close(&m_Mysql);
	return true;
}

bool CMySqlDatabase::InsertStudent(tSTUDENT& student)
{
	CString stemp = "";
	CString str = "insert into students(id,name,age) values('";
	stemp.Format("%d",student.nId);
	str = str + stemp + "','";
	str = str + student.sName + "','";
	stemp.Format("%d",student.nAge);
	str = str + stemp + "')";

	if(0 != mysql_query(&m_Mysql,str))
	{
		return false;
	}
	return true;
}

bool CMySqlDatabase::DeleteStudent(int nId)
{
	CString stemp = "";
	CString str = "delete from students where id = ";
	stemp.Format("%d",nId);
	str = str + stemp;

	if(0 != mysql_query(&m_Mysql,str))
	{
		return false;
	}
	return true;
}

bool CMySqlDatabase::UpdateStudent(int nId, CString sName)
{
	CString str = "update students set name='";
	str = str + sName + "'";
	str = str + " where id=";
	CString stemp = "";
	stemp.Format("%d",nId);
	str = str + stemp;

	if(0 != mysql_query(&m_Mysql,str))
	{
		return false;
	}
	return true;
}

bool CMySqlDatabase::GetStudentInfo()
{
	CString str = "select * from students";
	if(0 != mysql_query(&m_Mysql,str))
	{
		return false;
	}

	m_pResults = mysql_store_result(&m_Mysql);
	while(m_Record = mysql_fetch_row(m_pResults))
	{
		m_tStudent.nId = atoi(m_Record[0]);
		m_tStudent.sName = m_Record[1];
		m_tStudent.nAge = atoi(m_Record[2]);
	}
	mysql_free_result(m_pResults);

	return true;
}

6.数据库使用

C**App中：

#include "MySqlDatabase.h"
extern CMySqlDatabase g_Database;

CMySqlDatabase g_Database;

BOOL CMySqlTestApp::InitInstance()
{
	INITCOMMONCONTROLSEX InitCtrls;
	InitCtrls.dwSize = sizeof(InitCtrls);

	InitCtrls.dwICC = ICC_WIN95_CLASSES;
	InitCommonControlsEx(&InitCtrls);

	CWinApp::InitInstance();

	AfxEnableControlContainer();

	SetRegistryKey(_T("应用程序向导生成的本地应用程序"));

	if(!g_Database.ConnectDatabase())
	{
		AfxMessageBox("数据连接失败。");
		return FALSE;
	}
	else
	{
		AfxMessageBox("数据连接成功。");
	}

	CMySqlTestDlg dlg;
	m_pMainWnd = &dlg;
	dlg.DoModal();
	return FALSE;
}

int CMySqlTestApp::ExitInstance()
{
	g_Database.CloseDatabase();
	return CWinApp::ExitInstance();
}

C**Dlg中：

public:
	CString m_sStudentInfo;
	afx_msg void OnBnClickedBtnCheck();
	afx_msg void OnBnClickedBtnAdd();
	afx_msg void OnBnClickedBtnDelete();
	afx_msg void OnBnClickedBtnUpdate();

void CMySqlTestDlg::OnBnClickedBtnAdd()
{
	tSTUDENT student;
	student.nId = 111;
	student.sName = "小明";
	student.nAge = 18;
	if (g_Database.InsertStudent(student))
	{
		AfxMessageBox("数据添加成功。");
	}
	else
	{
		AfxMessageBox("数据添加失败。");
	}
}

void CMySqlTestDlg::OnBnClickedBtnDelete()
{
	int nId = 111;
	if (g_Database.DeleteStudent(nId))
	{
		AfxMessageBox("数据删除成功。");
	}
	else
	{
		AfxMessageBox("数据删除失败。");
	}
}

void CMySqlTestDlg::OnBnClickedBtnUpdate()
{
	if (g_Database.UpdateStudent(111, "小红"))
	{
		AfxMessageBox("数据更新成功。");
	}
	else
	{
		AfxMessageBox("数据更新失败。");
	}
}

void CMySqlTestDlg::OnBnClickedBtnCheck()
{
	g_Database.GetStudentInfo();

	tSTUDENT student;
	student = g_Database.m_tStudent;

	UpdateData(TRUE);
	CString stemp = "";
	CString str = "";
	stemp.Format("%d",student.nId);
	stemp = "学号："+ stemp;
	str = stemp + "\r";
	stemp = "姓名："+student.sName;
	str = str + stemp + "\r";
	stemp.Format("%d",student.nAge);
	stemp = "年龄："+ stemp;
	str = str + stemp + "\r";
	m_sStudentInfo = str;	
	
	UpdateData(FALSE);
}

7.效果演示

源码下载：http://download.csdn.net/detail/bingdianlanxin/9751845