ADO连接MS SQL

首先在stdafx.h中用宏创建ADO对象：

#import "c:/program files/common files/system/ado/msado15.dll" no_namespace rename("EOF","adoEOF") 

 

在应用程序类中初始化交互用的COM组件：

CWinApp::InitInstance();

 AfxEnableControlContainer();

 AfxOleInit();//初始化COM组件
 // 标准初始化
 // 如果未使用这些功能并希望减小

 

操作数据库的类：

#pragma once //访问数据库的类 class DBAccess { private: _ConnectionPtr m_ConnectionPtr;//连接指针 _RecordsetPtr m_RecordsetPtr;//结果字符集 CString m_ConnectString;//连接字串 public: DBAccess(void); ~DBAccess(void); // 获取记录集指针 _RecordsetPtr getRecordsetPtr(void); // 连接数据库 BOOL ConnectDB(void); //断开到数据库的连接 void DisconnectDB(void); // 执行SQL语句 BOOL Select(CString sql); // 更新数据 BOOL Update(CString sql); }; #include "StdAfx.h" #include "DBAccess.h" DBAccess::DBAccess(void) { m_ConnectString=L"driver={SQL Server};Server=127.0.0.1;DATABASE=personMIS;UID=sa;PWD=123456"; m_ConnectionPtr.CreateInstance(__uuidof(Connection)); } DBAccess::~DBAccess(void) { } // 获取记录集指针 _RecordsetPtr DBAccess::getRecordsetPtr(void) { return m_RecordsetPtr; } // 连接数据库 BOOL DBAccess::ConnectDB(void) { m_ConnectionPtr->ConnectionTimeout=2; HRESULT hr; try { hr=m_ConnectionPtr->Open(m_ConnectString.AllocSysString(),"","",adModeUnknown); } catch(_com_error e) { AfxMessageBox(e.Description()); return 0; } if(SUCCEEDED(hr)) { return 1; } return 0; } //断开到数据库的连接 void DBAccess::DisconnectDB(void) { if(m_ConnectionPtr->State==1) { m_ConnectionPtr->Close(); } } // 查询数据库 BOOL DBAccess::Select(CString sql) { if(m_ConnectionPtr->State==1) { m_RecordsetPtr.CreateInstance(__uuidof(Recordset)); m_RecordsetPtr->CursorLocation=adUseClient; _variant_t var; try { m_RecordsetPtr->Open(sql.AllocSysString(),m_ConnectionPtr.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText); } catch (_com_error e) { AfxMessageBox(e.Description()); return 0; } if(m_RecordsetPtr->adoEOF) { return 0; } return 1; } return 0; } // 更新数据 BOOL DBAccess::Update(CString sql) { if(m_ConnectionPtr->State==1) { _variant_t var; try { m_ConnectionPtr->Execute(sql.AllocSysString(),&var,adCmdText); } catch(_com_error e) { AfxMessageBox(e.Description()); return 0; } return 1; } return 0; }

 

 遍历结果集中的数据：

DBAccess x; if(x.ConnectDB()==1) { if(x.Select(L"select * from student")) { CString str,str1; while(!x.getRecordsetPtr()->adoEOF) { str1.Format(L"%d",x.getRecordsetPtr()->GetCollect(_variant_t("id")).intVal); str+=str1; x.getRecordsetPtr()->MoveNext(); } MessageBox(str); } x.DisconnectDB(); }

 

转换数据类型用来显示：

用GetCollect函数返回的类型是_variant

_variant_t到其他各类型的转换:

void ChangeType(
   VARTYPE vartype,
   const _variant_t* pSrc = NULL 
);

VARTYPE的取值：

  
  
   
   VT_EMPTY 
  
  
  
  

   
   Variable type is not specified. 
  
  
  
  
   
   VT_NULL 
  
  
  
  

   
   Variable type is NULL. 
  
  
  
  
   
   VT_I2 
  
  
  
  

   
   Variable type is 2-byte signed INT. 
  
  
  
  
   
   VT_I4 
  
  
  
  

   
   Variable type is 4-byte signed INT. 
  
  
  
  
   
   VT_R4 
  
  
  
  

   
   Variable type is 4-byte real. 
  
  
  
  
   
   VT_R8 
  
  
  
  

   
   Variable type is 8-byte real. 
  
  
  
  
   
   VT_CY 
  
  
  
  

   
   Variable type is currency. 
  
  
  
  
   
   VT_DATE 
  
  
  
  

   
   Variable type is date. 
  
  
  
  
   
   VT_BSTR 
  
  
  
  

   
   Variable type is binary string. 
  
  
  
  
   
   VT_DISPATCH 
  
  
  
  

   
   Variable type is 
   
   IDispatch FAR*. 
  
  
  
  
   
   VT_ERROR 
  
  
  
  

   
   Variable type is SCODE. 
  
  
  
  
   
   VT_BOOL 
  
  
  
  

   
   Variable type is Boolean; True=-1, False=0. 
  
  
  
  
   
   VT_VARIANT 
  
  
  
  

   
   Variable type is 
   
   VARIANT FAR*. 
  
  
  
  
   
   VT_UNKNOWN 
  
  
  
  

   
   Variable type is 
   
   IUnknown FAR*. 
  
  
  
  
   
   VT_UI1 
  
  
  
  

   
   Variable type is unsigned char. 
  
  
  
  
   
   VT_RESERVED 
  
  
  
  

   
   This element is reserved. 
  
  
  
  
   
   VT_BYREF 
  
  
  
  

   
   Variable type is a pointer to data. 
  
  
  
  
   
   VT_ARRAY 
  
  
  
  

   
   Variable type is a safe array. 
  
  举个例子：

     _variant_t var=_varinat_t("haha");

     var.ChangeType(VT_BSTR);

     CString str=var;

 

 其他类型到_variant_t的转换:

 一般用_variant_t(值)

如 _variant_t(50);     值得注意的是，这种方法可以轻松在各类型之间转换

下面是MSDN详细信息

     _variant_t( )   Constructs an empty _variant_t object, VT_EMPTY.

     _variant_t( VARIANT& varSrc )   Constructs a _variant_t object from a copy of the VARIANT object. The variant type is retained.

• _variant_t( VARIANT* pVarSrc )   Constructs a _variant_t object from a copy of the VARIANT object. The variant type is retained.

• _variant_t( _variant_t& var_t_Src )   Constructs a _variant_t object from another _variant_t object. The variant type is retained.

• _variant_t( VARIANT& varSrc, bool fCopy )   Constructs a _variant_t object from an existing VARIANT object. If fCopy is false, the VARIANT object is attached to the new object without making a copy.

• _variant_t( short sSrc, VARTYPE vtSrc = VT_I2 )   Constructs a _variant_t object of type VT_I2 or VT_BOOL from a short integer value. Any other VARTYPE results in an E_INVALIDARG error.

• _variant_t( long lSrc, VARTYPE vtSrc = VT_I4 )   Constructs a _variant_t object of type VT_I4, VT_BOOL, or VT_ERROR from a long integer value. Any other VARTYPE results in an E_INVALIDARG error.

• _variant_t( float fltSrc )   Constructs a _variant_t object of type VT_R4 from a float numerical value.

• _variant_t( double dblSrc, VARTYPE vtSrc = VT_R8 )   Constructs a _variant_t object of type VT_R8 or VT_DATE from a double numerical value. Any other VARTYPE results in an E_INVALIDARG error.

• _variant_t( CY& cySrc )   Constructs a _variant_t object of type VT_CY from a CY object.

• _variant_t( _bstr_t& bstrSrc )   Constructs a _variant_t object of type VT_BSTR from a _bstr_t object. A new BSTR is allocated.

• _variant_t( wchar_t *wstrSrc )   Constructs a _variant_t object of type VT_BSTR from a Unicode string. A new BSTR is allocated.

• _variant_t( char* strSrc )   Constructs a _variant_t object of type VT_BSTR from a string. A new BSTR is allocated.

• _variant_t( bool bSrc )   Constructs a _variant_t object of type VT_BOOL from a bool value.

• _variant_t( IUnknown* pIUknownSrc, bool fAddRef = true )   Constructs a _variant_t object of type VT_UNKNOWN from a COM interface pointer. If fAddRef is true, then AddRef is called on the supplied interface pointer to match the call to Release that will occur when the _variant_t object is destroyed. It is up to you to call Release on the supplied interface pointer. If fAddRef is false, this constructor takes ownership of the supplied interface pointer; do not call Release on the supplied interface pointer.

• _variant_t( IDispatch* pDispSrc, bool fAddRef = true )   Constructs a _variant_t object of type VT_DISPATCH from a COM interface pointer. If fAddRef is true, then AddRef is called on the supplied interface pointer to match the call to Release that will occur when the _variant_t object is destroyed. It is up to you to call Release on the supplied interface pointer. If fAddRef is false, this constructor takes ownership of the supplied interface pointer; do not call Release on the supplied interface pointer.

• _variant_t( DECIMAL& decSrc )   Constructs a _variant_t object of type VT_DECIMAL from a DECIMAL value.

• _variant_t( BYTE bSrc )   Constructs a _variant_t object of type VT_UI1 from a BYTE value.