Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
DataReader
The DataReader is the read-only, forward cursor over a rowset of ADO.NET data. The big change between it and the ADO Recordset is the use of a Read method at the start of data operations to advance the cursor to the first row. Subsequent Read calls load new rows into the object. If the Read returns a false Boolean we fall out of the loop. This helps prevent those infinite loops of the ADO Recordset where we forgot to call MoveNext.
The DataReader provides two main ways of getting column data: by ordinal position or by column name. The Listing 2 demo retrieved the CustomerID column by using the 0 position ordinal along with the strongly typed GetString method for returning characters. The ContactName column was chosen using the more convenient and more maintainable indexer syntax. I prefer the second way of retrieving the column value despite the small performance loss required to do a name lookup on the fly.
This chunk of demo code doesn't bother with a local transaction since we're doing a read-only operation. It still uses the try/finally structure to ensure that resources are closed appropriately. One key difference in our technique in this example is the use of the CommandBehavior enumeration when we call ExecuteReader.
A CloseClonnection value of the CommandBehavior type saves us some work in the finally block because we only have to check on and close the DataReader object. It calls the close of the Connection for us and prevents situations where the Connection is closed while the DataReader is still being used.
Batch Reads
The DataReader has the capability of receiving multiple result sets if the data provider supports batch SQL commands. Listing 4 shows a SELECT statement against both the Customers and Orders table from Northwind in a single SQL batch statement. The only difference in handling the data is the use of the NextResult to move the DataReader from the first result set to the next.
Listing 4 SQL Provider Batch Query Demo
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConnectedOpsDemo
{
class SqlCmdBatchDemo
{
[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();
SqlCommand cmd = new SqlCommand(
"SELECT TOP 10 * FROM Customers; " +
"SELECT TOP 10 * FROM Orders;", 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" +
" result sets from ExecuteReader",
cmd.CommandText);
Console.WriteLine("\nCustomers");
// read through Customers rows from the first result set
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"]);
}
// move to the second result set
rdr.NextResult();
Console.WriteLine("\nOrders");
// read through Orders rows
while (rdr.Read())
{
// display CustomerID (position 0),
// ContactName (by name) columns
// from the DataReader
Console.WriteLine("OrderID: {0} OrderDate: {1}" +
" CustomerID:{2}",
rdr["OrderID"], rdr["OrderDate"], rdr["CustomerID"]);
}
}
finally
{
// close the reader in the face of exceptions
if (rdr != null)
{
if (!rdr.IsClosed)
rdr.Close();
}
}
}
}
}
The output from the batch execute is shown in Listing 5. Notice how we can get two tables' worth of information in a single round trip. This is a great way to enhance data access performance.
Listing 5 SQL Provider Batch Query Demo Results
QUERY: SELECT TOP 10 * FROM Customers; SELECT TOP 10 * FROM Orders; Returned the following result sets from ExecuteReader Customers 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 Orders OrderID: 10248 OrderDate: 7/4/1996 12:00:00 AM CustomerID:VINET OrderID: 10249 OrderDate: 7/5/1996 12:00:00 AM CustomerID:TOMSP OrderID: 10250 OrderDate: 7/8/1996 12:00:00 AM CustomerID:HANAR OrderID: 10251 OrderDate: 7/8/1996 12:00:00 AM CustomerID:VICTE OrderID: 10252 OrderDate: 7/9/1996 12:00:00 AM CustomerID:SUPRD OrderID: 10253 OrderDate: 7/10/1996 12:00:00 AM CustomerID:HANAR OrderID: 10254 OrderDate: 7/11/1996 12:00:00 AM CustomerID:CHOPS OrderID: 10255 OrderDate: 7/12/1996 12:00:00 AM CustomerID:RICSU OrderID: 10256 OrderDate: 7/15/1996 12:00:00 AM CustomerID:WELLI OrderID: 10257 OrderDate: 7/16/1996 12:00:00 AM CustomerID:HILAA
Stored Procedures
Stored procedures are used to encapsulate database logic and increase database performance in a database application. So far we have not demonstrated their use and have stuck to raw SQL text. ADO.NET fortunately provides full support for stored procedures.
To demonstrate stored procedures, let's create a new one from our Northwind database. The stored procedure can be added via Visual Studio.NET or SQL Server Query Analyzer. Listing 6 shows that the SQL inside SelectCustByID.
Listing 6 SQL Server SelectCustByID Stored Procedure for Northwind Database
CREATE PROCEDURE dbo.SelectCustByID ( @CustID varchar(7), @CustCount int OUTPUT } AS SELECT @CustCount=Count(*) FROM Customers WHERE ContactName Like @CustID SELECT * FROM Customers WHERE ContactName Like @CustID RETURN
The next step is to generate a demo for calling the procedure (see Listing 7).
Listing 7 SQL Provider Stored Procedure Demo
using System;
using System.Data;
using System.Data.SqlClient;
namespace ConnectedOpsDemo
{
class SqlCmdStoredProcDemo
{
[STAThread]
static void Main(string[] args)
{
SqlConnection conn = null;
SqlDataReader rdr = null;
SqlCommand cmd = null;
try
{
// open connection to the data source
conn = new
SqlConnection(
"server=(local);database=Northwind;" +
"integrated security=true;");
conn.Open();
// set the command to execute a stored procedure named
// SelectCustomersByID
cmd = new SqlCommand("SelectCustByID", conn);
cmd.CommandType = CommandType.StoredProcedure;
// create a parameter for the @ContactName output parameter
SqlParameter custcount = new
SqlParameter("@CustCount",SqlDbType.Int);
custcount.Direction = ParameterDirection.Output;
cmd.Parameters.Add(custcount);
// create a parameter for the @Name input parameter
SqlParameter custid = new
SqlParameter("@CustID",SqlDbType.VarChar,7);
custid.Value = "%D%";
cmd.Parameters.Add(custid);
// execute command to return a datareader that closes our
// parent connection when it is closed
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine("\nSTORED PROC: {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();
}
// wait till the reader is closed to
// read the output parameter
Console.WriteLine("\n\nSTORED PROC: {0} @CustCount: {1}" +
"from ExecuteReader",
cmd.CommandText, cmd.Parameters["@CustCount"].Value);
}
}
}
}
The biggest difference between stored procedures and text commands is the value of the CommandType property of the Command object. The default is normally set to Text; here we set it to StoredProcedure. We also set the CommandText property to the name of the stored procedure instead of a SQL statement.
After we have outlined the name of the stored procedure to execute, we need to ensure that we have the appropriate parameters passed into and out of it. This is done using the Parameters collection of the Command object that holds instances of the SQLParameter class.
The SelectCustByID stored procedure takes an input parameter named @CustID and has a single output parameter named @CustCount. The @CustID parameter takes a SQL varchar value that is 7 characters wide. The @CustCount parameter is a SQL int. We create each parameter with the correct initialization information and then set the value before we add the parameter to the Parameters collection.
The ExecuteReader method is then called to get the result set back and allow the same iteration through the row values as before. An interesting quirk is the way we get the result value from the output param. We need to close down the DataReader before the value is passed back into the Parameters collection. Calling earlier, before the DataReader close, results in a null value.
The result set in Listing 8 shows we returned 27 rows, as reported by the output parameter.
Listing 8 SQL Provider Stored Procedure Demo Results
STORED PROC: SelectCustByID Returned the following rows from ExecuteReader CustomerID: ALFKI ContactName: Maria Anders CustomerID: AROUT ContactName: Thomas Hardy CustomerID: BERGS ContactName: Christina Berglund CustomerID: BLONP ContactName: Frédérique Citeaux CustomerID: COMMI ContactName: Pedro Afonso CustomerID: DALEM ContactName: Dale Michalk CustomerID: EASTC ContactName: Ann Devon CustomerID: ERNSH ContactName: Roland Mendel CustomerID: FISSA ContactName: Diego Roel CustomerID: FURIB ContactName: Lino Rodriguez CustomerID: GALED ContactName: Eduardo Saavedra CustomerID: GODOS ContactName: José Pedro Freyre CustomerID: GOURL ContactName: André Fonseca CustomerID: GREAL ContactName: Howard Snyder CustomerID: HILAA ContactName: Carlos Hernández CustomerID: LACOR ContactName: Daniel Tonini CustomerID: LINOD ContactName: Felipe Izquierdo CustomerID: MAISD ContactName: Catherine Dewey CustomerID: MORGK ContactName: Alexander Feuer CustomerID: OCEAN ContactName: Yvonne Moncada CustomerID: PARIS ContactName: Marie Bertrand CustomerID: PERIC ContactName: Guillermo Fernández CustomerID: PRINI ContactName: Isabel de Castro CustomerID: QUEDE ContactName: Bernardo Batista CustomerID: ROMEY ContactName: Alejandra Camino CustomerID: SPECD ContactName: Dominique Perrier CustomerID: TRADH ContactName: Anabela Domingues STORED PROC: SelectCustByID @CustCount: 27 from ExecuteReader