在这里先做一个讲解ADO和ODBC的关系,及其调用关系。
图1
从图中可以看出来 ,ADO 可以看出来 比如我想要连接到Oracle数据库是后 ,可以通过两种途径的到我想要的ADO connection的 连接字符串 ,一种是通过直接的ODBC,在连接字符串里面就是DNS,这个由ODBC数据源管理器设置DNS(图2,图3指示),还有一种是通过OLEDB,也就是创建.udl文件的方式来得到这样的字符串(这种方式晚上有很多,基本都是通过这样的方式 ,我就不讲解了,还有 OLEDB 选择的不是数据源驱动,OLEDB其关管理选项里面有ODBC的选项,这也和图1是相匹配的)(图4)。
在这里我所用的是第一种方式来连接数据库,创建DNS的方法有两种,一种界面的方式windowODBC数据源管理器添加一条用户DNS,注意区分ODBC数据源管理器是64位还是32位。一种通过代码,通过写注册表的方式来创建DNS(我这里是通过代码的方式)。
(注: 代码封装DLL在CSDN上了)
图2
图3
图4
下面是通过ADO来连接数据库的部分
代码在 http://download.csdn.net/download/wangchao712217/10147600
//构造、析构函数来创建ADO ACtivex控件
XhDBInterface::XhDBInterface(void)
{
if (CoInitialize(NULL) == S_OK)
m_bCOMInt = true;
else
m_bCOMInt = false;
m_bConnDB = false;
m_nDBType = Oracle;
m_pConnection = NULL;
m_strIP = _T("");
m_strPort = _T("0");
m_strUID = _T("");
m_strPWD = _T("");
m_nError = ERR_DB_NOERROR;
//获取时间点
SYSTEMTIME st;
::GetLocalTime(&st);
m_strLogName.Format("DBsys(%d%02d).log",st.wYear,st.wMonth);
}
XhDBInterface::~XhDBInterface(void)
{
m_strUID = "";
m_strPWD = "";
m_bConnDB = FALSE;
try
{
if (m_pConnection != NULL)
{
if(m_pConnection->State != adStateClosed)
m_pConnection->Close();
}
}
catch (_com_error e)
{
TRACE("关闭连接对象失败!:%s", e.ErrorMessage());
}
CoUninitialize();
}
//连接函数里面有通过注册表的方式来创建ODBC数据源DNS
int XhDBInterface::ConnectionDB(stConnDBInfo stConnInfo)
{
//打印调试日志参数获取
int bPDebug = 0;
CString strDebugNodeVal;
strDebugNodeVal = _T("0");
bPDebug = PrintDebugLog(_T("DBDebugLog.xml"),_T("ConnectionDB"),strDebugNodeVal);
//打印调试日志
if (bPDebug == 1 && strDebugNodeVal == "1")
{
CString strDebug,strLogName;
strLogName = _T("ConnectionDB.log");
WriteLog((LPCTSTR)strLogName,_T("===================================BEGIN===================================="));
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][数据库类型][%d]"),stConnInfo.nDBType);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][数据库][%s]"),stConnInfo.strDBName);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][数据库IP][%s]"),stConnInfo.strHostName);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][端口号][%d]"),stConnInfo.nPort);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][用户名][%s]"),stConnInfo.strUserName);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][密码][%s]"),stConnInfo.strPassword);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][TNS服务名称][%s]"),stConnInfo.strTNSSN);
WriteLog((LPCTSTR)strLogName,strDebug);
strDebug.Format(_T("[参数][服务器服务名称][%s]"),stConnInfo.strServerName);
WriteLog((LPCTSTR)strLogName,strDebug);
WriteLog((LPCTSTR)strLogName,_T(""));
}
m_nDBType = stConnInfo.nDBType;
if (m_nDBType == Oracle)
{
CreateTNSNamesOra(stConnInfo);
}
int bCDSN = 0;
//建DSN
bCDSN = CreateDBSourceName(stConnInfo);
if (!bCDSN)
{
m_bConnDB = false;
m_nError = ERR_DB_DISCONNECT;
return 0;
}
CoInitialize(NULL);
//连数据库
if(m_pConnection != NULL)
{
CString strErr;
strErr.Format(_T("[ConnectionDB]数据库已连接"));
WriteLog((LPCTSTR)m_strLogName,strErr);
m_nError = ERR_DB_CONNECTED;
return 0;
}
try
{
HRESULT hr;
hr = m_pConnection.CreateInstance(__uuidof(Connection));//"ADODB.Connection"
if(SUCCEEDED(hr))
{
m_pConnection->ConnectionTimeout = 10;
m_strDBName.Format(_T("%s"),stConnInfo.strDBName);
m_strIP.Format(_T("%s"),stConnInfo.strHostName);
m_strPort.Format(_T("%d"),stConnInfo.nPort);
m_strUID.Format(_T("%s"),stConnInfo.strUserName);
m_strPWD.Format(_T("%s"),stConnInfo.strPassword);
CString strConnString("");
strConnString.Format(_T("DSN=%s; Charset=gb2312"),m_strDBName);
m_pConnection->CursorLocation = adUseClient; //游标类型
hr = m_pConnection->Open(_bstr_t(strConnString), (_bstr_t)m_strUID, (_bstr_t)m_strPWD,adConnectUnspecified);
if (SUCCEEDED(hr))
{
m_nError = ERR_DB_SUCCESS;
m_bConnDB = true;
}
else
{
m_nError = ERR_DB_CONNECT_FAILED;
m_bConnDB = false;
}
}
else
{
CString strErr;
strErr.Format(_T("[ConnectionDB][ADO_ERR]建立连接失败!"));
WriteLog((LPCTSTR)m_strLogName,strErr);
m_nError = ERR_DB_CREATEADO_FAILED;
m_bConnDB = false;
}
}
catch(_com_error e)
{
TRACE("连接数据库失败!:%s", e.ErrorMessage());
CString strErr;
strErr.Format(_T("[ConnectionDB]连接数据库失败!:%s[%u]%s"), e.ErrorMessage(),e.Error(),(char*)e.Description());
WriteLog((LPCTSTR)m_strLogName,strErr);
m_pConnection = NULL;
m_nError = ERR_DB_CODE_ERR;
m_bConnDB = false;
return 0;
}
if (m_bConnDB)
{
return 1;
}
else
{
return 0;
}
}
//建立tns
int XhDBInterface::CreateTNSNamesOra(stConnDBInfo stConnInfo)
{
BOOL retcode = FALSE;
CRegistry m_CReg;
//通过操作系统位数求键位置
CString strKey(""),strKey1("");
if (IsWow64())
{
//64位操作系统
strKey = _T("SOFTWARE\\Wow6432Node\\ORACLE");
}
else
{
//32们操作系统
strKey = _T("SOFTWARE\\ORACLE");
}
retcode = m_CReg.SetRootKey(HKEY_LOCAL_MACHINE);
retcode = m_CReg.SetKey(strKey + _T("\\KEY_OraClient11g_home1"),FALSE);
if(!retcode)
{
WriteLog((LPCTSTR)m_strLogName,_T("创建TNSNames失败,未安装:Oracle_11gR2_Client软件"));
return retcode;
}
CString strOrgPath("");
strOrgPath = m_CReg.ReadString(_T("ORACLE_HOME"),_T(""));
if (strOrgPath == "")
{
retcode = FALSE;
WriteLog((LPCTSTR)m_strLogName,_T("创建TNSNames失败,未安装:Oracle_11gR2_Client软件"));
return retcode;
}
else
{
strOrgPath += _T("\\Network\\Admin\\tnsnames.ora");
}
//分析文件
CFileOperator cFileOP;
vector<CString> vecLines;
BOOL bFind = FALSE;
cFileOP.ReadTNSNamesOraData(strOrgPath.GetBuffer(strOrgPath.GetLength()),vecLines);
CString strTns(""),strService("");
strTns.Format(_T("%s"),stConnInfo.strTNSSN);
strTns.Trim();
strService.Format(_T("%s"),stConnInfo.strServerName);
strService.Trim();
for (int i=0;i<vecLines.size();i++)
{
stTNSNameOra stInfo;
cFileOP.ParseTNSNamesData(vecLines[i],stInfo);
if (stInfo.strTNSSN == strTns)
{
if (stInfo.strHost == stConnInfo.strHostName &&
stInfo.nPort == stConnInfo.nPort)
{
bFind = TRUE;
}
else
{
vecLines.erase(vecLines.begin()+i);
}
break;
}
}
//更改配置
if (!bFind && strTns != "")
{
CString strTNS("");
strTNS.Format(_T("%s =\r\n (DESCRIPTION =\r\n (ADDRESS_LIST =\r\n (ADDRESS = (PROTOCOL = TCP)(HOST = %s)(PORT = %d))\r\n )\r\n"),strTns,stConnInfo.strHostName,stConnInfo.nPort);
strTNS.Format(strTNS + _T(" (CONNECT_DATA =\r\n (SERVER = DEDICATED)\r\n (SERVICE_NAME = %s)\r\n )\r\n )"),strService);
vecLines.push_back(strTNS);
retcode = cFileOP.WriteTNSNamesOraData(strOrgPath.GetBuffer(strOrgPath.GetLength()),vecLines);
}
//初始化键
if (!retcode)
{
WriteLog((LPCTSTR)m_strLogName,_T("创建TNSNames失败"));
return retcode;
}
return retcode;
}
//建立DNS
int XhDBInterface::CreateDBSourceName(stConnDBInfo stConnInfo)
{
int nRval = 1;
switch (m_nDBType)
{
case Oracle:
{
stNormalDSN stDSNInfo;
strcpy(stDSNInfo.strName,stConnInfo.strDBName);
strcpy(stDSNInfo.strIP,stConnInfo.strHostName);
stDSNInfo.nPort = stConnInfo.nPort;
strcpy(stDSNInfo.strUID,stConnInfo.strUserName);
strcpy(stDSNInfo.strPwd,stConnInfo.strPassword);
CString strTns("");
strTns.Format(_T("%s"),stConnInfo.strTNSSN);
strTns.Trim();
nRval = CreateOracleDBSourceName(stDSNInfo,strTns);
}
break;
default:
{
//默认访问Oracle数据库
m_nDBType = Oracle;
stNormalDSN stDSNInfo;
strcpy(stDSNInfo.strName,stConnInfo.strDBName);
strcpy(stDSNInfo.strIP,stConnInfo.strHostName);
stDSNInfo.nPort = stConnInfo.nPort;
strcpy(stDSNInfo.strUID,stConnInfo.strUserName);
strcpy(stDSNInfo.strPwd,stConnInfo.strPassword);
CString strTns("");
strTns.Format(_T("%s"),stConnInfo.strTNSSN);
strTns.Trim();
nRval = CreateOracleDBSourceName(stDSNInfo,strTns);
}
break;
}
return nRval;
}
增删查改 就不在这介绍了