Visual CSharp 2005 Recipes (2006) [eng]

318 C H A P T E R 9 ■ D ATA B A S E A C C E S S

while (reader.Read())

{

// Display the product details. Console.WriteLine(" {0} = {1}", reader["ProductName"], reader["TotalPurchase"]);

}

}

}

}

public static void Main()

{

// Create a new SqlConnection object.

using (SqlConnection con = new SqlConnection())

{

//Configure the SqlConnection object's connection string. con.ConnectionString = @"Data Source = .\sqlexpress;" +

"Database = Northwind; Integrated Security=SSPI";

//Open the database connection and execute the example

//commands through the connection.

con.Open();

ParameterizedCommandExample(con, "5", "Cleaner");

Console.WriteLine(Environment.NewLine);

StoredProcedureExample(con, "Seafood", "1999"); Console.WriteLine(Environment.NewLine);

}

// Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine();

}

}

}

9-7. Process the Results of a SQL Query Using a Data Reader

Problem

You need to process the data contained in a System.Data.IDataReader instance returned when you execute the IDbCommand.ExecuteReader method (discussed in recipe 9-5).

Solution

Use the members of the IDataReader instance to move through the rows in the result set sequentially and access the individual data items contained in each row.

C H A P T E R 9 ■ D ATA B A S E A C C E S S

319

How It Works

The IDataReader interface represents a data reader, which is a forward-only, read-only mechanism for accessing the results of a SQL query. Each data provider includes a unique IDataReader implementation. Here is the list of IDataReader implementations for the five standard data providers:

•System.Data.Odbc.OdbcDataReader

•System.Data.OleDb.OleDbDataReader

•System.Data.OracleClient.OracleDataReader

•System.Data.SqlServerCe.SqlCeDataReader

•System.Data.SqlClient.SqlDataReader

The IDataReader interface extends the System.Data.IDataRecord interface. Together, these interfaces declare the functionality that provides access to both the data and the structure of the data contained in the result set. Table 9-5 describes some of the commonly used members of the

IDataReader and IDataRecord interfaces.

Table 9-5. Commonly Used Members of Data Reader Classes

Member	Description
Property
FieldCount	Gets the number of columns in the current row.
IsClosed	Returns true if the IDataReader is closed; false if it’s currently open.
Item	Returns an object representing the value of the specified column in the
	current row. Columns can be specified using a zero-based integer index or
	a string containing the column name. You must cast the returned value to
	the appropriate type. This is the indexer for data record and reader classes.
Method
GetDataTypeName	Gets the name of the data source data type for a specified column.
GetFieldType	Gets a System.Type instance representing the data type of the value
	contained in the column specified using a zero-based integer index.
GetName	Gets the name of the column specified by using a zero-based integer index.
GetOrdinal	Gets the zero-based column ordinal for the column with the specified name.
GetSchemaTable	Returns a System.Data.DataTable instance that contains metadata
	describing the columns contained in the IDataReader.
IsDBNull	Returns true if the value in the specified column contains a data source null
	value; otherwise, it returns false.
NextResult	If the IDataReader includes multiple result sets because multiple statements
	were executed, NextResult moves to the next set of results. By default, the
	IDataReader is positioned on the first result set.
Read	Advances the reader to the next record. The reader always starts prior to the
	first record.

In addition to those members listed in Table 9-5, the data reader provides a set of methods for retrieving typed data from the current row. Each of the following methods takes an integer argument that identifies the zero-based index of the column from which the data should be returned:

GetBoolean, GetByte, GetBytes, GetChar, GetChars, GetDateTime, GetDecimal, GetDouble, GetFloat, GetGuid, GetInt16, GetInt32, GetInt64, GetString, GetValue, and GetValues.

320 C H A P T E R 9 ■ D ATA B A S E A C C E S S

The SQL Server and Oracle data readers also include methods for retrieving data as data source–specific data types. For example, the SqlDataReader includes methods such as GetSqlByte,

GetSqlDecimal, and GetSqlMoney, and the OracleDataReader includes methods such as GetOracleLob,

GetOracleNumber, and GetOracleMonthSpan. Refer to the .NET Framework SDK documentation for more details.

When you have finished with a data reader, you should always call its Close method so that you can use the database connection again. IDataReader extends System.IDisposable, meaning that each data reader class implements the Dispose method. Dispose automatically calls Close, making the using statement a very clean and efficient way of using data readers.

The Code

The following example demonstrates the use of a data reader to process the contents of two result sets returned by executing a batch query containing two SELECT queries. The first result set is enumerated and displayed to the console. The second result set is inspected for metadata information, which is then displayed.

using System; using System.Data;

using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09

{

class Recipe09_07

{

public static void Main()

{

// Create a new SqlConnection object.

using (SqlConnection con = new SqlConnection())

{

//Configure the SqlConnection object's connection string. con.ConnectionString = @"Data Source = .\sqlexpress;" +

"Database = Northwind; Integrated Security=SSPI";

//Create and configure a new command.

using (SqlCommand com = con.CreateCommand())

{

com.CommandType = CommandType.Text;

com.CommandText = "SELECT BirthDate,FirstName,LastName FROM "+ "Employees ORDER BY BirthDate;SELECT * FROM Employees";

//Open the database connection and execute the example

//commands through the connection.

con.Open();

// Execute the command and obtain a SqlReader. using (SqlDataReader reader = com.ExecuteReader())

{

//Process the first set of results and display the

//content of the result set. Console.WriteLine("Employee Birthdays (By Age).");

while (reader.Read())
{
Console.WriteLine(" {0,18:D} - {1} {2}",
reader.GetDateTime(0),	//	Retrieve typed data
reader["FirstName"],	//	Use string	index

C H A P T E R 9 ■ D ATA B A S E A C C E S S

321

reader[2]);

// Use ordinal index

}

Console.WriteLine(Environment.NewLine);

//Process the second set of results and display details

//about the columns and data types in the result set. reader.NextResult();

Console.WriteLine("Employee Table Metadata.");

for (int field = 0; field < reader.FieldCount; field++)

{

Console.WriteLine(" Column Name:{0} Type:{1}", reader.GetName(field), reader.GetDataTypeName(field));

}

}

}

}

// Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine();

}

}

}

9-8. Obtain an XML Document from a SQL Server Query

Problem

You need to execute a query against a SQL Server 2000 (or later) database and retrieve the results as XML.

Solution

Specify the FOR XML clause in your SQL query to return the results as XML. Execute the command using the ExecuteXmlReader method of the System.Data.SqlClient.SqlCommand class, which returns a System.Xml.XmlReader object through which you can access the returned XML data.

How It Works

SQL Server 2000 (and later versions) provides direct support for XML. You simply need to add the clause FOR XML AUTO to the end of a SQL query to indicate that the results should be returned as XML. By default, the XML representation is not a full XML document. Instead, it simply returns the result of each record in a separate element, with all the fields as attributes. For example, this query:

SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO

returns XML with the following structure:

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste"/>

<Customers CustomerID="ANTON" CompanyName="Antonio Moreno Taquería"/>

<Customers CustomerID="GOURL" CompanyName="Gourmet Lanchonetes"/>

322 C H A P T E R 9 ■ D ATA B A S E A C C E S S

Alternatively, you can add the ELEMENTS keyword to the end of a query to structure the results using nested elements rather than attributes. For example, this query:

SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO, ELEMENTS

returns XML with the following structure:

<Customers>

<CustomerID>ALFKI</CustomerID> <CompanyName>Alfreds Futterkiste</CompanyName>

</Customers>

<Customers>

<CustomerID>ANTON</CustomerID> <CompanyName>Antonio Moreno Taquería</CompanyName>

</Customers>

<Customers>

<CustomerID>GOURL</CustomerID> <CompanyName>Gourmet Lanchonetes</CompanyName>

</Customers>

■Tip You can also fine-tune the format in more detail using the FOR XML EXPLICIT syntax. For example, this allows you to convert some fields to attributes and others to elements. Refer to SQL Server Books Online for more information.

When the ExecuteXmlReader command returns, the connection cannot be used for any other commands while the XmlReader is open. You should process the results as quickly as possible, and you must always close the XmlReader. Instead of working with the XmlReader and accessing the data sequentially, you can read the XML data into a System.Xml.XmlDocument. This way, all the data is retrieved into memory, and the database connection can be closed. You can then continue to interact with the XML document. (Chapter 6 contains numerous examples of how to use the XmlReader and XmlDocument classes.)

The Code

The following example demonstrates how to retrieve results as XML using the FOR XML clause and the ExecuteXmlReader method.

using System; using System.Xml; using System.Data;

using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09

{

class Recipe09_08

{

public static void ConnectedExample()

{

// Create a new SqlConnection object.

using (SqlConnection con = new SqlConnection())

{

// Configure the SqlConnection object's connection string. con.ConnectionString = @"Data Source = .\sqlexpress;" +

"Database = Northwind; Integrated Security=SSPI";

C H A P T E R 9 ■ D ATA B A S E A C C E S S

323

//Create and configure a new command that includes the

//FOR XML AUTO clause.

using (SqlCommand com = con.CreateCommand())

{

com.CommandType = CommandType.Text;

com.CommandText = "SELECT CustomerID, CompanyName" +

"FROM Customers FOR XML AUTO";

//Open the database connection. con.Open();

//Execute the command and retrieve an XmlReader to access

//the results.

using (XmlReader reader = com.ExecuteXmlReader())

{

while (reader.Read())

{

Console.Write("Element: " + reader.Name); if (reader.HasAttributes)

{

for (int i = 0; i < reader.AttributeCount; i++)

{

reader.MoveToAttribute(i); Console.Write(" {0}: {1}",

reader.Name, reader.Value);

}

// Move the XmlReader back to the element node. reader.MoveToElement(); Console.WriteLine(Environment.NewLine);

}

}

}

}

}

}

public static void DisconnectedExample()

{

XmlDocument doc = new XmlDocument();

// Create a new SqlConnection object.

using (SqlConnection con = new SqlConnection())

{

//Configure the SqlConnection object's connection string. con.ConnectionString = @"Data Source = .\sqlexpress;" +

"Database = Northwind; Integrated Security=SSPI";

//Create and configure a new command that includes the

//FOR XML AUTO clause.

SqlCommand com = con.CreateCommand(); com.CommandType = CommandType.Text; com.CommandText =

"SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO";

// Open the database connection. con.Open();

324C H A P T E R 9 ■ D ATA B A S E A C C E S S

//Load the XML data into the XmlDocument. Must first create a

//root element into which to place each result row element. XmlReader reader = com.ExecuteXmlReader(); doc.LoadXml("<results></results>");

//Create an XmlNode from the next XML element read from the

//reader.

XmlNode newNode = doc.ReadNode(reader);

while (newNode != null)

{

doc.DocumentElement.AppendChild(newNode); newNode = doc.ReadNode(reader);

}

}

// Process the disconnected XmlDocument. Console.WriteLine(doc.OuterXml);

}

public static void Main(string[] args)

{

ConnectedExample();

Console.WriteLine(Environment.NewLine);

DisconnectedExample();

Console.WriteLine(Environment.NewLine);

// Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine();

}

}

}

9-9. Perform Asynchronous Database Operations

Against SQL Server

Problem

You need to execute a query or command against a SQL Server database as a background task while your application continues with other processing.

Solution

Use the BeginExecuteNonQuery, BeginExecuteReader, or BeginExecuteXmlReader method of the

System.Data.SqlClient.SqlCommand class to start the database operation as a background task. These methods all return a System.IAsyncResult object that you can use to determine the operation’s status or use thread synchronization to wait for completion. Use the IAsyncResult object and the corresponding EndExecuteNonQuery, EndExecuteReader, or EndExecuteXmlReader method to obtain the result of the operation.

C H A P T E R 9 ■ D ATA B A S E A C C E S S

325

■Note Only the SqlCommand class supports the asynchronous operations described in this recipe. The equivalent command classes for the Oracle, SQL Server CE, ODBC, and OLE DB data providers do not provide this functionality.

How It Works

You will usually execute operations against databases synchronously, meaning that the calling code blocks until the operation is complete. Synchronous calls are most common because your code will usually require the result of the operation before it can continue. However, sometimes it’s useful to execute a database operation asynchronously, meaning that you start the method in a separate thread and then continue with other operations.

■Note To execute asynchronous operations over a System.Data.SqlClient.SqlConnection connection, you must specify the value Asynchronous Processing=true in its connection string.

As of .NET Framework 2.0, the SqlCommand class implements the asynchronous execution pattern similar to that discussed in recipe 4-2. As with the general asynchronous execution pattern described in recipe 4-2, the arguments of the asynchronous execution methods (BeginExecuteNonQuery, BeginExecuteReader, and BeginExecuteXmlReader) are the same as those of the synchronous variants (ExecuteNonQuery, ExecuteReader, and ExecuteXmlReader), but they take the following two additional arguments to support asynchronous completion:

•A System.AsyncCallback delegate instance that references a method that the runtime will call when the asynchronous operation completes. The method is executed in the context of a thread-pool thread. Passing null means that no method is called and you must use another completion mechanism (discussed later in this recipe) to determine when the asynchronous operation is complete.

•An object reference that the runtime associates with the asynchronous operation. The asynchronous operation does not use nor have access to this object, but it’s available to your code when the operation completes, allowing you to associate useful state information with an asynchronous operation. For example, this object allows you to map results against initiated operations in situations where you initiate many asynchronous operations that use a common callback method to perform completion.

The EndExecuteNonQuery, EndExecuteReader, and EndExecuteXmlReader methods allow you to retrieve the return value of an operation that was executed asynchronously, but you must first determine when it has finished. Here are the four techniques for determining if an asynchronous method has finished:

•Blocking: This method stops the execution of the current thread until the asynchronous operation completes execution. In effect, this is much the same as synchronous execution. However, you do have the flexibility to decide exactly when your code enters the blocked state, giving you the opportunity to carry out some additional processing before blocking.

•Polling: This method involves repeatedly testing the state of an asynchronous operation to determine if it’s complete. This is a very simple technique and is not particularly efficient from a processing perspective. You should avoid tight loops that consume processor time. It’s best to put the polling thread to sleep for a period using Thread.Sleep between completion tests. Because polling involves maintaining a loop, the actions of the waiting thread are limited, but you can easily update some kind of progress indicator.

326C H A P T E R 9 ■ D ATA B A S E A C C E S S

•Waiting: This method uses an object derived from the System.Threading.WaitHandle class to signal when the asynchronous method completes. Waiting is a more efficient version of polling and in addition allows you to wait for multiple asynchronous operations to complete. You can also specify time-out values to allow your waiting thread to fail if the asynchronous operation takes too long, or if you want to periodically update a status indicator.

•Callback: This a method that the runtime calls when an asynchronous operation completes. The calling code does not need to take any steps to determine when the asynchronous operation is complete and is free to continue with other processing. Callbacks provide the greatest flexibility, but also introduce the greatest complexity, especially if you have many concurrently active asynchronous operations that all use the same callback. In such cases, you must use appropriate state objects to match completed methods against those you initiated.

■Caution When using the asynchronous capabilities of the SQL Server data provider, you must ensure that your code does not inadvertently dispose of objects that are still being used by other threads. Pay particular attention to

SqlConnection and SqlCommand objects.

The Code

Recipe 4-2 provides examples of all of the completion techniques summarized in the preceding list. The following example demonstrates the use of an asynchronous call to execute a stored procedure on a SQL Server database. The code uses a callback to process the returned result set.

using System; using System.Data;

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

namespace Apress.VisualCSharpRecipes.Chapter09

{

class Recipe09_09

{

// A method to handle asynchronous completion using callbacks. public static void CallbackHandler(IAsyncResult result)

{

//Obtain a reference to the SqlCommand used to initiate the

//asynchronous operation.

using (SqlCommand cmd = result.AsyncState as SqlCommand)

{

// Obtain the result of the stored procedure.

using (SqlDataReader reader = cmd.EndExecuteReader(result))

{

// Display the results of the stored procedure to the console. lock (Console.Out)

{

Console.WriteLine(

"Price of the Ten Most Expensive Products:");

while (reader.Read())

{

// Display the product details. Console.WriteLine(" {0} = {1}",

reader["TenMostExpensiveProducts"],

reader["UnitPrice"]);

}

}

C H A P T E R 9 ■ D ATA B A S E A C C E S S

327

}

}

}

public static void Main()

{

// Create a new SqlConnection object.

using (SqlConnection con = new SqlConnection())

{

//Configure the SqlConnection object's connection string.

//You must specify Asynchronous Processing=true to support

//asynchronous operations over the connection. con.ConnectionString = @"Data Source = .\sqlexpress;" +

"Database = Northwind; Integrated Security=SSPI;" + "Asynchronous Processing=true";

//Create and configure a new command to run a stored procedure.

//Do not wrap it in a using statement because the asynchronous

//completion handler will dispose of the SqlCommand object. SqlCommand cmd = con.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Ten Most Expensive Products";

//Open the database connection and execute the command

//asynchronously. Pass the reference to the SqlCommand

//used to initiate the asynchronous operation.

con.Open(); cmd.BeginExecuteReader(CallbackHandler, cmd);

// Continue with other processing.

for (int count = 0; count < 10; count++)

{

lock (Console.Out)

{

Console.WriteLine("{0} : Continue processing...", DateTime.Now.ToString("HH:mm:ss.ffff"));

}

Thread.Sleep(500);

}

}

// Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine();

}

}

}

9-10. Write Database-Independent Code

Problem

You need to write code that can be configured to work against any relational database supported by an ADO.NET data provider.