- Choosing a Provider
- Connection
- Command
- DataReader
- SQL Provider Parameterization
- OLEDB Provider Parameterization
- Generic Connected Data Access
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Generic Connected Data Access
Earlier I promised to present code to make the data access code you write decoupled from the .NET data provider implementing the object model. Listing 17 shows one way of accomplishing the task.
The first thing I do is take the previous query demos and convert the provider-specific classes to their neutral interfaces: IDbConnection, IDataReader, and IDbCommand. I also abstract away the creation of the Connection object with its provider-specific connection string. In this example, I'm set up to use the OLEDB provider.
Once you have the Connection object, you can create the other objects of the ADO.NET connected model. The Connection object creates a Command object, and the Command object then creates the return objects. At this point you can operate with DataReader as we did in the previous examples. Listing 18 validates the results of our conversion.
The nice thing about this conversion was the small amount of time taken to convert, yet the results are huge if we have to switch providers midstream in a project.
Listing 17 Generic Query Demo
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
namespace ConnectedOpsDemo
{
enum ConnType
{
Sql,
OleDb
}
class GenericQueryDemo
{
static IDbConnection GetConnection(ConnType type)
{
IDbConnection conn = null;
if (type == ConnType.Sql)
conn = new SqlConnection("server=(local);database=Northwind;" +
"integrated security=true;");
else
conn = new OleDbConnection("Provider=SQLOLEDB.1;" +
"Integrated Security=SSPI;Initial Catalog=Northwind;" +
"Data Source=(local);");
return conn;
}
[STAThread]
static void Main(string[] args)
{
IDbConnection conn = null;
IDataReader rdr = null;
try
{
conn = GetConnection(ConnType.OleDb);
// open connection to the data source
conn.Open();
// return ContactName from DALEM row via ExecuteScalar
IDbCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT ContactName FROM Customers " +
"WHERE CustomerID='DALEM'";
cmd.Connection = conn;
string contactname = (string) cmd.ExecuteScalar();
Console.WriteLine("QUERY: {0} \nReturned ContactName={1} " +
"from ExecuteScalar",
cmd.CommandText,"DALEM");
// change CommandText to select a result set from the
//Customers table
cmd.CommandText = "SELECT * FROM Customers WHERE " +
"CustomerID LIKE '%D%'";
// execute command to return a datareader that closes our
// parent connection when it is closed
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine("\nQUERY: {0} \nReturned the following rows " +
"from ExecuteReader",
cmd.CommandText);
// read through all rows
while (rdr.Read())
{
// display CustomerID (position 0),
// ContactName (by name) columns
// from the DataReader
Console.WriteLine("CustomerID: {0} ContactName: {1}",
rdr.GetString(0),
rdr["ContactName"]);
}
}
finally
{
// close the reader in the face of exceptions
if (rdr != null)
{
if (!rdr.IsClosed)
rdr.Close();
}
}
}
}
}
Listing 18 Generic Query Demo Results
QUERY: SELECT ContactName FROM Customers WHERE CustomerID='DALEM' Returned ContactName=DALEM from ExecuteScalar QUERY: SELECT * FROM Customers WHERE CustomerID LIKE '%D%' Returned the following rows from ExecuteReader CustomerID: BOLID ContactName: Martín Sommer CustomerID: DALEM ContactName: Dale Michalk CustomerID: DRACD ContactName: Sven Ottlieb CustomerID: DUMON ContactName: Janine Labrune CustomerID: GALED ContactName: Eduardo Saavedra CustomerID: GODOS ContactName: José Pedro Freyre CustomerID: LINOD ContactName: Felipe Izquierdo CustomerID: MAISD ContactName: Catherine Dewey CustomerID: OLDWO ContactName: Rene Phillips CustomerID: QUEDE ContactName: Bernardo Batista CustomerID: SPECD ContactName: Dominique Perrier CustomerID: SUPRD ContactName: Pascale Cartrain CustomerID: TRADH ContactName: Anabela Domingues CustomerID: WANDK ContactName: Rita Müller