Teach Yourself Visual Studio® .NET 2003 in 21 Days [Electronic resources] نسخه متنی

Using Databinding with DataSets

In Visual Basic 6, databinding means that you can get data from a database and bind that data to various controls. This is still true in Visual Studio .NET, except that binding data to controls has been taken to new heights.

Most controls that exists in the Toolbox can be bound to a data source, and the data source doesn't have to be a database. It can be an array, a Web service, an XML file, a resource filealmost anything. The nice thing is that the model for databinding is consistent no matter what the source of data is. You tell a control what property you want to bind, you pass the control a data source and, like magic, the information appears. This type of flexible binding makes it very easy to write 100% data-driven applications.

There are two ways to bind a control to a data source:

• From the DataBinding property in the Properties window of the control




• Writing code to implement databinding

Tomorrow, when you learn about the data wizards in Visual Studio .NET, you'll use the Properties window in Visual Studio .NET to specify databinding properties. Today, you learn how to databind a DataSet to a ComboBox control and a DataGrid control by using ADO.NET code.

Understanding Simple and Complex Binding

There are two types of binding scenarios:
simple binding and complex binding. Simple binding is when you set the DataSource property of a single control to a single field in a DataSet. Complex binding means you bind multiple rows and fields to a single control. Complex binding is most common in list controls and grid controls.

The following code snippet demonstrates how to implement simple databinding on a TextBox control. The DataBindings collection has methods such as Add that enable you to set what field you want to bind from a DataSet. After the databindings on a control are set, you don't have to worry about setting the Text property of the controlit is automatically filled by whatever the data happens to be in the DataSet.

' Create a connection
Dim cn As New SqlConnection( _
"Server=(local)\NetSDK;DataBase=northwind;" _
& "Integrated Security=SSPI")
' Create a new SqlDataAdapter and pass the SQL Statement
' that gets the ID and Name from the Authors table and connection object
Dim da As SqlDataAdapter = New SqlDataAdapter _
("SELECT * from Customers", cn)
' Create a new DataSet
Dim ds As DataSet = New DataSet("Customers")
' Call the Fill method to load the DataSet
da.Fill(ds, "Customers")
' Add DataBinding to the TextBoxes
TextBox2.DataBindings.Add("Text", ds.Tables("Customers"), "CompanyName")
TextBox3.DataBindings.Add("Text", ds.Tables("Customers"), "Address")
TextBox4.DataBindings.Add("Text", ds.Tables("Customers"), "City")

// Get the connection
SqlConnection cn = new SqlConnection
(@"Server=(local)\NetSDK;DataBase=northwind;Integrated Security=SSPI");
// Create a new SqlDataAdapter and pass the SQL Statement
// that gets the ID and Name from the Authors table and connection object
SqlDataAdapter adp = new SqlDataAdapter("SELECT * from Customers", cn);
// Create a new DataSet
DataSet ds = new DataSet("Customers");
// Call the Fill method to load the DataSet
adp.Fill(ds, "Customers");
// Add DataBinding to the TextBoxes
textBox2.DataBindings.Add("Text", ds.Tables["Customers"], "CompanyName");
textBox3.DataBindings.Add("Text", ds.Tables["Customers"], "Address");
textBox4.DataBindings.Add("Text", ds.Tables["Customers"], "City");

Because most controls have a Text property, including a Form and Label, you can quickly set up simple binding to display data from a database or other source.

To finish up the DataAccess project, you're going to implement complex binding on a ComboBox control and a DataGrid control.

When binding to a ComboBox, you set two properties: the DisplayMember and ValueMember. The DisplayMember is what actually displays in the ComboBox, whereas the ValueMember is an underlying unique value for the row being displayed. This is normally the primary key from the database.

For the Authors table, the primary key that uniquely differentiates each row is the au_id field. au_id would be the ValueMember field. To write the binding code for the DataAccess application, double-click the DataBind with DataSet button to get to the BindWithDataSet event in the code window. Listing 10.7 is the code that handles the click event for this button.

Listing 10.7 BindWithDataSet Click Event Code

Private Sub BindWithDataSet_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles BindWithDataSet.Click
' Create a connection
Dim cn As New SqlConnection( _
"Server=(local)\NetSDK;DataBase=pubs;" _
& "Integrated Security=SSPI")
' Create a new SqlDataAdapter and pass the SQL Statement
' that gets the ID and Name from the Authors table and connection object
Dim da As SqlDataAdapter = New SqlDataAdapter _
("SELECT au_id, au_lname + ', ' + au_fname As FullName FROM Authors", cn)
' Create a new DataSet
Dim ds As DataSet = New DataSet("Authors")
' Call the Fill method to load the DataSet
da.Fill(ds, "Authors")
With ComboBox1
' Set the DataSource property of the ComboBox
' to the DataSet
.DataSource = ds.Tables("Authors")
' Set the DisplayMember, this will be what is loaded
' into the ComboBox and is visible to the user
.DisplayMember = "FullName"
' Set the ValueMember, this is to associate the
' display data with a unique ID that represents this row in the
' the DataSet
.ValueMember = "au_id"
End With
End Sub

private void BindWithDataSet_Click(object sender, System.EventArgs e)
{
// Clear the items
comboBox1.Items.Clear();
// Get the connection
SqlConnection cn = new SqlConnection
(@"Server=(local)\NetSDK;DataBase=pubs;Integrated Security=SSPI");
/* Create a new SqlDataAdapter object.
The overloaded constructor allows you to set the SQL Statement
and the connection object or connection string at the time
you create the SqlDataAdapter object */
SqlDataAdapter da = new SqlDataAdapter
("SELECT au_id, au_lname + ', ' + au_fname As
FullName FROM Authors", cn);
// Create a new DataSet to hold the data from the SqlDataAdapter
DataSet ds = new DataSet("Authors");
da.Fill(ds, "Authors");
// Set the DataSource property of the ComboBox
// to the DataSet
comboBox1.DataSource = ds.Tables["Authors"];
// Set the DisplayMember, this will be what is loaded
// into the ComboBox and is visible to the user
comboBox1.DisplayMember = "FullName";
// Set the ValueMember, this is to associate the
// display data with a unique ID that represents this row in the
// the DataSet
comboBox1.ValueMember = "au_id";
}

In each of the previous exercises, you wrote code to retrieve the information from the ListBox control and you displayed that in the Label controls. When you bind data to a control, the way you retrieve values from the bound list controls is slightly different from a nonbound list control. This is because the control can contain information for many fields, not just what's displayed. The following ComboBox1_SelectedIndexChanged code fills the labels with the DisplayMember and ValueMember of the currently selected item in the ComboBox.

To add the code in Listing 10.8, double-click the ComboBox1 control to get the ComboBox1_SelectedIndexChanged event.

Listing 10.8 Retrieving Values from a Bound ComboBox Control

Private Sub ComboBox1_SelectedIndexChanged( _
ByVal sender As Object, _
ByVal e As System.EventArgs) _
Handles ComboBox1.SelectedIndexChanged
' Display where the event came from
Label1.Text = "ComboBox1_SelectedIndexChanged DataBound"
' Display the selected item
Label2.Text = ComboBox1.Text
' Display the Value member
Dim itm As DataRowView
itm = ComboBox1.SelectedItem
Label3.Text = CType(itm("au_id"), String)
End Sub

If you run the application by pressing F5 and you click on the DataBind with DataSet button, you should see something similar to Figure 10.7.

When you click the DataBind with DataSet button, the ComboBox1 control is filled with the authors' names, which is the DisplayMember property, and the SelectedIndexChanged event of the control retrieves the au_id field stored in the ValueMember property. With only a few lines of code, you simplified your development by binding data directly from the DataSet object.