mysql+odbc+ado_MFC ado+mysql+odbc技术分享

第一步：建立数据库

假设有一个sql文件

mysql>use dbname;                   //创建一个数据库名为dbname的数据库(空数据库)

mysql>set names utf8;              //编码，mysql>source D:/dbname.sql;  //导入一个数据库源文件创建数据库内容

我做的工程数据库名称是db_grain

第二步创建数据源

三、搭建数据源。

本项目中是用的是odbc(驱动)+mysql的数据源

1、安装相应的mysql-connector(分32，64位)。

我装的是mysql-connector-odbc-5.1.5-win32.msi驱动。

2、控制面板è管理工具(或{性能与…..})è数据源(odbc)è添加è你相应的odbc驱动。

(我的是5.1)è完成

3.完成后会跳出下面对话框。Name可以自己填，软件项目中会用到下面你填的内容

database在填写server,port,user,password后直接下拉得到,如果没有则前面的填写错误

4.

配置ado cpp文件

1、在项目中导入下面两个文件到相应包下面。

5.修改ado.cpp文件的OnInitADOConn()//不同驱的数据源只要改onInitADOConn()中的两个值(bstr_t strConnect,m_pConnection->Open)就行了，其他不要改

void ADO::OnInitADOConn()

{

::CoInitialize(NULL);

try

{

m_pConnection.CreateInstance("ADODB.Connection"); //这里不用改

_bstr_t strConnect="DRIVER={MySQL ODBC 5.1 Driver};//这里改成你的驱动名称(假如你的是access数据驱动改成你的access驱动如Microsoft Access Driver (*.mdb))

Server=localhost;

/*

Persist Security Info ----是否保存安全信息User ID-------------------用户名PassWord------------------密码Initial Catalog-----------数据库的名称或者数据库ip或者目录Data Source---------------数据源

*/

PassWord=123456;

Persist Security Info=False;

User ID=root;

Data Source=autoresour";

m_pConnection->Open(strConnect,"","",adModeUnknown); //

/* Open (

const _variant_t & Source,

const _variant_t & ActiveConnection,

enum CursorTypeEnum CursorType,//””

enum LockTypeEnum LockType,//一般为””

long Options ); }*/ //一般是 adModeUnknown

catch(_com_error e)

{

AfxMessageBox(e.Description()); //

}

}

以我工程的数据源名称为"autoresour"为例

// ADO.cpp: implementation of the ADO class.

//

//

#include "stdafx.h"

#include "ADO.h"

#ifdef _DEBUG

#undef THIS_FILE

static char THIS_FILE[]=__FILE__;

#define new DEBUG_NEW

#endif

//

// Construction/Destruction

//

ADO::ADO()

{

}

ADO::~ADO()

{

}

void ADO::OnInitADOConn()

{

::CoInitialize(NULL);

try

{

m_pConnection.CreateInstance("ADODB.Connection"); //创建连接对象实例

_bstr_t strConnect="DRIVER={MySQL ODBC 5.2w Driver};Server=localhost;PassWord=123456;Persist Security Info=False;User ID=root;Data Source=autoresour";

m_pConnection->Open(strConnect,"","",adModeUnknown); //打开数据库

}

catch(_com_error e)

{

AfxMessageBox(e.Description()); //弹出错误处理

}

}

_RecordsetPtr& ADO::OpenRecordset(CString sql)

{

ASSERT(!sql.IsEmpty()); //SQL语句不能为空

try

{

m_pRecordset.CreateInstance(__uuidof(Recordset)); //创建记录集对象实例

m_pRecordset->Open(_bstr_t(sql), m_pConnection.GetInterfacePtr(),

adOpenDynamic, adLockOptimistic, adCmdText); //执行SQL得到记录集

}

catch(_com_error e) //捕获可能的异常

{

AfxMessageBox(e.Description());

}

return m_pRecordset;

}

void ADO::CloseRecordset()

{

if(m_pRecordset->GetState() == adStateOpen) //判断当前的记录集状态

m_pRecordset->Close(); //关闭记录集

}

void ADO::CloseConn()

{

m_pConnection->Close(); //关闭数据库连接

::CoUninitialize(); //释放COM环境

}

CString ADO::getTimeToULong(){

SYSTEMTIME sm;

::GetLocalTime(&sm);

CTime tmSCan(sm);

CString szTime = tmSCan.Format("%Y-%m-%d %H:%M:%S");

return szTime;

}

UINT ADO::GetRecordCountt(_RecordsetPtr pRecordset)

{

int nCount = 0; //声明保存记录数的变量

try{

pRecordset->MoveFirst(); //将记录集指针移动到第一条记录

}

catch(...) //捕捉可能出现的错误

{

return 0; //产生错误时返回0

}

if(pRecordset->adoEOF) //判断记录集中是否没有记录

return 0; //无记录时返回0

while (!pRecordset->adoEOF) //当记录集指针没有指向最后时

{

pRecordset->MoveNext(); //将记录集指针移动到下一条记录

nCount = nCount + 1; //记录个数的变量加1

}

pRecordset->MoveFirst(); //将记录集指针移动到第一条记录

return nCount; //返回记录数

}

CTime ADO::CStringToTime(CString string){

int first=string.Find('-');

int second=string.Find('-',first+1);

int year=atoi(string.Left(4));

int month=atoi(string.Mid(first+1,second-first));

int day=atoi(string.Mid(second+1,string.GetLength()-second-1));

CTime temp(year,month,day,0,0,0);

return temp;

}

CTime ADO::CStringToTimeComplete(CString cstring){//cstring 2013-02-05 01:02:03

int first=cstring.Find('-');

int second=cstring.Find('-',first+1);

int year=atoi(cstring.Left(4));

int month=atoi(cstring.Mid(first+1,second-first));

int day=atoi(cstring.Mid(second+1,10-second-1));

first = cstring.Find(':', second);

second = cstring.Find(':', first + 1);

int hour = atoi(cstring.Mid(first - 2,2));

int minute = atoi(cstring.Mid(second - 2,2));

int secd = atoi(cstring.Mid(second + 1,2));

CTime temp(year,month,day,hour,minute,secd);

return temp;

}

CString ADO::getYMD(CString string){

if(string.Find(" ") != -1){

string = string.Mid(0, string.Find(" "));

}

int first=string.Find('-');

int second=string.Find('-',first+1);

int year=atoi(string.Left(4));

int month=atoi(string.Mid(first+1,second-first));

int day=atoi(string.Mid(second+1,string.GetLength()-second-1));

CString trace;

trace.Format("%d%d%d", year, month, day);

return trace;

}

CString ADO::getYMDHMS(CString date, CString time){

int first=date.Find('-');

int second=date.Find('-',first+1);

int year=atoi(date.Left(4));

int month=atoi(date.Mid(first+1,second-first));

int day=atoi(date.Mid(second+1,date.GetLength()-second-1));

CString trace = "";

CString temp = "";

trace.Format("%d-", year);

temp.Format("%d-", month);

if(month/10 == 0){temp.Format("0%d-", month);}

trace += temp;

temp.Format("%d ", day);

if(day/10 == 0){temp.Format("0%d ", day);}

trace += temp;

temp = time;

if(temp.GetLength() == 7){temp ="0" + time;}

trace += temp;

//TRACE("TRACE:" + trace);

return trace;

}

/*

ADO m_Ado;

m_Ado.OnInitADOConn();//连接数据库

CString SQL = "select * from tb_data"; //设置查询字符串

m_Ado.m_pRecordset = m_Ado.OpenRecordset(SQL);//打开记录集

while(!m_Ado.m_pRecordset->adoEOF)

{

CString strSql = (LPCSTR)(_bstr_t)m_Ado.m_pRecordset->GetCollect("nowtime");

m_Ado.m_pRecordset->MoveNext();//将记录集指针移动到下一条记录

}

m_Ado.CloseRecordset();

m_Ado.CloseConn();//断开数据库连接

*/

修改你的工程的StdAfx.h文件中导入动链接库//在stdAfx.h中

// _AFX_NO_AFXCMN_SUPPORT

//加入#import ""C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace\

rename("EOF","adoEOF")rename("BOF","adoBOF")\//导入ADO动态链接库

//`AFX_INSERT_LOCATION`

//如果把msado15.dll放在工程目录下就以下面我写的为准，放的位置要夹在//AFX_NO_AFXCMN_SUPPORT和//`AFX_INSERT_LOCATION`之间。// stdafx.h : include file for standard system include files,

// or project specific include files that are used frequently, but

// are changed infrequently

//

#if !defined(AFX_STDAFX_H__068DB9EC_AC8F_4663_850A_031896F0B1F2__INCLUDED_)

#define AFX_STDAFX_H__068DB9EC_AC8F_4663_850A_031896F0B1F2__INCLUDED_

#if _MSC_VER > 1000

#pragma once

#endif // _MSC_VER > 1000

#define VC_EXTRALEAN // Exclude rarely-used stuff from Windows headers

#include // MFC core and standard components

#include // MFC extensions

#include // MFC Automation classes

#include // MFC support for Internet Explorer 4 Common Controls

#ifndef _AFX_NO_AFXCMN_SUPPORT

#include // MFC support for Windows Common Controls

#endif // _AFX_NO_AFXCMN_SUPPORT

#include "SkinPPWTL.h"

#include "sizecbar.h"

#include "scbarg.h"

#include "CoolTabCtrl.h"

#import "msado15.dll" no_namespace\ //导入工程同目录下的ADO动态链接库

rename("EOF","adoEOF")rename("BOF","adoBOF")

//`AFX_INSERT_LOCATION`

// Microsoft Visual C++ will insert additional declarations immediately before the previous line.

#endif // !defined(AFX_STDAFX_H__068DB9EC_AC8F_4663_850A_031896F0B1F2__INCLUDED_)

使用ado类的使用

假设对table为employees操作,ado数据操作对所有不同类型的数据源是通用的

注明： 下面的三个变量类型CString m_ID,m_Name,m_CultureADO m_Ado;

m_Ado.OnInitADOConn();

CString sql = "select * from employees";

m_Ado.m_pRecordset = m_Ado.OpenRecordset(sql);

try

{

/*

//删除操作必须有一下两条组成

m_Ado.m_pRecordset->Move((long)pos,vtMissing);// vtMissing固定字符

m_Ado.m_pRecordset->Delete(adAffectCurrent);

*/

//以下是修改操作

m_Ado.m_pRecordset->Move((long)pos,vtMissing);//vtMissing为const，不能改变的字符，这里就不用修改

m_Ado.m_pRecordset->PutCollect("编号",(_bstr_t)m_ID);

m_Ado.m_pRecordset->PutCollect("姓名",(_bstr_t)m_Name);

m_Ado.m_pRecordset->PutCollect("学历",(_bstr_t)m_Culture);

/*

//以下是添加操作

m_Ado.m_pRecordset->AddNew(); //添加新行

m_Ado.m_pRecordset->PutCollect("编号",(_bstr_t)m_ID);//(_variant_t)(long);_variant_t var;var.intVal = 2;pRs->PutCollect("Layer", var);

m_Ado.m_pRecordset->PutCollect("姓名",(_bstr_t)m_Name);

m_Ado.m_pRecordset->PutCollect("学历",(_bstr_t)m_Culture);

//以下是取值操作

CString tmp = (LPCSTR)(_bstr_t)m_Ado.m_pRecordset->GetCollect("outdtmp");

1.adAffectCurrent Deletes only the current record 仅删除当前记录

2.adAffectGroup Deletes only records that satisfy the Filter setting (Filter must be set to a FilterGroupEnum value or an array of Bookmarks)

对满足当前 Filter 属性设置的记录取消挂起更新。使用该选项时，必须将Filter属性设置为合法的FilterGroupEnum常量之一或设置成一个书签数组

更多参考msdn

*/ m_Ado.CloseRecordset();

m_Ado.CloseConn();

}

catch(...)

{

MessageBox("操作失败");

return;

}