Connect to the database using VC ++ ado

Connect to the database using VC ++ ado

I. Introduction to ADO

ADO (ActiveX Data Object) is a new interface for Microsoft database application development. It is a high-level database access technology built on top of ole db. Do not worry about this, even if you are, com can also easily deal with ADO without understanding it, because it is very easy to use, and even easier to use than the ODBC APIs, Dao, and rdo you used in the past. This article describes in detail how to use ADO for database application development under VC and provides sample code.

Sample Code

Ii. Basic Process

Everything is difficult at the beginning. Any new technology is the most important thing for beginners to learn about is getting started. Let's take a look at the basic process of ADO database development!

(1) initialize the com library and introduce the ADO library definition file.
(2) connect to the database using the connection object
(3) Use the established connection to execute SQL commands through the connection and command objects, or use the recordset object to obtain the result record set for query and processing.
(4) Close the connection and release the object after use.

Preparations:
For everyone to test the example provided in this article, we use the ACCESS database, you can also find this test. mdb directly in the sample code we provide.
Next we will introduce the above steps in detail and provide relevant code.

[1] com library Initialization
We can use afxoleinit () to initialize the com library, which is usually completed in the overload function of cwinapp: initinstance (). See the following code:

Bool cadotest1app: initinstance () {afxoleinit ();......

[2] use the # import command to introduce the ADO Type Library
We add the following statement to stdafx. h: (where can I find the stdafx. h file? You can find it in header files in FileView)

# Import "C:/program files/common files/system/ADO/msado15.dll" no_namespace Rename ("EOF", "adoeof ")
What is the role of this statement? The final function is similar to # include, which we are familiar with. during compilation, the system will generate msado15.tlh and ado15.tli C ++ header files to define the ADO library.

Notes:
(1) msado15.dll may not be in this directory in your environment. modify it as needed.
(2) The following warning may appear during compilation. Microsoft explained this in msdn and suggested that we ignore 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
First, we need to add a pointer to the connection object:
_ Connectionptr m_pconnection;
The following code demonstrates how to create a connection object instance, connect to a database, and capture exceptions.

Bool cadotest1dlg: oninitdialog (){
Cdialog: oninitdialog ();
Hresult hr;
Try {
HR = m_pconnection.createinstance ("ADODB. Connection"); // create a connection object

If (succeeded (HR ))
{
HR = m_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = test. mdb", "", "", admodeunknown );
/// Connect to the database /// in the above sentence, the provider in the connection string is for the Access2000 environment. For Access97, you need to change it to provider = Microsoft. Jet. oledb.3.51;
}
}
Catch (_ com_error e) // catch an exception
{
Cstring errormessage; errormessage. Format ("failed to connect to the database! /R/n error message: % s ", E. errormessage (); afxmessagebox (errormessage); // Display error message}

In this Code, we connect to the database through the open method of the connection object. The following is a prototype of this method.
Hresult connection15 ::
Open
(_ Bstr_t connectionstring, _ bstr_t userid, _ bstr_t password, long options)
Connectionstring is the connection string, userid is the user name, password is the login password, options is the connection option, used to specify the connection object to update the data permission,

Options can be the following constants:
Admodeunknown: default. The current permission is not set
Admoderead: Read-Only
Admodewrite: Write only
Admodereadwrite: read/write
Admodesharedenyread: blocks other connection objects and opens the connection with the read permission.
Admodesharedenywrite: blocks other connection objects and opens the connection with write permission.
Admodemo-exclusive: prevents other connection objects from opening connections.
Admodesharedenynone: allows other programs or objects to establish connections with any permissions.

We provide some common connection methods for your reference:
(1) connection to the Access2000 Database through the Jet Database Engine

M_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = C: // test. mdb", "", "", admodeunknown );

(2) Use the DSN data source to connect to any database that supports ODBC:

M_pconnection-> open ("Data Source = adotest; uid = sa; Pwd =;", "", "", admodeunknown );

(3) do not use DSN to connect to the SQL Server database:

M_pconnection->
Open (
"Driver = {sqlserver}; server = 127.0.0.1; database = vckbase; uid = sa; Pwd = 139", "", "", admodeunknown
);

The server is the name of the SQL Server, and the database is the name of the database.

There are many methods in addition to the open method of the connection object. First, we will introduce two useful attributes of the connection object: connectiontimeout and state.
Connectiontimeout is used to set the connection timeout time, which must be called before open, for example:

M_pconnection-> connectiontimeout = 5; // set the timeout value to 5 seconds.

M_pconnection-> open ("Data Source = adotest;", "", "", admodeunknown );

The state attribute specifies the status of the current connection object. The value 0 indicates that the connection object is closed, and the value 1 indicates that the connection object has been opened. You can read this attribute for processing. For example:

If (m_pconnection-> state) m_pconnection-> close (); // if the connection has been enabled, close it.

[4] execute the SQL command and obtain the result record set
To obtain the result record set, we define a pointer to the recordset object: _ recordsetptr m_precordset;
And create a recordset object for it: m_precordset.createinstance ("ADODB. recordset ");
SQL commands can be executed in multiple forms. The following describes how to execute these commands.

(1) Execute SQL commands using the execute method of the connection object

The prototype of the execute method is as follows:

_ Recordsetptr connection15 ::
Execute (_ bstr_t commandtext, variant * recordsaffected, long options)
Commandtext is a command string, usually an SQL command. The recordsaffected parameter indicates the number of rows affected by the operation. The options parameter indicates the content type in commandtext. options can be one of the following values:

Adshorttext: indicates that commandtext is a text command.
Adcmdtable: indicates that commandtext is a table name.
Ad1_proc: indicates that commandtext is a stored procedure.
Adcmdunknown: Unknown

After Execute is executed, a pointer pointing to the record set is returned. The specific code is provided and described below.

_ Variant_t recordsaffected;
/// Execute the SQL command: Create Table CREATE TABLE users. Users contains four fields: integer ID, string username, integer old, date type birthday

M_pconnection->

Execute ("create table users (ID integer, username text, old integer, birthday datetime)", & recordsaffected, ad1_text );
/// Add records to the table

M_pconnection->

Execute ("insert into users (ID, username, old, birthday) values (1, 'Washington ', 25, '2017/1')", & recordsaffected, ad1_text );
/// Add the value of all record old fields to one

M_pconnection->

Execute ("Update users set old = old + 1", & recordsaffected, ad1_text );
/// Execute the SQL statistics command to obtain the record set containing the number of records

M_precordset = m_pconnection->

Execute ("select count (*) from users", & recordsaffected, ad1_text );
_ Variant_t vindex = (long) 0;
_ Variant_t vcount = _ precordset->

Getcollect (vindex );

/// Get the value of the first field and put it in the vcount variable

M_precordset-> close ();
/// Close the record set

Cstring message;
Message. Format ("% d records in total", vcount. lval );
Afxmessagebox (Message );
/// Display the number of current records

(2) Execute SQL commands using command objects

_ Commandptr m_pcommand;
M_pcommand.createinstance ("ADODB. Command ");
_ Variant_t vnull;
Vnull. Vt = vt_error;
Vnull. scode = disp_e_paramnotfound;
/// Defined as no Parameter

M_pcommand-> activeconnection = m_pconnection;
/// A very critical sentence. Assign the established connection to it.

M_pcommand-> commandtext = "select * from users ";
/// Command string

M_precordset = m_pcommand-> execute (& vnull, & vnull, ad1_text );
/// Execute the command to obtain the record set

In this Code, we only use the command object to execute the SELECT query statement. The command object can truly reflect its role in calling the stored procedure. We will introduce it in detail next time.

[4] execute the SQL command and obtain the result record set

(3) directly use the recordset object for query to obtain the record set
For example

M_precordset->

Open ("select * from users", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );

The prototype of the open method is as follows:

Hresult recordset15: open (const _ variant_t & source, const _ variant_t & activeconnection, Enum cursortypeenum cursortype, Enum locktypeenum locktype, long options)

Where:
① Source is a data query string
② Activeconnection is a established connection (we need to use the connection object pointer to construct a _ variant_t object)
③ Cursortype the cursor type. It can be one of the following values. Please refer to this enumeration structure:

Enum cursortypeenum
{
Adopenunspecified =-1,
/// Do not specify

Adopenforwardonly = 0,
/// Roll the static cursor forward. The cursor can only browse the record set forward. For example, if movenext is used to scroll forward, this method can increase the browsing speed. However, neither bookmark, recordcount, absoluteposition, or absolutepage can be used.

Adopenkeyset = 1,
/// The record set with this cursor cannot see the new or delete operations of other users, but the operations to update the original records are visible to you.

Adopendynamic = 2,
/// Dynamic cursor. All database operations are immediately reflected in each user record set.

Adopenstatic = 3
/// Static cursor. It generates a static backup for your record set, but the addition, deletion, and update operations of other users are invisible to your record set.
};

④ Locktype: it can be one of the following values. See the following enumeration structure:

Enum locktypeenum
{
Adlockunspecified =-1,
/// Not specified

Adlockreadonly = 1,
/// Read-only record set

Adlockpessimistic = 2,
Pessimistic locking mode. Lock all other actions when data is updated. This is the most secure locking mechanism.

Adlockoptimistc = 3,
Optimistic Locking mode. The record is locked only when you call the update method. Before that, you can update, insert, or delete data.

Adlockbatchoptimistic = 4,
Optimistic batch update. The record is not locked during editing. Modification, insertion, and deletion are completed in batch mode.
};

⑤ For options, refer to the introduction to the execute method of the connection object in this article.

[5] record set traversal and update

According to the users table created by executing the SQL command just now, it contains four fields: ID, username, old, birthday
The following code is used:
Open the record set, traverse all records, delete the first record, add three records, move the cursor to the second record, change its age, and save it to the database.

_ Variant_t vusername, vbirthday, vid, vold;

_ Recordsetptr m_precordset;

M_precordset.createinstance ("ADODB. recordset ");

M_precordset->
Open ("select * from users", _ variant_t (idispatch *) m_pconnection, true), adopenstatic, adlockoptimistic, adshorttext );

While (! M_precordset-> adoeof)
/// Why is it adoeof instead of EOF? Do you still remember Rename ("EOF", "adoeof?

{Vid = m_precordset-> getcollect (_ variant_t (long) 0)
); // Obtain the value of the 1st column, which starts from 0. You can also give the column name directly, as shown in the following line.

Vusername = m_precordset-> getcollect ("username ");
/// Obtain the value of the username field

Vold = m_precordset-> getcollect ("old ");

Vbirthday = m_precordset-> getcollect ("Birthday ");
/// Records in the output window output record set in debug mode

If (VID. VT! = Vt_null & vusername. VT! = Vt_null & vold. VT! = Vt_null & vbirthday. VT! = Vt_null)

Trace ("ID: % d, name: % s, age: % d, birthday: % S/R/N", vid. lval, (lpctstr) (_ bstr_t) vusername, vold. lval, (lpctstr) (_ 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 values

For (INT I = 0; I <3; I ++)

{

M_precordset-> addnew (); // Add a new record
M_precordset-> putcollect ("ID", _ variant_t (long) (I + 10 )));
M_precordset-> putcollect ("username", _ variant_t ("Silverlight "));
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 ));
/// Move one record from the first record to the second record

M_precordset-> putcollect (_ variant_t ("old"), _ variant_t (long) 45 ));
/// Modify the age

M_precordset-> Update ();
/// Save it to the database

[6] disable record set and Connection

You can close Record Sets or connections using the close method.

M_precordset-> close (); // close the record set
M_pconnection-> close (); // close the connection

At this point, I think you are familiar with the general process of operating the database through ADO. Maybe you have a good idea. Maybe you still have something to worry about! I suggest you try to write a few examples to better familiarize yourself with ADO. Finally, I wrote a small example for you, in this example, all records are read and placed in the list control, and records can be added, deleted, or modified.
Click here to download the sample code

Note: Due to the length constraints, many content in ado has not yet been introduced. Next time, we will detail the attributes and methods of the recordset object and discuss several key technologies: the binding method processes record set data, call of stored procedures, transaction processing, storage and reading of images in the database, and use with table controls.
Goodbye next time!

 

Ii. development practices of ADO under VC

I. Preface

In the previous article "the first close contact with ADO", we introduced in detail the basic operation methods of ADO. In actual development, we often need to store large binary data objects, such: images, audio files, or other binary data, which are called Binary Large Object blob (Binary Large Object), are accessed in a different way than normal data. This article describes how to use ADO to access BLOB data in a database, and provides a complete example project to achieve image access and display.

Ii. Preparations

First, create a table named userinfo, which contains three fields: ID, username, old, and photo. photo is a field that can store binary data.

2.1 In SQL Server, you can directly enter the following statement in query analyzer to create the statement:

Create Table [DBO]. [userphoto] (
[ID] [int] identity (1, 1) not null,
[Username] [varchar] (50) null,
[Old] [int] Null,
[Photo] [Image] Null
) On [primary] textimage_on [primary]
Here, photo is defined as an image field.

2.2 The method used to create an access interface is as follows:

Create a new table that includes four fields: ID, username, old, and photo. Open the table, select design view from the View menu, and Set ID to auto-numbered incremental long integer and username as text, old is a number and photo is an OLE object.
The example project contains a database named Access2000, which can be used directly. 3. Steps

3.1 save BLOB Data

BLOB data cannot be stored in normal mode. We need to use the AppendChunk function. The AppendChunk is included in the Field object. The prototype is as follows:
Hresult AppendChunk (const _ variant_t & data );
From the function prototype, we can see that the key problem is that we need to assign binary data to variant type variables. Below we will provide specific code for simple analysis:

/// Assume that the m_pbmpbuffer Pointer Points to a binary data with a length of m_nfilelen, and the record set object m_precordset has been successfully opened ///

Char * pbuf = m_pbmpbuffer;

Variant varblob;

Safearray * PSA;

Safearraybound rgsabound [1];

M_precordset-> addnew (); // Add a new record

M_precordset-> putcollect ("username", _ variant_t ("Xiao Li"); // fill in the username field for the new record

M_precordset-> putcollect ("old", _ variant_t (long) 28); // fill in the old field

If (pbuf ){

Rgsabound [0]. llbound = 0;

Rgsabound [0]. celements = m_nfilelen;

PSA = safearraycreate (vt_ui1, 1, rgsabound); // create a safearray object

For (long I = 0; I <(long) m_nfilelen; I ++)

Safearrayputelement (PSA, & I, pbuf ++); // Save the binary data that pbuf points to the safearray object PSA

Varblob. Vt = vt_array | vt_ui1; // sets the varblob type to an array of the byte type.

Varblob. parray = psa; // assign m_precordset-> getfields ()-> getitem ("photo")-> AppendChunk (varblob) to the varblob variable; // Add BLOB Data

}

M_precordset-> Update (); // save our data to the database

So far, our data has been successfully saved to the database. Next we need to extract the data. Let's continue!

3.2 reading BLOB Data

Corresponding to the AppendChunk function used to save data, the getchunk function should be used to read data. The prototype of getchunk is as follows:
_ Variant_t getchunk (long length );
After the length of the data is given, getchunk will return the variant type variable containing the data. Then, we can use the safearrayaccessdata function to obtain the char * type pointer pointing to the data in the variant variable to facilitate our processing, the Code is as follows:

Long ldatasize = m_precordset-> getfields ()-> getitem ("photo")-> actualsize;
/// Obtain the Data Length

If (ldatasize> 0)

{

_ Variant_t varblob;

Varblob = m_precordset-> getfields ()-> getitem ("photo")-> getchunk (ldatasize );

If (varblob. Vt = (vt_array | vt_ui1) // you can check whether the data type is correct.

{

Char * pbuf = NULL;

Safearrayaccessdata (varblob. parray, (void **) & pbuf );
/// Get the pointer to the data/***** here we can process the data in pbuf *****/

Safearrayunaccessdata (varblob. parray );

}

}

We have successfully achieved BLOB data access in the database. In order to provide examples for reference, this article provides an example project. In this example project, we save image data in the database, you can also browse and modify these images. This example also involves how to use the BMP file data pointed to by char * to create a bitmap object and then display it.

Click here to download the sample project
 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 1556544