.NET Data Providers : A Basic Tutorial

The .NET data provider is the layer between the application and the database that takes care of the all the nitty gritties of the database interaction. They are designed for fast, efficient means of data retrieval and reconciliation. In this article I'll deal with the classes the .NET data provider offers and some common functions in each of the classes.

Now, the .NET Data provider can be manifested as SQL Server data provider, OLEDB data provider or the ODBC Data Provider. For the time being I'll be taking you through this article in the context of the first two Data providers.

SQL Server Data provider is the most efficient way to connect to an SQL Server Database(version 7.0 onwards). It uses a proprietary protocol for connecting to the Database which results in optimized usage of the SQL Server database along with faster data transactions.

The System.Data.Sqlclient namespace contains classes for the SQL Server data provider.

OLEDB Data Provider is used with databases that support the OLE DB interfaces. ADO.NET supports the following OLE DB Providers

SQLOLEDB - Microsoft OLE DB Provider for SQL Server.
MSDAORA - Microsoft OLE DB Provider for Oracle.
Microsoft.Jet.OLEDB.4.0 - OLE DB Provider for Microsoft Jet.

However, OLE DB Data Provider does not support OLE DB 2.5 Interfaces like the ones used for MS Exchange Server and Internet publishing. This data provider is the recommended one for Oracle, SQL Server 6.5.

The System.Data.Oledbclient namespace contains classes for the OLE DB Data provider.

The .NET data providers have 4 key classes which are derived from the IDbConnection, IDbCommand, IDataReader, IDbDataAdapter interfaces found in the System.Data namespace.

These four classes are manifested in their SQL Server or OLE DB providers as SqlConnection,OledbConnection for the Connection class; SqlCommand, OledbCommand for the Command class; SqlDataReader,OledbDataReader for the DataReader class; SqlDataAdapter, OledbDataAdapter for the DataAdapter class.

Lets look at each of these four classes in brief ...

The Connection class

This class is inherited from the IDbConnection interface and used to establish connection to the database. The commands to the database will be executed using this connection. The connection has to be closed explicitly after use especially if its part of a connection pool to make it available, for eg.

if the Provider is SQL Server then

Dim con As New _
SqlDbConnection("server=myserver;database=MLPierre;uid=sa;pwd=;")

or

if the Provider is an OLE DB provider then

Dim con As New _
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0.;" & _
"Data Source=C:\samples\dotnet\Testdb.mdb").

The Command class

Inherited from the IDbCommand interface and used to execute Transact SQL commands or stored procedure against the database. The command class exposes the following methods.

ExecuteReader: Return a stream of data in the form of an object that inherits from the IDataReader interface.

ExecuteScalar: A single value is returned from the query/procedure execution.
ExecuteNonQuery: Returns the status of a query for intance, number of rows effected from the query. for eg.

Public Sub readData(ByVal con As IDbConnection)
Dim cmd As IDbCommand = con.CreateCommand()
cmd.CommandText = "SELECT * FROM ProductMaster"
Dim reader As IDataReader = cmd.ExecuteReader()
While reader.Read()
Console.WriteLine("{0} :: {1}", _
reader.GetString(0), _
reader.GetString(1))
End While
End Sub

The DataReader class

Inherited from the IDataReader interface and used to read data from the database as a read-only, forward only stream. It is an efficient means for data retrieval and scarcely used memory resources because it reads one record at a time from the database. This class exposes the following methods.

Read: To iterate throught the rows in the returned stream of data. for eg.

While reader.Read()
Console.WriteLine("{0} :: {1}", _
reader.GetString(0), _
reader.GetString(1))
End While

another eg.

While reader.Read()
Console.WriteLine("{0} :: {1}", _
reader.Item(0).ToString(), _
reader.Item(1).ToString())
End While

NextResult: If there are more than 1 result sets, use this method to jump to the next. for eg.

Public Sub readData(ByVal con As IDbConnection)
Dim cmd As IDbCommand = con.CreateCommand()
cmd.CommandText = "SELECT * FROM ProductMaster; SELECT * From OrderMaster"
Dim reader As IDataReader = cmd.ExecuteReader()
Dim nextResult As Boolean = True
Do Until Not nextResult
While reader.Read()
Console.WriteLine("{0} :: {1}", _
reader.Item(0).ToString(), _
reader.Item(1).ToString())
End While
nextResult = reader.NextResult()
Loop
End Sub

The DataAdapter class

Inherited from the IDbDataAdapter interface and used to reconcile data to and from the dataset and the database. This class is basically used to interact with the dataset which is a disconnected source of data. The Dataset is mapped with the corresponding table's schema. More about Datasets in my forthcoming article. This class exposes the following methods.

Fill: Fills the dataset with data from the database and then the connection is released making the DataSet available for perusal. For eg.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Public Class DAdaptTest
Public Shared Sub Main()
Dim ds As New DataSet
Dim sda As New SqlDataAdapter( _"SELECT * FROM ProductMaster", _
"server=myserver;database=MLPierre;uid=sa;pwd=;")
sda.Fill(ds, "Products")
Dim ctr As Integer = 0
While i < ds.Tables("Products").Rows.Count - 1
Console.WriteLine("{0} :: {1}", _
ds.Tables("Products").Rows(i).Item(0).ToString(), _
ds.Tables("Products").Rows(i).Item(1).ToString())
ctr += 1
End While
End Sub
End Class

From this article, we can see that the .NET Data Provider encapsulates the database interaction part from the actual data making it versatile and simplifying development.