Building Simple ADO.NET Applications

It is tempting to go through some of the objects that make up ADO.NET before actually creating an application, but chances are you're ready to get your hands dirty with some code. Also, chances are you've already built a few applications that use ADO.NET, so some of this will look familiar to you. Therefore, you'll start by creating a simple application by coding the ADO.NET by hand, and then you'll use the controls given to you by the Visual Studio .NET environment in order to make life easier.

Creating a DataReader in Code

Open VS .NET and create a new ASP.NET Web Application and name it DataWebApp. Choose Visual Basic or C#, depending on your preference. To start, you'll create some very simple code, and you'll build on that a bit as you work this example.

The first thing to do is open the code view of the WebForm. You'll be adding a Connection object first, and which Connection object you use will depend on what database you are using. If you are using SQL Server 7.0 or SQL Server 2000, you will create a SqlConnection object. If you are using another database, you will need to use the OleDbConnection object. Because a free evaluation copy of SQL Server can be downloaded from Microsoft, this book will use SQL Server as a back-end database, and most examples will therefore use the SqlConnection object.

The SqlConnection object, like most of the objects in .NET, is fairly flexible. You can create it and initialize it at creation, or you can create it empty and then set properties. To initialize it at creation, you pass the connection string as an argument when you use the New keyword.

The connection string is also fairly flexible. For now, understand that in this sample string, the data source might be all you need to change. In fact, if you are running this Web application on the same machine that has SQL Server installed, you won't need to change anything. The data source is the name of the server on which SQL Server resides, and the name localhost just means the machine on which the code is running. Of course, if you are like most of the world, the user ID can be left as sa and there is no password. However, if you have actually turned on some security, feel free to use any user ID and password you choose.

Open the code view of the page; at the top, you need to reference the namespace you are using. Technically, you don't need to reference the namespace, but it will save you a tremendous amount of typing if you do. To have the least amount of typing when using the SQL Server–managed provider, enter the following line at the top of your code window (before the class):

Imports System.Data.SqlClient

Now, scroll down to the Page_Load subroutine, and add the Connection object by entering the following line of code:

Dim cn As New SqlConnection("data source=localhost;" & _
   "initial catalog=pubs;user id=sa;password=;")

If you had not added the Imports statement to the top, you would have had to refer to the class name as System.Data.SqlClient.SqlConnection, a rather long name to type. You will also notice from the connection string that the initial catalog is Pubs, which is the name of the database. Throughout this book, examples will use either the Pubs or the Northwind databases, both of which ship with SQL Server as sample databases.

Next, you need to create a Command object. In ADO, the Connection object could execute SQL statements using the Execute method. This is not true in ADO.NET, however, so you will need to explicitly create a Command object in order to execute SQL statements. The type of Command object you will create is the SqlCommand object, so add this line of code under the SqlConnection line you just added:

Dim cm As New SqlCommand("Select * from Authors", cn)

You have now created a SqlCommand object that uses the SqlConnection you created, called cn, and also has a SQL statement set that will retrieve all the rows and all the fields from the Authors table.

Next, you need to create a DataReader object. Understand that what the DataReader will do is allow us to access the data, one record at a time. It is a forward-only method of access, which means it is not a fully scrollable cursor. This is ADO.NET's connected model, if you will. To create the DataReader—in this case, a SqlDataReader—you must declare the variable and then use a method on the SqlCommand object in order to generate a DataReader. In the following three lines of code, you declare the SqlDataReader, open the SqlConnection created in the earlier lines, and create the SqlDataReader object by calling the ExecuteReader method of the SqlCommand. The code looks like this:

Dim dr As SqlDataReader
cn.Open()
dr = cm.ExecuteReader

You might be tempted to think that you could now start accessing the data in the DataReader. In reality, however, the data isn't quite ready to be read; instead, you have to call the Read method of the DataReader. The Read method works like MoveNext, advancing you to the next record. It also returns a Boolean indicating whether there are more records. When the DataReader is created, it defaults to a position prior to the first record, so you have to call the Read method even to access the first record.

After calling the Read method the first time, you are sitting on the first record. You can access the fields of the record using several methods, such as the index number of the field or the field name. Although not the most efficient method, using the field name will make this example as clear as possible.

You want to print out the name of the first person in the Authors table. If you have examined the Authors table, you know that the first name is stored in the au_fname field and the last name is stored in the au_lname field. Therefore, you can concatenate these two fields to display the person's name. In this example, you will use Response.Write to output something to the HTML stream. Your code would end up looking like this:

dr.Read()
Response.Write("Name: " & dr("au_fname") & " " & dr("au_lname"))

You have now taken the first record in the Authors table and displayed it in the browser. Your final code, minus the hidden region of Web Form Designer Generated Code, should look like the code in Listing 6.1.

Listing 6.1. A Simple Web Page with One Record

Imports System.Data.SqlClient

Public Class WebForm1
   Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

   Private Sub Page_Load(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles MyBase.Load
      'Put user code to initialize the page here
      Dim cn As New SqlConnection("initial catalog=pubs;" & _
         "data source=localhost;user id=sa;password=;")
      Dim cm As New SqlCommand("Select * from Authors", cn)
      Dim dr As SqlDataReader
      cn.Open()
      dr = cm.ExecuteReader
      '*** Lines below will be changing ***
      dr.Read()
      Response.Write("Name: " & dr("au_fname") & " " & _
         dr("au_lname"))
      '*** Lines above will be changing ***
      dr.Close()
      cn.Close()
   End Sub

End Class

You'll notice that the code contains comments around a section of code. That is because the code in between the comments will be changing as more features are added to the example. You'll also notice the addition of Close commands on the DataReader and Connection objects. This is not just a good idea, but closing the DataReader is the only way to free up the Connection object for more processing. You'll read more about the Close method throughout the chapter.

If you execute this code, the result might not appear to be very exciting, as shown in Figure 6.2. However, realize what you have done: You have retrieved a record from a database and displayed it on a Web page, in a few lines of code. Granted, this is nothing original, but it does work and it is all done using the .NET tools, so feel happy that you have your first ADO.NET example under your belt.

Displaying the first author's first and last names isn't exactly the most exciting thing you can do. Instead, you could list all of the fields for the authors, in an HTML table. You'll now modify the code between the commented lines in order to loop through all the records and build an HTML table that displays the results. Don't worry if you aren't an HTML wizard; that is not as important in this example as understanding what is happening with the data.

In this next example, you will loop through all the records coming back into the DataReader. In the past, you would have used a Do...Loop in order to determine the end-of-file (EOF) marker in a stream of data. With the DataReader, you simply check the Read method. It returns True as long as there are additional records, and returns False when no records are left. Therefore, your loop will look something like this:

While dr.Read...
' display code goes here
End While

This code will effectively loop you through all the records in a forward-only pass through the data. Within the loop, you will have code to display the individual fields. If you know the names of the fields, you can use the names as you did in the previous example. Or you can use the index numbers of the fields, starting at zero. In fact, you can use a For...Next loop to loop through the fields, from zero to the number of fields minus one. Your code would look something like this:

For iField = 0 To dr.FieldCount - 1
   Response.Write(dr(iField))
Next

Just running the code as shown so far won't produce very nice results inside a Web page. Instead, you can place the data in an HTML table, where each field is placed in its own cell, and each record is a separate row in the table. To accomplish this, change the code inside the comments so that the end result looks like Listing 6.2.

Listing 6.2. A Simple Web Page Displaying All Records from the Authors Table

'*** Lines below will be changing ***
Response.Write("<table border=1><tr>")
While dr.Read
   Dim iField As Integer
   For iField = 0 To dr.FieldCount - 1
      Response.Write("<td>" & dr(iField) & "</td>")
   Next
   Response.Write("</tr><tr>")
End While
Response.Write("</tr></table>")
'*** Lines above will be changing ***

If you run this page, your results should look something like those seen in Figure 6.3.

Using the DataReader to quickly scroll through the records is a common approach to dealing with data in a Web page. After all, Web pages are processed on the server and then displayed on the client. You rarely need to have a fully scrollable set of records when building a Web application. Therefore, the DataReader is often used for Web applications, and is very similar to the forward-only cursor used by many ADO developers today.

Although much has been made about ADO.NET's disconnected architecture, the DataReader uses a connected model. You are connected as you loop through the records, and the Connection object cannot be used for any other purpose as long as a DataReader is open. Therefore, when using a DataReader, you want to make sure that you loop through the records as soon as possible and then close the DataReader, freeing up the Connection object to carry out other tasks.

Even though the DataReader uses a connected paradigm similar to what ADO uses, the DataReader tends to be the most efficient way to bring records from the server to the client. In this case, the client is the ASP.NET page, not the client browser. Bringing the data from the server to the client can be done in other ways, but the DataReader tends to be the most efficient because it does not have to create a scrollable cursor of any sort. However, the data is not stored in memory on the client, so each time the page is called, it requires a trip to the server. In contrast, ADO.NET's object for storing data in memory, the DataSet, caches data in memory, making subsequent accesses of the data faster as long as the object is still in memory. Because a Web page falls out of scope after processing, the DataSet would be lost, which means that the DataSet does not always make sense in a Web application.

Using the Web Forms Controls

Using the graphical controls included in Visual Studio .NET, you can create a Web page that displays data without writing much code. You'll still have to write some code, but the creation of the HTML table itself is handled by a server control, which frees you up to concentrate on working with the data, not formatting it for output.

Add a new Web Form to your VS .NET project, and just leave it named WebForm2.aspx. Now, from the Toolbar, click on the Data tab and drag over a SqlConnection control and drop it on the WebForm designer for WebForm2.aspx. A new control, SqlConnection1, is added to the component tray of WebForm2. In the Properties window, click on the drop-down arrow in the ConnectionString property. You might already see one or more connections showing up, but assume that you have to add a new one. Click on <New Connection…> and the Data Link Properties dialog will open.

Enter your server name, the username and password you want to use, and the database (Pubs) you want to use. Your screen should look something like the one in Figure 6.4. Click the OK button on the Data Link Properties dialog box and you will see the ConnectionString property has been filled in with the appropriate data.

Next, drag a SqlCommand control from the Data tab of the Toolbox onto the designer. A new control, SqlCommand1, is added to the component tray. In the Properties window, expand the Existing node of the tree view to see the available connections, and set the Connection property to SqlConnection1. In the CommandText property, enter the following SQL statement:

Select * from Authors

After clicking OK, you will be prompted with a question asking if you want to regenerate the parameters collection. There are no parameters, so you can safely click No. On the other hand, you can also safely click Yes. This prompt is just asking you whether you want to automatically re-create a collection with all the parameters in the query. For example, you could add parameters that are passed in at runtime.

So far, you have done with controls what you did in code before. You have a graphical representation of an ADO.NET SqlConnection object; in this case, called SqlConnection1. It has a connection string to connect you to the Pubs database on a particular server. You also have a graphical representation of an ADO.NET SqlCommand object, named SqlCommand1. It uses the SqlConnection1 Connection object, and it issues the command to retrieve all the fields and all the records from the Authors table. This was all done with code in the previous example, but so far you haven't written any code.

Now, return to the Toolbox, but go to the Web Forms tab. Drag a DataGrid control from the Toolbox and drop it on the Web Form. If you prefer, you can click the Auto Format link in the Properties window and choose a format for the table, which adds colors and changes the font. When you are done, your form should look like the one shown in Figure 6.5, with the two controls in the component tray and the DataGrid on the form itself.

Before moving forward, you need to add some code. Think about what you've done: You've created a connection and a command, but you need something to actually retrieve the records. In keeping with the previous example, you need to create a DataReader and display the data on the Web page. However, it is easier when working with the controls, as you'll see when you write the code.

Go to the code view and find the Page_Load subroutine. You're going to add code to open the connection and then use the ExecuteReader method of the SqlConnection1 object to create a DataReader. Then you will bind the DataGrid to the DataReader, and you can then close the DataReader and the Connection. You do not need to loop through the DataReader, nor do you need to format the data for output. Instead, the DataGrid will handle all of this for you.

Go to the code window and at the top, add your Imports System.Data.SqlClient command. In the Page_Load subroutine, enter the code in Listing 6.3.

Listing 6.3. The Code Necessary to Bind a DataGrid Control to a DataReader Control Created Programmatically

Private Sub Page_Load(ByVal sender As System.Object, _
  ByVal e As System.EventArgs) Handles MyBase.Load
   SqlConnection1.Open()
   Dim dr As sqlDataReader
   dr = SqlCommand1.ExecuteReader()
   DataGrid1.DataSource = dr
   DataGrid1.DataBind()
   dr.Close()
   SqlConnection1.Close()
End Sub

The easiest way to view the page now is to save it, but then right-click on WebForm2.aspx in the Solution Explorer window and choose Build and Browse. This opens the page within the browser in VS .NET, as shown in Figure 6.6.

So far, you've seen two examples of using ADO.NET in a connected mode, similar to what you were likely doing with ADO. What about this ballyhooed disconnected model? That's where the DataSet comes into play.