Walkthrough: Basic Data Access Using the SqlDataSource Control in Web Pages

This walkthrough shows you how to create a simple data-bound page by using controls that are specially designed for data access.

During this walkthrough, you will learn how to do the following:

• Connect to a Microsoft SQL Server database in the Microsoft Visual Web Developer Web development tool.

• Use drag-and-drop editing to create data-access elements that can be used in the page without code.

• Use the SqlDataSource control to manage data access and binding.

• Display data with the GridView control.

• Configure the GridView control to allow for sorting and paging.

• Create a filtered query that displays only selected records.

A Visual Studio project with source code is available to accompany this topic: Download.

Prerequisites

In order to complete this walkthrough, you will need the following:

• Visual Studio or Visual Web Developer Express installed on your computer.

  Note

  If you are using Visual Studio, the walkthrough assumes that you selected the Web Development collection of settings when you started Visual Studio the first time. For more information, see How to: Select Web Development Environment Settings.

• Access to the SQL Server Northwind database. The simplest option is to use the file-based copy of Northwind that is included in the code example download for this topic. As an alternative, you can download and install a version of Northwind to run on an instance of SQL Server on your computer or your local network. For information about downloading and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

  Note

  If you need information about how to log on to the computer running SQL Server, contact the server administrator.

Creating the Web Site

If you have already created a Web site in Visual Studio or Visual Web Developer Express (for example, by completing Walkthrough: Creating a Basic Web Forms Page in Visual Studio), you can use that Web site and go to the next section. Otherwise, create a new Web site and page by following these steps.

This walkthrough uses a Web site project. You could use a Web application project instead. For information about the difference between these Web project types, see Web Application Projects versus Web Site Projects in Visual Studio.

To create a file system Web site

1. Open Visual Studio or Visual Web Developer Express.

2. In the File menu, click New Web Site.

   The New Web Site dialog box is displayed.

3. Under Installed Templates, click Visual Basic or Visual C# and then select ASP.NET Web Site.

4. In the Web location box, select File System, and then enter the name of the folder where you want to keep the pages for your Web site.

   For example, type the folder name C:\WebSites.

5. Click OK.

   Visual Studio creates a Web site project that includes prebuilt functionality for layout (a master page, the Default.aspx and About.aspx content pages, and a cascading style sheet), for Ajax (client script files), and for authentication (ASP.NET membership).

Adding a GridView Control to Display Data

To display data on an ASP.NET Web page, you need the following:

• A connection to a data source (such as a database).

  In the following procedure, you will create a connection to the SQL Server Northwind database.

• A data source control on the page, which executes queries and manages the results of the queries.

• A control on the page to actually display the data.

  In the following procedure, you will display data in a GridView control. The GridView control will get its data from the SqlDataSource control.

You can add these elements to the Web site separately. However, it is easiest to start by visualizing the data display using GridView control, and then using wizards to create the connection and data source control. The following procedure explains how to create all three of the elements that you must have to display data on the page.

To add and configure a GridView control for displaying data

1. In Visual Web Developer, switch to Design view.

2. From the Data folder in the Toolbox, drag a GridView control onto the Default.aspx page.

3. If the GridView Tasks shortcut menu does not appear, right-click the GridView control, and then click Show Smart Tag.

4. On the GridView Tasks menu, in the Choose Data Source list, click <New data source>.

   The Data Source Configuration dialog box appears.

   

5. Click Database.

   This specifies that you want to obtain data from a database that supports SQL statements. This includes SQL Server and other OLE-DB–compatible databases.

   In the Specify an ID for the data source box, a default data source control name appears (SqlDataSource1). You can leave this name.

6. Click OK.

   The Configure Data Source wizard appears, displaying a page on which you can choose a connection.

   

7. Click New Connection.

8. In the Choose Data Source dialog box, under Data source, click Microsoft SQL Server, and then click Continue.

   The Add Connection dialog box appears.

9. In the Server name box, enter the name of the SQL Server that you want to use.

   

10. For the logon credentials, select the option that is appropriate for accessing the SQL Server database (integrated security or specific ID and password) and if it is required, enter a user name and password.

11. Click Select or enter a database name, and then enter Northwind.

12. Click Test connection, and when you are sure that it works, click OK.

    The Configure Data Source - <DataSourceName> wizard appears and the connection information is filled in.

13. Click Next.

    The wizard appears, displaying a page on which you can choose to store the connection string in the configuration file. Storing the connection string in the configuration file has two advantages:

    • It is more secure than storing the connection string in the page.

    • You can reuse the same connection string in multiple pages.

14. Make sure that the Yes, save this connection as check box is selected, and then click Next. (You can leave the default connection string name of NorthwindConnectionString.)

    The wizard appears, displaying a page on which you can specify the data that you want to fetch from the database.

15. Under Specify columns from a table or view, in the Name list, click Customers.

16. Under Columns, select the CustomerID, CompanyName, and City check boxes.

    The wizard appears, displaying the SQL statement that you are creating in a box at the bottom of the page.

    

    Note

    The wizard lets you specify selection criteria (a WHERE clause) and other SQL query options. For this part of the walkthrough, you will create a simple statement without selection or sort options.

17. Click Next.

18. Click Test Query to make sure that you are fetching the data you want.

19. Click Finish.

    The wizard closes and you are returned to the page. Running the wizard has accomplished two tasks:

    • The wizard created and configured a SqlDataSource control (named SqlDataSource1), which incorporates the connection and query information that you specified.

    • The wizard bound the GridView control to the SqlDataSource. Therefore, the GridView control will display data that is returned by the SqlDataSource control.

    If you view the properties for the SqlDataSource control, you can see that the wizard has created values for the ConnectionString and SelectQuery properties.

    Note

    You can easily change the look of the GridView control. In Design view, right-click the GridView control, and then click Show Smart Tag. On the GridView Tasks menu, click Auto Format, and then apply a scheme.

Testing the Page

You can now run the page.

To test the page

1. Press CTRL+F5 to run the page.

   The page appears in the browser. The GridView control displays all data rows from the Customers table.

2. Close the browser.

Adding Sorting and Paging

You can add sorting and paging to the GridView control without writing any code.

To add sorting and paging

1. In Design view, right-click the GridView control, and then click Show Smart Tag.

2. On the GridView Tasks shortcut menu, select the Enable Sorting check box.

   The column headings in the GridView control change to links.

3. On the GridView Tasks menu, select the Enable Paging check box.

   A footer is added to the GridView control with page number links.

4. Optionally, use Properties to change the value of the PageSize property from 10 to a smaller page size.

5. Press CTRL+F5 to run the page.

   You will be able to click a column heading to sort by the contents of that column. If there are more records in the data source than the page size of the GridView control, you will be able to use the page navigation links at the bottom of the GridView control to move between pages.

6. Close the browser.

Adding Filtering

Frequently you want to display only selected data in the page. In this part of the walkthrough, you will modify the query for the SqlDataSource control so that users can select customer records for a particular city.

First, you will use a TextBox control to create a text box in which users can type the name of a city. Then, you will change the query to include a parameterized filter (WHERE clause). As part of that process, you will create a parameter element for the SqlDataSource control. The parameter element establishes how the SqlDataSource control will get the value for its parameterized query—namely, from the text box.

When you are finished with this part of the walkthrough, the page might look similar to the following in Design view.

To add the text box for specifying a city

1. From the Standard group in the Toolbox, drag a TextBox control and a Button control onto the page.

   The Button control is used only to post the page to the server. You will not need to write any code for it.

2. In Properties, for the TextBox control, set ID to textCity.

3. If you want, type City or similar text before the text box to act as a caption.

4. In Properties for the Button control, set Text to Submit.

You can now modify the query to include a filter.

To modify the query with a parameterized filter

1. Right-click the SqlDataSource control, and then click Show Smart Tag.

2. On the SqlDataSource Tasks menu, click Configure Data Source.

   The Configure Data Source - <Datasourcename> wizard appears.

3. Click Next.

   The wizard displays the SQL command that is currently configured for the SqlDataSource control.

4. Click WHERE.

   The Add WHERE Clause page appears.

5. In the Column list, click City.

6. In the Operator list, click =.

7. In the Source list, click Control.

8. Under Parameter properties, in the Control ID list, click textCity.

   

   The previous five steps specify that the query will get the search value for City from the TextBox control that you added in the preceding procedure.

9. Click Add.

   The WHERE clause that you have created appears in a box at the bottom of the page.

10. Click OK to close the Add WHERE Clause page.

11. In the Configure Data Source - <DataSourceName> wizard, click Next.

12. On the Test Query page, click Test Query.

    The wizard appears, displaying the Parameter Values Editor page, which prompts you for a value to use in the WHERE clause.

13. In the Value box, type London, and then click OK.

    The customer records for London appear.

14. Click Finish to close the wizard.

You can now test filtering.

To test filtering

1. Press CTRL+F5 to run the page.

2. In the text box, type London, and then click Submit.

   A list of customers from the city of London appears in the GridView control.

3. Try other cities, such as Buenos Aires and Berlin.

Next Steps

Data access is an important part of many Web applications, and this walkthrough has provided only a glimpse of what you can do with data in the Web pages. You might want to experiment with additional features of data access. For example, you might want to do the following:

• Edit and insert records.

  For detailed information, see Walkthrough: Editing and Inserting Data in Web Pages with the DetailsView Web Server Control.

• Work with records that have a master-detail relationship.

  For detailed information, see Walkthrough: Creating Master/Detail Web Pages in Visual Studio.

• Create a middle-tier (business) component that performs data access, and then use that as the data source in a page.

  For detailed information, see Walkthrough: Using the ObjectDataSource with an XML File.

See Also

Concepts

Data Source Web Server Controls