Solution to Question 20-1. In a relational database, the data is organized into tables, and the queries are defined by the relationships among the tables.

Solution to Question 20-2. A primary key is a column that contains values that are unique to the table in which it resides, which allows you to uniquely identify each row.

Solution to Question 20-3. A foreign key is a column in a table that is also the primary key in a different table. This allows you to identify the relationship among the tables.

Solution to Question 20-4. To retrieve the contents of the Title column in the Books table the appropriate query would be:

Select Title from Books

Solution to Question 20-5. To retrieve the contents of the Author column where the value in the Publisher column is “OReilly”, the appropriate query would be:

Select Author from Books where Publisher = 'OReilly'

Solution to Question 20-6. You would want to use a join when you want to filter the information retrieved from one table based on the contents of a different table.

Solution to Question 20-7. The DataSet object represents a subset of retrieved data, and can be used to view or manipulate the data.

Solution to Question 20-8. One good way to view the rows in a DataTable object is to iterate over the Rows collection with a foreach loop.

Solution to Question 20-9. The DataAdapter class provides the bridge between your application and the database. The DataAdapter can take a connection string and a query string, and can then be used to provide that data to a DataSet object.

Solution to Question 20-10. Use the DataAdapter.Fill( ) method to transfer the data to a DataSet for manipulation.

Solution to Exercise 20-1. Let’s start with a simple exercise. The Northwind database contains a table named Orders. Write a program to retrieve the order date and shipped date of all the records in the Orders table.

This exercise is, as we said, rather simple. The code from Example 20-1 serves nicely as a template. However, Example 20-1 uses the Customers table, and we specified the Orders table. You can expand the Tables folder in the Database Explorer to see the various tables available in Northwind, and then expand the Orders table to see the various columns, which include OrderDate and ShippedDate. From there, it’s just a matter of rewriting the command string, like this:

string commandString = "Select OrderDate, ShippedDate from Orders";

Example A-59 has the full code for this exercise.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Exercise_20_1
{
    class Program
    {
        static void Main(string[] args)
        {
            // create the data connection
            string connectionString = "server=.\sqlexpress; " +
                      "Trusted_Connection=yes;database=Northwind";

            // create the string to hold the SQL command
            // to get records from the Customers table
            string commandString =
                 "Select OrderDate, ShippedDate from Orders";

            // create the data adapter with the
            // connection string and command
            SqlDataAdapter myDataAdapter =
                new SqlDataAdapter(commandString, connectionString);

            // Create and fill the DataSet object
            DataSet myDataSet = new DataSet( );
            myDataAdapter.Fill(myDataSet);

            // Retrieve the Orders table
            DataTable myDataTable = myDataSet.Tables[0];

            // iterate over the rows collection and output the fields
            foreach (DataRow dataRow in myDataTable.Rows)
            {
                Console.WriteLine("Order Date: {0}. Shipped Date: {1}",
                        dataRow["OrderDate"], dataRow["ShippedDate"]);
            }
        }
    }
}

Solution to Exercise 20-2. We’ll try something slightly more complicated now. Write a program to display the name and ID of products with fewer than 10 units in stock.

This is another rather simple exercise, again focusing on the command string. This time, you need to include a where clause in your string. From the Products table, you want to retrieve the ProductID and ProductName columns, if the UnitsInStock column is less than 10. Note that you don’t actually retrieve the UnitsInStock column; you only use it to determine which records to retrieve. The command string looks like this:

string commandString = "Select ProductID, ProductName
                        from Products where UnitsInStock < 10";

The full listing for this exercise is shown in Example A-60.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Exercise_20_2
{
    class Program
    {
        static void Main(string[] args)
        {
            // create the data connection
            string connectionString = "server=.\sqlexpress; " +
                      "Trusted_Connection=yes;database=Northwind";

            // create the string to hold the SQL command
            // to get records from the Customers table
            string commandString = "Select ProductID, " +
                        "ProductName from Products " +
                        "where UnitsInStock < 10";

            // create the data adapter with the
            // connection string and command
            SqlDataAdapter myDataAdapter =
               new SqlDataAdapter(commandString, connectionString);

            // Create and fill the DataSet object
            DataSet myDataSet = new DataSet( );
            myDataAdapter.Fill(myDataSet);

            // Retrieve the Orders table
            DataTable myDataTable = myDataSet.Tables[0];

            // iterate over the rows collection
            // and output the fields
            Console.WriteLine(
                      "Products with less than 10 units in stock:");
            foreach (DataRow dataRow in myDataTable.Rows)
            {
                Console.WriteLine("ProductID: {0} 	Product Name: {1}",
                      dataRow["ProductID"], dataRow["ProductName"]);
            }
        }
    }
}

The output looks like this, if you want to check your results:

Products with less than 10 units in stock:
ProductID: 5    Product Name: Chef Anton's Gumbo Mix
ProductID: 8    Product Name: Northwoods Cranberry Sauce
ProductID: 17   Product Name: Alice Mutton
ProductID: 21   Product Name: Sir Rodney's Scones
ProductID: 29   Product Name: Thüringer Rostbratwurst
ProductID: 31   Product Name: Gorgonzola Telino
ProductID: 32   Product Name: Mascarpone Fabioli
ProductID: 45   Product Name: Rogede sild
ProductID: 53   Product Name: Perth Pasties
ProductID: 66   Product Name: Louisiana Hot Spiced Okra
ProductID: 68   Product Name: Scottish Longbreads
ProductID: 74   Product Name: Longlife Tofu

(Somebody needs to order more of Chef Anton’s Gumbo Mix.) If you want to check your results against what’s in the database itself, right-click on the Products table in Database Explorer and select Show Table Data.

Solution to Exercise 20-3. Now for an exercise that involves multiple tables. Write a program to display the first and last names of the employees in region 1.

This exercise involves the join keyword, and requires you to look around in the tables a bit. If you open the Region table, you’ll find the RegionID column, which is the value you want to compare. But the Region table doesn’t mention EmployeeIDs. The Employees table has a column EmployeeID, which is a good start, and a column Region, but the values there don’t match up with any columns in the Region table. Instead, you need to look at the EmployeeTerritories table. You can join Employees to EmployeeTerritories on the EmployeeID column. The only other column in the EmployeeTerritories table is the TerritoryID column, which is the foreign key for the Territories table. In the Territories table, you’ll find the TerritoryID column, and—aha!—the RegionID column. So, you can craft a query like this:

string commandString = "select e.FirstName, e.LastName "+
                       "from Employees e "+
                       "join EmployeeTerritories et on e.EmployeeID =
                                 et.EmployeeID "+
                       "join Territories t on et.TerritoryID =
                                 t.TerritoryID "+
                       "join Region r on t.RegionID = r.RegionID "+
                       "where r.RegionID = 1";

The where clause checks for the employees in region 1, and the three join clauses chain back up to the Employees table, where you select the first and last name fields. The full code for this exercise is found in Example A-61.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Exercise_20_3
{
    class Program
    {
        static void Main(string[] args)
        {
            // create the data connection
            string connectionString = "server=.\sqlexpress; " +
                   "Trusted_Connection=yes;database=Northwind";

            // create the string to hold the SQL command
            // to get records from the Customers table
            string commandString = "select e.FirstName, e.LastName " +
                                   "from Employees e "+
                                   "join EmployeeTerritories et on " +
                                   "e.EmployeeID = et.EmployeeID " +
                                   "join Territories t on et.TerritoryID = " +
                                   "t.TerritoryID "+
                                   "join Region r on t.RegionID = " +
                                   "r.RegionID " +
                                   "where r.RegionID = 1";

            // create the data adapter with the
            // connection string and command
            SqlDataAdapter myDataAdapter =
               new SqlDataAdapter(commandString, connectionString);

            // Create and fill the DataSet object
            DataSet myDataSet = new DataSet( );
            myDataAdapter.Fill(myDataSet);

            // Retrieve the Orders table
            DataTable myDataTable = myDataSet.Tables[0];

            // iterate over the rows collection
            // and output the fields
            Console.WriteLine("Employees in Region 1:");
            foreach (DataRow dataRow in myDataTable.Rows)
            {
                Console.WriteLine("{0} {1}",
                      dataRow["FirstName"], dataRow["LastName"]);
            }
        }
    }
}