/*
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();
}
}