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:
First, create the DataSet.
Next, create a new DataTable, and add it to the DataSet.Tables collection.
Next, define the structure of the table by adding DataColumn objects (one for each field) to the DataTable.Colums collection.
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()
dsInternal.Tables.Add("Users")
' Define two columns for this table.
dsInternal.Tables("Users").Columns.Add("Name")
dsInternal.Tables("Users").Columns.Add("Country")
' Add some actual information into the table.
Dim rowNew As DataRow = dsInternal.Tables("Users").NewRow()
rowNew("Name") = "John"
rowNew("Country") = "Uganda"
dsInternal.Tables("Users").Rows.Add(rowNew)
rowNew = dsInternal.Tables("Users").NewRow()
rowNew("Name") = "Samantha"
rowNew("Country") = "Belgium"
dsInternal.Tables("Users").Rows.Add(rowNew)
rowNew = dsInternal.Tables("Users").NewRow()
rowNew("Name") = "Rico"
rowNew("Country") = "Japan"
dsInternal.Tables("Users").Rows.Add(rowNew)
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:
Me.DataBind()
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.
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:
<configuration>
<connectionStrings>
<add name="Northwind" connectionString=
"Data Source=localhost\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=SSPI" />
</connectionStrings>
...
</configuration>
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 = _
WebConfigurationManager.ConnectionStrings("Northwind").ConnectionString
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.
con.Open()
' Define the binding.
lstProduct.DataSource = cmd.ExecuteReader()
lstProduct.DataTextField = "ProductName"
lstProduct.DataValueField = "ProductID"
' Activate the binding.
Me.DataBind()
con.Close()
' 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.
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", _
lstProduct.SelectedItem.Value)
' Retrieve the information for the selected product.
Using con
con.Open()
Dim reader As SqlDataReader = cmdProducts.ExecuteReader()
reader.Read()
' 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.
reader.Close()
' 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"
lstCategory.DataBind()
' 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.
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
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
