Home | | Service Oriented Architecture | The ADO.NET Data Provider

Chapter: XML and Web Services : Building XML-Based Applications : Leveraging XML in Visual Studio .NET

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 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.

 

Study Material, Lecturing Notes, Assignment, Reference, Wiki description explanation, brief detail
XML and Web Services : Building XML-Based Applications : Leveraging XML in Visual Studio .NET : The ADO.NET Data Provider |


Privacy Policy, Terms and Conditions, DMCA Policy and Compliant

Copyright © 2018-2023 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.