ADO.NET Primer using Managed C++

Abstract

The purpose of this article is to introduce data access and manipulation using ADO.NET with Managed C++. The code samples provided are simple examples of how the DataReader, DataSet, DataAdapter, DataRelation are used in creating a solution with SQL Server. This article is targeted towards the beginner with some knowledge of Managed C++ coding.

You will notice once you begin using the new object model that this is a complete paradigm shift from the traditional connection oriented data access technologies found in ADO and DAO. The new design of data access plays very well into an n-tiered application design reducing the need for connection and locking resources at the data source.

The relationships between the objects in the framework need to be understood before we can begin using them. You might consider the objects in the architecture as layers that sit on top of each other that providing support to the layers above. The DataSet is supported by the DataAdapter, the DataAdapter consists of some properties that are defined through command objects which sits on the Connection to the data source.

Although my description of the architecture as layers is a very simplistic view of the object model, the point is that ADO.NET although different than ADO, is not necessarily more complex.

The beauty of ADO.NET is that was design for the direction we were taking with ADO. That is, disconnected recordsets, data shaping, and XML for cross platform integration.

Not included in ADO.NET are the concepts of recordsets and cursors; and in its place are new objects to work with data: DataSets, DataAdapters, and DataReader.

A good primer on ADO.NET can easily fill a small book. This seven page article will not do ADO.NET any justice. I hope that at the end of this article you will have a good understanding on the ADO.NET framework and could begin using it.

This article is laid out in sections. The first section talks a bit on the architecture. The second section walks you through some code snippets. The final section covers the conclusion and some code examples on the topics we covered.

You will need Visual C++.NET to compile the examples. The examples connect to SQL Server using the Pubs database. To test the code you will need SQL Server and the Pubs database.

ADO.NET Architecture Overview

The ADO.NET architecture is a complete paradigm shift from ADO. The new object model is designed to be a disconnected approach to data access. At the implementation level, the most obvious change is the lack of recordsets and cursors in the framework. Once we get over the hurdle of knowing how to work without recordsets and cursors, we can see the advantage of using ADO.NET in n-tier designs.

Before we begin at looking at some code let me briefly go over the architecture of the new framework.

The architecture is comprised of two elements: .NET Data Provider and the DataSet. The components of the .NET Data Provider give us the mechanisms to connect to and work with our data source. The DataSet components provide us an in memory representation of the data source in the form of a result set similar to the disconnected recordset in ADO.

The .NET Data Provider library targets a database platform. The implementation section of this article talks a bit about the different libraries and where you might be able to find one you will need for your project.

The components that make up the .NET Data Provider are the Connection, Command,

DataReader

and the DataAdapter.The components found in the DataSet are collection objects for result sets (tables), rows, columns, constraints and relationships.

The

DataAdapter

sits between the DataSet and the Command objects. The purpose of the

DataAdapter

is to provide the interaction of the result set to the data source. The

DataAdapter

has four properties that dictate how the interaction is made. The properties are the SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. These properties hold the references to an instance of a Command object.

The DataAdapter uses the SelectCommand property to populate a result set in the DataSet. Changes that are made to the result set are analyzed by the

DataAdapter

once the Update method is called causing the appropriate command in one of the command properties to be issued. If the

DataAdapter

analyses a change and the command property has not been defined, an exception is thrown.

The DataReader object is used to retrieve data from a connection as quickly as possible. The data comes in a forward-only, read-only stream with the only memory required for the data is to only contain the current record.

The Connection and Command objects speak for themselves. The

Command

object defines an SQL command. An SQL command can be directly executed against the connection or referenced by the DataAdapter. Any results of a direct execution can be processed through a

DataReader

for processing.

The Connection object defines the connection to the data source. The

Connection

object and the DataReader are the only objects that need to be closed off once done with.

The DataSet

The DataSet is made up of collections exposing tables, rows, and columns. The collection of tables is commonly known as the result set and represented as the DataTable object. Each result set requires is own DataAdapter to maintain changes in the result set to the source table.

The result sets, rows, and columns in the collection are accessed by an index and the in-memory manipulation of a tables, rows, or columns is done using the objects DataTable, DataRow, DataColumn, and DataRelation.

Ultimately, the DataSet does not have any access to the underlying records sitting in the database. It only contains one or more copies of the data provided by the DataAdapter. It is the DataAdapter’s responsibility to initiate data modifications to the data source.

DataRelation

Part of the DataSet, the DataRelation object defines the relationship between the result sets of the DataSet. Defining a relationship creates a hierarchical dataset.

Working through the hierarchical result set requires setting the DataRow collection to retrieve a list of child records by calling GetChildRows method. This sounds more complicated than it should. I’ll walk you through a DataRelation example in the next section.

Implementation of ADO.NET

To begin using ADO.NET you need to include a reference to the System.Data.dll library in your source code. If you are using a DataSet you will need to also include a reference to System.Xml.dll.

#using <system.data.dll> // This is required for the ADO.NET Provider
#using <System.Xml.dll> // This is only required for the DataSet
 
using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::Xml; // This is required for the DataSet

There are different .NET Providers for the various data sources. The table describes the namespace required for your target source.

Provider namespace	Target source	Notes
System.Data.SqlClient	SQL Server 7 and higher, and including the MSDE engine
System.Data.OleDb	Microsoft SQL Server 6.5 and earlier, any other OLE DB supported platform. Microsoft Access.
Microsoft.Data.Odbc	ODBC Compliant data source	Requires a download
System.Data.OracleClient	Oracle data source	Requires a download

After adding the reference and setting the scope using the namespace we could begin using the framework to create a connection to the source.

SqlConnection * mySQLConnection;
mySQLConnection = new SqlConnection
             (S"server=local;Trusted_Connection=yes;database=pubs;");
mySQLConnection->Open (); // Open up the connection

The SqlCommand object is just as simple to setup.

SqlCommand * mySQL;
mySQL = new SqlCommand (S"select * from authors", mySQLConnection);

Setting up the DataReader and begin extracting records is also simple.

SqlDataReader * myReader;
myReader = mySQL->ExecuteReader ();

while(myReader->Read ())
    Console::WriteLine(myReader->get_Item ("au_lname")->ToString ());

Once you are finished using the DataReader and Connection you should close them off calling the Close method.

myReader->Close ();
mySQLConnection->Close();

Before setting up a DataSet we need to create a DataAdapter. The DataAdapter is used to fill a DataSet with a result set and allow for changes made to the result set to be applied back to source.

The following code snippet demonstrates creating a DataAdapter and setting the SelectCommand property.

SqlDataAdapter * myDataAdapter;
myDataAdapter = new SqlDataAdapter();
myDataAdapter->SelectCommand = new SqlCommand 
                      (S"select * from authors",mySQLConnection);

As you can see, the SelectCommand property references an SqlCommand object containing an SQL Select command. This property is used to populate the DataSet with the results of the Select. The code to create a DataSet and populate it with a result set is as follows:

DataSet * myDataSet;
myDataAdapter->Fill (myDataSet,"authors");
// DataSet is populated using the Select * from authors command

Because the DataAdapter is responsible for making updates to the source, we have to set up the appropriate command property to allow for the update. The DataAdapter decides which property to execute against the source based on the changes made to the result set.

A DataAdapter is needed for each source table you will be making updates to.

The following code example shows how the InsertCommand property of the DataAdapter is prepared for a new record update. The example uses the parameters collection.

SqlParameter * myParameter;
myDataAdapter->InsertCommand = new SqlCommand(S"insert into authors " + 
                "(au_id,au_lName,au_fname,contract) values @auID, " +
                "@l_name, @f_name, @contract)", mySQLConnection);

//Set up the four parameters

//Au_id parameter
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
    new SqlParameter ("@auID", SqlDbType::VarChar));
myParameter->SourceColumn = "au_id";
myParameter->SourceVersion = DataRowVersion::Current;

// l_name parameter
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
    new SqlParameter("@l_name", SqlDbType::VarChar));
myParameter->SourceColumn = "au_lname";
myParameter->SourceVersion = DataRowVersion::Current;

// f_name parameter
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
    new SqlParameter("@f_name", SqlDbType::VarChar));
myParameter->SourceColumn = "au_fname";
myParameter->SourceVersion = DataRowVersion::Current;

// contract parameter
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
    new SqlParameter("@contract", SqlDbType::Bit));
myParameter->SourceColumn = "contract";
myParameter->SourceVersion = DataRowVersion::Current;

Once the InsertCommand property has been set, new records can be added to the data source. Calling the Update method on the DataAdapter will initiate the DataAdapter to analyze the changes to the result set and issue the appropriate command from one of the command properties. In this example the DataAdapter will decide to use the InsertCommand property to make the update.

The code to create a new record into the authors result set and source table is shown below.

DataRow * myRow;

myRow = myDataSet->Tables->Item["authors"]->NewRow ();

myRow->Item [0]=S"123-45-6799";       // Key
myRow->Item [1]=S"Bill";        // au_fname field
myRow->Item [2]=S"Ferreira";    // au_lname field
myRow->Item [8]=S"true";        // contract field

myDataSet->Tables->Item ["authors"]->Rows->Add (myRow);

// The DataAdapter will analyze the update as requiring an insert and 
// use the insert from the insertcommand property.
myDataAdapter->Update (myDataSet,"authors");

After you are done with the connection you will need to close it off.

mySQLConnection->Close();

If we were to deleted a record from the result set, the Update method of the DataAdapter would throw an exception since the DeleteCommand property has not been initialized with any SQL Delete command.

Creating a relationship between multiply result sets is simple using the DataRelation object. This code snippet shows how a DataRelation object is used to create a single relationship between two result sets.

DataRelation * myRelation;

myRelation = myDataSet->Relations->Add ("titleauthers",
myDataSet->Tables->Item ["authors"]->Columns->Item ["au_id"],
myDataSet->Tables->Item ["titles"]->Columns->Item ["au_id"]);

As you iterate through the authors result set, you would call the GetChildRows method to pull all the child rows.

DataRow      * myAuthorRow;
DataRow      * myTitlesRow[];

for(inti=0;>= myDataSet->Tables->Item ["authors"]->Rows->Count; i++) {
    myAuthorRow = myDataSet->Tables->Item ["authors"]->Rows->Item[i];
    Console::WriteLine("Author ID : {0}",myAuthorRow->Item ["au_id"]->ToString ());
    myTitlesRow = myAuthorRow->GetChildRows (myRelation);
    for(intx=0; x < myTitlesRow->Count ;x++)
      Console::WriteLine("Titles ID : {0}",
             myTitlesRow[x]->Item ["title_id"]->ToString ());
}

Conclusion

I hope that this article has been of some use to you. I have added some references I have found useful when programming with ADO.NET. What I have covered in this article only scratches the surface of the power and flexibility of ADO.NET.

Included are four complete examples I have selected to demonstrate the objects discussed in this article.

The first example creates and instantiates a connection and command object. The command object is executed on a DataReader where the results are pulled to the console.

The second example creates and instantiates a Connection, Command, DataAdapter, and DataSet object. The DataSet object is looped displaying the contents of a field.

The third example shows a new record insert.

The fourth example shows how a DataRelation object is used to create a relationship between to results sets of a DataSet.

To those who are familiar with ADO.NET, you could agree with me that there is so much more that should be covered in a primer. I needed to stick to the basics, otherwise it would turn into something like a development project without a scope, the whole world ends up getting sucked into it.

I would appreciate any of your feedback on this article. I could be reached by email at gbferreira@hotmail.com.

References:

1. ADO.NET examples http://samples.gotdotnet.com/quickstart/latebreaking/
2. Best Practices for Using ADO.NET http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmqqc/html/msmqbest.asp
3. .NET Framework SDK Documentation

Source Code

// This is the main project file for VC++ application project
// generated using an Application Wizard.

#include "stdafx.h"
// Standard
#using <mscorlib.dll>
#using <System.dll>
#using <system.data.dll> // This is required for the ADO.NET Provider
#include <tchar.h>

using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;

// This is the entry point for this application
int _tmain(void)
{
    SqlConnection      * mySQLConnection;
    SqlCommand         * mySQL;
    SqlDataReader      * myReader;
                      
    try
    {
        mySQLConnection = new SqlConnection(
            S"server=local;Trusted_Connection=yes;database=pubs;");
        mySQL = new SqlCommand (S"select * from authors",
            mySQLConnection);

        mySQLConnection->Open (); // Open up the connection
        myReader = mySQL->ExecuteReader ();

        while(myReader->Read ())
            Console::WriteLine(myReader->get_Item("au_lname")->ToString ()); 
    }
    catch(Exception * e)
    {
        Console::Write(e->ToString () );
    }
    __finally
    {
        myReader->Close ();
        mySQLConnection->Close();
    }
    return 0;
}

// This is the main project file for VC++ application project
// generated using an Application Wizard.

#include "stdafx.h"
// Standard
#using <mscorlib.dll>

#using <System.dll>
#using <system.data.dll> // This is required for the ADO.NET Provider
#using <System.Xml.dll> // This is required for the DataSet
#include <tchar.h>
using namespace System;
using namespace System::Data;
using namespace System::Xml; // This is required for the DataSet
using namespace System::Data::SqlClient;


// This is the entry point for this application
int _tmain(void)
{
    SqlConnection      * mySQLConnection;
    SqlDataAdapter     * myDataAdapter;
    DataSet      * myDataSet;
                                
    try
    {
        mySQLConnection = new SqlConnection(
            S"server=local;Trusted_Connection=yes;database=pubs;");
        myDataAdapter = new SqlDataAdapter();
        myDataSet = new DataSet();  
                 
        mySQLConnection->Open ();    // Open up the connection

        // Assign the SelectCommand with an SQL Select command
        myDataAdapter->SelectCommand = new SqlCommand (
            S"select * from authors",mySQLConnection);

        // Use the DataAdapter to fill the DataSet
        // A DataSet can be made up of many results. I called
        // this result authors. The DataAdapter will know to use 
        // the SelectCommand property to populate the DataSet.
        myDataAdapter->Fill (myDataSet,"authors");

        for(inti=0;i < myDataSet->Tables->Item ["authors"]->Rows->Count; i++)
            Console::WriteLine("Name:{0}",
                myDataSet->Tables->Item ["authors"]->Rows->Item[i]->Item 
                ["au_lname"]->ToString ());    
    }
    catch(Exception * e)  {
        Console::Write(e->ToString () );
    }
    __finally {
        mySQLConnection->Close();
    }
    return 0;
}

// This is the main project file for VC++ application project
// generated using an Application Wizard.

#include "stdafx.h"
// Standard
#using <mscorlib.dll>
#using <System.dll>
#using <system.data.dll>
#using <System.Xml.dll>

#include <tchar.h>

using namespace System;
using namespace System::Data;
using namespace System::Xml;
using namespace System::Data::SqlClient ;

// This is the entry point for this application
int _tmain(void)
{
    SqlConnection      * mySQLConnection ;
    SqlDataAdapter     * myDataAdapter;
    DataSet      * myDataSet;
    DataRow      * myRow;
    SqlParameter       * myParameter;
                      
    try
    {
        mySQLConnection = new SqlConnection 
            (S"server=local;Trusted_Connection=yes;database=pubs;");
        myDataAdapter = new SqlDataAdapter();
        myDataSet = new DataSet();  
                 
        // Open up the connection
        mySQLConnection->Open ();   

        // Assign the SelectCommand with an SQL select command
        myDataAdapter->SelectCommand = SqlCommand (S"select * from authors",
            mySQLConnection);
        myDataAdapter->InsertCommand =
            new SqlCommand (S"insert into authors (au_id,au_lName," + 
                            S"au_fname,contract) values (@auID,@l_name," + 
                            S"@f_name,@contract)",mySQLConnection);

        //Au_id parameter
        myParameter = myDataAdapter->InsertCommand->Parameters->Add(
            new SqlParameter("@auID", SqlDbType::VarChar));
        myParameter->SourceColumn = "au_id";
        myParameter->SourceVersion = DataRowVersion::Current;

        // l_name parameter
        myParameter = myDataAdapter->InsertCommand->Parameters->Add(
            new SqlParameter("@l_name", SqlDbType::VarChar));
        myParameter->SourceColumn = "au_lname";
        myParameter->SourceVersion = DataRowVersion::Current;

        // f_name parameter
        myParameter = myDataAdapter->InsertCommand->Parameters->Add(
            new SqlParameter("@f_name", SqlDbType::VarChar));
        myParameter->SourceColumn = "au_fname";
        myParameter->SourceVersion = DataRowVersion::Current;

        // contract parameter
        myParameter = myDataAdapter->InsertCommand->Parameters->Add(
            new SqlParameter("@contract", SqlDbType::Bit));
        myParameter->SourceColumn = "contract";
        myParameter->SourceVersion = DataRowVersion::Current;

        // Use the DataAdapter to fill the DataSet
        // A DataSet can be made up of many results. This result set I
        // called authors.
        // The DataAdapter will know to use the SelectCommand object
        // to populate the DataSet.
        myDataAdapter->Fill (myDataSet,"authors");

        myRow = myDataSet->Tables->Item["authors"]->NewRow ();
        myRow->Item [0]=S"123-45-6799";       // Key
        myRow->Item [1]=S"Bill";        // au_fname
        myRow->Item [2]=S"Ferreira";    // au_lname
        myRow->Item [8]=S"true";        // contract
        myDataSet->Tables->Item ["authors"]->Rows->Add (myRow);

        //we use insertcommand property for the update.
        myDataAdapter->Update (myDataSet,"authors"); 
    }
    catch(Exception * e) {
        Console::Write(e->ToString () );
    }
    __finally {
        mySQLConnection->Close();
    }
    return 0;
}

// This is the main project file for VC++ application project
// generated using an Application Wizard.

#include "stdafx.h"
// Standard
#using <mscorlib.dll>

#using <System.dll>
#using <system.data.dll>
#using <System.Xml.dll>

#include <tchar.h>

using namespace System;
using namespace System::Data;
using namespace System::Xml;
using namespace System::Data::SqlClient ;
using namespace System::Diagnostics;

// This is the entry point for this application
int _tmain(void)
{
    SqlConnection      * mySQLConnection ;
    DataRelation       * myRelation;
    SqlDataAdapter     * myDataAdapter;
    SqlDataAdapter     * myDataAdapterB;
    DataSet      * myDataSet;
    DataRow      * myAuthorRow;
    DataRow      * myTitlesRow[];
             
    try
    {
        mySQLConnection = new SqlConnection
            (S"server=local;Trusted_Connection=yes;database=pubs;");
        myDataAdapter = new SqlDataAdapter();
        myDataAdapterB = new SqlDataAdapter();
        myDataSet = new DataSet();  
                                    
        // Open up the connection
        mySQLConnection->Open ();   

        // Assign the SelectCommand with an SQL select command
        myDataAdapter->SelectCommand = new SqlCommand 
            (S"select * from authors",mySQLConnection);
        myDataAdapterB->SelectCommand = new SqlCommand 
            (S"select * from titleauthor",mySQLConnection);

        myDataAdapter->Fill (myDataSet,"authors");
        myDataAdapterB->Fill (myDataSet,"titles");

        myRelation = myDataSet->Relations->Add ("titleauthers",
            myDataSet->Tables->Item ["authors"]->Columns->Item ["au_id"],
            myDataSet->Tables->Item ["titles"]->Columns->Item ["au_id"]);

                          
        for(inti=0;>= myDataSet->Tables->Item ["authors"]->Rows->Count;i++) 
        {
            myAuthorRow = myDataSet->Tables->Item["authors"]->Rows->Item[i];
            Console::WriteLine("ID        : {0}",
                myAuthorRow->Item ["au_id"]->ToString ());
            myTitlesRow = myAuthorRow->GetChildRows (myRelation);
            for(intx=0; x<= myTitlesRow->Count-1 ;x++)
                Console::WriteLine("Titles ID       : {0}",
                myTitlesRow[x]->Item ["title_id"]->ToString ());
        }
                 
    }
    catch(Exception * e) {
        Console::Write(e->ToString () );
        Trace::WriteLine (e->ToString ());

    }
    __finally {
        mySQLConnection->Close();
    }
    return 0;
}