I. ADO Introduction ADO (ActiveX Data Object) is a new interface developed by Microsoft Database Application. It is a high-level database access technology built on OLE DB. Please don't have to worry about it, even if you have Ole DB, COM doesn't understand. Easily deal with Ado because it is very easy to use, even more likely to use than you have contacted in the past, and it is easy to use, and it is not flexible. This article will detail how to use ADO to develop database applications in VC, and give sample code. This example code is two, the basic process is difficult, and any new technique is the most important thing for beginners to "get started" and master it. Let's take a look at the basic procedures developed by the ADO database! (1) Initialize the COM library, introduce the ADO library definition file (2) Connect the database (3) with the Connection object to use the established connection, execute the SQL command via the Connection, Command object, or use the Recordset object to obtain the results record set for querying, processing . (4) Turn off the connection release object after use. Preparation: For everyone to test the examples provided herein, we use the Access database, you can also find this Test.mdb directly in the sample code we provide. Below we will introduce the above steps and give relevant code. [1] Initialization of the COM library We can use AFXoleinit () to initialize the COM library. This work is usually done in the overload function of cwinapp :: initInstance (). Please see the following code: BOOL Cadotest1app :: InitInstance ()
{
Afxoleinit ();
......
[2] To introduce the ADO type library with a #import command We add the following statement in stdafx.h: (STDAFX.H This file can you find? You can find it in the Header Files in FileView) #import "C: / Program Files / Common files / system / ado / msado15.dll "No_namespace rename" What is the role of this statement? Its ultimate role is similar to our familiar #include. When compiling, the system will generate msado15.tlh, ado15.tli two C header files to define the ADO library. Some descriptions: (1) Msado15.dll in your environment is not necessarily in this directory, please modify (2) When compiling, it will appear as follows, and the Microsoft is in MSDN. And it is recommended that we don't pay attention to this warning. msado15.tlh (405): warning C4146: unary minus operator applied to unsigned type, result still unsigned 【3】 create a Connection object and connect to the database we need to add a reference to the Connection object pointer: _ConnectionPtr m_pConnection; The following code shows how Create a Connection object instance and how to connect the database and perform an exception capture. Bool Cadotest1dlg :: OnNitdialog ()
{
CDIALOG :: OnInitdialog ();
HRESULT HR;
Try
{
HR = m_pconnection.createInstance ("AdoDb.Connection"); // Create a Connection object
En (ac))
{
HR = m_pconnection-> open ("provider = microsoft.jet.Oledb.4.0; data source = test.mdb", ",", ", admodeunknown); /// connection database
/// The provider in the connection string in the above sentence is for the Access2000 environment, for Access 97, it needs to be changed to: provider = microsoft.jet.OleDb.3.51;
}
}
Catch (_COM_ERROR E) /// Capture Exception
{
CString ErrorMessage;
ErrorMessage.Format ("Connection Database Failed! / R / N Error Information:% S", E.ErrorMessage ());
AFXMessageBox; /// Display error message
}
In this code, we are connecting the database through the Open method of the Connection object. Here is the prototype of the method HRESULT Connection15 :: Open (_bstr_t connectionstring, _bstr_t userid, _bstr_t password, long options) Connectionstring is a connection string, userid Is a username, password is the login password, Options is the connection option, which is used to specify the update of the Connection object to the data. Options can be as follows: admodeunknown: Default. The current license is not adModeRead settings: read-only adModeWrite: write only adModeReadWrite: can read and write adModeShareDenyRead: Connection object to prevent other read permissions to open a connection adModeShareDenyWrite: Connection object to prevent other write permissions to open a connection adModeShareExclusive: Connection object to prevent other read and write Permissions Open AdmodesharedENYNONE: Block Other Connection objects From any permissions We give some common connection methods for your reference: (1) Connection M_PConnection-> Open through the Jet Database Engine to Access2000 Database ("Provider = Microsoft.jet. OLEDB.4.0; Data Source = C: //test.mdb "," ",", ", admodeunknown); (2) Connecting any support ODBC's database via DSN data sources: m_pconnection-> open (" Data Source = Adotest; UID = SA; PWD =; ",", ",", admodeunknown); (3) Do not connect to the SQL Server database via DSN: m_pConnection-> Open ("driver = {sql server}; server = 127.0. 0.1; Database = VCKBASE; UID = SA; PWD = 139 "," "," ", admodeunknown); where Server is the name of the SQL server, Database is the name of the library, and there are many ways to use the Open method. Describes that two useful properties in the Connection object ConnectionTimeout and StateConnectionTimeout are used to set the timeout timeout, you need to call before Open, for example: m_pconnection-> connectiontimeout = 5; // set timeout time is 5 seconds
m_pconnection-> Open ("Data Source = Adotest;", "," ", AdmodeUnknown); State property indicates the status of the current Connection object, 0 indicates that it is turned off, 1 indicates that it is already open, we can use this attribute as a corresponding Process, for example: if (m_pconnection-> state) m_pconnection-> close (); // If the connection has been opened, turn off it
[4] Execute the SQL command and acquire the result record set to obtain the result record set, we define a pointer to the Recordset object: _RecordSetPtr m_precordset; and create an instance of the Recordset object for it: m_precordset.createInstance ("adodb.recordset"); SQL The execution of the command can be used in a variety of forms, and we will explain it. (1) using the Connection object's Execute method executes the SQL command prototype Execute method is as follows: _RecordsetPtr Connection15 :: Execute (_bstr_t CommandText, VARIANT * RecordsAffected, long Options) wherein CommandText is a command string, usually SQL commands. Parameter recordsaffected is the number of rows affected after the operation is completed. The parameter Options represents the type of content in CommandText. Options can take one of the values: AdcmdText: Indications CommandText is a text command adcmdtable: indicate that CommandText is a table name AdcmdProc: indicating that CommandText is a table name AdcmdProc: Stored Procedure AdcmDunkNown: Unknown Execute End Returns a pointer to record sets, below we give specific code and instructions. _VARIANT_T RECORDSAFFECTED;
/// Execute SQL Command: CREATE TABLE Create Table Users, users contain four fields: shaping ID, string username, plastic OLD, date Birthday
M_PCONNECTION-> Execute ("Create Table Users (ID Integer, User, Birthday DateTime), & Recordsaffected, AdcmdText);
/// Add a record in the form
M_PCONNECTION-> Execute ("INSERT INTO Users (ID, Username, Old, Birthday) Values (1, 'Washington', 25, '1970/1/1')", & Recordsaffected, AdcmdText);
/// Put all the values of the record OLD field one
M_PConnection-> Execute ("Update Users Set Old = OLD 1", & Recordsaffected, AdcmdText);
/// Execute the SQL statistical command to get a recordset containing the number of records
M_PRecordSet = m_pconnection-> execute ("Select Count (*) from Users", & Recordsaffected, AdcmdText);
_VARIANT_T VINDEX = (long) 0;
_variant_t vcount = m_precordset-> getCollect (vindex); // / 5 Value of the first field is placed in a vcount variable
m_precordset-> close (); /// Close record set
CString Message; Message.Format ("a total of% D record", vcount.lval);
AfxMessageBox (Message); /// Displays the current record number
(2) Use the Command object to execute the SQL command _Commandptr m_pcommand;
M_PCommand.createInstance ("AdoDb.command");
_VARIANT_T VNULL;
vnull.vt = vt_error;
vnull.scode = disp_e_paramnotfound; // / defined as no parameters
M_PCommand-> ActiveConnection = m_pconnection; /// Very critical sentence, the established connection is assigned to it
m_pcommand-> commandtext = "select * from users"; /// command string
m_precordset = m_pcommand-> execute (& vnull, "& vnull, adcmdtext); // execute command, get record set
In this code, we just use the Command object to perform the SELECT query statement, and the Command object can really reflect its role in the call to the stored procedure. Next time we will introduce it. (3) Direct query directly with the Recordset object, for example, M_PRecordSet-> Open ("Select * from users", _ variant_t ((idispatch *) m_pconnection, true), adopenStatic, AdlockOptimistic, adcmdtext
Open prototype method is such that: HRESULT Recordset15 :: Open (const _variant_t & Source, const _variant_t & ActiveConnection, enum CursorTypeEnum CursorType, enum LockTypeEnum LockType, long Options) wherein: ①Source query string data is already established connections ②ActiveConnection (We need to construct a _variant_t object) 3CURSORTYPE cursor type, which can be one of the following values, please see this enumeration structure: enum cursortypeenum {adopenunSpecified = -1, /// Do not specify adopenforwardonly = 0 , // prior rolling static cursor. This cursor can only browse the record in front, such as scrolling forward with MoveNext, this way can improve the browsing speed. But such as Bookmark, RecordCount, AbsolutePosition, AbsolutePosition, AbsolutePage can't use AdopenKeyset = 1, /////> Records that use this cursor can not see new, delete operations, but for updating the original records, you are visible to you of. AdoPENDYNAMIC = 2, // / Dynamic Cursor. The operation of all databases will be immediately reacted on each user recordset. AdopenStatic = 3 /// Static cursor. It produces a static backup for your recordset, but the new, delete, and update operations of other users are invisible to your record set. }; 4LockType lock type, it can be one of the following values, please see the following enumeration: enum locktypeenum {adlockunSpecified = -1, /// Non-specified AdlockReadOnly = 1, /// read-only record set AdlockPESSIMISTIC = 2, pessimistic Locking method. Data locks all other actions at the time of update, this is the safest lock mechanism AdlockOptimistic = 3, optimistic locking mode. Lock the record only when you call the UPDATE method. You can still do data update, insert, delete, etc. before this, AdlockBatchOptimistic = 4, optimistic batch update. When editing, the record does not lock, change, insert, and delete it is done in batch mode. }; 5Options Please refer to the introduction of the EXECUTE method of the Connection object [5] record set in this article, the update is built according to the use of the SQL command, which contains four fields: ID, Username, Old, BIRTHDAY The following code implementation: Open the record set, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age, save to the database. _variant_t vusername, vbirthday, vid, vend; _recordsetptr m_precordset
M_PRecordSet.createInstance ("AdoDb.Recordset");
M_PRecordset-> Open ("Select * from users", _ variant_t (idispatch *) m_pconnection, true), adopenStatic, AdlockOptimistic, AdcmdText);
While (! m_precordset-> adoEOf) /// This is ADOEOF instead of EOF? Remember Rename ("EOF", "AdoEOF") This sentence?
{
Vid = m_precordset-> getCollect (_variant_t (long) 0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))) The value of the first column, starting from 0, you can also give the name directly, as the next Vusename = m_precordset-> getCollect ( "username"); // / get the value of the username field
VOLD = m_precordset-> getCollect ("old");
VbirdHDAY = m_precordset-> getCollect ("birthday");
/// In the DEBUG mode, the record is output in the record set.
IF (vid.vt! = vt_null && vusername.vt! = vt_null && vold.vt! = vt_null && vbirdhday.vt! = vt_null)
Trace ("ID:% D, Name:% S, Age:% D, Birthday:% S / R / N", Vid.Lval, (LPCTSTSTR) (_ BSTR_T) VUSERNAME, VOLD.LVAL, (LPCTSTSTR) (_ BSTR_T) vbirthday);
m_precordset-> MoveNext (); // / Move to the next record
}
m_precordset-> movefirst (); /// Move to the first record
m_precordset-> delete (adAffectCurrent); // Delete the current record
/// Add three new records and assign
For (int i = 0; i <3; i )
{
m_precordset-> addnew (); // / Add new record
M_PRecordset-> Putcollect ("ID", _ variant_t ((long) (i 10)));
M_PRecordset-> Putcollect ("UserName", _ variant_t ("Yeltsin"));
M_PRecordset-> Putcollect ("OLD", _ variant_t ((long) 71));
M_PRecordset-> Putcollect ("birthday", _ variant_t ("1930-3-15");
}
m_precordset-> Move (1, _variant_t ((long) adbookmarkfirst); ////-moving a record from the first record, that is, moved to the second record
m_precordset-> Putcollect (_variant_t ("old"), _ variant_t (long) 45)); // / Modify its age
m_precordset-> Update (); // / save to the library
[6] Closing the record set and the connection record set or connection can use the Close method to turn off m_precordset-> close (); /// Close record set
m_pconnection-> close (); /// turn off the connection
