Apress-Visual CSharp 2010 Recipes A Problem Solution Approach_4

Chia sẻ: Thao Thao | Ngày: | Loại File: PDF | Số trang:95

Thêm vào BST

Báo xấu

49
lượt xem 6
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Ví dụ sau đây cho thấy làm thế nào để lấy kết quả như XML bằng cách sử dụng mệnh đề FOR XML và phương pháp ExecuteXmlReader: sử dụng sử dụng sử dụng sử dụng hệ thống; System.Xml; System.Data; System.Data.SqlClient;

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Apress-Visual CSharp 2010 Recipes A Problem Solution Approach_4

CHAPTER 9 ■ DATABASE ACCESS 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"; // 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); } 450
CHAPTER 9 ■ DATABASE ACCESS // 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(); // 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(""); // 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); } 451
CHAPTER 9 ■ DATABASE ACCESS 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. ■ 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 452
CHAPTER 9 ■ DATABASE ACCESS 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. 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, in this case, you 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 whether 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. 453
CHAPTER 9 ■ DATABASE ACCESS • 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 timeout 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)) { 454
CHAPTER 9 ■ DATABASE ACCESS // 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"]); } } } } } 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); 455
CHAPTER 9 ■ DATABASE ACCESS // 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. Solution Program to the ADO.NET data provider interfaces in the System.Data namespace, as opposed to the concrete implementations, and do not rely on features and data types that are unique to specific database implementations. Use factory classes and methods to instantiate the data provider objects you need to use. How It Works Using a specific data provider implementation (the SQL Server data provider, for example) simplifies your code, and may be appropriate if you need to support only a single type of database or require access to specific features provided by that data provider, such as the asynchronous execution for SQL Server detailed in recipe 9-9. However, if you program your application against a specific data provider implementation, you will need to rewrite and test those sections of your code if you want to use a different data provider at some point in the future. Table 9-6 contains a summary of the main interfaces you must program against when writing generic ADO.NET code that will work with any relational database’s data provider. The table also explains how to create objects of the appropriate type that implement the interface. Many of the recipes 456
CHAPTER 9 ■ DATABASE ACCESS in this chapter demonstrate the use of ADO.NET data provider interfaces over specific implementation, as highlighted in the table. Table 9-6. Data Provider Interfaces Interface Description Demonstrated In IDbConnection Represents a connection to a relational database. You must Recipe 9-1 program the logic to create a connection object of the appropriate type based on your application’s configuration information, or use the DbProviderFactory.CreateConnection factory method (discussed in this recipe). IDbCommand Represents a SQL command that is issued to a relational Recipe 9-5 database. You can create IDbCommand objects of the appropriate type using the IDbConnection.CreateCommand or DbProviderFactory.CreateCommand factory method. IDataParameter Represents a parameter to an IDbCommand object. You can create Recipe 9-6 IDataParameter objects of the correct type using the IDbCommand.CreateParameter, IDbCommand.Parameters.Add, or DbProviderFactory.CreateParameter factory method. IDataReader Represents the result set of a database query and provides access Recipes 9-5 and to the contained rows and columns. An object of the correct type 9-6 will be returned when you call the IDbCommand.ExecuteReader method. IDbDataAdapter Represents the set of commands used to fill a System.Data.DataSet from a relational database and to update the database based on changes to the DataSet. You must program the logic to create a data adapter object of the appropriate type based on your application’s configuration information, or use the DbProviderFactory.CreateAdapter factory method (discussed in this recipe). The System.Data.Common.DbProviderFactory class provides a set of factory methods for creating all types of data provider objects, making it very useful for implementing generic database code. Most important, DbProviderFactory provides a mechanism for obtaining an initial IDbConnection instance, which is the critical starting point for writing generic ADO.NET code. Each of the standard data provider implementations (except the SQL Server CE data provider) includes a unique factory class derived from DbProviderFactory. Here is the list of DbProviderFactory subclasses: 457
CHAPTER 9 ■ DATABASE ACCESS • System.Data.Odbc.OdbcFactory • System.Data.OleDb.OleDbFactory • System.Data.OracleClient.OracleClientFactory • System.Data.SqlClient.SqlClientFactory You can obtain an instance of the appropriate DbProviderFactory subclass using the DbProviderFactories class, which is effectively a factory of factories. Each data provider factory is described by configuration information in the machine.config file, similar to that shown here for the SQL Server data adapter. This can be changed or overridden by application-specific configuration information if required.
CHAPTER 9 ■ DATABASE ACCESS The Code The following example demonstrates the enumeration of all data providers configured for the local machine and application. It then uses the DbProviderFactories class to instantiate a DbProviderFactory object (actually a SqlClientFactory) from which it creates the appropriate IDbConnection. It then uses the factory methods of the data provider interfaces to create other required objects, resulting in code that is completely generic. using System; using System.Data; using System.Data.Common; namespace Apress.VisualCSharpRecipes.Chapter09 { class Recipe09_10 { public static void Main(string[] args) { // Obtain the list of ADO.NET data providers registered in the // machine and application configuration files. using (DataTable providers = DbProviderFactories.GetFactoryClasses()) { // Enumerate the set of data providers and display details. Console.WriteLine("Available ADO.NET Data Providers:"); foreach (DataRow prov in providers.Rows) { Console.WriteLine(" Name:{0}", prov["Name"]); Console.WriteLine(" Description:{0}", prov["Description"]); Console.WriteLine(" Invariant Name:{0}", prov["InvariantName"]); } } // Obtain the DbProviderFactory for SQL Server. The provider to use // could be selected by the user or read from a configuration file. // In this case, we simply pass the invariant name. DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); // Use the DbProviderFactory to create the initial IDbConnection, and // then the data provider interface factory methods for other objects. using (IDbConnection con = factory.CreateConnection()) { // Normally, read the connection string from secure storage. // See recipe 9-3. In this case, use a default value. con.ConnectionString = @"Data Source = .\sqlexpress;" + "Database = Northwind; Integrated Security=SSPI"; 459
CHAPTER 9 ■ DATABASE ACCESS // Create and configure a new command. using (IDbCommand com = con.CreateCommand()) { com.CommandType = CommandType.StoredProcedure; com.CommandText = "Ten Most Expensive Products"; // Open the connection. con.Open(); // Execute the command and process the results. using (IDataReader reader = com.ExecuteReader()) { Console.WriteLine(Environment.NewLine); Console.WriteLine("Price of the Ten Most" + " Expensive Products."); while (reader.Read()) { // Display the product details. Console.WriteLine(" {0} = {1}", reader["TenMostExpensiveProducts"], reader["UnitPrice"]); } } } } // Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine(); } } } 9-11. Discover All Instances of SQL Server on Your Network Problem You need to obtain a list of all instances of SQL Server that are accessible on the network. Solution Use the GetDataSources method of the System.Data.Sql.SqlDataSourceEnumerator class. 460
CHAPTER 9 ■ DATABASE ACCESS How It Works The SqlDataSourceEnumerator class makes it easy to enumerate the SQL Server instances accessible on the network. You simply obtain the singleton SqlDataSourceEnumerator instance via the static property SqlDataSourceEnumerator.Instance and call its GetDataSources method. The GetDataSources method returns a System.Data.DataTable that contains a set of System.Data.DataRow objects. Each DataRow represents a single SQL Server instance and contains the following columns: • ServerName, which contains the name of the server where the SQL Server instance is hosted • InstanceName, which contains the name of the SQL Server instance or the empty string if the SQL Server is the default instance • IsClustered, which indicates whether the SQL Server instance is part of a cluster • Version, which contains the version of the SQL Server instance The Code The following example demonstrates the use of the SqlDataSourceEnumerator class to discover and display details of all SQL Server instances accessible (and visible) on the network. The IsClustered and Version columns may be blank for some versions of SQL Server. using System; using System.Data; using System.Data.Sql; namespace Apress.VisualCSharpRecipes.Chapter09 { class Recipe09_11 { public static void Main(string[] args) { // Obtain the DataTable of SQL Server instances. using (DataTable SqlSources = SqlDataSourceEnumerator.Instance.GetDataSources()) { // Enumerate the set of SQL Servers and display details. Console.WriteLine("Discover SQL Server Instances:"); foreach (DataRow source in SqlSources.Rows) { Console.WriteLine(" Server Name:{0}", source["ServerName"]); Console.WriteLine(" Instance Name:{0}", source["InstanceName"]); Console.WriteLine(" Is Clustered:{0}", source["IsClustered"]); Console.WriteLine(" Version:{0}", source["Version"]); } } 461
CHAPTER 9 ■ DATABASE ACCESS // Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine(); } } } 9-12. Create an In-Memory Cache Problem You need to create an in-memory cache of part of the database. Solution Use System.Data.DataSet to represent the data and System.Data.SqlClient.SqlDataAdapter to read and sync data with the database. How It Works The System.Data.DataSet class contains one or more instances of System.Data.DataTable, each of which contains instances of System.Data.DataRow, representing data rows from the database. The SqlDataAdapter class acts as the bridge between the database and the DataSet, allowing you to populate the DataSet with data and write back any changes to the database when you are done. The sequence for using a DataSet is as follows: 1. Create a SqlConnection to your database as normal (see recipe 9-1). 2. Create a new instance of DataSet using the default constructor. 3. Create a new instance of SqlDataAdapter, passing in a query string for the data you require and the SqlConnection you created in step 1 as constructor arguments. 4. Create an instance of SqlCommandBuilder, passing in the SqlDataAdapter you created. 5. Call the SqlDataAdapter.Fill instance method, passing the DataSet you created in step 2 as a method argument. 6. Use the DataSet to access the DataTables contained within—read and modify data as required. 7. Call the SqlDataAdapter.Update method to write any changes back to the database. To create a new row in a table, call the DataTable.NewRow instance method to obtain an instance of DataRow that has the same schema as the DataTable. The new row is not automatically added to the table 462
CHAPTER 9 ■ DATABASE ACCESS when you call NewRow—call DataTable.Rows.Add once you have set the values for the row. Changes that you make to the data in the DataSet are not written back to the database until you call the SqpDataAdapter.Update method. The Code The following example creates a DataSet and fills it with the contents of the Region table of the Northwind sample database. The DataSet contains one DataTable, whose schema and contents are printed out. A new record is added and an existing one modified before the changes are written back to the database. using System; using System.Data; using System.Data.SqlClient; namespace Apress.VisualCSharpRecipes.Chapter09 { class Recipe09_12 { static void Main(string[] args) { // 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. con.Open(); // Create the query string. string query = "SELECT * from Region"; // Create the data set. DataSet dataset = new DataSet(); // Create the SQL data adapter. SqlDataAdapter adapter = new SqlDataAdapter(query, con); // Create the command builder so we can do modifications. SqlCommandBuilder commbuilder = new SqlCommandBuilder(adapter); // Populate the data set from the database. adapter.Fill(dataset); 463
CHAPTER 9 ■ DATABASE ACCESS // Print details of the schema. Console.WriteLine("\nSchema for table"); DataTable table = dataset.Tables[0]; foreach (DataColumn col in table.Columns) { Console.WriteLine("Column: {0} Type: {1}", col.ColumnName, col.DataType); } // Enumerate the data we have received. Console.WriteLine("\nData in table"); foreach (DataRow row in table.Rows) { Console.WriteLine("Data {0} {1}", row[0], row[1]); } // Create a new row. DataRow newrow = table.NewRow(); newrow["RegionID"] = 5; newrow["RegionDescription"] = "Central"; table.Rows.Add(newrow); // Modify an existing row. table.Rows[0]["RegionDescription"] = "North Eastern"; // Enumerate the cached data again. // Enumerate the data we have received. Console.WriteLine("\nData in (modified) table"); foreach (DataRow row in table.Rows) { Console.WriteLine("Data {0} {1}", row[0], row[1]); } // Write the data back to the database. adapter.Update(dataset); } // Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine(); } } } 464
CHAPTER 9 ■ DATABASE ACCESS Running the example produces the following results: Schema for table Column: RegionID Type: System.Int32 Column: RegionDescription Type: System.String Data in table Data 1 Eastern Data 2 Western Data 3 Northern Data 4 Southern Data in (modified) table Data 1 North Eastern Data 2 Western Data 3 Northern Data 4 Southern Data 5 Central Main method complete. Press Enter. 465
CHAPTER 9 ■ DATABASE ACCESS 9-13. Create a DataSet Programmatically Problem You need to work with in-memory data without a database. Solution Create an instance of System.Sql.DataSet and manually populate it with instances of System.Data.Datatable. Create a schema for each table and create rows to represent data elements. How It Works In the previous recipe, we demonstrated how to use the DataSet and DataTable classes as part of a memory cache, in order to achieve disconnected data manipulation. However, you can create instances of these classes to represent data programmatically by calling constructors for the classes directly. The example code for this recipe illustrates how to do this in order to create the same kind of DataSet and DataTable that we used previously. The Code The following code creates a DataSet that contains a single DataTable and populates it with instances of DataRow. Once populated, the same queries, modifications, and additions are performed upon it as in the previous recipe. using System; sing System.Data; namespace Apress.VisualCSharpRecipes.Chapter09 { class Recipe09_13 { static void Main(string[] args) { // Create the data set. DataSet dataset = new DataSet(); // Create the table and add it to the data set. DataTable table = new DataTable("Regions"); dataset.Tables.Add(table); // Create the colums for the table. table.Columns.Add("RegionID", typeof(int)); table.Columns.Add("RegionDescription", typeof(string)); 466
CHAPTER 9 ■ DATABASE ACCESS // Populate the table. string[] regions = { "Eastern", "Western", "Northern", "Southern" }; for (int i = 0; i < regions.Length; i++) { DataRow row = table.NewRow(); row["RegionID"] = i + 1; row["RegionDescription"] = regions[i]; table.Rows.Add(row); } // Print details of the schema. Console.WriteLine("\nSchema for table"); foreach (DataColumn col in table.Columns) { Console.WriteLine("Column: {0} Type: {1}", col.ColumnName, col.DataType); } // Enumerate the data we have received. Console.WriteLine("\nData in table"); foreach (DataRow row in table.Rows) { Console.WriteLine("Data {0} {1}", row[0], row[1]); } // Create a new row. DataRow newrow = table.NewRow(); newrow["RegionID"] = 5; newrow["RegionDescription"] = "Central"; table.Rows.Add(newrow); // Modify an existing row. table.Rows[0]["RegionDescription"] = "North Eastern"; // Enumerate the cached data again. // Enumerate the data we have received. Console.WriteLine("\nData in (modified) table"); foreach (DataRow row in table.Rows) { Console.WriteLine("Data {0} {1}", row[0], row[1]); } // Wait to continue. Console.WriteLine(Environment.NewLine); Console.WriteLine("Main method complete. Press Enter."); Console.ReadLine(); } } } The program produces the following output: 467
CHAPTER 9 ■ DATABASE ACCESS Schema for table Column: RegionID Type: System.Int32 Column: RegionDescription Type: System.String Data in table Data 1 Eastern Data 2 Western Data 3 Northern Data 4 Southern Data in (modified) table Data 1 North Eastern Data 2 Western Data 3 Northern Data 4 Southern Data 5 Central Main method complete. Press Enter. 9-14. Perform a LINQ Query Problem You need to use LINQ to query a database. 468
CHAPTER 9 ■ DATABASE ACCESS Solution Create or obtain an instance of DataTable (see recipes 9-12 and 9-13) and call the AsEnumerable instance method to obtain an IEnumerable, which can be used as a data source for LINQ queries. How It Works LINQ performs queries on the IEnumerable type, which you can obtain from instances of DataTable using the AsEnumerable instance method. When using SQLDataAdapter to populate instances of DataTable with data (see recipe 9-12), remember that you are working with cached data that will not reflect changes made to the database. See Chapter 16 for recipes that demonstrate LINQ features. The Code The following example creates a DataSet that contains a DataTable with all of the rows of the Northwind Region table, and then performs a LINQ query using the DataTable as the data source: using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace Apress.VisualCSharpRecipes.Chapter09 { class Recipe09_14 { static void Main(string[] args) { // 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. con.Open(); // Create the query string. string query = "SELECT * from Region"; // Create the data set. DataSet dataset = new DataSet(); 469