Section 5.1. Bind to Data Without Writing Code | Visual C# 2005: A Developers Notebook

5.1. Bind to Data Without Writing Code

In .NET 2.0 you write far less code to set up database interactions, but you do not sacrifice control when creating, displaying, updating, or deleting data. In fact, you can create a very powerful sorted, paged grid of data from your database with virtually no code at all.

5.1.1. How do I do that?

Tip: You will need to have Northwind installed on your database to do the labs in this chapter. If you have installed SQL Server (Developer or Production version) or Access, you already have Northwind. If you have SQL Server Express, download the appropriate setup files from the Microsoft web site (http://www.microsoft.com).

First, create a new web application and name it DataBindingNoCode. Click the Data tab of the Toolbox and drag a GridView control onto the default web form, Default.aspx.

Your first job is to set up the database connection. You can do this in a number of ways, but it's easiest to click the smart tag and drop down the Choose Data Source list. Click <New data source...>, as shown in Figure 5-1.

Figure 5-1. Creating a new data source

Note: With .NET 2.0 you can create complex database connections without writing code.

When you click <New data source...> the Data Source Configuration Wizard opens. For this lab, click Database and then click OK, as shown in Figure 5-2.

Figure 5-2. The Data Source Configuration Wizard

The wizard will bring you to the Configure Data Source dialog. Assuming you do not already have a connection to the database with Northwind, click the New Connection... button, as shown in Figure 5-3.

Figure 5-3. Choosing New Connection

This will bring you to the Add Connection step, as shown in Figure 5-4, where you can click the drop down to select the server you want to use, set the password, and then choose the Northwind database. Be sure to click Test Connection to test that you've set it all up properly.

Figure 5-4. Testing the new Northwind connection

Once the connection is working, click OK and you'll return to the Data Source Configuration Wizard. You should see your connection string in the "Connection string" box (indicating a trusted connection if you used Windows authentication, or your username and password otherwise). Click Next.

Now you are offered the opportunity to store the connection string in your web site's configuration file. This will add a section in Web.config, and even better, the configuration will be encrypted. Check the checkbox and name your configuration string.

The next step is to configure your select statement. You can specify columns from a table or view, or you can specify a custom SQL statement or name a stored procedure. For this simple example, drop the Name drop down to Customers and then check the checkbox for every column except ContactTitle.

Notice the buttons for adding a where clause and an order by clause. Click the Advanced Options button, and the Advanced SQL Generation Options dialog check the checkbox marked "Generate Insert, Update, and Delete statements." When you do, the "Use optimistic concurrency" option becomes available; check that as well. All of this is shown in Figure 5-5.

Figure 5-5. The Advanced SQL Generation Options dialog

The next dialog lets you test your query and see the results so that you can do a sanity check. If you are satisfied, click Finish.

Your GridView is instantly updated and a SqlDataSource control is added to your page. You can modify this data source control at any time by clicking the smart tag and choosing Configure Data Source.

Click the smart tag on the grid. Choose AutoFormat to pick a nice formatting schema. With the smart tag open, check all the checkboxes, turning on features such as paging and sorting that in previous versions you'd have had to write code for, as shown in Figure 5-6.

Figure 5-6. Turning on paging, etc.

Click the Source tab and look at the GridView that has been updated for you, as shown in Example 5-1.

Note: Use Windows authentication for a trusted connection.Optimistic concurrency does not lock records; it adds a where clause to updates and deletes to ensure that the data has not changed since you read it. This is the preferred method for handling concurrency issues.

Example 5-1. Updated GridView

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs"  Inherits="Default_aspx" %>      <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/ DTD/xhtml11.dtd">      <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server">     <title>Untitled Page</title> </head> <body>     <form  runat="server">     <div>         <asp:GridView          AllowPaging="True"          AllowSorting="True"          BackColor="#CCCCCC" BorderColor="#999999"         BorderStyle="Solid" BorderWidth="3px"          CellPadding="4"          CellSpacing="2" DataSource         ForeColor="Black"  runat="server">             <FooterStyle BackColor="#CCCCCC" />             <Columns>               <asp:CommandField ShowDeleteButton="True"  ShowEditButton="True" ShowSelectButton= "True">                 </asp:CommandField>             </Columns>             <RowStyle BackColor="White" />             <PagerStyle BackColor="#CCCCCC" ForeColor="Black"   HorizontalAlign="Left" />             <SelectedRowStyle BackColor="#000099" Font-Bold="True"  ForeColor="White" />             <HeaderStyle BackColor="Black" Font-Bold="True"   ForeColor="White" />             <EditRowStyle Font-Bold="False" Font-Italic="False" />         </asp:GridView>         <asp:SqlDataSource          DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @original_CustomerID  AND [CompanyName] = @original_CompanyName AND [ContactName] = @original_ContactName  AND [ContactTitle] = @original_ContactTitle AND [Address] = @original_Address AND  [City] = @original_City AND [Region] = @original_Region AND [PostalCode] =  @original_PostalCode AND [Country] = @original_Country AND [Phone] = @original_Phone  AND [Fax] = @original_Fax"          runat="server"          InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName],  [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country],  [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address,  @City, @Region, @PostalCode, @Country, @Phone, @Fax)"         SelectCommand="SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle],  [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax] FROM [Customers]"         UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName]  = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City,  [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,  [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID AND [CompanyName] =  @original_CompanyName AND [ContactName] = @original_ContactName AND [ContactTitle] =  @original_ContactTitle AND [Address] = @original_Address AND [City] = @original_City  AND [Region] = @original_Region AND [PostalCode] = @original_PostalCode AND [Country]  = @original_Country AND [Phone] = @original_Phone AND [Fax] = @original_Fax"  ConflictDetection="CompareAllValues" ConnectionString="<%$  ConnectionStrings:NorthwindConnectionString %>">             <DeleteParameters>              <asp:Parameter Name="original_CustomerID" Type="String" />              <asp:Parameter Name="original_CompanyName" Type="String" />              <asp:Parameter Name="original_ContactName" Type="String" />              <asp:Parameter Name="original_ContactTitle" Type="String" />              <asp:Parameter Name="original_Address" Type="String" />              <asp:Parameter Name="original_City" Type="String" />              <asp:Parameter Name="original_Region" Type="String" />              <asp:Parameter Name="original_PostalCode" Type="String" />              <asp:Parameter Name="original_Country" Type="String" />              <asp:Parameter Name="original_Phone" Type="String" />              <asp:Parameter Name="original_Fax" Type="String" />          </DeleteParameters>          <UpdateParameters>              <asp:Parameter Name="CompanyName" Type="String" />              <asp:Parameter Name="ContactName" Type="String" />              <asp:Parameter Name="ContactTitle" Type="String" />              <asp:Parameter Name="Address" Type="String" />              <asp:Parameter Name="City" Type="String" />              <asp:Parameter Name="Region" Type="String" />              <asp:Parameter Name="PostalCode" Type="String" />              <asp:Parameter Name="Country" Type="String" />              <asp:Parameter Name="Phone" Type="String" />              <asp:Parameter Name="Fax" Type="String" />              <asp:Parameter Name="original_CustomerID" Type="String" />              <asp:Parameter Name="original_CompanyName" Type="String" />              <asp:Parameter Name="original_ContactName" Type="String" />              <asp:Parameter Name="original_ContactTitle" Type="String" />              <asp:Parameter Name="original_Address" Type="String" />              <asp:Parameter Name="original_City" Type="String" />              <asp:Parameter Name="original_Region" Type="String" />              <asp:Parameter Name="original_PostalCode" Type="String" />              <asp:Parameter Name="original_Country" Type="String" />              <asp:Parameter Name="original_Phone" Type="String" />              <asp:Parameter Name="original_Fax" Type="String" />          </UpdateParameters>          <InsertParameters>              <asp:Parameter Name="CustomerID" Type="String" />              <asp:Parameter Name="CompanyName" Type="String" />              <asp:Parameter Name="ContactName" Type="String" />              <asp:Parameter Name="ContactTitle" Type="String" />              <asp:Parameter Name="Address" Type="String" />              <asp:Parameter Name="City" Type="String" />              <asp:Parameter Name="Region" Type="String" />              <asp:Parameter Name="PostalCode" Type="String" />              <asp:Parameter Name="Country" Type="String" />              <asp:Parameter Name="Phone" Type="String" />              <asp:Parameter Name="Fax" Type="String" />             </InsertParameters>         </asp:SqlDataSource>          </div>     </form> </body> </html>

Before going any further, run the application to see the effect of the changes you've made.

Next, click the smart tag on the DataGrid and click the Edit Columns link to open the Fields dialog, as shown in Figure 5-7.

Figure 5-7. The Fields dialog

You have incredible control here. Start in the Command Field Properties window. Notice that ButtonType is set to Link; change it to Button (this changes the update, delete, etc., commands from links to buttons). Scroll down in the Command Field Properties window. You'll see that you can change the text for each button and you can choose to show or hide each command button.

Uncheck the Auto-Generate Fields checkbox. Click each bound field, and click Add to add them to the Selected Fields box (or just double-click the field). As you do, you'll see their properties in the Bound Field Properties window, where you can change the header text (For example, from CompanyName to Company).

When you click OK your changes are reflected in the GridView Designer, as shown in Figure 5-8.

Figure 5-8. Changes reflected in the GridView Designer

5.1.2. What about...

...interacting with the updating GridView? Can I do that even though I created all of this declaratively?

Yes, you can. GridView has a number of events that fire as data is added, updated, etc.

For example, click the GridView control, then click the Events button in the properties window and double-click RowUpdating. This will generate the GridView1_RowUpdating event handler. Try adding this code:

void GridView1_RowUpdating(object sender,   GridViewUpdateEventArgs e) {    string updateCmd = this.SqlDataSource1.UpdateCommand; }

Put a break point on this line of code; run the application, edit the first entry, and change the name to Jesse Liberty.

When you get to the break point press F10 to load the updateCmd string. Drag the string and the GridViewUpdateEventArgs object (e) into the Watch Window.

Tip: You can hover your mouse cursor over an object and a Debugger tool tip will appear with the contents that would be shown in the Watch Window.

The updateCmd string holds the raw SQL statement:

"UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] =  @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City,  [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone,  [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID"

Where do the substitution values come from? Open e. It contains two interesting collections: NewValues and OldValues. Open NewValues, then open KeyedList and then Values. The second value should be the new value you entered, as shown in Figure 5-9.

Figure 5-9. New values to update the database

Examine the SQL statement. To avoid concurrency issues, the WHERE clause checks to make sure the table has not changed; to do so it needs the original values. You'll find these listed in the OldValues collection, as shown in Figure 5-10.

Figure 5-10. Old values to avoid concurrency issues

...what if I want to create these objects programmatically?

No problem. You can instantiate a GridView and/or a DataSourceControl at runtime.

You can create a DataSourceControl using any of its three overloaded constructors (the default constructor, a constructor taking a connection and select strings, or a constructor taking a provider name, a connection string, and a select command string).

You also can create a Parameter object for use with the DataSourceControl, which in turn has four overloaded constructors, allowing you optionally to pass in the name of the parameter, its type, and its default value.

5.1.3. Where can I learn more?

For more on this topic, see my article "ASP.NET 2.0 Data Binding Controls" on the ONDotnet site (http://www.ondotnet.com). Also see the article "ASP.NET 2.0 and Data-Bound Controls: A New Perspective" by Dino Esposito in the MSDN Library.