O'Reilly logo

ASP.NET 2.0: A Developer's Notebook by Wei-Meng Lee

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 4. Data Access

Data access is one of the most common tasks that you're likely to perform when you write web applications. This is made evident by the number of new data controls that ship with Visual Studio 2005. Most visible in ASP.NET 2.0 is the new GridView control, which is a much improved version of the venerable DataGrid control (the older DataGrid control is still supported in ASP.NET 2.0, though). In addition, ASP.NET 2.0 ships with several new data source controls that make it easier to consume a variety of data sources.

Display Data in a Table

If you've done serious ASP.NET programming you've probably used the DataGrid control. DataGrid is a very powerful and flexible control for displaying structured data from a data source such as a database. However, when it comes to manipulating the content of a DataGrid control, such as editing the rows or simply sorting the columns of a table, you need to write a moderate amount of code to customize it.

This is where the GridView control comes in handy. Instead of requiring you to write code to perform such common functions as editing or displaying rows in multiple pages, the GridView control now accomplishes the same tasks as the DataGrid control, but with much less coding and work.

The GridView control can be found in the Toolbox under the Data tab (see Figure 4-1).

The GridView control

Figure 4-1. The GridView control

Note

Using the new GridView control, you can now do more with less. If you are a DataGrid die-hard, life will never be the same after using the GridView control!

How do I do that?

To see how powerful and configurable the GridView control is, let's create a page that contains a drop-down listbox and a GridView control. We'll use the pubs sample database (that comes with SQL Server 2000) together with SQL Server 2005 Express to populate the drop-down listbox with a list of the states where the authors in the database live. When a user selects a state, the GridView control will display the names of all the authors who live there.

Note

You can now sort rows of records based on field names and perform editing and deleting of records in the GridView control, all without writing any code.

Tip

SQL Server 2005 Express does not ship with the pubs and Northwind sample databases, but you can install them by downloading their installation scripts at http://www.microsoft.com/downloads/search.aspx?displaylang=en.

Once the scripts are installed on your system, go to the Visual Studio 2005 command prompt (Start Programs Microsoft Visual Studio 2005 Visual Studio Tools Visual Studio 2005 Command Prompt) and type in the following to install the pubs and Northwind databases (assuming your installation scripts are stored in C:\):

C:\>sqlcmd -S .\SQLEXPRESS -i instpubs.sqlC:\>sqlcmd -S .\SQLEXPRESS -i instnwnd.sql
            
  1. Launch Visual Studio 2005 and create a new web site project. Name the project C:\ASPNET20\Chap04-GridView.

  2. Drag and drop the DropDownList control from the Toolbox into the default Web Form.

  3. On the DropDownList Tasks menu, click the Choose Data Source... link to select a data source to populate the items in the control (see Figure 4-2).

    Configuring the DropDownList control

    Figure 4-2. Configuring the DropDownList control

  4. The Data Source Configuration Wizard dialog will appear. In the "Select a data source" drop-down list, select <New data source...> to create a new connection to a data source (see Figure 4-3).

    Creating a new data source

    Figure 4-3. Creating a new data source

  5. In the "Where will the application get data from" section, select Database and click OK (see Figure 4-4). Use the default name SqlDataSource1 as the ID for the data source.

    Selecting a new data source type

    Figure 4-4. Selecting a new data source type

    Tip

    The Database data source type can connect to any SQL database (such as Oracle), not just SQL Server.

    You use the SqlDataSource control to declaratively establish a connection to a SQL data source without writing any code. In the days of ASP.NET 1.x, you had to write elaborate code to access data sources using ADO.NET.

    In ASP.NET 2.0, you now have data source controls that encapsulate all the logic needed to access a data source. (I will discuss the rest of the data source controls throughout this chapter.)

  6. Click the New Connection... button to establish a connection to the database you want to use (see Figure 4-5).

    Creating a new data connection

    Figure 4-5. Creating a new data connection

  7. You will be asked to select the type of data source you want to connect to. Select SQL Server to connect to a SQL Server 2005 Express database (see Figure 4-6). Click Continue.

    Specifying the new data connection properties

    Figure 4-6. Specifying the new data connection properties

  8. Specify the database server information as well as the database to use (see Figure 4-7). For this example, use the pubs database. Click the Test Connection button to verify that the connection can be established. Click OK.

    Specifying the information for a database server

    Figure 4-7. Specifying the information for a database server

  9. You should now see the data connection that you have selected (winxp2\sqlexpress.pubs.dbo). Click Next (see Figure 4-8).

    The newly created database connection

    Figure 4-8. The newly created database connection

  10. You will be asked if you want to save the database connection in the application configuration file (Web.config). This is the preferred option because it allows you to modify your database server settings without modifying your code. This is especially useful after you have deployed your application. To save the connection string in Web.config, use the suggested pubsConnectionString and ensure that the "Yes, save this connection as:" checkbox is selected. Click Next (see Figure 4-9).

    Tip

    It is a good practice to save your connection string in the Web.config file so that any changes to the database can be modified easily.

    Saving the connection string in Web.config

    Figure 4-9. Saving the connection string in Web.config

  11. You can now select the database fields you want to use. You can either specify a custom SQL statement (or use a stored procedure), or make use of the checkboxes to select the fields you want. For this example, select the "Specify columns from a table or view" radio button and select the authors table. Select the state and "Return only unique rows" checkboxes. Click Next (see Figure 4-10).

    Configuring the Select statement

    Figure 4-10. Configuring the Select statement

  12. You can now test your query. Click Test Query to see the results returned from your selection. Click Finish (see Figure 4-11). You should now see a list of states.

    Testing the query

    Figure 4-11. Testing the query

  13. Finally, configure the DropDownList control to use the data source that you have just created. Select SqlDataSource1 as the data source and select state as the field to display, as well as the value to use for the DropDownList control. Click OK (see Figure 4-12).

    Specifying the data field value for display and binding

    Figure 4-12. Specifying the data field value for display and binding

  14. Your page should now look like Figure 4-13. A SqlDataSource control is created for you.

    The SqlDataSource control

    Figure 4-13. The SqlDataSource control

  15. Press F5 to test the application. You should see the DropDownList control displaying a list of states that all the authors live in (see Figure 4-14).

Listing all the states in the DropDownList control

Figure 4-14. Listing all the states in the DropDownList control

In the next series of steps, you will add a GridView control to the Web Form so that when a state is selected from the DropDownList control, all of the authors that live in that selected state will be displayed in an orderly way.

  1. First, check the Enable AutoPostBack checkbox in the DropDownList Tasks menu (see Figure 4-15). Doing so will cause a postback to occur whenever the item in the DropDownList control is changed, so that the GridView control can display the related records.

    Enabling a postback to occur when the item in the DropDownList control is changed

    Figure 4-15. Enabling a postback to occur when the item in the DropDownList control is changed

    Note

    Remember to set the AutoPostBack property of the ListBox control so that a postback is performed when an item within the ListBox is selected.

  2. Next, drag and drop a GridView control (found in the Toolbox under the Data tab) onto the form.

  3. In the GridView Tasks menu, select a new data source (see Figure 4-16). You will configure a new SqlDataSource control to retrieve rows from the pubs table based on the selection in the DropDownList control.

    Configuring the GridView control

    Figure 4-16. Configuring the GridView control

  4. You will be asked to choose the data source type (choose Database) as well as the database connection to use (use the one configured earlier, pubsConnectionString).

  5. You should now see the window shown in Figure 4-17. Check the "Specify columns from a table or view" option and select the asterisk (*) checkbox. You will display all the fields in the authors table. Click the WHERE button to customize the SQL statement to retrieve only authors from a particular state.

    Specifying an SQL statement for use with the control

    Figure 4-17. Specifying an SQL statement for use with the control

  6. In the Add WHERE Clause window, select the options as shown in Figure 4-18. Basically, you specify that the state field (in the SQL statement) must match the value of the DropDownList control. Click Add and the following SQL expression will be shown in the "WHERE clause" box:

     SELECT * FROM [authors] WHERE ([state] = @state)
    Specifying a parameter in the SQL statement

    Figure 4-18. Specifying a parameter in the SQL statement

  7. Click OK in the Add WHERE Clause window. You can now test the connection. Click Test Query and you will be prompted to give a value to the state field. Enter CA to see a list of authors from California (see Figure 4-19).

    Testing the query

    Figure 4-19. Testing the query

  8. Click Finish to complete the configuration (see Figure 4-20). A new SqlDataSource control (SqlDataSource2) will be created.

    Retrieving the list of authors from California

    Figure 4-20. Retrieving the list of authors from California

  9. Press F5 to test your application. When you select a state from the DropDownList control, the GridView control should display the list of authors who live in that state (see Figure 4-21).

The GridView control in action

Figure 4-21. The GridView control in action

What about...

...configuring the DropDownList control declaratively?

By all means! One of the nice features of ASP.NET 2.0 is that, within the Web.config files, you can declaratively execute all the cool things you can do with its wizards. In fact, after a while, you may find that configuring the controls declaratively is a much more efficient way of developing your application.

So, instead of using the wizard to create a database connection string for you, you can simply add the <connectionStrings> element to your Web.config file. The following connection string, pubsConnectionString, connects to SQL Server Express 2005 on the local computer and uses integrated security to access the pubs database:

<?xml version="1.0"?>
<configuration 
  xmlns="http://schemas.microsoft.com/.NetConfiguration/
  v2.0">
   <appSettings/>
      <connectionStrings>
         <add name="pubsConnectionString" connectionString=
                   "Data Source=.\SQLEXPRESS;Initial
                    Catalog=pubs; Integrated 
                    Security=True" providerName=
                   "System.Data.SqlClient"/>
      </connectionStrings>
   <system.web>
   ...
   ...
   </system.web>
</configuration>

To establish a connection to the pubs database, you can use the SqlDataSource control. You can configure the SqlDataSource control to use the connection string defined in Web.config by specifying the following in the Source View of the form:

<asp:SqlDataSource 
     ID="SqlDataSource1" runat="server" 
     SelectCommand="SELECT DISTINCT [state] FROM [authors]"
     ConnectionString=
        "<%$ ConnectionStrings:pubsConnectionString %>">
</asp:SqlDataSource>
To display the records in the pubs database, bind a DropDownList control to 
the SqlDataSource control through the DataSourceID attribute:
<asp:DropDownList ID="DropDownList1" runat="server" 
     DataSourceID="SqlDataSource1" 
     DataTextField="state" 
     DataValueField="state">
</asp:DropDownList

You can also bind the SqlDataSource and DropDownList controls programmatically, like this:

DropDownList1.DataSource = SqlDataSource1
DropDownList1.DataBind( )

...formatting the GridView control to give it a professional look?

Most certainly. The GridView control comes with a few themes that you can apply. To apply a theme to the GridView control, select the Auto Format... link in the GridView Tasks menu (see Figure 4-22).

Using the Auto Format feature of the GridView control

Figure 4-22. Using the Auto Format feature of the GridView control

Figures Figure 4-23 and Figure 4-24 show the Professional and Sand & Sky themes applied to the GridView control.

The Professional theme applied to the GridView control

Figure 4-23. The Professional theme applied to the GridView control

The Sand & Sky theme applied to the GridView control

Figure 4-24. The Sand & Sky theme applied to the GridView control

Where can I learn more?

We have barely touched the surface of the GridView control. Be sure to check out the MSDN Help topic on "GridView Class" for the list of properties and methods exposed by the GridView control.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required