Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
Like this article? We recommend

OLEDB Provider Parameterization

The code so far works fairly generically between the SQL and the OLEDB providers except for the parameterization demo. The OLEDB .NET data provider supports a different syntax for named parameter queries. Instead of the @ symbol with a name, it substitutes ? characters for the variable locations in the SQL statement. The order in which the parameters are added to the Command object is what lines them up with the question marks. An example is shown in Listing 11.

Listing 11 OLEDB Provider Named Parameter Query Demo

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectedOpsDemo
{
class OleDbCmdParamDemo
{
[STAThread]
static void Main(string[] args)
{
   OleDbConnection conn = null;
   OleDbDataReader rdr = null;

   try
   {
     // open connection to the data source using
     // OLEDB connection string
     conn = new OleDbConnection("Provider=SQLOLEDB.1; " + 
      "Integrated Security=SSPI;Initial Catalog=Northwind;" + 
      "Data Source=(local);");

     conn.Open();

     // create a parameter for the LIKE clause to a
     // query that returns DataReader via ExecuteReader
     OleDbCommand cmd = new OleDbCommand(
      "SELECT * FROM Customers WHERE CustomerID LIKE ?",
       conn);
     OleDbParameter name = new OleDbParameter();
     name.OleDbType = OleDbType.VarChar;
     name.Size = 7;
     name.Value = "%D%";

     // the ?'s are filled by the order we add params to the
     // parameters collection
     cmd.Parameters.Add(name);

     // 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();
     }
   }
}
}
}

The result set is identical to that from the SQL provider and our previous queries with SQL text, as shown in Listing 12.

Listing 12 OLEDB Provider Named Parameter Query Demo Results

QUERY: SELECT * FROM Customers WHERE CustomerID LIKE ?
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

OLEDB Provider TableDirect query

One feature that the OLEDB provider has that the SQL provider doesn't is the ability to query and open up the entire contents of table with a minimal amount of code. Listing 13 shows how to do this.

Listing 13 OLEDB Provider TableDirect Query Demo

using System;
using System.Data;
using System.Data.OleDb;

namespace ConnectedOpsDemo
{
class OleDbCmdTableDirect
{
[STAThread]
static void Main(string[] args)
{
   OleDbConnection conn = null;
   OleDbDataReader rdr = null;

   try
   {
     // open connection to the data source
     //using OLEDB connection string
     conn = new OleDbConnection("Provider=SQLOLEDB.1;" + 
      "Integrated Security=SSPI;Initial Catalog=Northwind;" + 
      "Data Source=(local);");
     conn.Open();

     OleDbCommand cmd = new OleDbCommand("Customers", conn);
     cmd.CommandType = CommandType.TableDirect;

     // execute command to return a datareader that holds the
     // entire table in its result set
     rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

     Console.WriteLine("\nTABLEDIRECT QUERY: {0} from " +
      "ExecuteReader",
     cmd.CommandText);

     Console.WriteLine("First 10 rows");
     // read through all rows

     int rcount = 0;
     while (rdr.Read() && rcount < 10)
     {
        // display CustomerID (position 0),
        // ContactName (by name) columns
        // from the DataReader
        Console.WriteLine("CustomerID: {0} ContactName: {1}",
        rdr.GetString(0), rdr["ContactName"]);
        rcount++;
     }
   }
   finally
   {
     // close the reader in the face of exceptions
     if (rdr != null)
     {
        if (!rdr.IsClosed)
          rdr.Close();
     }
   }
}
}
}

The big change to the previous example is the setting of the CommandType property of the Command object to TableDirect and the CommandText property to the name of the table. In Listing 13 we chose our favorite table, Customers, and set up a loop counter to display the first 10 rows to save output space for this article. Listing 14 shows the output from this execution.

Listing 14 OLEDB Provider TableDirect Query Demo Results

TABLEDIRECT QUERY: Customers from ExecuteReader
First 10 rows
CustomerID: ALFKI ContactName: Maria Anders
CustomerID: ANATR ContactName: Ana Trujillo
CustomerID: ANTON ContactName: Antonio Moreno
CustomerID: AROUT ContactName: Thomas Hardy
CustomerID: BERGS ContactName: Christina Berglund
CustomerID: BLAUS ContactName: Hanna Moos
CustomerID: BLONP ContactName: Frédérique Citeaux
CustomerID: BOLID ContactName: Martín Sommer
CustomerID: BONAP ContactName: Laurence Lebihan
CustomerID: BOTTM ContactName: Elizabeth Lincoln

NULLs

Database NULLs are one of those things in live that you can't live with and you can't live without. Fortunately .NET takes a balanced view and supports working with NULLs.

The System.DbNull class has a Value property that represents the database NULL and can be used in comparison with return values. You have to be cognizant of this fact when you're working with the DataReader or you'll access a NULL column and throw an exception when you assign it to a variable.

In the example shown in Listing 15, I take the approach of comparing the value to DBNull to make a decision of using a dummy string value or the string value of the column. I use the Region column of the Northwind Customers table as an example, since it allows NULL values.

Listing 15 OLEDB Provider TableDirect Query Demo

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConnectedOpsDemo
{
class SqlCmdDBNullDemo
{
[STAThread]
static void Main(string[] args)
{
   SqlConnection conn = null;
   SqlDataReader rdr = null;

   try
   {
     // open connection to the data source
     conn = new SqlConnection("server=(local);database=Northwind;" + 
      "integrated security=true;");
     conn.Open();

     // query all the customerID d rows in the Customers table
     SqlCommand cmd =
      new SqlCommand(
      "SELECT * FROM Customers WHERE CustomerID LIKE '%D%'",
      conn);

     // 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())
     {
        Console.Write("CustomerID: {0} ContactName: {1}",
          rdr["CustomerID"], rdr["ContactName"]);

        // check the Region column for a null value
        string region;
        if (rdr["Region"] == System.DBNull.Value)
          region = "<blank>";
        else
          region = (string) rdr["Region"];
        Console.WriteLine("Region: {0}", region);
     }
   }
   finally
   {
     // close the reader in the face of exceptions
     if (rdr != null)
     {
        if (!rdr.IsClosed)
          rdr.Close();
     }
   }
}
}
}

Listing 16 shows the results.

Listing 16 OLEDB Provider TableDirect Query Demo Results

QUERY: SELECT * FROM Customers WHERE CustomerID LIKE '%D%'
Returned the following rows from ExecuteReader
CustomerID: BOLID ContactName: Martín SommerRegion: <blank>
CustomerID: DALEM ContactName: Dale MichalkRegion: NC
CustomerID: DRACD ContactName: Sven OttliebRegion: <blank>
CustomerID: DUMON ContactName: Janine LabruneRegion: <blank>
CustomerID: GALED ContactName: Eduardo SaavedraRegion: <blank>
CustomerID: GODOS ContactName: José Pedro FreyreRegion: <blank>
CustomerID: LINOD ContactName: Felipe IzquierdoRegion: Nueva Esparta
CustomerID: MAISD ContactName: Catherine DeweyRegion: <blank>
CustomerID: OLDWO ContactName: Rene PhillipsRegion: AK
CustomerID: QUEDE ContactName: Bernardo BatistaRegion: RJ
CustomerID: SPECD ContactName: Dominique PerrierRegion: <blank>
CustomerID: SUPRD ContactName: Pascale CartrainRegion: <blank>
CustomerID: TRADH ContactName: Anabela DominguesRegion: SP
CustomerID: WANDK ContactName: Rita MüllerRegion: <blank>
  • + Share This
  • 🔖 Save To Your Account