NDP ADO.NET- Provider basics

Provider intro

.Net Framework Data Providers. Coordinated set of components including Connection, Command, DataReader.

Example: If you working with SQLServer, you could use the SQL Server native drivers. This would involve the coordinated use of SqlConnection, SqlCommand, SqlDataReader.

Alternatively, you could use OLE DB drivers. Then you would use classes OleDbConnection, OleDbCommand, OleDbDataReader.

These classes are coordinated. If you ask a SqlConnection for a command object, it will give you a SqlCommand. An OleDbConnection object would give you an OleDbCommand.

Similarly, a command object requires a connection. A SqlCommand can only take a SqlConnection.

Provider classes

Table 22-1. Core Objects of an ADO.NET Data Provider [AT]

Object	Base Class	Implemented Interfaces	Meaning in Life
Connection	DbConnection	IDbConnection	Provides the ability to connect to and disconnect from the data store. Connection objects also provide access to a related transaction object.
Command	DbCommand	IDbCommand	Represents a SQL query or name of a stored procedure. Command objects also provide access to the provider's data reader object.
DataReader	DbDataReader	IDataReader, IDataRecord	Provides forward-only, read-only access to data.
DataAdapter	DbDataAdapter	IDataAdapter, IDbDataAdapter	Transfers DataSets between the caller and the data store. Data adapters contain a set of four internal command objects used to select, insert, update, and delete information from the data store.
Parameter	DbParameter	IDataParameter, IDbDataParameter	Represents a named parameter within a parameterized query.
Transaction	DbTransaction	IDbTransaction	Performs a database transaction.

Coordinated sets:

	SqlServer	OLE DB	ODBC	Oracle
Connection	SqlConnection	OleDbConnection	OdbcConnection	OracleConnection
Command	SqlCommand	OleDbCommand	OdbcCommand	OracleCommand
DataReader	SqlDataReader	OleDbDataReader	OdbcDataReader	OracleDataReader
DataAdapter	SqlDataAdapter	OleDbDataAdapter	OdbcDataAdapter	OracleDataAdapter

There are others.
Data adapters are used to transfer data into a DataSet. (Later.)
We will use primarily SQLServer and OLE DB.
Through interfaces, we can isolate the DB-specific code to a small section and work polymorphically most of the time.
My code samples may not be so nice.

Namespaces and assemblies

Table 22.2 Microsoft ADO.NET Data Providers [AT]

Data Provider	Namespace	Assembly
OLE DB	System.Data.OleDb	System.Data.dll
Microsoft SQL Server	System.Data.SqlClient	System.Data.dll
Microsoft SQL Server Mobile	System.Data.SqlServerCe	System.Data.SqlServerCe.dll
ODBC	System.Data.Odbc	System.Data.dll
Oracle	System.Data.OracleClient	System.Data.OracleClient.dll

When you create project in Visual Studio, it will already contain a reference to System.Data.dll, so no extra work will be required to make your project compile.

Table 22-4. Additional ADO.NET-centric Namespaces [AT]

Namespace	Contains
Microsoft.SqlServer.Server	This new .NET 2.0 namespace provides types that allow you to author stored procedures via managed languages for SQL Server 2005.
System.Data	This namespace defines the core ADO.NET types used by all data providers.
System.Data.Common	This namespace contains types shared between data providers, including the .NET 2.0 data provider factory types.
System.Data.Design	This new .NET 2.0 namespace contains various types used to construct a design-time appearance for custom data components.
System.Data.Sql	This new .NET 2.0 namespace contains types that allow you to discover Microsoft SQL Server instances installed on the current local network.
System.Data.SqlTypes	This namespace contains native data types used by Microsoft SQL Server. Although you are always free to use the corresponding CLR data types, the SqlTypes are optimized to work with SQL Server.

The basic classes provided by the providers are:

Interface	What it does [MS]
IDbConnection	Establishes a connection to a data source.
IDbCommand	Executes a command against a data source.
IDbDataReader IDataRecord	Reads a forward-only, read-only stream of data from a data source.
IDbDataAdapter IDataAdapter	Populates a DataSet and resolves updates with the data source.

In addition, various other classes are provided by each provider group:

Interface	What it does [MS]
IDbTransaction	Enables you to enlist commands in transactions at the data source.
(CommandBuilder)	A helper object that will automatically generate command properties of a DataAdapter or will derive parameter information from a stored procedure and populate the Parameters collection of a Command object. [No generic interface.]
IDbDataParameter IDataParameter	Defines input, output, and return value parameters for commands and stored procedures.
(Exception)	Returned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception.[No generic interface.]
(Error)	Exposes the information from a warning or error returned by a data source.[No generic interface.]

OleDB example

Compare our previous code using SQLServer provider classes to code using OleDB provider classes. (Both come from project Connected/Start1).

string connStr = ConfigurationManager.AppSettings["SqlConnStr"];
SqlConnection conn = new SqlConnection(connStr);

string connStr = ConfigurationManager.ConnectionStrings["OleDbConnStr"].ConnectionString;
OleDbConnection conn = new OleDbConnection(connStr);

SqlCommand OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT Id, FirstName, LastName FROM Student";
cmd.CommandType = CommandType.Text;  // default
 
conn.Open();

SqlDataReader OleDbReader reader 
   = cmd.ExecuteReader(CommandBehavior.CloseConnection);

while (reader.Read())
  Console.WriteLine("{0}\t{1}\t{2}", reader["Id"], reader["FirstName"], reader["LastName"]);

reader.Close();

We will need a different connection string. At the very least, in addition to other properties, OLE DB needs a Provider value.
In this case, we decided to use a different section of the configuration file, the <connectionStrings> section. This is the preferred place for connection string data. This is what VS will use.

app.config

(extra line breaks added)

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="SqlConnStr"  
         value="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=School"/>
  </appSettings>
  <connectionStrings>
    <add name="OleDbConnStr" 
       connectionString="Provider=SQLOLEDB;Data Source=localhost;
                              Integrated Security=SSPI;Initial Catalog=School"/>    
  </connectionStrings>
</configuration>

Using interfaces

We can try to isolate provider-specific code in a data-access layer (DAL). Here is the same code re-worked one more time (also in Connected\Start1):

   IDbConnection conn = GetDbConnection();
  
   IDbCommand cmd = conn.CreateCommand();
   cmd.CommandText = "SELECT Id, FirstName, LastName FROM Student";
   cmd.CommandType = CommandType.Text;  // default

   conn.Open();

   IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

   while (reader.Read())
       Console.WriteLine("{0}\t{1}\t{2}", reader["Id"], reader["FirstName"], reader["LastName"]);

   reader.Close();
   
static IDbConnection GetDbConnection()
{
   return new SqlConnection(ConfigurationManager.AppSettings["SqlConnStr"]);
}

We will see some other approaches later.

Using interfaces does not allow you to mix-and-match. Provider class use must be coordinated. The following will compile but will fail at run-time.

IDbConnection conn = new SqlConnection(connStr);



IDbCommand cmd = new OleDbConnection(queryStr);


cmd.Connection = conn;  // this will fail at run-time