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.
If you've done serious ASP.NET
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).
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!
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.
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.
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:\):
sqlcmd -S .\SQLEXPRESS -i instpubs.sqlC:\>
sqlcmd -S .\SQLEXPRESS -i instnwnd.sql
Launch Visual Studio 2005 and create a new web site project. Name the project C:\ASPNET20\Chap04-GridView.
Drag and drop the DropDownList control from the Toolbox into the default Web Form.
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).
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).
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.
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.)
Click the New Connection... button to establish a connection to the database you want to use (see Figure 4-5).
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.
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.
You should now see the data connection that you have selected (winxp2\sqlexpress.pubs.dbo). Click Next (see Figure 4-8).
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
pubsConnectionString and ensure that the
"Yes, save this connection as:"
checkbox is selected. Click Next (see Figure 4-9).
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.
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).
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.
Finally, configure the DropDownList control to use the data source
that you have just created. Select
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).
Your page should now look like Figure 4-13. A SqlDataSource control is created for you.
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).
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.
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.
Next, drag and drop a GridView control (found in the Toolbox under the Data tab) onto the form.
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.
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,
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.
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)
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).
Click Finish to complete the configuration (see Figure 4-20). A new SqlDataSource control
SqlDataSource2) will be created.
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).
...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
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).