Some ideas of learning ADO and ODBC Access database

ado|odbc| Access | data | database

Recently has been studying the use of VC + + access to the database, using ADO, also used ODBC. There are several ways to connect these two interfaces to a data source, either by using the database or by using a dataset. Use this word to sort out the ideas I'm learning.

　　one, use ADO to connect a data source

In general, it is easier to use ADO when creating a dialog based application (this is a matter of personal habit).

1. Directly in the application of the establishment and database connection

2. Write a adoconnection class as the interface of the application operation database, so that the level of operation is more obvious, the layered thought of the program also embodies better.

The following is a summary of these two methods.

1. Directly in the application of the establishment and database connection

To use ADO in your application, you need to introduce the Dynamic Connection library for ADO. The method is:

In the StdAfx.h:

#import "c:\program Files\Common Files\system\ado\msado15.dll" \

No_namespace\

Rename ("EOF", "adoeof")

Objective: To produce MSADO15.TLH and Msado15.tli two header files to define the ADO library.

After you have defined the ADO library, you should initialize the COM library environment. The method is:

Define _CONNECTIONPTR m_pconnection in c***app::initinstance;

AfxOleInit ()///use AfxOleInit to initialize ole/com library environment

Create an ADO connection

M_pconnection.createinstance (_uuidof (Connection));

Using Try...catch to catch errors in ADO operations

Try

{M_pconnection->open ("Provider=sqloledb;server=zengyh;database=postms;uid=sa;pwd=hongyi", "" "," "), adModeUnknown);

}

catch (_com_error e)

{

AfxMessageBox ("Connection database failed!");

return FALSE;

}

and the corresponding release of the connection to the database:

In C***app:: ExitInstance:

M_pconnection->close ();

:: OleUninitialize ();

At this point, the connection and disconnect on the database can be implemented smoothly, next will be the operation of the table in the database, that is, the recordset operation. When manipulating a table in a database, you first declare the dataset pointer: _RecordsetPtr M_precordset; Similar to creating an ADO connection, you need to create a Recordset object in the OnInitDialog () of the dialog class that you are manipulating:

M_precordset.createinstance (_uuidof (Recordset));

After that, you will be able to use M_precordset to query, modify, delete databases, and so on.

Open the table you want to manipulate using the M_precordset Open function M_precordset->open ("SELECT * from STUDENTS", TheApp.m_pConnection.GetInterfacePtr ( ), Adopendynamic,adlockoptimistic,adcmdtext);

Query operations for recordsets:

You can use the following functions to implement the movement of a recordset MoveFirst, MoveNext, Moveprev,movelast.

Extract Column Properties: Getcollect ("column name"), note: The return value type here is: _variant_t, this type class contains many conversion functions that are supported by C + +, and is more commonly used with CString type conversions (LPCSTR) _bstr_t (_ variant_t type variable).

Add operations to recordsets:

Make sure that the recordset you want to manipulate is opened, first call M_precordset->addnew (), and AddNew automatically generate a new empty record for the recordset field value to hold the data information that will be added. The SQL INSERT statement is automatically executed for inserting records.

Then set the logging information by setting the column properties: Putcollect ("column name", _variant_t &pvar)

Make sure to call M_precordset->update to update the recordset after setting

Recordset Modification Actions:

The modification operation is similar to the increment operation, except that the AddNew is not invoked first, and the recordset pointer is moved directly to the record where it needs to be modified to perform the putcollect operation.

and then update.

Delete Operations for recordsets:

When a record is found that needs to be deleted, M_precordset->delete (adAffectCurrent) is used, and the current record can be deleted. Update can be.

Of course, you should open the recordset for the table you want to operate on each time you need to manipulate the recordset, and you should close the recordset so that you can save resources by declaring only one recordset pointer and one recordset at a time.

In addition, you may need to query for records that meet the criteria in the modification and deletion operations:

Str.　　　　　　Format ("select * from STUDENTS where stuid= '%s '", M_stuid); M_precordset->open (str. AllocSysString (), TheApp.m_pConnection.GetInterfacePtr (), adopendynamic,adlockoptimistic,adcmdtext);

2. Write a adoconnection class as an interface to the application operations database

Encapsulates the interface to the application operations database through a custom adoconnection class (the _connectionptr and _recordsetptr pointers should be declared first in this class), and the functionality to be implemented in this class includes: Defining the ADO library, initializing the connection to ADO, Releasing the connection, initializing the recordset (Method 1 initialization is implemented in the OnInitDialog () of the corresponding class), executing the SQL statement

Define the ADO library, initialize the connection with ADO, release the connection and method 1 consistent;

To initialize a set of records:

_RecordsetPtr & Cadoconnection::getrecordset (_bstr_t mySql)

{

Try

{

if (m_pconnection==null)

{

Oninitadoconneciton ();

}

M_precordset.createinstance (_uuidof (Recordset));

M_precordset->open (Mysql,m_pconnection.getinterfaceptr (), adopendynamic,adlockoptimistic,adcmdtext);

}

catch (_com_error *e)

{

AfxMessageBox (E->errormessage ());

}

return m_precordset;

}

Execution of SQL statements is implemented primarily using connection (unlike Method 1, which is implemented using recordsets)

BOOL Cadoconnection::executesql (_bstr_t MYSQL)

{M_pconnection->execute (mysql,null,adcmdtext);}

At this point, you can do so by defining an instance of this adoconnection in your application.

Query action:

First define adoconnection, perform initialization ADO connection, and get recordset pointers based on query criteria:

_bstr_t mysql= "SELECT * from courses where couid= '" +m_couid+ "";

_RecordsetPtr Myrecordset=myconnection.getrecordset (MYSQL);

Processed according to the return of the pointer.

Add Action:

First of all, based on the actual situation to determine whether the record to be added exists (like a query), if the returned pointer is empty, the record to add does not exist, you can perform the insert:

Mysql= "INSERT into courses values (' +m_couid+", ' "+m_couname+", "+couscore+", "+coutea+", "+m_coukind+") ";

Myconnection.executesql (MYSQL);

Modify operation, delete operation:

Like the add operation, execute the UPDATE and DELETE statements

Description: Initialization adoconnection and releases should correspond to each function.

Compared to Method 1, a single action Recordset is not required, and a recordset pointer can declare multiple, which is more convenient for executing SQL statements.

The initialization of a recordset can be universal to the ADOConnection class, without the need to initialize the respective recordset in different classes in Method 1.

　　second, use ODBC to connect to a data source

Use this method to connect to a data source in general when you are building an application based on a document view.

1. Execute the SQL statement using the ExecuteSQL in the CDatabase class

When you create an application based on a single document, if you select database view with file support in the second step, you need to configure an ODBC data source such as a student table, and the base class for the generated application is Recordview. The bound dataset class (CStudentSet M_studentset) is automatically generated in the Doc class of the generated application, which is automatically generated (CStudentSet *m_pset) in the view class.

To connect to a database:

The connection to the database is overloaded with bool Cstudentdoc::onnewdocument ():

Add such code to this function (M_database is the member variable I defined in the document class CDatabase):

if (m_pstudentset==null)

{

M_pstudentset=new CStudentSet (&m_database);

CString Strconnect=m_pstudentset->getdefaultconnect ();

M_database. Open (Null,false,false,strconnect,false);

}

Here M_pstudentset is a CStudentSet recordset pointer redefined in the document class, so that you can establish a connection between your application and the database. To add a record operation:

Declare CDatabase m_database in the corresponding function

Define CStudentSet MySet (&m_database) in the function;

Open the Recordset Myset.open () again;

Myset.addnew ();

CString MYSQL;

Mysql= "INSERT into STUDENTS (stuid,stuname) VALUES (' +m_pset->m_stuid+" ', ' "+m_pset->m_stuname+ ')";

M_database. ExecuteSQL (MYSQL);

Myset.update ();

To delete a record, modify a record operation:

Define CStudentSet MySet (&m_database) in the function;

Open the Recordset Myset.open () again;

Myset.edit ();

Mysql= .....

M_database. ExecuteSQL (MYSQL);

Myset.update ();

Query Logging actions:

You can still use the Getcollect to move the recordset pointer.

Finally, don't forget Myset.close ().

Of course, you can also use the CRecordset class to perform queries, deletions, modifications, and additions. The basic approach is consistent with the above mentioned. This is my first time in the blog post, for immature or incorrect ideas also ask you to put forward, thank you.