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

Using DataAdapters and DataSets

If you need more flexibility Figure 10.2, you saw where the DataSet fits in a disconnected data access scenario. The DataSet itself doesn't connect to a database; it simply holds data and table information in its DataTables collection. To get data into a DataSet, you use a DataAdapter. The DataAdapter supplies the methods and properties to connect to a database, retrieve data, and populate the DataSet with that data. There are two key methods of the DataAdapter object: Fill and Update. The Fill method takes a DataSet and DataTable parameter to fill the records from a Command object's SQL statement. You still use a Command object when dealing with DataAdaptersthat remains constant when doing any type of data access. The Command object always contains the SQL statements that interact with the data source.

When accessing SQL Server, you should use the SqlDataAdapter class, and when accessing other data sources, use the OleDbDataAdapter class.

To see how this works, the following code snippet uses DataAdapter, Command, and DataSet objects to retrieve data from the Authors table. This code assumes the existence of a valid connection named cn.

' Create a new SqlDataAdapter object.
Dim da As SqlDataAdapter = New SqlDataAdapter()
' Create a Command Object and pass a valid Connection object
Dim cmd As New SqlCommand("Select * from Authors", cn)
' Set the SelectCommand property to the Command object
da.SelectCommand = cmd
' Create a new DataSet to hold the data from the SqlDataAdapter
Dim ds As DataSet = New DataSet()
' Call the Fill method to execute the Command
' and load the data into the DataSet.
da.Fill(ds, "Authors")

// Create a new SqlDataAdapter object.
SqlDataAdapter da = new SqlDataAdapter();
// Create a Command Object and pass a valid Connection object
SqlCommand cmd = new SqlCommand("Select * from Authors", cn);
// Set the SelectCommand property to the Command object
da.SelectCommand = cmd;
// Create a new DataSet to hold the data from the SqlDataAdapter
DataSet ds = new DataSet();
// Call the Fill method to execute the Command
// and load the data into the DataSet.
da.Fill(ds, "Authors");

The Fill method of the DataAdapter class actually does all the work. When the method is called, it refreshes the data in the DataSet based on the Command object's SQL statement. Although the code assumes there's a valid Connection object named cn, there's no code to call the Open method on the Connection object. A nice feature of the DataAdapter class is it checks to see whether a connection is open and, if it isn't, it opens one for you implicitly. After the Fill method is completed, the connection is closed automatically for you.

The Update method of the DataAdapter object, which you learn about tomorrow, compares original values in the DataSet against what the user modified and sends those records back to the database. The UpdateCommand and DeleteCommand properties on the DataAdapter handle this work. Through UpdateCommand and DeleteCommand, the DataAdapter supports batch updates. Batch updating means you can be disconnected for a long period of time and successfully update multiple rows and tables with a single Update call.

Note

When you learn about the visual database tools in Visual Studio .NET tomorrow, you'll learn more about the various methods for updating data using a DataSet and DataAdapter. Today, we're just going to cover how to retrieve data from a DataSet, how to loop through the collection or rows that are returned, and how to bind data to Windows Forms.

The constructor for the DataAdapter is overloaded, so you can pass the SQL statement directly when creating the new DataAdapter instance. The following Visual Basic .NET code creates a SqlDataAdapter and passes a query and Connection object all at once.

Dim da As SqlDataAdapter = New SqlDataAdapter
("SELECT * from Customers", cn)

Now that you have a basic understanding of what a DataSet and DataAdapter can do, you can continue working on the DataAccess application you started earlier.

Writing Data Access Code with a DataAdapter

You're now going to write the code for the Using a DataSet button. What you write here is very similar to the code snippet you saw earlier that demonstrated the general syntax of using a DataAdapter, but now you're going to iterate through the data you retrieve from SQL Server and load it into the CheckedListBox1 control. Double-click the Using a DataSet button on the default Form1 to get to the code window, and add the code in Listing 10.5 to the UseDataSet_Click event of the form.

Listing 10.5 UseDataSet_Click Event Code

Private Sub UseDataSet_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles UseDataSet.Click
' Create a connection
Dim cn As 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
Dim da As SqlDataAdapter = 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
Dim ds As DataSet = New DataSet("Authors")
da.Fill(ds, "Authors")
' Create a DataRow object
Dim dr As DataRow
' Loop thru the table rows and add the items
' in each dataset table row to the CheckedListBox
For Each dr In ds.Tables("Authors").Rows
CheckedListBox1.Items.Add(dr("FullName"))
Next
End Sub

private void UseDataSet_Click(object sender, System.EventArgs e)
{
// Clear the items
checkedListBox1.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");
// Loop thru the table rows and add the items
// in each dataset table row to the CheckedListBox
foreach (DataRow dr in ds.Tables["Authors"].Rows)
{
checkedListBox1.Items.Add(dr["FullName"]);
}
}

Next, you need to write the code for the CheckedListBox_SelectedIndexChanged event. This code is similar to the code you wrote for the ListBox1_SelectedIndexChanged event with the exception of using the GetItemChecked method of the CheckListBox control to determine whether an item is checked. Double-click the CheckListBox control and add the code in Listing 10.6 for the control's SelectedIndexChanged event.

Listing 10.6 SelectedIndexChanged Code for the CheckedListBox Control

Private Sub CheckedListBox1_SelectedIndexChanged _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles CheckedListBox1.SelectedIndexChanged
' Display where the event came from
Label1.Text = "CheckedListBox1_SelectedIndexChanged"
' Display the selected item
Label2.Text = CheckedListBox1.Items(CheckedListBox1.SelectedIndex)
' Display if the item is checked or not
Label3.Text = "Checked = " & CheckedListBox1.GetItemChecked _
(CheckedListBox1.SelectedIndex)
End Sub

private void checkedListBox1_SelectedIndexChanged(object sender,
System.EventArgs e)
{
// Display where the event came from
label1.Text = "CheckedListBox1_SelectedIndexChanged";
// Display the selected item
label2.Text = checkedListBox1.Text;
// Display if the item is checked or not
label3.Text = "Checked = " +
checkedListBox1.GetItemChecked(checkedListBox1.SelectedIndex);
}

If you press F5 to run the project and click the Using a DataSet button, you should see something similar to Figure 10.5.

In the UseDataSet_Click event, you created a SqlDataAdapter and passed the ad hoc select statement and Connection object to its overloaded constructor. You then created a new DataSet object, and called the Fill method of the SqlDataAdapter to hold the data from the database. The DataTable in this case is called Authors, and the second parameter of the Fill method is the name of the DataTable. This can be any friendly name that you choose. The idea behind the DataTable is that a DataSet can hold multiple sets of records from multiple data sources, and you need a way to reference the DataTables in the DataSet container in your code. The For Each loop uses a DataRow variable to loop through the collection of DataRows in the DataTable and adds the row item FullName to the CheckedListBox control.

It sounds like a lot to remember, but remember these bullets and you'll always know what to do with a DataSet:

• DataSets contain DataTable objects from one or more data sources.




• The DataTables collection in the DataSet has a DataRows collection.




• You can use any number of looping techniques to iterate through the collection of DataRows in a DataTable that are in a DataSet.

You can see by my description that a hierarchy exists in the DataSet. There are more than tables and rows; there are also columns, constraints, and relationships. Figure 10.6 gives you an idea of the hierarchy of objects in a DataSet.

Next, you need to see how you can use databinding with a DataSet to make your life even easier.