1) Introduce the ADO library definition file
# Import "C: / Program Files / Common Files / System / ADO / MSADO15.DLL" NO_NAMESPACE
Rename ("EOF", "Adoeof")
Generate two headers for our engineering files when compiling:
Msado15.tlh and ado15.tli
Note that the path to Msado15.dll may be different, please check the path to your own computer
2) Initialize the COM library
In the initInstance () function of the project's app class
Afxoleinit (); // mfc usage
Conventional method of C is --------
::: Coinitialize (NULL);
.
:: Couninitialize ();
3) Use record set object to get the basic step of a recordset
/ / Generate a Connection object and connect the database
_ConnectionPTR m_PConnection;
HRESULT HR;
Try
{
HR = m_pconnection.createInstance (__ uuidof (connection));
En (ac))
{
m_pConnection-> Open ("provider = microsoft.jet.Oledb.4.0;
Data Source = Primary School. MDB ",", "", AdmodeunkNown;
}
}
Catch (_COM_ERROR E)
{
CString ErrorMessage;
ErrorMessage.Format ("Connection Database Failed! / R / N / Error Information:% S", E.ErrorMessage ());
AfXMessageBox; ErrorMessage;
}
/ / Generate a Recordset object and get a recordset
_RecordSetPtr m_precordset;
M_PRecordSet.createInstance ("AdoDb.Recordset");
CString Temp = "SELECT *" TABLENAME; // TableName assumes a cString type table name
_variant_t sql;
SQL.SetString (TEMP);
m_precordset-> open (SQL,
m_pconnection.getInterfacePtr (),
AdoPendynamic, AdlockOptimistic, AdcmdText);
4) Dynamic query
CString Tablename;
TableName = _t ("Study");
CSTRING TEMP = _T ("SELECT *") TABLENAME _T ("Where name = '")
DLG.M_NAME _T ("'");
_variant_t sql;
SQL.SetString (TEMP);
m_precordset-> Open (SQL, M_PConnection.GetInterfacePtr (),
AdoPendynamic, AdlockOptimistic, AdcmdText);
5) Traverse the record
INT line = 0;
While (! m_precordset-> adoeof) // Traverse record set, and display all records in the list view
{
. . . . . .
Line ;
M_PRecordset-> MoveNext ();
}
The rest is simple, you can see other articles, the most critical is, don't forget to close the record, because this usually does not cause an exception 6) Use the ADO to connect different databases:
According to different data providers can be divided into several ways such as ODBC and OLEDB, ODBC compatibility is better, supporting OLEDB databases relatively less;
Connect the SQL Server database:
1) Microsoft OLE DB Provider for ODBC
M_PCONNECTION-> Open ("provider = sqloledb.1; driver = sqlserver; server = lzhm; database = Haitang", "sa", "sa", admodeunknown);
2) Unknown method, but feasible, pay attention to Initial Catalog with spaces
M_PConnection-> Open ("provider = sqloledb.1; data source = lzhm; initial catalog = haitang", "sa", "sa", admodeunknown);
M_PConnection-> Open ("provider = SQLOLEDB; DATA SOURCE = 172.20.2.97; network library = dbmsocn; initial catalog = Haitang", "SA", "sa", admodeunknown);
m_pConnection-> Open ("provider = SQLOLEDB; Network Address = 172.20.2.97; Initial Catalog = Haitang", "SA", "SA", AdmodeunkNown;
M_PConnection-> Open ("provider = SQLOLEDB; Network Address = 172.20.2.2.97; network library = dbmsocco; initial catalog = Haitang", "SA", "sa", admodeunknown);
3) Microsoft Ole DB Provider for SQL Server
M_PConnection-> Open ("provider = sqloledb.1; data source = lzhm; initial catalog = haitang", "sa", "sa", admodeunknown);
4) Connection without DSN
M_PConnection-> Open ("driver = {sql server}; server = lzhm; database = Haitang", "sa", "sa", admodeunknown);
Connect to Access 2000 Database:
m_pConnection-> Open ("provider = microsoft.jet.Oledb.4.0; data source = primary school. MDB", ",", admodeunknown);
Connect to the Access 97 Database:
m_pConnection-> Open ("provider = microsoft.jet.OleDb.3.51; data source = primary school. MDB", ",", admodeunknown); 7) a commonly used function, pass a SQL statement, then open a recordset , Assume that the record set object m_precordset exists:
Bool Chaitangview :: GetRecordset (CString SQL)
{// If the generated record set is not empty, return true
IF (m_precordset-> getState () == 1) // If the recordset object has been opened, first close
m_precordset-> close ();
HRESULT HR;
_VARIANT_T V_SQL;
v_sql.setstring (SQL);
Try
{
HR = m_precordset-> open (v_sql, m_pconnection.getInterfacePtr (), AdoPENDYNAMIC, ADLOCKOPTIMISTICISTIC, ADCMDTEXT
En (ac))
{
}
}
Catch (_COM_ERROR E)
{
CString ErrorMessage;
ErrorMessage.Format ("Query Data Failed! / R / N Error Information:% S", E.ErrorMessage ());
AfXMessageBox; ErrorMessage;
Return 0;
}
M_PRecordset-> MoveFirst ();
If (! m_precordset-> adoEOf) // unable to determine if the generation of record set is empty with successdeded (hr)
{// So if the maximum number of records after the generated record is 0 to discriminate
Return True; // succeededed (HR) should be to discriminate abnormalities, and the record set is empty.
}
Else
{
MessageBox ("Sorry, you can't find a record, please contact your system administrator");
Return False;
}
}
8) Call the issue of press :: PutCollect :: PUTCOLLECT:
SELECT shore properties table. Sequence number, S1.NAME, shore - shore properties, shoreline nature, shore properties, starting point, shore - line property table. Terminal number, shore - line property table. Board length, shoreline Property table. Embankment elevation, shore properties table. Structural form small class, shore type property table. Structural form, shore - line property table. Bottom standard, shore property table. Geographical location, shore type property table. Remark from FROM Shoreland attribute table, data dictionary table as s1 where s1.id = shore property table. Shoreline name Order By Shore Property Table. Serial number
Select * from Hetong Greening Table ORDER BY Number
SELECT Shore Property Table. Sequence Number, S1.Name, S2.Name From Shore Property Table, Data Dictionary Table AS S1, Data Dictionary Table As S2 WHERE S1.ID = Shore Property Table. Shore Name and S2.ID = Shoreland property table. Responsible unit ORDER BY Board properties table. Serial number
During the development of Haitang, it is found that if the SQL statement is used to use the RECORDSET :: Open method to open the recordset, if the order by statement is used in the SQL statement, write data by using the Recordset :: PUTCOLLECT method, at least This is VC, ADO, and
SQL Server 7.0 or 2000.
9) Call the stored procedure, stored in ATL
StdMethodimp cfbget :: getDataByProc (BSTR BSTRPROCNAME, BSTR BSTRPARAM, BSTR * PLPOWER) {
// Todo: Add Your Implementation Code Here
* Plpower = NULL;
Uses_Conversion;
/// Establish and database connections
_ConnectionPTR m_PCON;
m_pcon.createInstance (_UUIDOF (connection));
Char array [200];
GetWindowsDirectory (Array, 200);
STRCAT (Array, "// fb_data.ini");
Char Servername [100];
GetPrivateProfileString ("ServerName", "Name", "", ServerName, 100, Array;
Char Str_Con [1024];
WSPrintf (str_con, "provider = sqloledb.1; data source =% s; initial catalog = haitang; user id = haitang; pwd = haitang", servername);
m_pcon-> open (str_con, ",", admodeunknown);
///
/// Establish a command object
_Commandptr m_pcommand;
M_PCommand.createInstance ("AdoDb.command");
M_PCOMMAND-> ActiveConnection = m_PCON;
_VARIANT_T Name = "proc_user";
BSTR BNAME = SYSALLOCSTRING (OLESTR ("proc_user");
m_pcommand-> commandtext = BNAME;
m_pcommand-> commandtype = adcmdstoredProc;
/// Convert parameters to a char * type, extract two char * parameters
Char * p = w2a (bstrparam);
Char * TEMP = P;
Char * p1 = TEMP;
While (* temp! = 't')
{
TEMP ;
}
* temp = '/ 0';
TEMP ;
Char * p2 = TEMP;
While (* temp! = 't')
{
TEMP ;
}
* temp = '/ 0';
// Convert two parameters to BSTR type
BSTR BSTRP1 = A2W (P1);
BSTR BSTRP2 = A2W (P2);
/// Establish parameter object 1
_ParameterPtr P_Param1;
P_Param1.createInstance ("AdoDb.Parameter");
P_Param1-> name = "name";
p_PARAM1-> Type = advarchar;
P_PARAM1-> SIZE = 50;
P_PARAM1-> Direction = Adparaminput;
P_PARAM1-> Value = BSTRP1;
m_pcommand-> parameters-> append (p_param1);
/// Establish parameter object 2
_ParameterPtr p_param2;
P_Param2.createInstance ("AdoDb.Parameter"); p_param2-> name = "key";
P_PARAM2-> Type = advarchar;
P_PARAM2-> size = 50;
P_PARAM2-> Direction = Adparaminput;
P_PARAM2-> Value = BSTRP2;
M_PCommand-> parameters-> append (p_param2);
/// Establish parameter object 3
_BSTR_T BSTRP3;
_Parameterptr p_param3;
P_Param3.createInstance ("AdoDb.Parameter");
p_param3-> name = "power";
p_PARAM3-> Type = adINteger;
p_PARAM3-> size = 10;
P_PARAM3-> Direction = Adparamoutput;
// p_param3-> value = BSTRP3; // Due to the output parameter, the value value cannot be specified.
M_PCommand-> parameters-> append (p_param3);
// / Execute a stored procedure and get an output parameter
M_PCommand-> Execute (NULL, NULL, ADCMDSTOREDPROC);
_BSTR_T value = p_param3-> getValue ();
Const char * PPP = VALUE;
* plpower = sysallocstring (A2W (PPP));
Return S_OK;
}
Note that if you only write only a SELECT statement in the stored procedure, you will return a recordset object after execution, and the ADO can pass
M_PRecordet = m_pcommand-> execute (....) is accepted.
However, if the stored procedure contains multiple SELECT statements, ADO accepts record set objects are not available.
10) Use the command object to execute SQL statements
_Commandptr m_pcommand;
M_PCommand.createInstance ("AdoDb.command");
m_pcommand-> activeConnection = m_pcon; // m_pcon is a database connection object
m_pcommand-> commandtext = "Update table1 set num_id = '2' where value = '44'"
m_pcommand-> commandtype = adcmdtext;
M_PCommand-> parameters-> refresh ();
// / Execute a stored procedure and get an output parameter
M_PCommand-> Execute (NULL, NULL, ADCMDUNKNOWN);
11) Get the number of recorded fields
m_precordset-> fields-> getCount ();
Field types can also be obtained in a similar manner
Fieldsptr Fields = M_REC-> getfields ();
* pcactualcolsize = fields-> getCount ();
Variant varindex;
Variantinit (& varIndex);
Varindex.vt = vt_i4;
Fieldptr Field;
DataTyPeenum adotype;
For (long i = 0; i <* pCActualcolsize; i ) {
Varindex.lval = i;
Field = Fields-> GetItem (varindex);
adotype = field-> gettype ();
}
Here is the type corresponding table
ENUM DATATYPEENUM
{Adempty = 0,
ADTINYINT = 16,
ADSMALLINT = 2,
Adinteger = 3,
Adbigint = 20,
ADUNSIGNEDTINYINT = 17,
Adunsignedsmallint = 18,
Adunsignedint = 19,
AdunsignedBigint = 21,
ADSINGLE = 4,
Addouble = 5,
ADCURRENCY = 6,
Addecimal = 14,
Adnumeric = 131,
Adboolean = 11,
adror = 10,
AduserDefined = 132,
Advariant = 12,
Adidispatch = 9,
Adiunknown = 13,
Adguid = 72,
Addate = 7,
AddBdate = 133,
Addbtime = 134,
AddBTimeStamp = 135,
Adbstr = 8,
ADCHAR = 129,
Advarchar = 200,
Adlongvarchar = 201,
Adwchar = 130,
Advarwchar = 202,
Adlongvarwchar = 203,
Adbinary = 128,
Advarbinary = 204,
Adlongvarbinary = 205,
ADCHAPTER = 136,
Adfiletime = 64,
Adpropvariant = 138,
AdvarNumeric = 139
DataTyPeenum;
Typedef Enum ParameterDirectionNum
{Dbparaminput = 1,
dbparamoutput = 2,
DBPARAMINPUTOUTPUT = 3,
dbparamreturnValue = 4
ParameterDirectionNum;
