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 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 runat="server" DataSource 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. |
