MFC ado+mysql+odbc技术分享-白红宇

MFC ado+mysql+odbc技术分享

阅读量：6091 次

发布时间：2019-06-20

本文共 10098 字，大约阅读时间需要 33 分钟。

本例所有涉及到的文件下载地址

第一步：建立数据库

假设有一个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后直接下拉得到,如果没有则前面的填写错误

配置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_FILEstatic 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:03int 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_Culture

ADO 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;    }

转载于:https://blog.51cto.com/7071976/1220322

你可能感兴趣的文章