The ADO.NET Data Provider

The ADO.NET Data Provider

The ADO.NET data providers are comprised of components that facilitate connecting to a data store, executing commands against that data store, and retrieving the results, which can be processed by the data provider or can be placed into a data set. The ADO.NET data provider consists of the following four components:

   Command

   Connection

   DataAdapter

   DataReader

The base versions of these components can be found in the System.Data namespace. However, specialized versions can be found within the System.Data.SqlClient and System.Data.OleDb namespaces. The differences between the two are very slight. The System.Data.SqlClient namespace is optimized for access to a Microsoft SQL Server database, whereas the System.Data.OleDb namespace can be used to access any OLE DB–compliant database. Granted, the class names are different within the two name-spaces, but the basic functionality remains the same.

The System.Data.SqlClient Namespace

The classes within the System.Data.SqlClient namespace are optimized for access to Microsoft SQL Server versions 7.0 or later. They consist of the following implementa-tions of the key components of the .NET data providers:

   SqlCommand

   SqlConnection

   SqlDataAdapter

   SqlDataReader

In a normal programming scenario, a connection is made to the server using the SqlConnection class, and the SqlDataAdapter class is used in conjunction with the SqlConnection class to populate a DataSet class.

In its simplest form, the C# code would appear as follows:

string  cConn  =  “Data  Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlDataAdapter  oDA  =  new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn);

System.Data.DataSet  oDS  =  new  System.Data.DataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

Here’s how the VB .NET code would appear (also in its simplest form):

Dim  cConn  As  string  =  “Data  Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial  Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new ➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim  oDA  As  System.Data.SqlClient.SqlDataAdapter  =  new

➥ System.Data.SqlClient.SqlDataAdapter( ➥ ”Select * From Customers”, oConn)

Dim  oDS  As  System.Data.DataSet  =  new  System.Data.DataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

These examples demonstrate how to create a connection to a Microsoft SQL Server 2000 database and how to use SqlDataAdapter to populate a DataSet object.

A DataSet object keeps the entire result set in memory, which for larger applications or larger result sets might not be the most optimal method of data retrieval. For this reason, Microsoft has included the SqlDataReader and SqlCommand classes. Using these classes in conjunction with each other allows an application to load one record at the time into memory. This means that at any given point in time, the application is using only enough resources to keep that one record in memory, whereas DataSet would use whatever resources it needed to keep all the records in memory. This may not be a big issue when you’re dealing with a result set of 1,000 or so records, but when you’re dealing with 10,000 or more, the memory problems quickly become apparent. For instance, let’s say each record takes 100 bytes of memory. First of all, you’re looking at somewhere around 100,000 bytes to keep the first DataSet object in memory. Second, you’re looking at 1,000,000 bytes. That’s a big difference—and that’s just for one DataSet object; that’s not counting the other objects that exist in the application to perform the actual processing and business logic needed. If you were to use the SqlDataReader class, no matter what, you’d only use 100 bytes at any given time, and 100 bytes versus 1,000,000 bytes is a big difference when you’re talking about memory management and resource availability.

To populate a SqlDataReader class,  you could write C# code as follows:

string  cConn  =  “Data  Source=TRAVISNOTEBOOK\\SQL2000;Integrated

➥ Security=SSPI;Initial Catalog=Northwind”; System.Data.SqlClient.SqlConnection oConn = new ➥ System.Data.SqlClient.SqlConnection(cConn);

oConn.Open();

System.Data.SqlClient.SqlCommand  oCommand  =  new

➥ System.Data.SqlClient.SqlCommand( ➥ ”Select * From Customers”, oConn);

System.Data.SqlClient.SqlDataReader oDR = ➥ oCommand.ExecuteReader();

.

.

oDR.Close();

oConn.Close();

For VB .NET, the code would appear as this:

Dim  cConn  As  string  =  “Data  Source=

➥ TRAVISNOTEBOOK\SQL2000;Integrated

➥ Security=SSPI;Initial  Catalog=Northwind”

Dim oConn As System.Data.SqlClient.SqlConnection = new ➥ System.Data.SqlClient.SqlConnection(cConn)

oConn.Open();

Dim  oCommand  As  System.Data.SqlClient.SqlCommand  =  new

➥ System.Data.SqlClient.SqlCommand( ➥ ”Select * From Customers”, oConn)

Dim oDR As System.Data.SqlClient.SqlDataReader = ➥ oCommand.ExecuteReader()

.

.

.

oDR.Close()

oConn.Close()

The drawback to using the SqlDataReader class is that it must maintain a constant con-nection to the data store. Once the data reader has been populated, you can move to the next record by calling the Read() method on the data reader. Also, once the data reader has been created, no other operations can be performed using the Connection object: It’s too busy servicing the requests from the data reader to be of any use to any other objects. Therefore, until you call the Close() method on the data reader, the Connection object used for the data reader will be unable to process requests from other sources.

The System.Data.OleDb Namespace

The classes within the System.Data.OleDb namespace provide data-access mechanisms to an OLE DB–compliant database and consist of the following implementations of the key components of the .NET data providers:

   OleDbCommand

   OleDbConnection

   OleDbDataAdapter

   OleDbDataReader

Just as with the classes provided in the System.Data.OleDb namespace, in a normal pro-gramming scenario, a connection is made to the server using the OleDbConnection class, and the OleDbDataAdapter class is used in conjunction with the OleDbConnection class to populate a DataSet class.

In its simplest form, the C# code would appear as follows:

string  cConn  =  “Provider=SQLOLEDB;Data  Source=

➥ TRAVISNOTEBOOK\\SQL2000;Initial Catalog=Northwind;

➥ Integrated Security=SSPI;”; System.Data.OleDb.OleDbConnection oConn = new

➥ System.Data.OleDb.OleDbConnection(cConn);

oConn.Open();

System.Data.OleDb.OleDbDataAdapter  oDA  =  new

➥ System.Data.OleDb.OleDbDataAdapter(

➥ ”Select * From Customers”, oConn);

System.Data.DataSet  oDS  =  new  System.Data.DataSet();

oDA.Fill(oDS,”Customers”);

oConn.Close();

For VB .NET, the code would appear as this:

Dim  cConn  As  string  =  “Provider=SQLOLEDB;Data  Source=

➥ TRAVISNOTEBOOK\SQL2000;Initial Catalog=Northwind; ➥ Integrated Security=SSPI;”

Dim oConn As System.Data.OleDb.OleDbConnection = new ➥ System.Data.OleDb.OleDbConnection(cConn)

oConn.Open();

Dim  oDA  As  System.Data.OleDb.OleDbDataAdapter  =  new

➥ System.Data.OleDb.OleDbDataAdapter( ➥ ”Select * From Customers”, oConn)

Dim  oDS  As  System.Data.DataSet  =  new  System.Data.DataSet()

oDA.Fill(oDS,”Customers”)

oConn.Close()

These examples demonstrate how to create a connection to a Microsoft SQL Server 2000 database and how to use OleDbDataAdapter to populate a DataSet object.

As for implementing OleDbDataReader, the C# code would appear as follows:

string  cConn  =  “Provider=SQLOLEDB;Data  Source=

➥ TRAVISNOTEBOOK\\SQL2000;Initial Catalog=Northwind;

➥ Integrated Security=SSPI;”; System.Data.OleDb.OleDbConnection oConn = new

➥ System.Data.OleDb.OleDbConnection(cConn);

oConn.Open();

System.Data.OleDb.OleDbCommand  oCommand  =  new

➥ System.Data.OleDb.OleDbCommand(

➥ ”Select * From Customers”, oConn); System.Data.OleDb.OleDbDataReader oDR = oCommand.ExecuteReader();

.

.

.

oDR.Close();

oConn.Close();

Here’s the code for VB .NET:

Dim  cConn  As  string  =  “Provider=SQLOLEDB;Data  Source=

➥ TRAVISNOTEBOOK\SQL2000;Initial Catalog=Northwind;

➥ Integrated Security=SSPI;”

Dim oConn As System.Data.OleDb.OleDbConnection = new

➥ System.Data.OleDb.OleDbConnection(cConn)

oConn.Open();

Dim  oCommand  As  System.Data.OleDb.OleDbCommand  =  new

➥ System.Data.OleDb.OleDbCommand( ➥ ”Select * From Customers”, oConn)

Dim  oDR  As  System.Data.OleDb.OleDbDataReader  =  oCommand.ExecuteReader()

.

.

.

oDR.Close()

oConn.Close()

Just as with SqlDataReader, the drawback to using OleDbDataReader is that it must maintain a constant connection to the data store. Once the OleDbDataReader object has been populated, you can move to the next record by calling the Read() method on OleDbDataReader. Also, once the OleDbDataReader object has been created, no other operations can be performed using the OleDbConnection object; it’s too busy servicing the requests from the data reader to be of any use to any other objects. Therefore, until you call the Close() method on OleDbDataReader, the OleDbConnection object used for OleDbDataReader will be unable to process requests from other sources.