ASP.NET 2.0: A Developeramp;#039;s Notebook [Electronic resources] نسخه متنی

4.1. 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).

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!

4.1.1. 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

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).

Figure 4-2. Configuring the DropDownList control

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).

Figure 4-3. Creating a new data source

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.

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.)

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

Figure 4-5. Creating a new data connection

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.

Figure 4-6. Specifying the new data connection properties

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.

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

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

Figure 4-8. The newly created database connection

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.

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

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).

Figure 4-10. Configuring the Select statement

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.

Figure 4-11. Testing the query

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).

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

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

Figure 4-13. The SqlDataSource control

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).

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.

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.

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.

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.

Figure 4-16. Configuring the GridView 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,
pubsConnectionString).

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.

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

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)

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

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).

Figure 4-19. Testing the query

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

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

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).

Figure 4-21. The GridView control in action

4.1.2. 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).

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.

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

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

4.1.3. 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.