Interacting with Databases - Creating a Database and Tables

(Page 3 of 4 )

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.

custNumber	custFirstName	custLastName
1234	Bob	Smith
5678	Mary	Jones

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()
%>

Next: Were Any Rows Returned? >>
 

More ASP.NET Articles
More By McGraw-Hill/Osborne

Comments On: Interacting with Databases

· This article is an excerpt from the book "ASP.NET 2.0 DeMYSTiFieD," published by...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter 10 of ASP.NET 2.0 DeMYSTiFieD, written by Jim Keogh (McGraw-Hill/Osborne; ISBN: 0072261412). Check it out today at your favorite bookstore. Buy this book now.