Create a new project called ReportOrders by using the Console Application template. Save it in the \Microsoft Press\Visual CSharp Step By Step\Chapter 23 folder in your My Documents folder. Click OK.

In the Solution Explorer, change the name of Program.cs to Report.cs.

Notice that the name of the Program class in the Code and Text Editor window changes to Report automatically.

In the Code And Text Editor window add the following statement under the using System.Text; statement:

using System.Data.SqlClient;

The System.Data.SqlClient namespace contains the specialized ADO.NET classes used to gain access to SQL Server.

Locate the Main method of the Report class. Add the following statement that declares a SqlConnection object: SqlConnection dataConnection = new SqlConnection(); SqlConnection is a subclass of the ADO.NET Connection class. It is designed to handle connections to SQL Server databases only.

After the variable declaration, add a try/catch block to the Main method. All the code that you will write for gaining access to the database goes inside the try part of this block—remember that you must be prepared to handle exceptions whenever you use a database.

try
{
    // You will add your code here in a moment
}
catch(Exception e)
{
    Console.WriteLine("Error accessing the database: " + e.Message);
}

Replace the comment in the try block with the following code that connects to the database:

dataConnection.ConnectionString = "Integrated Security=true;" +
                                  "Initial Catalog=Northwind;" +
                                  "Data Source= YourServer\\SQLExpress";
dataConnection.Open();

The contents of the ConnectionString property of the SqlConnection object are the same as those generated by the Data Source Configuration Wizard that you saw in step 7 of the earlier exercise, “Create a data source.” This string specifies that the connection will use Windows Authentication to connect to the Northwind database on your local instance of SQL Server 2005 Express Edition. This is the preferred method of access because you do not have to prompt the user for any form of user name or password, and you are not tempted to hard-code user names and passwords into your application. Notice that a semicolon separates all the elements in the ConnectionString.

There are also many other parameters that you can encode in the ConnectionString. See the MSDN Library for Visual Studio 2005 for details.

Add the following statements after the dataConnection.Open(); statement:

Console.Write("Please enter a customer ID (5 characters): ");
string customerId = Console.ReadLine();

These statements prompt the user for a CustomerID and get the user's response in the string variable customerId.

Type the following statements after the code you just entered:

SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;
dataCommand.CommandText = 
    "SELECT OrderID, OrderDate, " +
    "ShippedDate, ShipName, ShipAddress, ShipCity, " +
    "ShipCountry ";
dataCommand.CommandText += 
    "FROM Orders WHERE CustomerID='" +
    customerId + "'";
Console.WriteLine("About to execute: {0}\n\n", dataCommand.CommandText);

The first statement creates an SqlCommand object. Like SqlConnection, this is a specialized version of an ADO.NET class, Command, that has been designed for gaining access to SQL Server. A Command object is used to execute a command against a data source. In the case of a relational database, the text of the command is an SQL statement.

The second line of code sets the Connection property of the SqlCommand object to the database connection you opened in the previous exercise. The next two statements populate the CommandText property with an SQL SELECT statement that retrieves information from the Orders table for all orders that have a CustomerID that matches the value in the customerId variable (you could do this in a single statement, but it has been split over two lines to make it easier to read). The Console.WriteLine statement just repeats the command about to be executed to the screen.

Add the following statement after the code you just entered:

SqlDataReader dataReader = dataCommand.ExecuteReader();

The fastest way to get data from an SQL Server database is to use the SqlDataReader class. This class extracts rows from the database as fast as your network allows and deposits them in your application.

Add the while loop shown below after the statement that creates the SqlDataReader object:

while (dataReader.Read())
{
    // Code to display the current row
}

The Read method of the SqlDataReader class fetches the next row from the database. It returns true if another row was retrieved successfully; otherwise, it returns false, usually because there are no more rows. The while loop you have just entered keeps reading rows from the dataReader variable and finishes when there are no more rows.

Add the following statements to the body of the while loop you created in the previous step:

int orderId = dataReader.GetInt32(0);
DateTime orderDate = dataReader.GetDateTime(1);
DateTime shipDate = dataReader.GetDateTime(2);
string shipName = dataReader.GetString(3);
string shipAddress = dataReader.GetString(4);
string shipCity = dataReader.GetString(5);
string shipCountry = dataReader.GetString(6);
Console.WriteLine(
    "Order {0}\nPlaced {1}\nShipped {2}\n" +
    "To Address {3}\n{4}\n{5}\n{6}\n\n", orderId, orderDate,
    shipDate, shipName, shipAddress, shipCity, shipCountry);

This process is how you read the data from an SqlDataReader object. An SqlDataReader object contains the most recent row retrieved from the database. You can use the GetXXX methods to extract the information from each column in the row—there is a GetXXX method for each common type of data. For example, to read an int value, you use the GetInt32 method; to read a string, you use the GetString method; and you can probably guess how to read a DateTime value. The GetXXX methods take a parameter indicating which column to read: 0 is the first column, 1 is the second column, and so on. The previous code reads the various columns from the current Orders row, stores the values in a set of variables, and then prints out the values of these variables.

In the Code pane, add the following statements after the while loop:

dataReader.Close();

This statement closes the SqlDataReader object. You should always close an SqlData-Reader when you have finished with it because you are not able to use the current SqlConnection object to run any more commands until you do. It is also considered good practice to do it even if all you are going to do next is close the SqlConnection.

After the catch block, add the following finally block:

finally
{
    dataConnection.Close();
}

Database connections are scarce resources. You need to ensure that they are closed when you have finished with them. Putting this statement in a finally block guarantees that the SqlConnection will be closed, even if an exception occurs; remember that the code in the finally block will be executed when the catch handler has finished.

On the Debug menu, click Starting Without Debugging to build and run the application.

By default, the Start Without Debugging command runs the application and then prompts you before it closes the Console window so that you get a chance to read the output. If you click the Start Debugging command instead, the Console window closes as soon as the application finishes without giving you the same opportunity.

At the customer ID prompt, type VINET and press Enter. The SQL SELECT statement appears, followed by the orders for this customer. You can scroll back through the Console window to view all the data. Press the Enter key to close the Console window when you have finished.

Run the application again, and then type BONAP when prompted for the customer ID.

Some rows appear, but then an error message is displayed: “Error accessing the database. Data is Null.” The problem is that relational databases allow some columns to contain null values. A null value is a bit like a null variable in C#: it doesn't have a value and, if you try to use it, you get an error. In the Orders table, the ShippedDate column can contain null if the order has not yet been shipped.

Press Enter to close the Console window.

In the Code And Text Editor window, locate the while loop that iterates through the rows retrieved by using the dataReader variable. Change the body of the while loop as shown here:

while (dataReader.Read())
{
    int orderId = dataReader.GetInt32(0);
    if (dataReader.IsDBNull(2))
    {
        Console.WriteLine("Order {0} not yet shipped\n\n", orderId);
    }
    else
    {
        DateTime orderDate = dataReader.GetDateTime(1);
        DateTime shipDate = dataReader.GetDateTime(2);
        string shipName = dataReader.GetString(3);
        string shipAddress = dataReader.GetString(4);
        string shipCity = dataReader.GetString(5);
        string shipCountry = dataReader.GetString(6);
        Console.WriteLine(
            "Order {0}\nPlaced {1}\nShipped{2}\n" +
            "To Address {3}\n{4}\n{5}\n{6}\n\n", orderId, orderDate,
            shipDate, shipName, shipAddress, shipCity, shipCountry);
    }
}

The if statement uses the IsDBNull method to determine whether the ShippedDate column (column 2 in the table) is null. If it is null, no attempt is made to fetch it (or any of the other columns, which should also be null if there is no ShippedDate value); otherwise, the columns are read and printed as before.

Compile and run the application again. Type BONAP for the CustomerID when prompted.

This time you do not get any errors, but you receive a list of orders that have not yet been shipped.