ADO 连接sql server

注意：

下面的sql server数据库连接方式，性能从低到高
1.DSN
   strConnect = "DSN=mng;DRIVER={SQL Server};DATABASE=msdb;";
2.DSN-less
   strConnect = "driver={SQL Server};Server=125.44.5.22;Database=eett;uid=sa;pwd=sa";
3.OLE DB
   strConnect = "Provider=SQLOLEDB;Server=125.44.5.22;Database=eett;uid=sa;pwd=sa";

 

2.stdafx.h中添加

   #import "C:\Program Files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF")

3.一个sql连接类

           1）CSqlServer.h文件

#pragma once

class CSqlServer
{
public:
 CSqlServer(void);
public:
 ~CSqlServer(void);

public:
 bool Open();
 void Close();
 bool Execute(char* commandText, _RecordsetPtr& recordsetPtr=(_RecordsetPtr)NULL);
 bool Execute(CString strCommandText, _RecordsetPtr& recordsetPtr=(_RecordsetPtr)NULL);

 void WriteConnectInfo();
 void ReadConnectInfo();
public:
 _ConnectionPtr m_pConnection;

 CString m_strProvider;
 CString m_strServer;
 CString m_strDatabase;
 CString m_strUid;
 CString m_strPwd;
};

2）CSqlServer.cpp文件

#include "StdAfx.h"
#include "SqlServer.h"

CSqlServer::CSqlServer(void)
{
 ::CoInitialize(NULL);
}

CSqlServer::~CSqlServer(void)
{
 ::CoUninitialize();
}

bool CSqlServer::Open()
{
 ReadConnectInfo();
 CString strConnect = "Provider=" + m_strProvider +
      ";Server=" + m_strServer +
      ";Database=" + m_strDatabase +
      ";uid=" + m_strUid +
      ";pwd=" + m_strPwd;

 try{
  //创建Connection 对象
  if (FAILED(m_pConnection.CreateInstance("ADODB.Connection")))
   return false;

  // 开始连接
  if(FAILED(m_pConnection->Open(_bstr_t(strConnect),"","",adModeUnknown)))
  {
   m_pConnection.Release();
   return false;
  }
 }
 catch(_com_error e)
 {
  return false;
 }

 return true;
}

void CSqlServer::Close()
{
 //不能多次关闭，否则会出现错误
 if(m_pConnection->State)
  m_pConnection->Close();
}

bool CSqlServer::Execute(char* commandText, _RecordsetPtr& recordsetPtr)
{
 return Execute(CString(commandText), recordsetPtr);
}

bool CSqlServer::Execute(CString strCommandText, _RecordsetPtr& recordsetPtr)
{
 if (strCommandText.IsEmpty())
  return false;

 try
 {
  COleVariant vtOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
  recordsetPtr = m_pConnection->Execute(_bstr_t(strCommandText),&vtOptional,-1);
 }
 catch (_com_error e)
 {
  return false;
 }

 return true;
}

void CSqlServer::WriteConnectInfo()
{
 ::WritePrivateProfileString("ConnectInfo", "Provider", m_strProvider, ".\\SqlServerConnect.ini");
 ::WritePrivateProfileString("ConnectInfo", "Server",   m_strServer,   ".\\SqlServerConnect.ini");
 ::WritePrivateProfileString("ConnectInfo", "Database", m_strDatabase, ".\\SqlServerConnect.ini");
 ::WritePrivateProfileString("ConnectInfo", "uid",      m_strUid,      ".\\SqlServerConnect.ini");
 ::WritePrivateProfileString("ConnectInfo", "pwd",      m_strPwd,      ".\\SqlServerConnect.ini");
}

void CSqlServer::ReadConnectInfo()
{
 char cInfo[20];

 ::GetPrivateProfileString("ConnectInfo", "Provider", "", cInfo, 20, ".\\SqlServerConnect.ini");
 m_strProvider = CString(cInfo);

 ::GetPrivateProfileString("ConnectInfo", "Server",   "", cInfo, 20, ".\\SqlServerConnect.ini");
 m_strServer = CString(cInfo);

 ::GetPrivateProfileString("ConnectInfo", "Database", "", cInfo, 20, ".\\SqlServerConnect.ini");
 m_strDatabase = CString(cInfo);

 ::GetPrivateProfileString("ConnectInfo", "uid",      "", cInfo, 20, ".\\SqlServerConnect.ini");
 m_strUid = CString(cInfo);

 ::GetPrivateProfileString("ConnectInfo", "pwd",      "", cInfo, 20, ".\\SqlServerConnect.ini");
 m_strPwd = CString(cInfo);
}

 

3）SqlServerConnect.ini文件

[ConnectInfo]
Provider=SQLOLEDB
Server=125.44.5.22
Database=eett
uid=sa
pwd=sa

4)测试

CString sqlCommand = "select * from eee";
 _RecordsetPtr recordsetPtr;
 if (m_sqlServer.Execute(sqlCommand, recordsetPtr))
  MessageBox("执行成功");
 else
 {
  MessageBox("执行失败");
  return;
 }

 while (!recordsetPtr->GetadoEOF())
 {
    _variant_t  _eee;
    _eee = recordsetPtr->GetCollect ("eee");

   char* tt;

    if (_eee.vt == VT_NULL)
      return false;
    else
    {

       _bstr_t str_eee = _eee;

       tt= str_eee;
    }

 

  //  没有做判断处理的操作

  // int sss = recordsetPtr->GetCollect ("sss");

  recordsetPtr->MoveNext();
 }