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>
#using <System.Xml.dll>
using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::Xml;
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 ();
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");
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);
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter ("@auID", SqlDbType::VarChar));
myParameter->SourceColumn = "au_id";
myParameter->SourceVersion = DataRowVersion::Current;
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter("@l_name", SqlDbType::VarChar));
myParameter->SourceColumn = "au_lname";
myParameter->SourceVersion = DataRowVersion::Current;
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter("@f_name", SqlDbType::VarChar));
myParameter->SourceColumn = "au_fname";
myParameter->SourceVersion = DataRowVersion::Current;
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";
myRow->Item [1]=S"Bill";
myRow->Item [2]=S"Ferreira";
myRow->Item [8]=S"true";
myDataSet->Tables->Item ["authors"]->Rows->Add (myRow);
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:
- ADO.NET examples http://samples.gotdotnet.com/quickstart/latebreaking/
- Best Practices for Using ADO.NET http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmqqc/html/msmqbest.asp
- .NET Framework SDK Documentation
Source Code
#include "stdafx.h"
#using <mscorlib.dll>
#using <System.dll>
#using <system.data.dll>
#include <tchar.h>
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
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 ();
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;
}
#include "stdafx.h"
#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;
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 ();
myDataAdapter->SelectCommand = new SqlCommand (
S"select * from authors",mySQLConnection);
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;
}
#include "stdafx.h"
#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 ;
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();
mySQLConnection->Open ();
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);
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter("@auID", SqlDbType::VarChar));
myParameter->SourceColumn = "au_id";
myParameter->SourceVersion = DataRowVersion::Current;
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter("@l_name", SqlDbType::VarChar));
myParameter->SourceColumn = "au_lname";
myParameter->SourceVersion = DataRowVersion::Current;
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter("@f_name", SqlDbType::VarChar));
myParameter->SourceColumn = "au_fname";
myParameter->SourceVersion = DataRowVersion::Current;
myParameter = myDataAdapter->InsertCommand->Parameters->Add(
new SqlParameter("@contract", SqlDbType::Bit));
myParameter->SourceColumn = "contract";
myParameter->SourceVersion = DataRowVersion::Current;
myDataAdapter->Fill (myDataSet,"authors");
myRow = myDataSet->Tables->Item["authors"]->NewRow ();
myRow->Item [0]=S"123-45-6799";
myRow->Item [1]=S"Bill";
myRow->Item [2]=S"Ferreira";
myRow->Item [8]=S"true";
myDataSet->Tables->Item ["authors"]->Rows->Add (myRow);
myDataAdapter->Update (myDataSet,"authors");
}
catch(Exception * e) {
Console::Write(e->ToString () );
}
__finally {
mySQLConnection->Close();
}
return 0;
}
#include "stdafx.h"
#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;
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();
mySQLConnection->Open ();
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;
}