The Northwind database describes a fictional company buying and selling food products. The data for Northwind is divided into 13 tables, including Customers, Employees, Orders, Order Details, Products, and so forth.

Every table in a relational database is organized into rows, where each row represents a single record. The rows are organized into columns. All the rows in a table have the same column structure. For example, the Orders table has these columns: OrderID, CustomerID, EmployeeID, OrderDate, etc.

For any given order, you need to know the customer’s name, address, contact name, and so forth. You could store that information with each order, but that would be very inefficient. Instead, you use a second table called Customers, in which each row represents a single customer. In the Customers table is a column for the CustomerID. Each customer has a unique ID, and that field is marked as the primary key for that table. A primary key is the column or combination of columns that uniquely identifies a record in a given table.

The Orders table uses the CustomerID as a foreign key. A foreign key is a column (or combination of columns) that is a primary (or otherwise unique) key from a different table. The Orders table uses the CustomerID (the primary key used in the Customers table) to identify which customer has placed the order. To determine the address for the order, you can use the CustomerID to look up the customer record in the Customers table.

This use of foreign keys is particularly helpful in representing one-to-many or many- to-one relationships between tables. By separating information into tables that are linked by foreign keys, you avoid having to repeat information in records. A single customer, for example, can have multiple orders, but it is inefficient to place the same customer information (name, phone number, credit limit, and so on) in every order record. The process of removing redundant information from your records and shifting it to separate tables is called normalization .

Normalization not only makes your use of the database more efficient, but also it reduces the likelihood of data corruption. If you kept the customer’s name in both the Customers table and the Orders table, you would run the risk that a change in one table might not be reflected in the other. Thus, if you changed the customer’s address in the Customers table, that change might not be reflected in every row in the Orders table (and a lot of work would be necessary to make sure that it was reflected). By keeping only the CustomerID in Orders, you are free to change the address in Customers, and the change is automatically reflected for each order.

Just as C# programmers want the compiler to catch bugs at compile time rather than at runtime, database programmers want the database to help them avoid data corruption. The compiler helps avoid bugs in C# by enforcing the rules of the language (for example, you can’t use a variable you’ve not defined). SQL Server and other modern relational databases avoid bugs by enforcing constraints that you request. For example, the Customers database marks the CustomerID as a primary key. This creates a primary key constraint in the database, which ensures that each CustomerID is unique. If you were to enter a customer named Liberty Associates, Inc. with the CustomerID of LIBE, and then tried to add Liberty Mutual Funds with a CustomerID of LIBE, the database would reject the second record because of the primary key constraint.

Relational databases use Declarative Referential Integrity (DRI) to establish constraints on the relationships among the various tables. For example, you might declare a constraint on the Orders table that dictates that no order can have a CustomerID unless that CustomerID represents a valid record in Customers. This helps avoid two types of mistakes. First, you can’t enter a record with an invalid CustomerID. Second, you can’t delete a Customer record if that CustomerID is used in any order. The integrity of your data and its relationships is thus protected.

The most popular language for querying and manipulating databases is SQL, usually pronounced “sequel.” SQL is a declarative language, as opposed to a procedural language, and it can take a while to get used to working with a declarative language when you are used to languages such as C#.

The heart of SQL is the query . A query is a statement that returns a set of records from the database.

For example, you might like to see all the CompanyNames and CustomerIDs of every record in the Customers table in which the customer’s address is in London. To do so, write:

Select CustomerID, CompanyName from Customers where city = 'London'

This returns the following six records as output:

CustomerID CompanyName
---------- ---------------------------------------- 
AROUT      Around the Horn
BSBEV      B's Beverages
CONSH      Consolidated Holdings
EASTC      Eastern Connection
NORTS      North/South
SEVES      Seven Seas Imports

SQL is capable of much more powerful queries. For example, suppose the Northwind manager would like to know what products were purchased in July of 1996 by the customer “Vins et alcools Chevalier.” This turns out to be somewhat complicated. The Order Details table knows the ProductID for all the products in any given order. The Orders table knows which CustomerIDs are associated with an order. The Customers table knows the CustomerID for a customer, and the Products table knows the product name for the ProductID. How do you tie all this together? Here’s the query:

select  o.OrderID, productName
from [Order Details] od
join orders o on o.OrderID = od.OrderID
join products p on p.ProductID = od.ProductID
join customers c on o.CustomerID = c.CustomerID
where c.CompanyName = 'Vins et alcools Chevalier' 
and orderDate >= '7/1/1996' and orderDate <= '7/31/1996'

This asks the database to get the OrderID and the product name from the relevant tables. First, look at Order Details (which we’ve called od for short), then join that with the Orders table for every record in which the OrderID in the Order Details table is the same as the OrderID in the Orders table.

When you join two tables, you can say either “Get every record that exists in either table” (this is called an outer join), or as I’ve done here, “Get only those records that exist in both tables” (called an inner join). That is, an inner join states to get only the records in Orders that match the records in Order Details by having the same value in the OrderID field (on o.Orderid = od.Orderid).

The SQL statement goes on to ask the database to create an inner join with Products, getting every row in which the ProductID in the Products table is the same as the ProductID in the Order Details table.

Then create an inner join with customers for those rows where the CustomerID is the same in both the Orders table and the Customers table.

Finally, tell the database to constrain the results to only those rows in which the CompanyName is the one you want, and the dates are in July.

The collection of constraints finds only three records that match:

OrderID     ProductName                              
----------- ---------------------------------------- 
10248       Queso Cabrales
10248       Singaporean Hokkien Fried Mee
10248       Mozzarella di Giovanni

This output shows that there was only one order (10248) in which the customer had the right ID and in which the date of the order was July 1996. That order produced three records in the Order Details table, and using the product IDs in these three records, you got the product names from the Products table.

You can use SQL not only for searching for and retrieving data, but also for creating, updating, and deleting tables, and generally managing and manipulating both the content and the structure of the database.

For a full explanation of SQL and tips on how to put it to best use, I recommend Transact SQL Programming (O’Reilly). If you are using a SQL database that is not SQL Server, you’ll want to consult O’Reilly’s SQL Pocket Guide because each provider may use a slightly different “dialect” of SQL.

The DataTable can be created programmatically or as a result of a query against the database. The DataTable has a number of public properties, including the Columns collection, which returns the DataColumnCollection object, which in turn consists of DataColumn objects. Each DataColumn object represents a column in a table.

In addition to the Tables collection, the DataSet has a Relations property, which returns a DataRelationCollection consisting of DataRelation objects. Each DataRelation represents a relationship between two tables through DataColumn objects. For example, in the Northwind database the Customers table is in a relationship with the Orders table through the CustomerID column.

The nature of the relationship is one-to-many, or parent-to-child. For any given order, there will be exactly one customer, but any given customer might be represented in any number of orders.

DataTable ’s Rows collection returns a set of rows for that table. Use this collection to examine the results of queries against the database, iterating through the rows to examine each record in turn. Programmers experienced with ADO are often confused by the absence of the RecordSet with its moveNext and movePrevious commands. With ADO.NET, you don’t iterate through the DataSet; instead, access the table you need, and then you can iterate through the Rows collection, typically with a foreach loop. You’ll see this in the first example in this chapter.

The DataSet is an abstraction of a relational database. ADO.NET uses a DataAdapter as a bridge between the DataSet and the data source, which is the underlying database. DataAdapter provides the Fill() method to retrieve data from the database and populate the DataSet.

The DBConnection object represents a connection to a data source. This connection can be shared among different command objects. The DBCommand object allows you to send a command (typically a SQL statement or a stored procedure) to the database. Often these objects are implicitly created when you create a DataAdapter, but you can explicitly access these objects, as you’ll see in a subsequent example.

Instead of tying the DataSet object too closely to your database architecture, ADO. NET uses a DataAdapter object to mediate between the DataSet object and the database. This decouples the DataSet from the database and allows a single DataSet to represent more than one database or other data source.

An alternative to creating a DataSet (and DataAdapter) is to create a DataReader . The DataReader provides connected, forward-only, read-only access to a collection of tables, by executing either a SQL statement or stored procedures. DataReaders are lightweight objects that are ideally suited for filling controls with data and then breaking the connection to the backend database.

If you absolutely, positively insist on creating these connection objects by hand, you can certainly do so, and you can manually wire them up to your data grid as well.

Create a new project called ProgrammaticDataDisplay. Rename the .cs file and the form and set the title appropriately. Drag a DataGridView in place, but ignore the action menu.

Right-click the form and choose View Code. Add these lines to the constructor:

string connectionString = "server=localhost;" +
"Trusted_Connection=yes; database=northwind";
string commandString =
    "Select CompanyName, ContactName, ContactTitle, "
    + "Phone, Fax from Customers";

// create a data set and fill itSqlDataAdapter DataAdapter =
    new SqlDataAdapter( commandString, connectionString );
DataSet DataSet = new DataSet( );
DataAdapter.Fill( DataSet, "Customers" );

// bind the DataSet to the grid
dataGridView1.DataSource =
    DataSet.Tables["Customers"].DefaultView;

In this case, you are setting the connection string and command string by hand, and then creating a SqlDataAdapter and DataSet to retrieve the data. You then bind the dataGridView’s DataSource property to the Default view of the Customers table you retrieved.

You are free to set the DataGridView’s properties programmatically or declaratively, and you can mix and match approaches. But clearly, Microsoft has gone a long way to allowing you to just drag and drop database connection controls onto your form to make interacting with ADO.NET much simpler.

It is possible to control precisely every aspect of creating the DataSet, instead of using the default settings. You may do so declaratively or programmatically.

In the previous example, when you created the DataSet, you passed in a commandString and a connectionString:

SqlDataAdapter DataAdapter = 
   new SqlDataAdapter(commandString, connectionString);

These were assigned internally to a SqlCommand object and a SqlConnection object, respectively. You can instead explicitly create these objects.

In this next example, you’ll give the class four new members:

private System.Data.SqlClient.SqlConnection myConnection;
private System.Data.DataSet myDataSet;
private System.Data.SqlClient.SqlCommand myCommand;
private System.Data.SqlClient.SqlDataAdapter DataAdapter;

The connection is created by instantiating a SqlConnection object with the connection string:

string connectionString = "server=(local)\NetSDK;" + 
    "Trusted_Connection=yes; database=northwind";         
myConnection = new System.Data.SqlClient.SqlConnection(connectionString);

and then it is opened explicitly:

myConnection.Open();

By hanging on to this connection, you can reuse it (as you’ll see in a subsequent example) and you can also use its transaction support if needed.

SqlConnection myConnection = myAdapter.Connection

Next, explicitly create the DataSet object and set one of its properties:

myDataSet = new System.Data.DataSet();
myDataSet.CaseSensitive=true;

Setting CaseSensitive to true indicates that string comparisons within DataTable objects are case-sensitive.

Next, explicitly create the SqlCommand object and give that new command object the connection object and the text for the command:

myCommand = new System.Data.SqlClient.SqlCommand()
myCommand.Connection=myConnection;
myCommand.CommandText = "Select * from Customers";

Finally, create the SqlDataAdapter object and assign to it the SqlCommand object you just established. Then tell the DataAdapter how to map the table columns, using the table you’re searching, and instruct the SqlDataAdapter to fill the DataSet object:

DataAdapter = new System.Data.SqlClient.SqlDataAdapter();
DataAdapter.SelectCommand= myCommand;
DataAdapter.TableMappings.Add("Table","Customers");
DataAdapter.Fill(myDataSet);

With that done, you’re ready to fill the DataGridView (note that this time I’ve used the default name for the DataGrid):

dataGrid1.DataSource=
    myDataSet.Tables["Customers"].DefaultView;

Example 14-3 provides the complete source code.

#region Using directives

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

#endregion

namespace CustomizedDataSet
{
   partial class CustomizedDataSet : Form
   {
      private System.Data.SqlClient.SqlConnection myConnection;
      private System.Data.DataSet myDataSet;
      private System.Data.SqlClient.SqlCommand myCommand;
      private System.Data.SqlClient.SqlDataAdapter DataAdapter;

      public CustomizedDataSet( )
      {
         InitializeComponent( );
         string connectionString = "server=localhost;" +
            "Trusted_Connection=yes; database=northwind";
         myConnection = new
            System.Data.SqlClient.SqlConnection( connectionString );
         myConnection.Open( );

         // create the DataSet and set a property
         myDataSet = new System.Data.DataSet( );
         myDataSet.CaseSensitive = true;

         // create the SqlCommand  object and assign the
         // connection and the select statement
         myCommand = new System.Data.SqlClient.SqlCommand( );
         myCommand.Connection = myConnection;
         myCommand.CommandText = "Select * from Customers";

         // create the DataAdapter object and pass in the
         // SQL Command object and establish the table mappings
         DataAdapter = new System.Data.SqlClient.SqlDataAdapter( );
         DataAdapter.SelectCommand = myCommand;
         DataAdapter.TableMappings.Add( "Table", "Customers" );

         // Tell the DataAdapter object to fill the DataSet
         DataAdapter.Fill( myDataSet );

         // display it in the grid
         dataGridView1.DataSource =
            myDataSet.Tables["Customers"].DefaultView;
      }
   }
}