Updating and Adding Database Records Through ADO

• Download source code - 113.9 Kb

Introduction

This is the article which brings you in the world of database mystery and explore some little and basic concepts of OLE DB and ODBC. Why am i writing this little article ? ...Simply to share knowledge with programmers and also when i was developing my first c++ application with database integration then i faced many problems related to the database connectivity and its working, so that i hope after the reading of this article, Programmer will got idea and concept related to the Database hazard in c++. You will find there , introductions of two APIs OLE DB and ODBC and little bit code snip.

Background

No need of any special background for this article, simply you have the knowledge about database concepts and well familiarizes with vc++ and MFC environment. It is better if you study them further

http://www.codeproject.com/database/connectionstrings.asp

http://www.codeproject.com/database/oledbconsumer1.asp

Using the code

When you study it entirely then you find it that very tricky and conceptual and I hope that time you easily understand code snippets and its uses. For your ease I attach some code with some algorithms to play with DB and also this article in .PPT format, You download and enjoy it.

Techniques used in VC++ to access database

Following techniques are provided in vc++ for using and accessing databases in your application.
1) Data Access Objects(DAO)
2) pen DataBase Connector(ODBC)
3) OLE(Object Linking and Embedding) DB
4) ActiveX Data Objects(ADO)
we will see ADO in detail with some ODBC and OLE DB discussion.

OLE DB & ODBC

OLE DB & ODBC are APIs designed to provide access to a wide range of data sources.

ODBC is designed to provide access primarily to SQL data in a multiplatform environment.

OLE DB includes the SQL functionality defined in ODBC but also defines interfaces suitable for gaining access to data other than SQL data.

ODBC was created to access relational databases

OLE DB is designed for the relational and non non relational information sources , such as text and graphical data for the Web.
Any thing that might have data could possibly be accessed through the OLE DB technology.

What is ADO?

ADO was designed as another layer on top of OLE DB, specifically for database access.
A Web browser user can cache an entire set of data records on the client. ADO control is distributed with Microsoft's Internet Explorer Web browser (version 4.0 & above).

ADO Objects

1-Basic Objects are:
2-Connection
3-Error
4-Command
5-Parameter
6-Recordset
7-Field

Connection Object

->Used for establishing and maintaining a connection to a database.
->To establish a connection
->Configure the object with the connection information, including database location, user ID, and password, before opening the connection.
->Call its Open method to open the connection.
->Call its Close method to close the connection.
->Through Connection object any high-level connection functionality is controlled.
->This includes all transaction control, through the Connection object's BeginTrans, CommitTrans, and RollbackTrans methods.

Error Object

->Whenever a database error occurs, the error information from the database is placed into an ADO Error object.
->The error information in the error object is the database error information, not ADO error information .

Command Object

->Used to execute commands in the database
->This object can be used to run SQL statements or call stored procedures (SQL functions that are stored in the database).

Parameter Object

. ->Used for passing variables and for calling stored procedures or parameterized queries
->attached to a Command object for use in calling the command

Recordset Object

->This object contains a set of records from the database.
->The set of records is the result of a command being sent to the database that results in a set of records being returned.

Field Object

It represents a single column in the Recordset. ->The Field objects always contain a Variant data value.
->A programmer working with the ADO objects will have to convert the value from a Variant to whatever its data type is needed, as well as convert it back to a Variant when updating the value.

Using the ADO ActiveX Control

->There are two different ways to use the ADO control in Visual C++ applications.
->The simple way to incorporate ADO into your application through the use of ActiveX controls is :
->Add ADO ActiveX control to the project.

->Specify the database connection.

->Specify the record source

->Specify the ADO control as the data source for the control.

->A running ADO control Database Application

Using this approach involves a lot of unnecessary overhead in building ADO applications. For each SQL query or table that you want to pull in a separate record set, you have to add a separate ADO control. Each of these ADO controls will establish a separate connection to the database, which could cause problems with databases that have a limited number of connections available .
All data-bound controls are not ADO enabled.

Importing the ADO DLL

->There are no classes for use with ADO in MFC class hierarchy.
->Microsoft has provided other means for to create and use classes for each of the objects in ADO, through the use of a new C++ precompiler directive called #import.
This #import directive tells the Visual C++ compiler to import the DLL specified by it , creating header files that are automatically included in the project. These header files have the filename extensions .TLH and .TLI These two files contain definitions of classes for each of the objects in the DLL that can be used in the code. The #import directive eliminate the need to include the .LIB file for the DLL in the project. Import ADO DLL by placing the following code at the beginning of the header file in which database objects are defined:

#define INITGUID 
#import "C:\Program Files\CommonFiles\System\ADO\msado15.dll"     rename_namespace("ADOCG")      
rename("EOF", "EndOfFile") 
   using namespace ADOCG; 
   #include "icrsint.h" 

Connecting to a Database

COM is the fundamental "object model" on which ActiveX controls and OLE are built. Before using any of the ADO objects, the COM environment for the application must be initialized , to make calls to ActiveX objects: ::CoInitialize(NULL);
After finishing with all ADO activity, the COM environment must be shut down by calling the CoUninitialize function, as follows:
CoUninitialize();
This function cleans up the COM environment and prepares the application for shutting down.

Creating a Connection to the Database

Declare a Connection object pointer, _ConnectionPtr 

_ConnectionPtr pConn; pConn.CreateInstance(__uuidof(Connection)); 

pConn->Open(L"Provider=MSDASQL.1;DataSource=TYVCDB", L"", L"",adOpenUnspecified); 

Executing Commands and Retrieving Data

Declare a Command object pointer, _CommandPtr, and then create an instance of it using the UUID of the Command object, as follows:

_CommandPtr pCmd;
pCmd.CreateInstance(__uuidof(Command)); 
pCmd->ActiveConnection = pConn; 

Next, specify the SQL command to be executed by setting the CommandText property of the Command object, as follows:

pCmd->CommandText = "Select * from Addresses"; 

At this point, there are two options for how to execute this command and retrieve the records.

1)     _RecordsetPtr pRs; 
    pRs = pCmd->Execute(); 

2)    _RecordsetPtr pRs; pRs.CreateInstance(__uuidof(Recordset));
     pRs->PutRefSource(pCmd); 


// Create the variant NULL 
_variant_t vNull; 
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
// Open the recordset 
pRs->Open(vNull, vNull,    adOpenDynamic,adLockOptimistic,    adCmdUnknown); 

Another approach to accomplish all of the preceding tasks with only a few lines of code

Skip the use of the Command and Connection objects altogether, placing all the necessary connection information in the Recordset's Open function.

_RecordsetPtr pRs;
 pRs.CreateInstance(__uuidof(Recordset));
pRs->Open(_T("Provider=MSDASQL.1;Data Source=TYVCDB"), _T("select * from Addresses"),  
adOpenDynamic, adLockOptimistic, adCmdUnknown); 

Navigating the Recordset

Functions need to navigate the set of records :
MoveFirst, MoveLast, MovePrevious, and MoveNext
Recordset object also has two properties, BOF and EOF

Accessing Field Values

All data elements that are retrieved from fields in the ADO Recordset are variant values.
They have to be converted into the data types that are needed to be used.
There are two ways of doing this.

1)Retreive values into a variant and then convert them
    _variant_t vName; 
    CString strName; 
    vName = pRs-> GetCollect(_variant_t("Name"));
    vName.ChangeType(VT_BSTR); strName = vName.bstrVal; 

2)Microsoft has created a series of macros that perform the conversion and that maintain a set of variables of the records in the set. To do this, define a new class to use as the interface for the record set. This class will be a descendent of the CADORecordBinding class, which is defined in the icrsint.h header file.

class CCustomRs : public CADORecordBinding { 
    BEGIN_ADO_BINDING(CCustomRs) 
     ADO_FIXED_LENGTH_ENTRY (1, adInteger, m_ID, IDStatus,     FALSE)
     ADO_VARIABLE_LENGTH_ENTRY2(2, adVarChar, m_Name, sizeof(m_Name), NameStatus, TRUE)  
ADO_FIXED_LENGTH_ENTRY(3, adDate, m_Birthdate, BirthdateStatus, TRUE) 
    END_ADO_BINDING() 
  public: 
        LONG m_ID; 
    ULONG IDStatus; 
        CHAR m_Name[20]; 
        ULONG NameStatus; 
        DATE m_Birthdate; 
        ULONG BirthdateStatus;  
  }; 

Declare a variable of this class Next, create a pointer to an IADORecordBinding interface CCustomRs m_RecSet; IADORecordBinding *Rs = NULL; IADORecordBinding Interface binds the field of a Recordset object to C/C++ variables. Whenever the current row of the bound Recordset changes, all the bound fields in the recordset are copied to variables.

IADORecordBinding Interface Methods

1)BindToRecordset
2)AddNew
3)Update
Once the set of records are retrieved, retrieve the pointer to the IADORecordBinding interface and bind the custom record set class to the Recordset object, as in the following code:

if (FAILED(pRs->QueryInterface(__uuidof(IADORecordBinding), (LPVOID *)&Rs)))
    _com_issue_error(E_NOINTERFACE); Rs->BindToRecordset(&m_RecSet); 

Macros

BEGIN_ADO_BINDING macro sets up the structure definition that is created with the rest of the macros.

The set of macros is closed by the END_ADO_BINDING macro.

ADO_FIXED_LENGTH_ENTRY
it is used for any database fields that are fixed in size
ADO_NUMERIC_ENTRY Macros macros with numeric fields only
ADO_VARIABLE_LENGTH_ENTRY Macros Use this series of macros with database fields that are likely to vary in length

Updating Records

If you retrieved each field and converted it from a variant yourself, you need to update each individual field that has been changed. <bre>_variant_t vName, vValue; vName.SetString("Name"); vValue.SetString("Saqib"); pRs->Update(vName, vValue); If you created your record class and bound it to the recordset, updating the record is a little simpler. Rs->Update(&m_RecSet);

Adding and Deleting

For deleting the current record, call the Recordset object's Delete method. pRs->Delete(adAffectCurrent); pRs->MovePrevious();

Add a New Record

You cannot just begin entering data values into the fields. To allow the user to immediately enter the various data elements in the new record, blank out the values in the record class and pass Recordset class variable as the only parameter to the AddNew method. Call it through the record-binding interface pointer, as in the following example:

CString strBlank = " ";
COleDateTime dtBlank;
m_RecSet.m_ID = 0; strcpy(m_RecSet.m_Name, (LPCTSTR)strBlank);
m_RecSet.m_dtBirthdate =  (DATE)dtBlank;
Rs->AddNew(&m_RecSet); 

Closing the Recordset and Connection Objects

Once you finish working with a record set, pRs->Close(); Once you finish all database interaction for the entire application, close the connection to the database by calling the Connection object's Close method: pConn->Close();

Points of Interest

Always try to code a wrapper class of builtin MFC, STL, ATL class to utilizes it in best mode. This article is completed with the help of my friend GOLD.. I learnt from him that helps other to got better.

History

Keep a running update of any changes or improvements you've made here. if you got any confusion then please inform me. thanks