ADO是microsoft数据库应用程序开发的连连接口，是建立在OLE DB之上的高层
ADO使用方法步骤：
1.初始化COM库，引入ADO库定义
2.用connection对象连接数据库
3.利用连接好的连接，通过connection，command对象执行sql 语句。或利用recordset对象取得结果集进行处理
4.使用完关闭连接对象
#pragma once
#import "C:\\Program Files\\common files\\system\\ado\\msado15.dll" no_namespace rename("EOF","adoEOF") rename("BOF","adoBOF")
#include <vector>
using namespace std;
class CDataBaseADO
{
public:
CDataBaseADO(void);
public:
~CDataBaseADO(void);
public:
bool Open(_bstr_t strConnection); //打开数据库
bool Close(void); //关闭数据库 需要成对出现
bool Select(BSTR strsql,vector<_variant_t> strName, vector< vector<_variant_t> > & Ret);
int ExcuteSQL(_bstr_t CommandText,bool IsText = true);
private:
_ConnectionPtr m_pConnection;
};
#include "StdAfx.h"
#include "DataBaseADO.h"
CDataBaseADO::CDataBaseADO(void)
{
::CoInitialize(NULL);
m_pConnection = NULL;
}
CDataBaseADO::~CDataBaseADO(void)
{
if(m_pConnection)
{
m_pConnection->Close();
m_pConnection=NULL;
}
::CoUninitialize();
}
bool CDataBaseADO::Open( _bstr_t strConnection )
{
if(FAILED(m_pConnection.CreateInstance(__uuidof(Connection))))//初始化Connection
return false;
try
{
m_pConnection->Open(strConnection,"","",0);
}
catch (_com_error e)
{
AfxMessageBox(e.Description());
return false;
}
return true;
}
bool CDataBaseADO::Close( void )
{
if(m_pConnection)
{
m_pConnection->Close();
m_pConnection = NULL;
return true;
}
return false;
}
bool CDataBaseADO::Select( BSTR strsql,vector<_variant_t>strName,vector<vector<_variant_t> >& Ret )
{
_RecordsetPtr pRecordset;
if(FAILED(pRecordset.CreateInstance(__uuidof(Recordset))))//初始化recordset指针
return false;
try
{
pRecordset->Open(strsql,(IDispatch*)m_pConnection,adOpenDynamic,adLockOptimistic,adCmdText);//adOpenDynamic:动态 adLockOptimistic:乐观封锁法 adCmdText:文本查询法
pRecordset->MoveFirst();
while(!pRecordset->adoEOF) //遍历所有结果
{
vector<_variant_t> vTheValue;//VARIANT数据类型的泛型
for (int i=0; i < strName.size();i++)
{
vTheValue.push_back(pRecordset->GetCollect(strName.at(i)));//得到字段的值并添加到容器
}
Ret.push_back(vTheValue);
pRecordset->MoveNext(); //移动到下一条记录
}
pRecordset->Close();//关闭连接
pRecordset = NULL;
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
return false;
}
return true;
}
int CDataBaseADO::ExcuteSQL( _bstr_t CommandText,bool IsText /*= true*/ )
{
_variant_t RecordAffected;
try
{
if(IsText)
{
m_pConnection->Execute(CommandText,&RecordAffected,adCmdText);
}
else
{
m_pConnection->Execute(CommandText,&RecordAffected,adCmdStoredProc);
}
}
catch(_com_error e)
{
return -1;
}
return RecordAffected.intVal;
}
CDataBaseADO m_DataBase;
if(FAILED(m_DataBase.Open("Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sa;Initial Catalog=student;Data Source=HJ-PC")))
return ;
int n = m_DataBase.ExcuteSQL("insert into student values(‘5‘,‘wakawaka‘)");
CString str;
str.Format("已增加一条数据",n);
AfxMessageBox(str);
m_DataBase.Close();
return ;
ODBC API 编程连数据库
#include "sql.h" //含有基本ODBC API定义
#include "sqlext.h" //含有扩展的ODBC定义
#include "sqltypes.h"
#include <stdlib.h>
#include <stdio.h>
#include <vector>
#include <map>
#include <string>
#pragma comment(lib,"odbc32.lib"); //库文件
#pragma once
#define FIELD_NUM 1024
using namespace std;
typedef vector<map<string,string> > ResFromDB;
class CODBCSqlServer
{
private:
SQLHENV V_OD_Env; //Handle ODBC environment
SQLHDBC V_OD_hdbc; //handle connection连接句柄
SQLHSTMT V_OD_hstmt; //sql语句的句柄
SQLINTEGER V_OD_err; //sql语句执行后的错误代码
public:
CODBCSqlServer(void);
public:
~CODBCSqlServer(void);
public:
bool open();
bool close();
bool Connect( const char * pszDSN, const char *pszUName, const char * passUPasswd);
bool disConnect();
unsigned int SQLQuery(const char * pszSQL,ResFromDB & resAll); //每个字段长度最多30
unsigned int SQLExec( const char* pszSQL );
void ReportError(SQLHSTMT &hstmt, int iHandleType, CString strAlert);
bool ShowError( HANDLE hdbc, SQLSMALLINT type );
};
#include "StdAfx.h"
#include "ODBCSqlServer.h"
CODBCSqlServer::CODBCSqlServer(void)
{
V_OD_err = 0;
}
CODBCSqlServer::~CODBCSqlServer(void)
{
}
bool CODBCSqlServer::open()
{
V_OD_err = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&V_OD_Env);
if( (V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO))
{
OutputDebugString("allochandle error\n");
return false;
}
V_OD_err = SQLSetEnvAttr(V_OD_Env,SQL_ATTR_ODBC_VERSION,(void *)SQL_OV_ODBC3,SQL_IS_INTEGER);
if( (V_OD_err != SQL_SUCCESS) &&(V_OD_err != SQL_SUCCESS_WITH_INFO) )
{
OutputDebugString("setEnv failed\n");
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
return false;
}
if(V_OD_err == SQL_SUCCESS_WITH_INFO)
{
ShowError(V_OD_hstmt,SQL_HANDLE_STMT);
}
return true;
}
bool CODBCSqlServer::close()
{
if(V_OD_hstmt != NULL)
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
if(V_OD_hdbc != NULL)
{
SQLDisconnect(V_OD_hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
}
if(V_OD_Env != NULL)
SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env);
return true;
}
bool CODBCSqlServer::Connect( const char * pszDSN, const char *pszUName, const char * passUPasswd )
{
if( pszDSN == NULL)
return false;
V_OD_err = SQLAllocHandle( SQL_HANDLE_DBC, V_OD_Env, &V_OD_hdbc);
if( (V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO) )
{
OutputDebugString("allockHDB faile\n");
return false;
}
if(V_OD_err == SQL_SUCCESS_WITH_INFO)
{
ShowError(V_OD_Env,SQL_HANDLE_ENV);
}
V_OD_err = SQLConnect(V_OD_hdbc,(SQLCHAR *)pszDSN,SQL_NTS,(SQLCHAR*)pszUName,SQL_NTS,(SQLCHAR*) passUPasswd,SQL_NTS);
if((V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO))
{
OutputDebugString("sqlconnect failed\n");
SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
return false;
}
if(V_OD_err == SQL_SUCCESS_WITH_INFO)
{
ShowError(V_OD_Env,SQL_HANDLE_ENV);
}
OutputDebugString("Conected!\n");
return true;
}
bool CODBCSqlServer::disConnect()
{
if(V_OD_hstmt != NULL)
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
if(V_OD_hdbc != NULL)
{
SQLDisconnect(V_OD_hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,V_OD_hdbc);
}
return true;
}
unsigned int CODBCSqlServer::SQLQuery( const char * pszSQL,ResFromDB & resAll )
{
if(pszSQL == NULL)
return 0;
V_OD_err = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc , &V_OD_hstmt);
if ((V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO))
{
OutputDebugString("Fehler im AllocStatement\n");
SQLDisconnect( V_OD_hdbc );
SQLFreeHandle( SQL_HANDLE_DBC,V_OD_hdbc );
return false;
}
if(V_OD_err == SQL_SUCCESS_WITH_INFO)
{
ShowError(V_OD_hstmt,SQL_HANDLE_STMT);
}
V_OD_err = SQLExecDirect(V_OD_hstmt,(SQLCHAR *)pszSQL, SQL_NTS);
if( (V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO) )
{
OutputDebugString("sqlexecdriect failed\n");
return 0;
}
if(V_OD_err == SQL_SUCCESS_WITH_INFO)
{
ShowError(V_OD_hstmt,SQL_HANDLE_STMT);
}
SQLSMALLINT colNum;
V_OD_err = SQLNumResultCols(V_OD_hstmt,&colNum); //得到有多少列
SQLCHAR (*name)[20];
SQLSMALLINT ColumnNameLenthPtr;
SQLSMALLINT DataTypePtr;
SQLUINTEGER ColumnSizePtr;
SQLSMALLINT DecimalDigits;
SQLSMALLINT NullAblePTr;
name = new SQLCHAR[colNum][20];
for(int i=1; i<=colNum ; i++) //拿每一列的详细信息,将列名称放入colName的vector
{
memset(name[i-1],0,20);
V_OD_err = SQLDescribeCol(
V_OD_hstmt,
i,
(SQLCHAR *)name[i-1],
20,
&ColumnNameLenthPtr,
&DataTypePtr,
&ColumnSizePtr,
&DecimalDigits,
&NullAblePTr
);
}
/*/////////////////////////////////////////
把得到的列名称绑定到结果集中
/////////////////////////////////////////*/
SQLINTEGER ID;
SQLCHAR Name[30];
SQLINTEGER lenthID,lenthName;
SQLCHAR (*colValue)[30] = new SQLCHAR[colNum][30]; //申请动态内存存放每一行数据，单格 数据长度不能操作30个字符
for(int j = 0; j<colNum; j++) //初始化动态内存，绑定内存到结果集
{
memset(colValue[j],0,30);
V_OD_err = SQLBindCol(V_OD_hstmt,j+1,SQL_C_CHAR,&colValue[j],30,&lenthName);
}
while (TRUE)
{
map<string,string> each;
V_OD_err = SQLFetch(V_OD_hstmt);
if(V_OD_err == SQL_ERROR || V_OD_err == SQL_SUCCESS_WITH_INFO)
{
if(V_OD_err = SQL_SUCCESS_WITH_INFO)
{
ShowError(V_OD_hstmt,SQL_HANDLE_STMT);
}
OutputDebugString("sqlfetch error\n");
}
if(V_OD_err == SQL_SUCCESS || V_OD_err == SQL_SUCCESS_WITH_INFO)
{
for(int k=0; k<colNum; k++)
{
each.insert( std::pair<string,string>((char *)name[k],(char *)colValue[k]) );
//each["11"]="22";
//each.insert(std::pair<int,string>(0,"2"));
}
}
else
{
break;
}
resAll.push_back(each);
}
delete colValue;
delete name;
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
return colNum;
}
unsigned int CODBCSqlServer::SQLExec( const char* pszSQL )
{
if(pszSQL == NULL)
return 0;
V_OD_err = SQLAllocHandle(SQL_HANDLE_STMT,V_OD_hdbc,&V_OD_hstmt);
if( (V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO) )
{
OutputDebugString("SQLAllocHandle stmt failed!\n");
ShowError(V_OD_hstmt,SQL_HANDLE_STMT);
return -1;
}
V_OD_err = SQLExecDirect(V_OD_hstmt,(unsigned char *)pszSQL,SQL_NTS);
if( (V_OD_err != SQL_SUCCESS) && (V_OD_err != SQL_SUCCESS_WITH_INFO) && (V_OD_err != SQL_NO_DATA))
{
ShowError(V_OD_hstmt,SQL_HANDLE_STMT);
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
V_OD_hstmt = NULL;
return -1;
}
SQLFreeHandle(SQL_HANDLE_STMT,V_OD_hstmt);
V_OD_hstmt = NULL;
return 0;
}
void CODBCSqlServer::ReportError( SQLHSTMT &hstmt, int iHandleType, CString strAlert )
{
unsigned char *SQLState = new unsigned char[6];
if(SQLState == NULL)
{
AfxMessageBox("报告错误发生的原因时，分配sqlstate内存失败");
return ;
}
char Message[500] = "\0";
short iMesLen;
CString strError;
SQLGetDiagRec(iHandleType, hstmt, 1, SQLState, NULL,
(unsigned char *)Message, 500, &iMesLen);
strError.Format("%s, %s", strAlert, Message);
AfxMessageBox(strError);
delete SQLState;
SQLState = NULL;
}
bool CODBCSqlServer::ShowError( HANDLE hdbc, SQLSMALLINT type )
{
SQLCHAR SqlState[6] = {0}, Msg[SQL_MAX_MESSAGE_LENGTH] = {0};
SQLSMALLINT i = 1, MsgLen = 0;
SQLINTEGER NativeError = 0;
SQLRETURN rc2 = SQL_SUCCESS;
CString sqlerror;
while((rc2 = SQLGetDiagRec(type, hdbc, i, SqlState, &NativeError, Msg, sizeof(Msg), &MsgLen))
!= SQL_NO_DATA)
{
sqlerror = SqlState;
AfxMessageBox((char *)Msg);
i++;
}
return true;
}
void CTestODBCDlg::OnBnClickedButton1()
{
// TODO: 在此添加控件通知处理程序代码
CODBCSqlServer odbcServer;
bool bRes = odbcServer.open();
if( !bRes)
{
OutputDebugString("open failed\n");
return;
}
OutputDebugString("open ok\n");
bRes = odbcServer.Connect("LocalServer","sa","123456");
if(!bRes)
{
}
int nRes = 0;
ResFromDB resALL;
nRes = odbcServer.SQLQuery("SELECT * FROM student",resALL);
odbcServer.disConnect();
char *pszBuf = NULL;
int nBuf = 0;
int i = 0;
odbcServer.disConnect();
odbcServer.close();
return;
}
void CTestODBCDlg::OnBnClickedButton2()
{
// TODO: 在此添加控件通知处理程序代码
CODBCSqlServer odbcDemo;
bool re = odbcDemo.open();
odbcDemo.Connect("LocalServer","sa","123456");
int n = odbcDemo.SQLExec("insert into student values(10,‘qqqq‘)");
odbcDemo.disConnect();
odbcDemo.close();
return ;
}
原文：http://www.cnblogs.com/marktubu/p/4877262.html