Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account