MFC uses ADO to connect to SQL Server database

VC uses ADO to access the database. This paper introduces the various objects and methods that VC uses ADO to access the database. It is very classical and practical. It is worth seeing.


text

Overview of ADO
ADO is designed by Microsoft for OLE DB, the latest and most powerful data access paradigm. It is a user-friendly application layer interface. ADO enables you to write applications to access and manipulate data in database servers through OLE. DB providers. The main advantages of ADO are easy to use, fast speed, less memory expenditure and less disk legacy. ADO uses the least network traffic in key applications and the least number of layers between the front end and data sources, all of which are designed to provide lightweight, high-performance interfaces. Why is it called ADO is a familiar metaphor, OLE automation interface.

OLE DB is a group of COM interfaces. It is a new low-level database interface. It encapsulates the functions of ODBC and accesses data stored in different information sources in a unified way. OLE DB is the technical foundation of Microsoft UDA(Universal Data Access) strategy. OLE DB provides high-performance access to any data source, including relational and non-relational databases, e-mail and file systems, text and graphics, custom business objects, and so on. In other words, OLE DB is not limited to ISAM, Jet or even relational data sources. It can process any type of data without considering their format and storage methods. In practical applications, this diversity means that data residing in Excel spreadsheets, text files, e-mail/directory services and even mail servers, such as Microsoft Exchange, can be accessed. However, the purpose of OLE DB application programming interface is to provide the best functionality for various applications, which does not meet the requirements of simplification. The API you need should be a bridge between the application and OLE DB, which is ActiveX Data Objects (ADO).


Second, use ADO in VC (good development steps:)

1. Introducing ADO library files
Before using ADO, the ADO library file must be introduced into the stdafx.h header file of the project with the direct import symbol # import so that the compiler can compile correctly. The code is as follows:
Introducing ADO Library Files with # import
#import "c:\program files\common files\system\ado\msado15.dll"no_namespaces rename("EOF" adoEOF")
This Statement Declares the use of ADO in engineering, but does not use ADO's namespace, and to avoid constant conflicts, the constant EOF is renamed adoEOF. Now you can use the ADO interface without adding another header file.

2. Initialization of OLE/COM Library Environment
It must be noted that the ADO library is a set of COM dynamic libraries, which means that the application must initialize the OLE/COM library environment before calling ADO. In MFC applications, a better way is to initialize the OLE/COM library environment in the InitInstance member function of the main class of the application.
BOOL CMyAdoTestApp: : InitInstance()
{
If (! AfxOleInit ()// / This is the initialization COM Library
{
AfxMessageBox("OLE initialization error!");
return FALSE;
}
……
}

3. Introduction of ADO Interface
The ADO library contains three basic interfaces: the _ConnectionPtr interface, _CommandPtr interface and _RecordsetPtr interface.
_ The ConnectionPtr interface returns a recordset or a null pointer. Usually it is used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure. Returning a recordset using the _ConnectionPtr interface is not a good way to use it. The _RecordsetPtr is usually used for operations to return records. While using _ To get the number of records in the Connection Ptr operation, you have to traverse all records, but you don't need to use _RecordsetPtr.
_ The CommandPtr interface returns a recordset. It provides a simple way to execute stored procedures and SQL statements that return recordsets. When using the _CommandPtr interface, you can either use the global _ConnectionPtr interface or use the connection string directly in the _CommandPtr interface. If you only perform one or more data access operations, the latter is a better choice. But if you want to visit the database frequently and return many recordsets, you should use the global_ConnectionPtr interface to create a data connection, and then use the _ The CommandPtr interface executes stored procedures and SQL statements.

_ RecordsetPtr is a recordset object. Compared with the above two objects, it provides more control functions for recordset, such as record locking, cursor control and so on. with _ Like the CommandPtr interface, it does not have to use a data connection that has been created. Instead of a connection pointer assigned to the connection member variable of _RecordsetPtr, it can use a connection string to create its own data connection. If you want to use multiple recordsets, the best way is to use the global _ConnectionPtr interface that has created the data connection, like the Command object, and then use _RecordsetPtr to execute stored procedures and SQL statements.

4. Use the _ConnectionPtr interface

_ Connection Ptr is mainly a connection interface, which obtains and connects with data base Connection. Its connection string can either be written directly by itself or point to an ODBC DSN.

_ConnectionPtr pConn;  
if (FAILED(pConn.CreateInstance("ADODB.Connection")))  
{  
<span style="white-space:pre">  </span>AfxMessageBox("Create Instance failed!");  
<span style="white-space:pre">  </span>return;  
}  
CString strSRC;  
strSRC="Driver=SQL Server;Server=";  
strSRC+="suppersoft";  
strSRC+=";Database=";  
strSRC+="mydb";  
strSRC+=";UID=SA;PWD=";  
  
CString strSQL = "Insert into student(no,name,sex,address) values(3,"aaa","male","beijing")";  
  
_variant_t varSRC(strSRC);  
_variant_t varSQL(strSQL);  
_bstr_t bstrSRC(strSRC);  
  
if (FAILED(pConn->Open(bstrSRC,"","",-1)))  
{  
AfxMessageBox("Can not open Database!");  
pConn.Release();  
return;  
}  
  
COleVariant vtOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);  
  
pConn->Execute(_bstr_t(strSQL),&vtOptional,-1);  
  
pConn.Release();  
AfxMessageBox("ok!");  

5. Use the _RecordsetPtr interface (take connecting to SQL Server as an example)

_RecordsetPtr pPtr;  
if (FAILED(pPtr.CreateInstance("ADODB.Recordset")))  
{  
AfxMessageBox("Create Instance failed!");  
return FALSE;  
}  
  
CString strSRC;  
strSRC="Driver=SQL Server;Server=";  
strSRC+="210.46.141.145";  
strSRC+=";Database=";  
strSRC+="mydb";  
strSRC+=";UID=sa;PWD=";  
strSRC+="sa";  
  
CString strSQL = "select id,name,gender,address from personal";  
  
_variant_t varSRC(strSRC);  
_variant_t varSQL(strSQL);  
  
if(FAILED(pPtr->Open(varSQL,varSRC,adOpenStatic,adLockOptimistic,adCmdText)))  
{  
<span style="white-space:pre">  </span>AfxMessageBox("Open table failed!");  
<span style="white-space:pre">  </span>pPtr.Release();  
<span style="white-space:pre">  </span>return FALSE;  
}  
  
while(!pPtr->GetadoEOF())  
{  
_variant_t varNo;  
_variant_t varName;  
_variant_t varSex;  
_variant_t varAddress;  
  
varNo = pPtr->GetCollect ("id");  
varName = pPtr->GetCollect ("name");  
varSex = pPtr->GetCollect ("gender");  
varAddress = pPtr->GetCollect ("address");  
  
CString strNo =(char *)_bstr_t(varNo);  
CString strName =(char *)_bstr_t(varName);  
CString strSex =(char *)_bstr_t(varSex);  
CString strAddress =(char *)_bstr_t(varAddress);  
  
strNo.TrimRight();  
strName.TrimRight();  
strSex.TrimRight();  
strAddress.TrimRight();  
  
int nCount = m_list.GetItemCount();  
int nItem = m_list.InsertItem (nCount,_T(""));  
m_list.SetItemText (nItem,0,strNo);  
m_list.SetItemText (nItem,1,strName);  
m_list.SetItemText (nItem,2,strSex);  
m_list.SetItemText (nItem,3,strAddress);  
  
pPtr->MoveNext();  
}  
  
pPtr->Close();  
pPtr.Release();  
6. Use the _CommandPtr interface
_ The CommandPtr interface returns a Recordset object and provides more recordset control functions. The following code illustrates how to use the _CommandPtr interface:

Data acquisition using the _CommandPtr interface

_CommandPtr pCommand;  
_RecordsetPtr pRs;  
pCommand.CreateInstance(__uuidof(Command));  
pCommand->ActiveConnection=pConn;  
pCommand->CommandText="select * from student";  
pCommand->CommandType=adCmdText;  
pCommand->Parameters->Refresh();  
pRs=pCommand->Execute(NULL,NULL,adCmdUnknown);  
_variant_t varValue = pRs->GetCollect("name");  
CString strValue=(char*)_bstr_t(varValue);  

6. As the COM object is cross-platform, it uses a general method to process all kinds of data.
Therefore, Cstring class and COM object are incompatible. We need a set of API s to transform COM object and C++ type data. _ vatiant_t and _bstr_t are two such objects. They provide a common way to transform COM objects and C++ type data.

Tags: Database SQL less odbc

Posted on Tue, 16 Apr 2019 16:06:33 -0400 by habs20