/* Mastering Visual C# .NET by Jason Price, Mike Gunderloy Publisher: Sybex; ISBN: 0782129110 */ /* Example23_6.cs illustrates how to specify and use a relationship between two DataTable objects */ using System; using System.Data; using System.Data.SqlClient; public class Example23_6 { public static void Main() { // formulate a string containing the details of the // database connection string connectionString = "server=localhost;database=Northwind;uid=sa;pwd=sa"; // create a SqlConnection object to connect to the // database, passing the connection string to the constructor SqlConnection mySqlConnection = new SqlConnection(connectionString); // formulate a string containing a SELECT statement to // retrieve a row from the Customers table string selectString = "SELECT CustomerID, CompanyName " + "FROM Customers " + "WHERE CustomerID = 'ALFKI'"; // create a SqlCommand object to hold the SELECT statement SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); // set the CommandText property of the SqlCommand object to // the SELECT string mySqlCommand.CommandText = selectString; // create a SqlDataAdapter object SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); // set the SelectCommand property of the SqlAdapter object // to the SqlCommand object mySqlDataAdapter.SelectCommand = mySqlCommand; // create a DataSet object to store the results of // the SELECT statement DataSet myDataSet = new DataSet(); // open the database connection using the // Open() method of the SqlConnection object mySqlConnection.Open(); // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the database, storing the rows // in a DataTable named "Customers" mySqlDataAdapter.Fill(myDataSet, "Customers"); // formulate a string containing a SELECT statement to // retrieve the rows from the Orders table where the CustomerID // column is equal to ALFKI selectString = "SELECT OrderID, CustomerID " + "FROM Orders " + "WHERE CustomerID = 'ALFKI'"; // set the CommandText property of the SqlCommand object to // the SELECT string mySqlCommand.CommandText = selectString; // use the Fill() method of the SqlDataAdapter object to // retrieve the rows from the database, storing the rows // in a DataTable named "Orders" mySqlDataAdapter.Fill(myDataSet, "Orders"); // use the Add() method through the Relations property // to define a relationship between the Customers and // Orders DataTable objects myDataSet.Relations.Add( "Orders", myDataSet.Tables["Customers"].Columns["CustomerID"], myDataSet.Tables["Orders"].Columns["CustomerID"] ); // display the rows in the Customers and Orders DataTable objects, // using the GetChildRows() method to get the orders for the // customer DataTable customers = myDataSet.Tables["Customers"]; foreach (DataRow customer in customers.Rows) { Console.WriteLine("CustomerID = " + customer["CustomerID"]); Console.WriteLine("CompanyName = " + customer["CompanyName"]); DataRow[] orders = customer.GetChildRows("Orders"); Console.WriteLine("This customer placed the following orders:"); foreach (DataRow order in orders) { Console.WriteLine(" OrderID = " + order["OrderID"]); } } // close the database connection using the Close() method // of the SqlConnection object mySqlConnection.Close(); } }