VC ++ connects to SQL Server using ADO
I. Reference ADO database
Use # import to introduce the ADO library file and reference it in the program's header file (for example, stdafx. h ).
# Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll" no_namespace Rename ("EOF", "adoeof ")
Ii. initialize the com Library
: Coinitialize (null );
3. Use the interfaces used in the ADO Library
The ADO library contains three basic interfaces: _ connectionptr, _ commandptr, and _ recordsetptr.
_ Connectionptr interface returns a record set or a null pointer. It is usually used to create a data connection or execute an SQL statement that does not return any results, such as a stored procedure. Using the _ connectionptr interface to return a record set is not a good method. Generally, like cdatabase, you can use it to create a data connection and use other objects to perform data input/output operations.
The _ commandptr interface returns a record set. It provides a simple method to execute stored procedures and SQL statements that return record sets. When using the _ commandptr interface, you can use the global _ connectionptr interface, or you can directly use the connection string in the _ commandptr interface. If you only perform one or several data access operations, the latter is a good choice. However, if you want to frequently access the database and return many record sets, you should use the global _ connectionptr interface to create a data connection, use the _ commandptr interface to execute stored procedures and SQL statements.
_ Recordsetptr is a record set object. Compared with the above two types of objects, it provides more control functions for the record set, such as record lock and cursor control. Like the _ commandptr interface, it does not have to use a created data connection. You can use a connection string to replace the connection pointer with the connection Member variable assigned to _ recordsetptr, create a data connection by yourself. If you want to use multiple record sets, the best way is to use the global _ connectionptr interface that has created a data connection like the command object, and then use _ recordsetptr to execute the stored procedure and SQL statement.
1. Use _ connectionptr to connect to the database (two methods)
Database connection code
Define the object in the program header file
_ Connectionptr m_pconnection; // connection object
In the program, the program to connect to the database is as follows:
Hresult hr;
Try
{
HR = m_pconnection.createinstance (_ T ("ADODB. Connection"); // create a connection object
If (succeeded (HR ))
{
HR = m_pconnection-> open ("provider = sqloledb; Data Source = servername; server = database; uid = sa; Pwd =;", "", "", admodeunknown );
// This is a non-DSN connection string, in which servername, "database server name"; database, "Database Name"; uid, "User Name"; PWD, "password ";
// HR = m_pconnection-> open ("DSN = database; uid = sa; Pwd =;", "", "", admodeunknown );
// This is a database string connected to the database using the DSN method, where the database, "Data Source defined in The DSN"
}
Else
{
Afxmessagebox (_ T ("connection failed! "));
}
}
Catch (_ com_error e) // catch an exception
{
Cstring errormessage;
Errormessage. Format (_ T ("failed to connect to the database! \ R \ n error message: % s "), E. errormessage ());
Afxmessagebox (errormessage); // displays the error message.
}
2. Use the _ recordsetptr Interface
Code for getting table data
_ Recordsetptr myset; // defines the record set object
Myset. createinstance (_ uuidof (recordset ));
Myset-> open ("select * From some_table", m_pconnection.getinterfaceptr (), adopendynamic, adlockoptimistic, adshorttext );
// The following code uses the _ recordsetptr interface to obtain record set data and fill in the ListBox control (m_list ):
_ Variant_t holder
Try {
While (! Myset-> adoeof)
{
Holder = myset-> getcollect ("field_1"); // name of the table field in the database
If (holder. VT! = Vt_null)
M_list.addstring (char *) _ bstr_t (holder ));
Myset-> movenext ();
}
}
Catch (_ com_error * E)
{
Cstring error = e-> errormessage ();
Afxmessagebox (e-> errormessage ());
}
3. Use the _ commandptr Interface
Obtain the table data code using commands and datasets
_ Commandptr pcommand;
_ Recordsetptr myset;
Pcommand. createinstance (_ uuidof (command ));
Pcommand-> activeconnection = m_pconnection;
Pcommand-> commandtext = "select * From some_table ";
Pcommand-> commandtype = ad1_text;
Pcommand-> parameters-> refresh ();
Myset = pcommand-> execute (null, null, adcmdunknown );
_ Variant_t thevalue = myset-> getcollect ("field_1 ");
Cstring svalue = (char *) _ bstr_t (thevalue );
4. Data Type Conversion
Because the COM object is cross-platform, it uses a common method to process various types of data. Therefore, the cstring class is incompatible with the COM object, we need a set of APIs to convert data of the COM Object and C ++ type. _ Vatiant_t and _ bstr_t are two types of objects. They provide common methods to convert data of the COM Object and C ++ type.