MFC ado+mysql+odbc技术分享

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

第一步:建立数据库

   假设有一个sql文件

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

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

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


第二步创建数据源

三、搭建数据源。

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

   1、安装相应的mysql-connector(分3264位)。

   我装的是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)就行了,其他不要改


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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"为例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
// 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文件中导入动链接库      

1
2
3
4
5
//在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`之间。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// 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 <afxwin.h>         // MFC core and standard components
#include <afxext.h>         // MFC extensions
#include <afxdisp.h>        // MFC Automation classes
#include <afxdtctl.h>     // MFC support for Internet Explorer 4 Common Controls
#ifndef _AFX_NO_AFXCMN_SUPPORT
#include <afxcmn.h>           // 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
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();
     }