ADO.NET Programming in Visual Basic .NET (2nd Edition)

By Steve  Holzner, Bob  Howell

Now that we've laid the groundwork , we can begin discussing how data binding works in a Web Form. Remember that the form as displayed on the user's screen is standard HTML. This means that all of the binding must be done before the HTML is emitted and transferred to the client. This is accomplished by the controls emitting their tags with the value attribute set to the value of the current row and column in the DataSet to which the control is. So you can see that the data binding can only be one-way. When the user posts the form back to the server, the data comes back in the Request.Forms ("ControlName") format, so there is no way to support two-way binding.

Creating the Project

Let's create our demo project called ADOBook09-01. Since this is a web project, you will have had to set up your machine to run web applications. Create the new project. When prompted, select Web Forms as in Figure 9.6.

Notice that it creates the project on the Web. You could just as easily have used the web address of another server, an application development web server, for example. The project is created in the folder pointed to by the web address. In many cases this is c:\inetpub\ wwwroot \< path to application>. Once the project is created, it creates a default Web Form called webform1. The Web Form consists of two parts , the HTML page and the VB code module associated with it. Notice that the project is a class library, which will compile to a DLL.

The first part of creating our data-bound form is to add the requisite data components . We will need a DataAdapter, Connection, and at least one DataSet. To add the DataAdapter, drag an SqlDataAdapter from the toolbox and drop it on the form. Go through all the wizard pages and use the Customers table from the Northwind database. Next , we will create another DataAdapter using the Customers table. This time, we will only create an SQL Select statement. When you mark the columns to use in the Select statement, only select the CustomerID and CompanyName columns . We will use this DataAdapter to populate the list portion of a combo box.

Next we will create the visual design of our form. This is similar to creating a Windows Form. You can drag objects from the toolbox and drop them on the form. We will make our form look like Figure 9.7.

If you designed Web Forms in previous versions of Visual Studio, you know that absolute placement of controls on a form was difficult. Somebody finally listened, because now the placement is the default. If you still want to autoflow your form elements, you can, just change the PageLayout property of the form to FlowLayout. Flow layout is when the browser determines the final placement to the elements on the page, and the elements flow just like text, wrapping to the next line if necessary. With absolute positioning or grid layout, the controls will never move. Scroll bars will appear in the user's browser if necessary.

The Specification

The way the form will work is that the user will select a company name from the drop down, at which time the controls on the form will fill with the details of the selected company. The user can then edit the record and save it by clicking the Submit button. The user can also delete the record by clicking the Delete button. If the user wants to add a new record, he or she can click the New button. This clears the fields and allows the user to enter a new record.

To save the new record the user would click Submit. The form has two "modes." The default is LookUp mode. In this mode the user can select records from the drop down. In LookUp mode, the CustomerID field is read-only. The other mode is Add. In this mode the drop down is disabled and the user can enter a new CustomerID as well as the other information. The mode is indicated at the top of the screen by a label control. If the user wants to abort adding a new record, he or she can click the Cancel Add button. The Refresh List button repopulates the list of the drop down with fresh data. The various buttons will enable and disable themselves based on the current context of the form.

Binding Controls

Binding controls in Web Forms is a little different than in Windows Forms. We will start with a simple text box. A quick look at the property windows for a web text box and it is easy to see that this is not your Windows Forms text box. Many of the properties have to do with HTML and how the control is rendered. Let's use the CustomerID columns to begin with. Select the first text box on the page, TextBox1. To bind the text box to a column in the DataSet, click on the ellipsis button in the DataBindings property. This opens the binding editor as in Figure 9.8.

Leave Simple Binding selected and find the CustomerID column in the tree. You have the option of including a custom format string as well. This is useful for date and numeric fields. If you notice the grayed-out text box, you will see a peculiar looking statement. This is actually the HTML that gets placed in the tag on the web page that is also created. You can override the generated HTML and type in your own custom binding expression here. Here is the full tag for TextBox1 in HTML:

<asp:textbox id=TextBox1 runat="server" Text='<%# DataBinder.Eval(DsCust1, "Tables[Customers].DefaultView.[0].CustomerID") %>'></asp:textbox>

Notice the text after the text= attribute. This is the same thing that we saw in the custom DataBinding area of the DataBinding dialog in Figure 9.8. Now go ahead and create the bindings for the other controls on the form.

We need to bind the dropdown control's list to a list of customers. The idea here is that the user will choose a customer from the list, then the details will display for editing. To bind the list portion of the drop down, we use the DataSource and DataTextField and DataValueField properties. The DataSource is where we put the DataSet name. The DataMember is where we put the DataTable name. Use dsCustList1 as the DataSource and Customers as the DataMember.

The DataTextField is the column we want to display in the list. This should be a descriptive column. We will use the CompanyName column in our list. For the DataValueField property we will use the CustomerID, since this is the primary key of the Customers table in the database. Do not bind the drop down to the main table in dsCust1. We are using this control as a selector only. The editable control will be the text box.

Now comes the fun part of getting it all working. There are two things we have to do on form start-up. The first thing is to determine if we are starting the form for the first time or if this is a PostBack. We do this with the IsPostBack property of the form itself. Then we have to initialize the form or restore its state. The page load event for the form ends up looking like the following code snippet.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then SqlDataAdapter2.Fill(DsCustList1) Session("CustList") = DsCustList1 Drop downList1.DataBind() Session("IsAdding") = False End If End Sub

This initializes the DataSet that contains the rows that will populate the list portion of the dropdown box. It also initializes some Session variables . A Session variable is a value that is stored by the IIS, rather than in the program's data space. This is so we can preserve values across requests , as we said before. It is stored in an object called Session. The Session object does not have to be created because it is automatically passed to the form from the IIS. Notice that we can save an entire DataSet in a Session variable. We don't have to worry about cleanup because IIS will automatically dereference the variable when the session timeout expires , then the CLR's garbage collector will destroy the object. This is a big advantage over using ActiveX . dlls , which, despite Microsoft's claims to the contrary, never worked as well as ASP applications. It was common for orphaned applications to remain active indefinitely; the only solution was to reboot the server. Notice that there is no code path if it is a PostBack because this is taken care of elsewhere in the code.

Now that we have the list populated , we can display the page. This happens automatically with ASP .NET pages. Unlike older ASP applications, there is no need to use the Response object to write the HTML stream. Now the form is displayed inside the web browser. Remember, the program has now terminated . When the page is first displayed it looks like Figure 9.9.

The next problem to solve is how to get the data into the fields on the page. It's very simple, thanks to a property of the DropdownList control (and many other web controls) called AutoPostBack. Normally, web controls don't fire events back to the server every time the user does something with them. Remember what we've been saying all along about the program terminating once the page has been sent to the client. If web controls fired similar events to Windows Forms controls, we would be executing, processing, and terminating the server-side program for each keystroke. This would be terribly inefficient. But there is a way to make web controls cause a PostBack to occur. A PostBack is different from an event, because the PostBack acts as if the user clicked the Submit button. It includes parameters in the request that indicate which control caused the posting and which procedure to execute when the program executes. It's a kind of pseudoevent.

So to make the application respond to the user selecting an item from the list of the DropdownList we set the control's AutoPostBack property to true. Now the question becomes, which event procedure runs? If you look at the list of available events, only one really fits the bill. It's the SelectedIndexChanged event as in Figure 9.10.

The code for this event is as follows :

Private Sub Drop downList1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Drop downList1.SelectedIndexChanged If Me.IsPostBack Then SqlDataAdapter1.SelectCommand.Parameters("@CustID").Value = Drop downList1.SelectedItem.Value Session("selIndex") = Drop downList1.SelectedIndex SqlDataAdapter1.Fill(DsCust1) Me.DataBind() Button5.Enabled = True Session("dsCust1") = DsCust1.Copy End If End Sub

It's important to understand the order of events when this occurs. Why does the Page_Load event fire? Remember, the program is executing all over again. The Page_Load event fires when the code starts, not when the page is displayed on the user's browser. If I seem repetitive on this it is because it is difficult for many programmers who are used to Windows Forms to get used to how Web Forms work. Many programmers automatically associate Page_Load with Form_Load and while similar, they are not the same. Form_Load only fires once when the form is first loaded. Page_Load can fire many times during the time a Web Form is in use by the user. So when Page_Load fires, we don't want to reload the DataSet that contains the data for the list. What we do is bypass this code if it is a PostBack.

Next the DropdownList event fires. Here is where we do most of the processing for getting the selected record. The code to fetch the record is standard and we have seen it many times before. Unlike Windows Forms, you have to call DataBind, a special method of the Web Form to cause the binding to take effect. Then we enable the Delete button and save a copy of the DataSet in a Session variable. Once this procedure exits, we are almost ready to display the form.

There is one more thing to do.

Remember, we reexecuted the code. Nowhere did we repopulate the DropdownList control. If we want the control to keep displaying the list, we have to rebind it every time the code runs. Just before ASP .NET is ready to send the HTML to the user, it fires the Form_PreRender event. This fires so that you have a chance to do any final processing before the form HTML is created. At this point all we have to do is restore the dsCustList DataSet from the Session variable we stored it in and then rebind it to the DropdownList.

In our last step we restore the selected index so the list still appears to the user to be unchanged. Here is the code.

Private Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.PreRender If IsPostBack Then DsCustList1 = Session("CustList") Drop downList1.DataBind() Drop downList1.Items(Session("selIndex")).Selected = True End If End Sub

Let's try it. Run the program. When you select a customer from the list, the data should fill the form. It should appear seamless. It happens so fast that the form does not appear to redisplay, but it does. Figure 9.11 shows how the form looks filled in.

So far so good. We've been able to display data. Now we must add the code to be able to update the database. Here is where the one-way read-only data binding glares back at you.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try DsCust1 = CType(Session("dsCust1"), dsCust).Copy DsCust1.Customers.Rows(0)("CompanyName") = TextBox2.Text DsCust1.Customers.Rows(0)("ContactName") = TextBox3.Text DsCust1.Customers.Rows(0)("ContactTitle") = TextBox4.Text DsCust1.Customers.Rows(0)("Address") = TextBox5.Text DsCust1.Customers.Rows(0)("City") = TextBox6.Text DsCust1.Customers.Rows(0)("Region") = TextBox7.Text DsCust1.Customers.Rows(0)("PostalCode") = TextBox8.Text DsCust1.Customers.Rows(0)("Country") = Textbox9.Text DsCust1.Customers.Rows(0)("Phone") = TextBox10.Text SqlDataAdapter1.Update(DsCust1) Session("dsCust1") = DsCust1.Copy TextBox1.ReadOnly = True Drop downList1.Enabled = True Button3.Enabled = True Button2.Enabled = False Button4.Enabled = True lblError.Text = "" Catch errobj As Exception lblError.Text = errobj.Message & vbCrLf & errobj.StackTrace End Try End Sub

It is smart enough to restore the TextBox values to the Text property of each control. It is not smart enough to restore the bound values to the DataSet. To me this is strange . I understand the architectural reasons why data binding in Web Forms is one way. What I don't understand is why, if they can reestablish the association between the Text property of the web control and its HTML counterpart on the client's browser, they can't take it one step further and reestablish the data bindings as well.

What we have to do, besides restoring the DataSet from the Session variable, is assign the values of the text boxes to their associated values in the DataTable. Then we call the normal Update method on the DataAdapter. And, yes, we have to save the DataSet again and repopulate the DropdownList control. We have to do it every time. Fortunately for us, it happens quite quickly. However, this is one of the reasons I am still not a big fan of web applications. Imagine a large database of customers and having to download the list to the user's browser after each request. You couldn't use this method. For really robust UIs, Windows Forms are still the way to go.

Adding New Records

Our next task is to incorporate support for adding new records. To do this, the user will click the New button, the fields will clear, and the user will be able to enter the new record. The add operation is a two-part process. First, the user clicks the New button. This clears the fields and sets up the buttons. After the user has entered the data, he clicks the Submit button. The program must take a different path when adding a record than when updating the record, so we must remember that we are in Add mode between requests. We will use a Session variable for this purpose. Figure 9.12 shows how the screen looks in Add mode.

The code for the New button is as follows:

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Try ClearScreen() Session("IsAdding") = True lblNew.Text = "Add New" TextBox1.ReadOnly = False Drop downList1.Enabled = False Button3.Enabled = False Button2.Enabled = True Button4.Enabled = False Button5.Enabled = False Catch errobj As Exception lblError.Text = errobj.Message & vbCrLf & errobj.StackTrace End Try End Sub

Private Sub ClearScreen() TextBox1.Text = "" TextBox2.Text = "" TextBox3.Text = "" TextBox4.Text = "" TextBox5.Text = "" TextBox6.Text = "" TextBox7.Text = "" Textbox8.Text = "" Textbox9.Text = "" TextBox10.Text = "" lblError.Text = "" End Sub

This code enables or disables buttons and controls, clear the fields, and sets the Session variable IsAdding to True. It does not do anything at all to the database or DataSet. That comes when the user clicks Submit. If the user decides not to add the record and clicks Cancel Add, the procedure does the opposite of the Add button. Here is the code for canceling:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click ClearScreen() lblNew.Text = "Lookup" TextBox1.ReadOnly = True Drop downList1.Enabled = True Session("IsAdding") = False Button3.Enabled = True Button2.Enabled = False Button4.Enabled = True Button5.Enabled = True End Sub

Once the user enters the fields and clicks the Submit button, the code is a little more involved. Here is the updated Submit button procedure after adding the code to support adding a record:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Try If Not Session("IsAdding") Then DsCust1 = CType(Session("dsCust1"), dsCust).Copy DsCust1.Customers.Rows(0)("CompanyName") = TextBox2.Text DsCust1.Customers.Rows(0)("ContactName") = TextBox3.Text DsCust1.Customers.Rows(0)("ContactTitle") = TextBox4.Text DsCust1.Customers.Rows(0)("Address") = TextBox5.Text DsCust1.Customers.Rows(0)("City") = TextBox6.Text DsCust1.Customers.Rows(0)("Region") = TextBox7.Text DsCust1.Customers.Rows(0)("PostalCode") = Textbox8.Text DsCust1.Customers.Rows(0)("Country") = Textbox9.Text DsCust1.Customers.Rows(0)("Phone") = TextBox10.Text Else Session("dsCust1") = Nothing DsCust1 = New dsCust() DsCust1.Customers.AddCustomersRow( _ TextBox1.Text, _ TextBox2.Text, _ TextBox3.Text, _ TextBox4.Text, _ TextBox5.Text, _ TextBox6.Text, _ TextBox7.Text, _ Textbox8.Text, _ Textbox9.Text, _ TextBox10.Text) End If SqlDataAdapter1.Update(DsCust1) Session("IsAdding") = False Session("dsCust1") = DsCust1.Copy Button2_Click(Button2, New System.EventArgs()) Catch errobj As Exception lblError.Text = errobj.Message & vbCrLf & errobj.StackTrace End Try End Sub

The first thing it does is check the Session variable IsAdding. If this is false, we do the normal update code that we had before. If it is true, we do not restore the old DataSet; instead we create a new empty one. We then use the AddCustomersRow method of the typed DataSet to add a new row to the Customers table, which started out empty since we created a DataSet. We then fall out of the If statement and the same code we had previously executes. First it calls the Update method of the DataAdapter, then it executes the Cancel Add button click event, which puts the form back into Look-up mode.

In order to include our new record in the DropdownList control, we need to refresh its list. We can do this by clicking the Refresh List button. The code for this is as follows:

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click DsCustList1.Clear() SqlDataAdapter2.Fill(DsCustList1) Session("CustList") = DsCustList1 Drop downList1.DataBind() End Sub

Deleting Records

To delete a record, the user simply clicks the Delete button with a record displayed on the screen. The code for this button follows:

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click DsCust1 = CType(Session("dsCust1"), dsCust).Copy DsCust1.Customers.Rows(0).Delete() SqlDataAdapter1.Update(DsCust1) ClearScreen() Session("dsCust1") = Nothing Button4_Click(Me, New System.EventArgs()) End Sub

First we restore the DataSet from the Session variable. Then we delete the row. Since we know we are always dealing with a one-row DataTable we can assume we are on row zero. Note that it is important to use the Delete method and not one of the Remove methods because the Remove methods remove the row from the collection whereas the Delete method marks the row as deleted but does not actually remove it. This is important because the Update method of the DataAdapter looks for rows marked deleted. It cannot delete removed rows because they no longer exist in the collection.

Web Forms are a definite improvement over previous web programming technologies, but there still could be improvements. Getting rid of the stateless model would be good. What if we could have the code executing continually on the server side, so the browser could communicate with it in real time? These are all things that can be addressed via Web Services, which we will look at in the next chapter. Next we will take a more detailed look at the data binding in simple controls and complex controls, such as the Web DataGrid.