Lesson 2: Using Data Sets on Web Forms

Lesson 2: Using Data Sets on Web Forms

Most ASP.NET server controls support data binding, which is a way to link data in your application, such as data sets, to properties of a control. In particular, the DataGrid and DataList server controls are specifically designed to make displaying data sets on a Web form easy and efficient.

In this lesson, you ll learn how to display a data set on a Web form using the DataGrid, DataList, and other server controls. You ll learn how to select records from a data set on a Web form and how to perform SQL commands on a database from control event procedures.

After this lesson, you will be able to

• Quickly display a data set on a Web form through data binding

• Format the data displayed in a DataGrid, DataList, or other data-bound server control

• Display data from a data set in a server control using code rather than data binding

• Get records from a data set displayed on a Web form

• Perform SQL commands on a database to return values or to modify data directly

Estimated lesson time: 30 minutes

Displaying a Data Set in a DataGrid Control

The simplest way to display a data set on a Web form is through a DataGrid control using data binding.

To display a data set in this way, follow these steps:

1. Create the database connection, adapter, and data set objects as described in the previous lesson.

2. Add a DataGrid control to the Web form.

3. Set the DataSource property of the DataGrid control to the name of the data set.

4. Add code to the Web form s Page_Load event procedure to fill the data set from the adapter and to bind the data from the data set to the DataGrid control.

For example, the following event procedure creates a data set from the Contacts database s Contacts table and then displays the data in a DataGrid control:

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Fill the data set. adptContacts.Fill(dsContacts1) ' Bind the data to the DataGrid control. grdContacts.DataBind() End Sub

Visual C#

private void Page_Load(object sender, System.EventArgs e) { // Fill the data set. adptContacts.Fill(dsContacts1); // Bind the data to the DataGrid control. grdContacts.DataBind(); }

This procedure and code displays all of the columns in the data set without formatting, as shown in Figure 5-16.

Figure 5-16. The DataGrid control without formatting

This is generally not what you want. For instance, the Birthdate column (not shown in Figure 5-16) includes a time as well as the date.

To select the columns you want to display and to format the data in the DataGrid control, follow these steps:

1. Click the Property Builder link in the Properties window. Visual Studio displays the Properties dialog box for the DataGrid control.

2. Select the Columns item on the left side of the Properties dialog box to select and format the columns of data displayed from the data set. Visual Studio displays the columns properties, as shown in Figure 5-17.

   

   Figure 5-17. Specifying columns and formatting for a DataGrid control

To display specific columns from the data set, follow these steps:

1. Clear the Create Columns Automatically At Run Time check box. This limits the columns in the DataGrid control to only the ones you select.

2. Select the column in the Available Columns list, and click the Add (>) button to add the column to the Selected Columns list.

To format data in a column, follow these steps:

1. Select the column to format in the Selected Columns list.

2. Type the format expression in the Data Formatting Expression box. Data formatting expressions take the form:

{0:formattingexpression}

where formattingexpression is one of the predefined Microsoft .NET Framework format expressions, as described in the Date and Time Format Strings, Numeric Format Strings, and Custom Format Strings topics in the Visual Studio online Help.

You use the DataGrid Properties dialog box to specify all aspects of the appearance and behavior of the DataGrid, including formatting the column headings, specifying how the grid scrolls (pages) through records, specifying whether borders or rules are displayed, and much more.

Figure 5-18 shows the Contacts data set displayed in a DataGrid control with formatting applied. The DataGrid control displays only four columns from the data set and formats the Birthdate column as a short date ({0:d}).

Figure 5-18. Selected columns from a data set with formatting applied

Displaying a Data Set in a DataList Control

Use the DataList control to display information from a data set as a list of rows, rather than rows and columns as in a DataGrid control.

To display a data set in a DataList control, follow these steps:

1. Create the database connection, adapter, and data set objects as described in the previous lesson.

2. Add a DataList control to the Web form.

3. Set the DataSource property of the DataGrid control to the name of the data set.

4. Add code to the Web form s Page_Load event procedure to fill the data set from the adapter and to bind the data from the data set to the DataGrid control.

5. Edit the DataList control s header, item, and separator templates to create the appearance of the DataList.

The DataList control uses templates to determine the content of its headers, footers, and rows. You edit these templates to add literals and controls that bind to items in the data set. The DataList control provides three different categories of templates to control the different aspects of its appearance:

• Header and footer templates

  These include the title and rule lines that appear at the top and the bottom of the DataList control.

• Items templates

  These determine the contents of the rows in the data list and allow you to alternate the appearance of odd, even, selected, or edited rows.

• Separator templates

  These add rule lines or other separators between rows.

To edit a DataList template:

1. Right-click the DataList control, point to Edit Template, and then select the template to edit from the shortcut menu.

2. The DataList control changes its appearance when it is in Edit mode, as shown in Figure 5-19. Type the text you want to appear in the template and add controls as desired.

   

   Figure 5-19. DataList control in template Edit mode

3. To display an item from the data set in the template, add a control to the template, and then click the DataBindings property for the control. Click the ellipsis button to the right of the DataBindings property. Visual Studio displays the DataBindings dialog box for the control, as shown in Figure 5-20.

   

   Figure 5-20. DataBindings dialog box

4. When you have finished editing the DataList control s templates, right-click the DataList control and select End Template Editing from the shortcut menu.

Data binding for controls in the DataList control s Items template can be a simple binding to an item from the container, as shown in Figure 5-20, or it can be a more complex expression using the Eval method. For example, the following custom binding expression combines first name and last name information in a single control:

Visual Basic .NET

DataBinder.Eval(Container, "DataItem.FirstName") & " " & _ DataBinder.Eval(Container, "DataItem.LastName")

Visual C#

DataBinder.Eval(Container, "DataItem.FirstName") + " " + DataBinder.Eval(Container, "DataItem.LastName");

The DataBinder object s Eval method is actually what is generated by Visual Studio in the Web form s HTML when you create a DataList control template with data binding. It is sometimes easier to edit templates directly in HTML than through the edit template procedure described previously.

To edit a DataList template directly in HTML, right-click the Web form and select View HTML Source from the shortcut menu. Visual Studio displays the Web form in HTML mode.

For example, the following HTML displays a DataList control that contains name, birthday, and telephone information from the Contacts data set:

<asp:DataList  runat="server" DataSource="<%# dsContacts1 %>"> <HeaderTemplate> <h2> Contact Information <HR width="100%" SIZE="1"> </h2> </HeaderTemplate> <FooterTemplate> <HR> </FooterTemplate> <ItemTemplate> <asp:Label  runat="server"  Text='<%# DataBinder.Eval(Container, "DataItem.FirstName") + " "  + DataBinder.Eval(Container,"DataItem.LastName") %>'> </asp:Label> &nbsp;Birthdate: <asp:Label  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.Birthdate",  "{0:d}") %>'> </asp:Label> &nbsp;Work phone: <asp:Label  runat="server"  Text='<%# DataBinder.Eval(Container, "DataItem.WorkPhone") %>'> </asp:Label> </ItemTemplate> <SeparatorTemplate> <HR> </SeparatorTemplate> </asp:DataList>

To display the data set in the preceding control, fill the data set from the data adapter and bind to the DataList control, as shown by the following Page_Load event procedure:

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Fill the data set. adptContacts.Fill(dsContacts1) ' Bind the data to the DataList control dlstContacts.DataBind() End Sub

Visual C#

private void Page_Load(object sender, System.EventArgs e) { // Fill the data set. adptContacts.Fill(dsContacts1); // Bind the data to the DataList control. dlstContacts.DataBind(); }

At run time, the preceding DataList control displays the header, items, and separators, as shown in Figure 5-21.

Figure 5-21. DataList control with data binding at run time

Displaying Data Items in Other List Controls

Of course, you can bind the items from a data set to any list control on a Web form. The DataGrid and DataList controls are just two of the most versatile ways to display a data set.

To display items from a data set in a ListBox, DropDownList, CheckBoxList, or RadioButtonList control, follow these steps:

1. Set the control s DataSource property to the name of the data set.

2. Set the control s DataText property to the data set member to display as the Text property of the list item.

3. Set the control s DataValue property to the data set member to return as the Value property of the list item.

4. In code, fill the data set from data adapter and bind to the control.

The following HTML shows the property settings for a DropDownList control that is bound to the Contacts data set:

<asp:DropDownList  runat="server" Width="384px" Height="22px"  DataSource="<%# dsContacts1 %>" DataTextField='LastName' DataValueField="ContactID"></asp:DropDownList>

The following code fills the data set and binds the data to the DropDownList control:

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load If Not IsPostBack Then ' Fill the data set. adptContacts.Fill(dsContacts1) ' Bind the data to the control. drpContacts.DataBind() End If End Sub

Visual C#

private void Page_Load(object sender, System.EventArgs e) { if (!IsPostBack) { // Fill the data set. adptContacts.Fill(dsContacts1); // Bind the data to the DataList control. drpContacts.DataBind(); } }

At run time, the DropDownList control displays a list of the last names from the Contacts table, and the SelectedItem.Value property returns the ContactID, which is useful for looking up other information about the individual.

The limitation here is that you can include only one value for the list control s DataText and DataValue properties. If you want to include the first and last names of the contact in the DropDownList control, you have to do it from code. The following Page_Load event procedure adds the first and last names to the DropDownList control and includes the ContactID as the Value property for each list item:

Visual Basic .NET

Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Run first time page is displayed. If Not IsPostBack Then ' Fill the Contacts data set. adptContacts.Fill(dsContacts1) ' For each row in the table... Dim drowItem As dsContacts.ContactsRow For Each drowItem In dsContacts1.Contacts ' Create a new list item. Dim lstNew As New ListItem lstNew.Text = drowItem.FirstName & " " & drowItem.LastName lstNew.Value = drowItem.ContactID ' Add the list item to the drop-down list. drpContacts.Items.Add(lstNew) Next End If End Sub

Visual C#

private void Page_Load(object sender, System.EventArgs e) { // Run first time page is displayed. if (!IsPostBack) { // Fill the Contacts data set. adptContacts.Fill(dsContacts1); // For each row in the table... foreach (dsContacts.ContactsRow drowItem in dsContacts.Contacts) { // Create a new list item. ListItem lstNew = new ListItem(); lstNew.Text = drowItem.FirstName + " " + drowItem.LastName; lstNew.Value = drowItem.ContactID.ToString(); // Add the list item to the drop-down list. drpContacts.Items.Add(lstNew); } } }

Selecting Specific Records

The DropDownList control created in the preceding section is useful for selecting items from the Calls table in the Contacts database. Because ContactID is a unique key in the database, you can use it to select the Call records for a specific contact. The following code builds a data set containing the calls for the contact selected from the DropDownList control:

Visual Basic .NET

Private Sub drpContacts_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles drpContacts.SelectedIndexChanged ' When a contact is selected, display the contact's calls. adptCalls.SelectCommand.CommandText = "SELECT * FROM Calls" & _  " WHERE ContactID =" & drpContacts.SelectedItem.Value adptCalls.Fill(dsCalls1) If dsCalls1.Tables("Calls").Rows.Count > 0 Then ' Display the results in a data grid. grdCalls.DataBind() grdCalls.Visible = True lblMsg.Text = "" Else grdCalls.Visible = False lblMsg.Text = drpContacts.SelectedItem.Text + " has no calls." End If End Sub

Visual C#

private void drpContacts_SelectedIndexChanged(object sender, EventArgs e) { // When a contact is selected, display the contact's calls. adptCalls.SelectCommand.CommandText = "SELECT * FROM Calls" +  " WHERE ContactID =" + drpContacts.SelectedItem.Value.ToString(); adptCalls.Fill(dsCalls1); if (dsCalls1.Tables["Calls"].Rows.Count > 0) { // Display the results in a data grid. grdCalls.DataBind(); grdCalls.Visible = true; lblMsg.Text = ""; } else { grdCalls.Visible = false; lblMsg.Text = drpContacts.SelectedItem.Text + " has no calls."; } }

At run time, the Web form displays a list of the calls for the contact selected in the DropDownList control, as shown in Figure 5-22.

Figure 5-22. Selecting Records with a DropDownList control

Executing Commands on a Database

In addition to working with data sets, you can perform commands directly on a database connection. The database connection object provides these three command methods:

• ExecuteScalar

  Performs query commands that return a single value, such as counting the number of records in a table.

• ExecuteNonQuery

  Performs commands that change the database but do not return a specific value, including adding and deleting items from a database. The ExecuteNonQuery method returns the number of rows affected by the command.

• ExecuteReader

  Reads records sequentially from the database.

To use these methods, follow these steps:

1. Create a connection to the database.

2. Open the connection.

3. Create a command object containing the SQL command or stored procedure to execute.

4. Execute the method on the command object.

5. Close the database connection.

Because any command that executes on a database has at least some potential to fail, you should always use exception handling to ensure that the database connection is closed whether or not the command succeeds. Calling the connection s Close method from a Finally/finally exception-handling clause ensures that the database connection is closed before the code continues.

Returning a Value from a Database

Use the ExecuteScalar method to execute a command that returns a single value from a database. For example, the following code executes a SQL MAX function to retrieve the highest number used for ContactID and then uses that value as the seed value for adding a new row to the Contacts table:

Visual Basic .NET

' Create a database connection. Dim connContacts As New SqlConnection("integrated security=SSPI;" + _  "data source=(local);initial catalog=Contacts") Private Sub butAdd_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butAdd.Click Try ' Create a SQL command to get a unique ContactID. Dim cmdNewID As New SqlCommand("SELECT MAX(ContactID)" & _  " FROM Contacts", connContacts) ' Open the database connection. connContacts.Open() ' Execute the SQL command. Dim intNextID As Integer = CInt(cmdNewID.ExecuteScalar()) + 1 ' Create a command to add a new row. Dim cmdAddRow As New SqlCommand(String.Format("INSERT INTO " + _  "Contacts(ContactID, FirstName, LastName, WorkPhone) " + _  "VALUES({0}, '{1}', '{2}', '{3}')", intNextID, txtFirstName.Text, _ txtLastName.Text, txtPhone.Text), connContacts) ' Execute the command. If cmdAddRow.ExecuteNonQuery Then lblMsg.Text = "Record added." Else lblMsg.Text = "Couldn't add record." End If Catch ex As Exception lblMsg.Text = "Couldn't access database due to this error: " + _ ex.Message Finally ' Close connection connContacts.Close() End Try End Sub

Visual C#

SqlConnection connContacts = new SqlConnection("integrated security=SSPI;" +  "data source=(local);initial catalog=Contacts"); private void butAdd_Click(object sender, System.EventArgs e) { try { // Create a SQL command to get a unique ContactID. SqlCommand cmdNewID =new SqlCommand("SELECT MAX(ContactID)" +  " FROM Contacts", connContacts); // Open the database connection. connContacts.Open(); // Execute the SQL command. int intNextID = (int)cmdNewID.ExecuteScalar() + 1; // Create a command to add a new row. SqlCommand cmdAddRow = new SqlCommand(String.Format("INSERT " +  "INTO Contacts(ContactID, FirstName, LastName, WorkPhone) " +  "VALUES({0}, '{1}', '{2}', '{3}')", intNextID, txtFirstName.Text, txtLastName.Text, txtPhone.Text), connContacts); // Execute the command. if (cmdAddRow.ExecuteNonQuery() > 0) { lblMsg.Text = "Record added."; // Database changed, so refresh list. RefreshList(); // Clear text boxes. ClearText(); } else { lblMsg.Text = "Couldn't add record."; } } catch (Exception ex) { lblMsg.Text = "Couldn't access database due to this error: " + ex.Message; } finally { // Close connection connContacts.Close(); } }

The ExecuteScalar method can be used with any SQL statement that returns a single value. To execute other types of SQL statements, use the ExecuteNonQuery or ExecuteReader method.

Changing Records Directly in a Database

The ExecuteNonQuery method performs commands that do not return a data set, such as SQL INSERT, DELETE, or UPDATE. For example, the following code uses ExecuteNonQuery to delete a row directly from the database:

Visual Basic .NET

' Create a database connection. Dim connContacts As New SqlConnection("integrated security=SSPI;" + _  "data source=(local);initial catalog=Contacts") Private Sub butDelete_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butDelete.Click Try ' Create a SQL command to delete record. Dim sqlDelete As New _ SqlCommand("DELETE FROM Contacts WHERE ContactRecord deleted." Else lblMsg.Text = "Couldn't find record to delete." End If Catch ex As Exception lblMsg.Text = "Couldn't access database due to this error: " + _ ex.Message Finally ' Close connection connContacts.Close() End Try End Sub

Visual C#

SqlConnection connContacts = new SqlConnection("integrated security=SSPI;" +  "data source=(local);initial catalog=Contacts"); private void butDelete_Click(object sender, System.EventArgs e) { try { // Create a SQL command to delete record. SqlCommand sqlDelete = new SqlCommand("DELETE FROM Contacts " +  "WHERE ContactRecord deleted."; // Database changed, so refresh list. RefreshList(); // Clear text boxes ClearText(); } else { lblMsg.Text = "Couldn't find record to delete."; } } catch (Exception ex) { lblMsg.Text = "Couldn't access database due to this error: " + ex.Message; } finally { // Close connection connContacts.Close(); } }

The ExecuteNonQuery method acts directly on the database connection it does not go through a data adapter or a data set. If you make changes to a table in the database through ExecuteNonQuery, you must update any data sets affected by those changes by calling the Fill method on the data adapter.

Retrieving Records Directly from the Database

The ExecuteReader method performs commands that return records, such as SQL SELECT. Each record is returned as a data reader object, which is sort of a read-only version of a data set. Because the ExecuteReader method acts directly on the database connection, there are two versions of the data reader object: OleDbDataReader and SqlDataReader.

Using ExecuteReader to create data reader objects provides better performance than creating a data set from a data adapter object, but it doesn t provide you with much flexibility. Data reader objects are read-only and they only read forward, one record at a time. Data sets allow you to get records in any order and, more important, allow you to write changes back to the database.

The following code gets all the contacts and displays their names in a drop-down list using a data reader object and the Execute reader command:

Visual Basic .NET

' Create a database connection. Dim connContacts As New SqlConnection("integrated security=SSPI;" + _  "data source=(local);initial catalog=Contacts") Private Sub Page_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Get values for drop-down list the first time ' the page is displayed. If Not IsPostBack Then RefreshList() End If End Sub ' Loads values from database into a drop-down list. Sub RefreshList() Try ' Clear the list drpContacts.Items.Clear() ' Create SQL command to get table. Dim cmdGetContacts As New SqlCommand("Select * From Contacts", _ connContacts) ' Open database connection if it is closed. If connContacts.State = ConnectionState.Closed Then _ connContacts.Open() ' Execute command. Dim readContacts As SqlDataReader = cmdGetContacts.ExecuteReader() ' Read the names and contact IDs into a drop-down list Do While readContacts.Read() ' Create a new list item. Dim NewItem As New ListItem ' Set the item's values. NewItem.Text = readContacts.GetString(1) + " " _ + readContacts.GetString(2) NewItem.Value = readContacts.GetValue(0) ' Add the new item to the list. drpContacts.Items.Add(NewItem) Loop Catch ex As Exception lblMsg.Text = "Couldn't access data due to this error: " + _ ex.Message Finally ' Close the connection. connContacts.Close() End Try End Sub

Visual C#

SqlConnection connContacts = new SqlConnection("integrated security=SSPI;" +  "data source=(local);initial catalog=Contacts"); private void Page_Load(object sender, System.EventArgs e) { // Get values for drop-down list the first time // the page is changed. if (!IsPostBack) { RefreshList(); } } private void RefreshList() { try { // Clear the list drpContacts.Items.Clear(); // Create SQL command to get table. SqlCommand cmdGetContacts = new SqlCommand("Select * " +  "From Contacts", connContacts); // Open database connection if it is closed. if (connContacts.State == ConnectionState.Closed) connContacts.Open(); // Execute command. SqlDataReader readContacts = cmdGetContacts.ExecuteReader(); // Read the names and contact IDs into a drop-down list while (readContacts.Read()) { // Create a new list item. ListItem NewItem = new ListItem(); // Set the item's values. NewItem.Text = readContacts.GetString(1) + " "  + readContacts.GetString(2); NewItem.Value = readContacts.GetValue(0).ToString(); // Add the new item to the list. drpContacts.Items.Add(NewItem); } } catch (Exception ex) { lblMsg.Text = "Couldn't access data due to this error: " + ex.Message; } finally { // Always close the connection. connContacts.Close(); } }

The data reader object is a read-forward set of records, so the Read method reads each subsequent line until it reaches the end of the record set. A reader object locks the database connection while it is executing, so you should call the reader object s Close method when you have finished getting records, as shown in the preceding code.

Executing Stored Procedures

One well-known performance tip when you re working with databases is to move frequently used tasks into stored procedures. Stored procedures execute within the context of the database manager and therefore can be optimized for ideal performance.

Use the ExecuteScalar, ExecuteNonQuery, or ExecuteReader method to run stored procedures. For example, the following code executes a stored procedure that returns the ten most expensive products in the Northwind Traders database.

Visual Basic .NET

Private Sub butExecute_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles butExecute.Click ' Create a connection for NorthWind Traders database. Dim connNWind As New SqlConnection("integrated security=SSPI;" + _  "data source=(local);initial catalog=Northwind") ' Create a command object to execute. Dim cmdTopTen As New SqlCommand("Ten Most Expensive Products", _ connNWind) ' Set the command properties. cmdTopTen.CommandType = CommandType.StoredProcedure ' Create a data reader object to get the results. Dim drdTopTen As SqlDataReader ' Open the connection. connNWind.Open() Try ' Excecute the stored procedure. drdTopTen = cmdTopTen.ExecuteReader() ' Display a header. litData.Text = "<h3>Ten Most Expensive Products:</h3>" ' Display the results on the page. Do While drdTopTen.Read() ' Create an array to receive data. Dim items() As Object = {"", "", "", "", "", ""} ' If the row contains items. If drdTopTen.GetValues(items) > 0 Then Dim item As Object ' Get each row item and add it to the literal control. For Each item In items litData.Text += item.ToString + " " Next ' Add a break between rows. litData.Text += "<br>" End If Loop Catch ex As Exception litData.Text = "The following error occurred: <br>" litData.Text += ex.Message Finally ' Close the connection. connNWind.Close() End Try End Sub

Visual C#

private void butExecute_Click(object sender, System.EventArgs e) { // Create a connection for NorthWind Traders database. SqlConnection connNWind = new SqlConnection("integrated " +  "security=SSPI;data source=(local);initial catalog=Northwind"); // Create a command object to execute. SqlCommand cmdTopTen = new SqlCommand("Ten Most Expensive Products", connNWind); // Set the command properties. cmdTopTen.CommandType = CommandType.StoredProcedure; // Create a data reader object to get the results. SqlDataReader drdTopTen; // Open the connection. connNWind.Open(); try { // Excecute the stored procedure. drdTopTen = cmdTopTen.ExecuteReader(); // Display a header. litData.Text = "<h3>Ten Most Expensive Products:</h3>"; // Display the results on the page. while (drdTopTen.Read()) { // Create an array to receive data. object[] items = {"", "", "", "", "", ""}; // If the row contains items. if (drdTopTen.GetValues(items) > 0) { // Add each row item to the literal control. foreach(object item in items) litData.Text += item.ToString() + " "; // Add a break between rows. litData.Text += "<br>"; } } } catch (Exception ex) { litData.Text = "The following error occurred: <br>"; litData.Text += ex.Message; } finally { // Close the connection. connNWind.Close(); } }