- 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
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>