Microsoft ASP.NET 4 : Repeated-Value Data Binding (part 2) - Creating a Record Editor

10/16/2012 2:58:33 AM

7. Data Binding with ADO.NET

When you're using data binding with the information drawn from a database, the data binding process takes place in the same three steps. First you create your data source, which will be a DataReader or DataSet object. A DataReader generally offers the best performance, but it limits your data binding to a single control because it is a forward-only reader. As it fills a control, it traverses the results from beginning to end. Once it's finished, it can't go back to the beginning; so it can't be used in another data binding operation. For this reason, a DataSet is a more common choice.

The next example creates a DataSet and binds it to a list. In this example, the DataSet is filled by hand, but it could just as easily be filled using a DataAdapter object.

To fill a DataSet by hand, you need to follow several steps:

  1. First, create the DataSet.

  2. Next, create a new DataTable, and add it to the DataSet.Tables collection.

  3. Next, define the structure of the table by adding DataColumn objects (one for each field) to the DataTable.Colums collection.

  4. Finally, supply the data. You can get a new, blank row that has the same structure as your DataTable by calling the DataTable.NewRow() method. You must then set the data in all its fields, and add the DataRow to the DataTable.Rows collection.

Here's how the code unfolds:

' Define a DataSet with a single DataTable.
Dim dsInternal As New DataSet()

' Define two columns for this table.

' Add some actual information into the table.
Dim rowNew As DataRow = dsInternal.Tables("Users").NewRow()
rowNew("Name") = "John"
rowNew("Country") = "Uganda"

rowNew = dsInternal.Tables("Users").NewRow()
rowNew("Name") = "Samantha"
rowNew("Country") = "Belgium"

rowNew = dsInternal.Tables("Users").NewRow()
rowNew("Name") = "Rico"
rowNew("Country") = "Japan"

Next, you bind the DataTable from the DataSet to the appropriate control. Because list controls can only show a single column at a time, you also need to choose the field you want to display for each item by setting the DataTextField property:

' Define the binding.
lstUser.DataSource = dsInternal.Tables("Users")
lstUser.DataTextField = "Name"

Alternatively, you could use the entire DataSet for the data source, instead of just the appropriate table. In that case, you would have to select a table by setting the control's DataMember property. This is an equivalent approach, but the code is slightly different:

' Define the binding.
lstUser.DataSource = dsInternal
lstUser.DataMember = "Users"
lstUser.DataTextField = "Name"

As always, the last step is to activate the binding:


The final result is a list with the information from the specified database field, as shown in Figure 4. The list box will have an entry for every single record in the table, even if it appears more than once, from the first row to the last.

Figure 4. DataSet binding

8. Creating a Record Editor

The next example is more practical. It's a good example of how you might use data binding in a full ASP.NET application. This example allows the user to select a record and update one piece of information by using data-bound list controls.

The first step is to add the connection string to your web.config file. This example uses the Products table from the Northwind database included with many versions of SQL Server. Here's how you can define the connection string for SQL Server Express:

    <add name="Northwind" connectionString=
"Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />


To use the full version of SQL Server, remove the \SQLEXPRESS portion. To use a database server on another computer, supply the computer name for the Data Source connection string property. 

The next step is to retrieve the connection string and store it in a private variable in the Page class so that every part of your page code can access it easily. Once you've imported the System.Web.Configuration namespace, you can create a member variable in your code-behind class that's defined like this:

Private connectionString As String = _

The next step is to create a drop-down list that allows the user to choose a product for editing. The Page.Load event handler takes care of this task—retrieving the data, binding it to the drop-down list control, and then activating the binding. Before you go any further, make sure you've imported the System.Data.SqlClient namespace, which allows you to use the SQL Server provider to retrieve data.

Protected Sub Page_Load(ByVal sender As Object, _
  ByVal e As EventArgs) Handles Me.Load

    If Not Me.IsPostBack
        ' Define the ADO.NET objects for selecting products from the database.
        Dim selectSQL As String = "SELECT ProductName, ProductID FROM Products"
        Dim con As New SqlConnection(connectionString)
        Dim cmd As New SqlCommand(selectSQL, con)

        ' Open the connection.

        ' Define the binding.
        lstProduct.DataSource = cmd.ExecuteReader()
        lstProduct.DataTextField = "ProductName"
        lstProduct.DataValueField = "ProductID"

        ' Activate the binding.


        ' Make sure nothing is currently selected in the list box.
        lstProduct.SelectedIndex = −1
    End If
End Sub


Once again, the list is only filled the first time the page is requested (and stored in view state automatically). If the page is posted back, the list keeps its current entries. This reduces the amount of database work, and keeps the page working quickly and efficiently. You should also note that this page doesn't attempt to deal with errors.

The example uses a Select statement but carefully limits the returned information to just the ProductName and ProductID fields, which are the only pieces of information it will use. The resulting window lists all the products defined in the database, as shown in Figure 5.

Figure 5. Product choices

The drop-down list enables AutoPostBack, so as soon as the user makes a selection, a lstProduct.SelectedItemChanged event fires. At this point, your code performs the following tasks:

  • It reads the corresponding record from the Products table and displays additional information about it in a label. In this case, a Join query links information from the Products and Categories tables. The code also determines what the category is for the current product. This is the piece of information it will allow the user to change.

  • It reads the full list of CategoryNames from the Categories table and binds this information to a different list control. Initially, this list is hidden in a panel with its Visible property set to False. The code reveals the content of this panel by setting Visible to True.

  • It highlights the row in the category list that corresponds to the current product. For example, if the current product is a Seafood category, the Seafood entry in the list box will be selected.

The full listing is as follows:

Protected Sub lstProduct_SelectedIndexChanged(ByVal sender As Object, _
  ByVal e As EventArgs) Handles lstProduct.SelectedIndexChanged

    ' Create a command for selecting the matching product record.
    Dim selectProduct As String = "SELECT ProductName, QuantityPerUnit, " & _
     "CategoryName FROM Products INNER JOIN Categories ON " & _
     "Categories.CategoryID=Products.CategoryID " & _
     "WHERE ProductID=@ProductID"

    ' Create the Connection and Command objects.
    Dim con As New SqlConnection(connectionString)
    Dim cmdProducts As New SqlCommand(selectProduct, con)
    cmdProducts.Parameters.AddWithValue("@ProductID", _

    ' Retrieve the information for the selected product.
    Using con
        Dim reader As SqlDataReader = cmdProducts.ExecuteReader()

        ' Update the display.
        lblRecordInfo.Text = "<b>Product:</b> " & _
          reader("ProductName") & "<br />"
        lblRecordInfo.Text &= "<b>Quantity:</b> " & _
          reader("QuantityPerUnit") & "<br />"
        lblRecordInfo.Text &= "<b>Category:</b> " & reader("CategoryName")

        ' Store the corresponding CategoryName for future reference.
        Dim matchCategory As String = reader("CategoryName")

        ' Close the reader.

        ' Create a new Command for selecting categories.
        Dim selectCategory As String = "SELECT CategoryName, " & _


"CategoryID FROM Categories"
        Dim cmdCategories As New SqlCommand(selectCategory, con)

        ' Retrieve the category information, and bind it.
        lstCategory.DataSource = cmdCategories.ExecuteReader()
        lstCategory.DataTextField = "CategoryName"
        lstCategory.DataValueField = "CategoryID"

        ' Highlight the matching category in the list.
        lstCategory.Items.FindByText(matchCategory).Selected = True
    End Using

    pnlCategory.Visible = True
End Sub

You could improve this code in several ways. It probably makes the most sense to remove these data access routines from this event handler and put them into more generic functions. For example, you could use a function that accepts a ProductID and returns a single DataRow with the associated product information. Another improvement would be to use a stored procedure to retrieve this information.

The end result is a window that updates itself dynamically whenever a new product is selected, as shown in Figure 6.

Figure 6. Product information

This example still has one more trick in store. If the user selects a different category and clicks Update, the change is made in the database. Of course, this means creating new Connection and Command objects, as follows:

Protected Sub cmdUpdate_Click(ByVal sender As Object, _
  ByVal e As EventArgs) Handles cmdUpdate.Click

    ' Define the Command.
    Dim updateCommand As String = "UPDATE Products " & _
     "SET CategoryID=@CategoryID WHERE ProductID=@ProductID"

    Dim con As New SqlConnection(connectionString)
    Dim cmd As New SqlCommand(updateCommand, con)

    cmd.Parameters.AddWithValue("@CategoryID", lstCategory.SelectedItem.Value)
    cmd.Parameters.AddWithValue("@ProductID", lstProduct.SelectedItem.Value)

    ' Perform the update.
    Using con
    End Using
End Sub  

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone