Querying Information in a Microsoft SQL Server Database

After completing this chapter, you will be able to:

• Fetch and display data from a Microsoft SQL Server database by using Microsoft ADO.NET.

• Define entity classes for holding data retrieved from a database.

• Use DLINQ to query a database and populate instances of entity classes.

• Create a custom DataContext class for accessing a database in a typesafe manner.

In Part IV of this book, “Working with Windows Applications,” you learned how to use Microsoft Visual C# to build user interfaces and present and validate information. In Part V, you will learn about managing data by using the data access functionality available in Microsoft Visual Studio 2008 and the Microsoft .NET Framework. The chapters in this part of the book describe ADO.NET, a library of objects specifically designed to make it easy to write applications that use databases. In this chapter, you will also learn how to query data by using DLINQ—extensions to LINQ based on ADO.NET that are designed for retrieving data from a database. In Chapter 26, “Displaying and Editing Data by Using Data Binding,” you will learn more about using ADO.NET and DLINQ for updating data.

Querying a Database by Using ADO.NET

The ADO.NET class library contains a comprehensive framework for building applications that need to retrieve and update data held in a relational database. The model defined by ADO.NET is based on the notion of data providers. Each database management system (such as SQL Server, Oracle, IBM DB2, and so on) has its own data provider that implements an abstraction of the mechanisms for connecting to a database, issuing queries, and updating data. By using these abstractions, you can write portable code that is independent of the underlying database management system. In this chapter, you will connect to a database managed by SQL Server 2005 Express Edition, but the techniques that you will learn are equally applicable when using a different database management system.

The Northwind Database

Northwind Traders is a fictitious company that sells edible goods with exotic names. The Northwind database contains several tables with information about the goods that Northwind Traders sells, the customers they sell to, orders placed by customers, suppliers from whom Northwind Traders obtains goods to resell, shippers that they use to send goods to customers, and employees who work for Northwind Traders. Figure 25-1 shows all the tables in the Northwind database and how they are related to one another. The tables that you will be using in this chapter are Orders and Products.

Click to view larger image

Creating the Database

Before proceeding further, you need to create the Northwind database.

Create the Northwind database

1. On the Windows Start menu, click All Programs, click Accessories, and then click Command Prompt to open a command prompt window. If you are using Windows Vista, in the command prompt window type the following command to go to the \Microsoft Press\Visual CSharp Step by Step\Chapter 25 folder under your Documents folder. Replace Name with your user name.

   cd "\Users\Name\Documents\Microsoft Press\Visual CSharp Step by Step\Chapter 25"

   If you are using Windows XP, type the following command to go to the \Microsoft Press\Visual CSharp Step by Step\Chapter 25 folder under your My Documents folder, replacing Name with your user name.

   cd "\Documents and Settings\Name\My Documents\Microsoft Press\Visual CSharp Step by
   Step\Chapter 25"

2. In the command prompt window, type the following command:

   sqlcmd -S YourComputer\SQLExpress -E -iinstnwnd.sql

   Replace YourComputer with the name of your computer.

   This command uses the sqlcmd utility to connect to your local instance of SQL Server 2005 Express and run the instnwnd.sql script. This script contains the SQL commands that create the Northwind Traders database and the tables in the database and fills them with some sample data.

   TIP

   Ensure that SQL Server 2005 Express is running before you attempt to create the Northwind database. (It is set to start automatically by default. You will simply receive an error message if it is not started when you execute the sqlcmd command.) You can check the status of SQL Server 2005 Express, and start it running if necessary, by using the SQL Configuration Manager available in the Configuration Tools folder of the Microsoft SQL Server 2005 program group.

3. When the script finishes running, close the command prompt window.

Using ADO.NET to Query Order Information

In the next set of exercises, you will write code to access the Northwind database and display information in a simple console application. The aim of the exercise is to help you learn more about ADO.NET and understand the object model it implements. In later exercises, you will use DLINQ to query the database. In Chapter 26, you will see how to use the wizards included with Visual Studio 2008 to generate code that can retrieve and update data and display data graphically in a Windows Presentation Foundation (WPF) application.

The application you are going to create first will produce a simple report displaying information about customers’ orders. The program will prompt the user for a customer ID and then display the orders for that customer.

Connect to the database

1. Start Visual Studio 2008 if it is not already running.

2. Create a new project called ReportOrders by using the Console Application template. Save it in the \Microsoft Press\Visual CSharp Step By Step\Chapter 25 folder under your Documents folder, and then click OK.

   NOTE

   Remember, if you are using Visual C# 2008 Express Edition, you can specify the location for saving your project by setting the Visual Studio projects location in the Projects and Solutions section of the Options dialog box on the Tools menu.

3. In Solution Explorer, change the name of the file Program.cs to Report.cs. In the Microsoft Visual Studio message, click Yes to change all references of the Program class to Report.

4. In the Code and Text Editor window, add the following using statement to the list at the top of the file:

   using System.Data.SqlClient;

   The System.Data.SqlClient namespace contains the SQL Server data provider classes for ADO.NET. These classes are specialized versions of the ADO.NET classes, optimized for working with SQL Server.

5. In the Main method of the Report class, add the following statement shown in bold type, which declares a SqlConnection object:

   static void Main(string[] args)
   {
       SqlConnection dataConnection = new SqlConnection();
   }

   SqlConnection is a subclass of an ADO.NET class called Connection. It is designed to handle connections to SQL Server databases.

6. 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. In the catch block, add a simple handler that catches SqlException exceptions. The new code is shown in bold type here:

   static void Main(string[] args)
   {
       ...
       try
       {
           // You will add your code here in a moment
       }
       catch(SqlException e)
       {
           Console.WriteLine("Error accessing the database: {0}", e.Message);
       }
   }

   A SqlException is thrown if an error occurs when accessing a SQL Server database.

7. Replace the comment in the try block with the code shown in bold type here:

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

This code attempts to create a connection to the Northwind database. The contents of the ConnectionString property of the SqlConnection object contain elements that specify 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.

You can also encode many other elements in the connection string. See the documentation supplied with Visual Studio 2008 for details.

string userName = ...;
string password = ...;
// Prompt the user for their name and password, and fill these variables
string connString = String.Format(
    "User ID={0};Password={1};Initial Catalog=Northwind;"  +
    "Data Source=YourComputer\\SQLExpress", username, password);
myConnection.ConnectionString = connString;

The next step is to prompt the user for a customer ID and then query the database to find all of the orders for that customer.

Query the Orders table

1. Add the statements shown here in bold type to the try block after the dataConnection.Open(); statement:

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

   These statements prompt the user for a customer ID and read the user’s response in the string variable customerId.

2. Type the following statements shown in bold type after the code you just entered:

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

   The first statement creates a SqlCommand object. Like SqlConnection, this is a specialized version of an ADO.NET class, Command, that has been designed for performing queries against a SQL Server database. An ADO.NET 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 a SQL statement.

   The second line of code sets the Connection property of the SqlCommand object to the database connection you opened in the preceding exercise. The next two statements populate the CommandText property with a 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. The Console.WriteLine statement just repeats the command about to be executed to the screen.

   Important

   If you are an experienced database developer, you will probably be about to e-mail me telling me that using string concatenation to build SQL queries is bad practice. This approach renders your application vulnerable to SQL injection attacks. However, the purpose of this code is to quickly show you how to execute queries against a SQL Server database by using ADO.NET, so I have deliberately kept it simple. Do not write code such as this in your production applications.

   For a description of what a SQL injection attack is, how dangerous it can be, and how you should write code to avoid such attacks, see the SQL Injection topic in SQL Server Books Online, available at http://msdn2.microsoft.com/en-us/library/ms161953.aspx.

3. Add the following statement shown in bold type after the code you just entered:

   try
   {
       ...
       SqlDataReader dataReader = dataCommand.ExecuteReader();
   }

   The ExecuteReader method of a SqlCommand object constructs a SqlDataReader object that you can use to fetch the rows identified by the SQL statement. The SqlDataReader class provides the fastest mechanism available (as fast as your network allows) for retrieving data from a SQL Server.

The next task is to iterate through all the orders (if there are any) and display them.

Fetch data and display orders

1. Add the while loop shown here in bold type after the statement that creates the SqlDataReader object:

   try
   {
       ...
       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.

2. Add the statements shown here in bold type to the body of the while loop you created in the preceding step:

   while (dataReader.Read())
   {
       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 block of code shows how you read the data from the database by using a SqlDataReader object. A 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 preceding 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.

When you have finished using a database, it’s good practice to close your connection and release any resources you have been using.

Disconnect from the database, and test the application

1. Add the statement shown here in bold type after the while loop in the try block:

   try
   {
       ...
       while(dataReader.Read())
       {
           ...
       }
       dataReader.Close();
   }

   This statement closes the SqlDataReader object. You should always close a SqlDataReader object when you have finished with it because you will 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.

   NOTE

   If you activate multiple active result sets (MARS) with SQL Server 2005, you can open more than one SqlDataReader object against the same SqlConnection object and process multiple sets of data. MARS is disabled by default. To learn more about MARS and how you can activate and use it, consult SQL Server 2005 Books Online.

2. After the catch block, add the following finally block:

   catch(SqlException e)
   {
       ...
   }
   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 after the catch handler has finished.

   TIP

   An alternative approach to using a finally block is to wrap the code that creates the SqlDataConnection object in a using statement, as shown in the following code. At the end of the block defined by the using statement, the SqlConnection object is closed automatically, even if an exception occurs:

   using (SqlConnection dataConnection = new SqlConnection())
   {
       try
       {
           dataConnection.ConnectionString = "...";
           ...
       }
       catch (SqlException e)
       {
           Console.WriteLine("Error accessing the database: {0}", e.Message);
       }
   }

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

4. At the customer ID prompt, type the customer ID VINET, and press Enter.

   The SQL SELECT statement appears, followed by the orders for this customer, as shown in the following image:

   

   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.

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

   Some rows appear, but then an error occurs. If you are using Windows Vista, a message box appears with the message “ReportOrders has stopped working.” Click Close program (or Close the program if you are using Visual C# Express). If you are using Windows XP, a message box appears with the message “ReportOrders has encountered a problem and needs to close. We are sorry for the inconvenience.” Click Don’t Send.

   An error message containing the text “Data is Null. This method or property cannot be called on Null values” appears in the console window.

   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, but if you try to read it, you get an error. In the Orders table, the ShippedDate column can contain a null value if the order has not yet been shipped. You should also note that this is a SqlNullValueException and consequently is not caught by the SqlException handler.

6. Press Enter to close the console window and return to Visual Studio 2008.

SqlDataReader dataReader =
    dataCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

Handle null database values

1. In the Main method, change the code in the body of the while loop to contain an if … else block, as shown here in bold type:

   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.

2. Build and run the application again.

3. Type BONAP for the customer ID when prompted.

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

4. When the application finishes, press Enter and return to Visual Studio 2008.