Interacting with Databases

Whenever you log into your favorite e-commerce web site, you set off a series of routines behind the scenes that, among other things, links the web site with a database that contains product information and probably information about you.

You too can link your ASP.NET web pages with your own database by using the ADO.NET connection. Think of ADO.NET as your pipeline into popular commercial database management software (DBMS) such as Microsoft SQL Server, the Oracle database server, and Microsoft Access.

In this chapter you’ll learn how to make this connection and how to write SQL statements in a query that direct the DBMS to perform tasks that are commonly used in many commercial web sites.

The ADO.NET Connection

Customer data and other information that is typically used by an ASP.NET web page are stored in a database that is managed by database management software (DBMS). As you remember from the preceding chapter, a DBMS such as Microsoft SQL Server, the Oracle database server, or Microsoft Access is a filing cabinet and file clerk all rolled up into one and maintains and accesses data as requested.

Your application interacts with a DBMS by sending it queries using the Structured Query Language (SQL). For example, if you wanted to retrieve a customer’s account information, you’d write an SQL query and send it to the DBMS. The DBMS locates and returns the account information to your application.

However, before you can send the query, you need to connect your application to the DBMS. You do this by using classes provided by ADO.NET. ADO.NET is part of the .NET framework. Although this sounds imposing, it really isn’t.

As you’ll recall from Chapter 2, an object is a real thing that is described by a class definition. In the case of ADO.NET, these objects are database-related “things” such as rows, columns, tables, and databases. ADO.NET contains class definitions that are used to access a database. Remember that a class contains functions and attributes. A function is a block of code that is executed by calling the name of the function. An attribute is data associated with the class.

Therefore, ADO.NET contains code that you call within your application to connect your application to a DBMS and enables your application to send queries to and receive data from a DBMS.

Namespaces and Classes

ADO.NET contains sets of classes designed to interact with a specific DBMS. Each set is identified by a namespace. A namespace organizes classes in a hierarchy of classes to prevent naming conflicts. This sounds a little strange, but you won’t give the term namespace a second thought once you begin to use it in your application.

The most important point to understand is that you must import into your application the namespace that corresponds to the DBMS that is accessed by your application. Here are commonly used namespaces:

• System.Data.SqlClient  Used for Microsoft SQL Server version 7.0 or higher
  
• System.Data.OleDb  Used for OLE DB DMBSs such as Microsoft Access
  
• System.Data.Odbc  Used for ODBC driver-based DBMSs. ODBC is used in Windows to connect to many popular DBMSs.
  
• System.Data.OracleClient  Used for the Oracle database server

Throughout this chapter, we’ll be showing examples that use System.Data .SqlClient to interact with the Microsoft SQL Server and System.Data.OleDb used to interact with Microsoft Access. Techniques used in these examples are similar to the way you use the other namespaces to interact with other DBMSs.

Although each namespace refers to different classes, there are similarities among them. For example, SqlConnection is used to open a DBMS connection using the System.Data.SqlClient namespace. OleDbConnection performs the same task when using the System.Data.OleDb namespace.

Likewise, SqlCommand is used to send a query to the DBMS in the System .Data.SqlClient namespace. OleDbCommand does the same using the System.Data .OleDb namespace.

Opening a Connection to a DBMS

Your application must open a connection to the DBMS before sending or requesting data from the DBMS. Think of a DBMS connection as the same as a telephone connection. Before you can talk to your friend, you must dial your friend’s telephone number and wait for her to answer. You talk as long as you want once the connection is made, and you close the connection after you are through, enabling someone else to connect to your friend.

To create a connection to the DBMS, follow these steps:

1. Import the namespace  This identifies the set of classes that you’ll be using within your application to interact with the database. Developers import the namespace so that they don’t have to write the fully qualified class name, which is much longer than if the namespace is imported.
   
2. Create an instance of the connection class Remember from Chapter 2 that a class definition describes a class much as a stencil describes a letter of the alphabet. You create a real object described by the class by creating an instance of the class. This is similar to using the stencil to create a real letter of the alphabet.
   
3. Open the connection  You do this by calling an appropriate instance function of the instance of the class.

Let’s see how this is done by creating a connection to Microsoft SQL Server. The initial step is to import that namespace. The namespace for Microsoft SQL Server is System.Data.SqlClient. We import that namespace by using the following page directive at the beginning of the ASP.NET web page.

<%@ Import Namespace="System.Data.SqlClient" %>

The next step is to create an instance of the connection class. First, declare a variable that references the instance, and second, create the instance and assign it to the variable as shown here:

Dim conMyDb As SqlConnection
conMyDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=mydatabase")

We need to create an instance of the SqlConnection class and pass the constructor of the SqlConnection class information it needs to link to the DBMS. The constructor creates the instance of a class. There are three pieces of information that you must provide.

The first is the location of the server that contains the DBMS. This is the URL of the server or localhost if the DBMS resides on your computer. For this example, we’re assuming that you have the database on your local computer.

The next two pieces of information are needed to log onto the DBMS. These are user ID (uid) and the password (pwd). These are assigned by directly interacting with the DBMS. In a business environment, the database administrator is the person who assigns logon information to everyone.

The last piece of information is the name of the database. You’ll remember from Chapter 2 that the DBMS maintains many databases, each having its own unique name. You must identify the database that you want to link to by assigning the database name to the database parameter when you create an instance of the SqlConnection class.

The SqlConnection constructor returns a reference to the instance to the variable. You then use the variable to access functions and attributes of the class. One of the first of these is called the Open() function, which opens the database connection as shown here:

conMyDb.Open()

Now let’s put these statements together to create an ASP.NET web page that accesses the customer database. We’ll show two examples. The first is for Microsoft SQL Server, and the second is for Microsoft Access. Notice that the connection to the database is made in the Page_Load subroutine. Each time the page is loaded, the ASP.NET engine establishes a connection with the database.

<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
   Sub Page_Load
      Dim custDb As SqlConnection
      custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
      custDb.Open()
   End Sub
</Script>

Here is the example for linking to Microsoft Access. You’ll notice a few differences between this example and the preceding example. The first is that we’re importing the SystemData.OleDb namespace, which enables us to use the OleDb classes that are needed to interact with Microsoft Access.

Another difference is in the OleDbConnection constructor. Notice that there are two parameters. The first is Provider, which is the name of the DBMS. OleDB is the provider of Microsoft.Jet.OLEDB.4.0, which Microsoft Access is associated with. The second parameter is DataSource, which is the location and name of the database. In this example the database is located on the C: drive and is called cust.mdb.

The other parts of the example are identical to the preceding example.

<%@ Import Namespace="System.Data.OleDb" %>
<Script Runat="Server">
   Sub Page_Load ( s As Object, e As EventArgs )
      Dim custDb as OleDbConnection
      custDb = New
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:cust.mdb" )
      custDb.Open()
   End Sub
</Script>

Creating a Database and Tables

Before continuing, it is important to create a database and at least one table in order to work through examples in this chapter. You should install a DBMS (see Chapter 9). It is beyond the scope of this book to explain how to install a DBMS.

Most DBMSs have a user interface that you can use to create a database and tables. In the next chapter we’ll show you how to create these using a query from within your application. For now, you’ll need to create a database. You can call it MyBusiness. And then create the following table and call it custContact. Here are the column definitions:

• custNumber CHAR(4)
  
• custFirstName CHAR(30)
  
• custLastName CHAR(30)

Insert these rows so that you’ll be able to retrieve them when you send a query to the DBMS in examples throughout this chapter.

Sending a Query to the DBMS

In order to retrieve information stored in the database, you need to create a query using SQL and then send the query to the DBMS over an open database connection. A query can be as simple as asking for the number of orders placed by a particular customer—or as complex as asking for the number of times a customer ordered each product and the dates of the orders.

We’ll use simple queries in this chapter so that you can focus on how to interact with the database using your application. You’ll learn how to create more complex queries in the next chapter, which focuses on SQL.

Let’s get started by requesting the names of all our customers and displaying them on the web page. Here are the steps you need to perform:

1. Create a database connection and open the connection. 
   
2. Create a query. 
   
3. Send the query to the DBMS. 
   
4. Read the rows returned by the DBMS and display them on the screen.

You already learned how to perform the first step in the preceding section of this chapter. The second step requires you to create a query. There are two tasks involved here. The first is writing the query using SQL, and the next is to assign the query to an instance of the command class.

You must provide the DBMS with two pieces of information. The first is the name of the columns that you want returned from the database. The second is the name of the table that contains these columns.

We want two columns returned. These are custFirstName and custLastName, which are part of the custContact table. We tell the DBMS the columns that we want returned by using the Select statement. The Select statement is another way of saying, “This is the information I want returned.” The From clause is used to identify the table that contains these columns.

Here is the query written in SQL:

Select custFirstName, custLastName
From custContact

Now we need to create an instance of the command class and initialize it with the query and reference to the opened database connection. Here’s how this is done using Microsoft SQL Server. First we declare a variable that will be assigned a reference to the instance of the SqlCommand class, and then we create an instance of SqlCommand. Notice that we pass it the query as the first parameter. The second parameter is the variable that references the database connection. This is like saying to the instance of the SqlCommand, there is my query. Send it over the custDb connection.

Dim cmdSelectCustomers As SqlCommand
cmdSelectCustomers = New SqlCommand("Select custFirstName, custLastName From
custContact", custDb)

We need to create an instance of SqlDataReader class in order to read the information returned to us by the DBMS. The SqlDataReader class contains functions that you call to access information returned by the DBMS.

There are two steps needed to create a reader. First, you need to declare a variable that will be assigned a reference to the instance of the reader. Next, you need to create an instance of the SqlDataReader class. Here’s how this is done:

Dim dtrCustomers As SqlDataReader
dtrCustomers = cmdSelectCustomers.ExecuteReader()

The instance of the reader is returned by calling the ExecuteReader() function from the SqlCommand class. This function returns the instance of the reader, which is assigned to the variable. You then refer to the variable (dtrCustomers) each time you need to access the reader.

In response to a query, the DBMS can return no information, a single piece of information, or multiple pieces of information, depending on your query and the number of rows that match your query. This information is returned as a list that you step through from within your application.

You do this by using a While loop as shown here. Notice that we call the Read() function of the SqlDataReader class. The Read() function can return a true or a false. A true value means there is at least a current row of information. A false value means there isn’t a row. That is, no data exists that corresponds to your query.

You retrieve information returned by the DBMS by using the column name of the information. The following example illustrates how to access the custFirstName and custLastName, which are then displayed on the web page. The second statement within the While loop causes the cursor to be moved to the next line. The application exits the While loop when there are no more rows to read. The Reader is then closed by calling its Close() method.

While dtrCustomers.Read()
Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
    CStr(dtrCustomers.Item("custLastName")))
 Response.Write("<BR>")
End While

Here is the full example of how to query the DBMS and read the information returned by the DBMS. Remember that this example is used to access data that is managed by Microsoft SQL Server:

<% Import Namespace="System.Data.SqlClient" %>
<%
   Dim custDb As SqlConnection
   Dim cmdSelectCustomers As SqlCommand
   Dim dtrCustomers As SqlDataReader
   custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
   custDb.Open()
   cmdSelectCustomers = New SqlCommand( "Select custFirstName, custLastName From
custContact", custDb)
   dtrCustomers = cmdSelectCustomers.ExecuteReader()
   While dtrCustomers.Read()
      Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
                     CStr(dtrCustomers.Item("custLastName")))
      Response.Write("<BR>")
   End While
   dtrCustomers.Close()
   custDb.Close()
%>

Here’s how to do this with Microsoft Access:

<% Import Namespace="System.Data.OleDb" %>
<%
   Dim custDb As OleDbConnection
   Dim cmdSelectCustomers As OleDbCommand
   Dim dtrCustomers As OleDbDataReader
   custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:
cust.mdb")
   custDb.Open()
   cmdSelectCustomers = New OleDbCommand( "Select custFirstName, custLastName From
custContact", custDb)
   dtrCustomers = cmdSelectCustomers.ExecuteReader()
   While dtrCustomers.Read()
      Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
                     CStr(dtrCustomers.Item("custLastName")))
      Response.Write("<BR>")
   End While
   dtrCustomers.Close()
   custDb.Close()
%>

Were Any Rows Returned?

The question that your application needs to answer after sending a query to a DBMS is whether or not the DBMS found any information that matches your query. The easiest way to answer this question is to examine the HasRows property of the DataReader class.

The value of the HasRows property determines if any records were returned by the DBMS. It is true if records are returned; otherwise, the value of the HasRows property is false. It is important to remember that the HasRows property does not tell you the number of records that are returned. Instead, it simply states if any are returned.

Let’s modify the previous examples to include the HasRows property. We’ll begin with the Microsoft SQL Server example.

<% Import Namespace="System.Data.SqlClient" %>
<%
   Dim custDb As SqlConnection
   Dim cmdSelectCustomers As SqlCommand
   Dim dtrCustomers As SqlDataReader
   custDb = New SqlConnection("Server=localhost;uid=myID;pwd=mypassword;
database=customer")
   custDb.Open()
   cmdSelectCustomers = New SqlCommand( "Select custFirstName, custLastName From
custContact", custDb)
   dtrCustomers = cmdSelectCustomers.ExecuteReader()
   If dtrCustomers.HasRows Then
      While dtrCustomers.Read()
 Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
                     CStr(dtrCustomers.Item("custLastName")))
Response.Write("<BR>")
      End While
   Else
      Response.Write("There are no customers.")
   End If
   dtrCustomers.Close()
   custDb.Close()
%>

Here’s how to do this with Microsoft Access:

<% Import Namespace="System.Data.OleDb" %>
<%
   Dim custDb As OleDbConnection
   Dim cmdSelectCustomers As OleDbCommand
   Dim dtrCustomers As OleDbDataReader
   custDb = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=c:
ust.mdb")
   custDb.Open()
   cmdSelectCustomers = New OleDbCommand( "Select custFirstName, custLastName From
custContact", custDb)
   dtrCustomers = cmdSelectCustomers.ExecuteReader()
   If dtrCustomers.HasRows Then
      While dtrCustomers.Read()
Response.Write(CStr(dtrCustomers.Item("custFirstName")) & " " & _
                     CStr(dtrCustomers.Item("custLastName")))
         Response.Write("<BR>")
      End While
   Else
      Response.Write("There are no customers.")
   End If
   dtrCustomers.Close()
   custDb.Close()
%>

Retrieving a Specific Row

It is very common that you’ll need to look for particular information stored in a database such as a customer number. To do this, you’ll need to include a Where clause in your query. The Where clause requires two pieces of information: a search value and the column that contains the search value.

Let’s say that you want to retrieve the customer number and customer name for customer number 1234. Here’s the query that you’ll need to write:

Select custNumber, custFirstName, custLastName
From custContact
Where custNumber = '1234'

The Select statement is nearly identical to the query you wrote earlier in this chapter, except we’ve included the custNumber column. Remember that columns that appear in the Select statement are returned by the DBMS.

The From clause is the same as in other queries in that it tells the DBMS to use the custContact table.

The Where clause is new to the query. It tells the DBMS to search for 1234 in the custNumber column. Only rows that have 1234 in the custNumber column are returned by the DBMS. There is only one row in our example that has 1234 as a customer number, so only that row is returned.

Replace the query in the previous examples with this query and run the application to retrieve customer Bob Smith from the DBMS.

Please check back next week for the continuation of this article.