次の方法で共有

Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control

  • [アーティクル]

This walkthrough shows you how to display data in the GridView control by using a stored procedure. A stored procedure is an SQL query that is stored in a Microsoft SQL Server database instead of in your application code. Stored procedures are more secure than query code and often run faster. In this walkthrough, you will create a SqlDataSource control that retrieves the results of a stored procedure. The SqlDataSource control acts as the data source for a GridView control. The GridView control reads the results generated by the stored procedure and creates rows and tables to display the data.

During this walkthrough, you will learn how to:

  • Connect to a SQL Server database in Visual Web Developer.

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

  • Add a stored procedure to return data that will be displayed in the GridView control.

  • Display data that is returned from the stored procedure in the GridView control.

Prerequisites

In order to complete this walkthrough, you will need:

  • Visual Studio or Visual Web Developer Express.

    Note

    The Add New Stored Procedure feature that is used in this walkthrough is not supported by Visual Web Developer Express.

  • SQL Server Express Edition. If you have SQL Server installed, you can use that instead, but you must make small adjustments to some of the procedures.

  • Microsoft Data Access Components (MDAC) version 2.7 or later versions.

    If you are using Microsoft Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Microsoft Windows 2000, you might have to upgrade the MDAC already installed on your computer. For more information, see Microsoft Data Access Components (MDAC) Installation on the MSDN Web site.

  • Access to the SQL Server Northwind database. For information about how to download 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 in to SQL Server, contact the server administrator.

Creating a Web Site

Create a new Web site and page by following these steps.

To create a file system Web site

  1. Open Visual Web Developer.

  2. On the File menu, click New, and then click Web Site. If you are using Visual Web Developer Express, on the File menu, click New Web Site.

    The New Web Site dialog box is displayed.

  3. Under Visual Studio installed templates, click ASP.NET Web Site.

  4. In the first Location box, select File System; in the second, enter the name of the folder where you want to keep the pages of your Web site.

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

  5. In the Language list, click the programming language you prefer to work in.

  6. Click OK.

    Visual Web Developer Express creates the folder and a new page named Default.aspx.

Adding a Stored Procedure to Return 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 stored procedure that returns data for display.

  • A data source control on the page, which executes the stored procedure and manages the results.

  • A control on the page to 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.

The first step is to create a stored procedure.

To create a stored procedure to return data

  1. In Server Explorer, right-click Data Connections, and then click Add Connection. If you are using Visual Web Developer Express, use Database Explorer.

    The Add Connection dialog box is displayed.

    • If the Data source list does not display Microsoft SQL Server (SqlClient), click Change, and in the Change Data Source dialog box, select Microsoft SQL Server.

    • If the Choose Data Source dialog box is displayed instead of the Connection Properties dialog box, in the Data source list, select the type of data source you will use. For this walkthrough, the data source type is Microsoft SQL Server. In the Data provider list, click .NET Framework Data Provider for SQL Server, and then click Continue.

      Note

      If the Server Explorer tab is not visible in Visual Web Developer, in the View menu, click Server Explorer. In Visual Web Developer Express, in the View menu, click Database Explorer

  2. In the Add Connection box, enter your server name in the Server Name text box.

  3. For the Log on to the server section, select the appropriate option to access the running SQL Server database (integrated security or specific ID and password) and, if it is required, enter a user name and password.

  4. If you entered a password, select the Save my Password check box.

  5. Under Select or enter a database name, enter Northwind.

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

    The new connection is created and displayed under Data Connections in Server Explorer.

  7. In Server Explorer, under Data Connections, expand the data connection that you just created. If you are using Visual Web Developer Express, use Database Explorer.

    The data connection expands to include child connections, such as Tables, Views, and Stored Procedures.

  8. Right-click Stored Procedures, and then click Add New Stored Procedure.

    Note

    The Add New Stored Procedure feature is not supported by Visual Web Developer Express.

  9. Add the following code in the new stored procedure, overwriting any existing code.

    CREATE PROCEDURE GetEmployees
AS
    Select EmployeeID, LastName, FirstName from Employees
RETURN

  10. Close the window and click Yes to create the stored procedure.

    Note

    The CREATE keyword changes to ALTER when the procedure is saved.

Adding a GridView Control to Display Data

After you have established a connection to a data source, you need the following:

  • A data source control on the page, which executes the stored procedure and manages the results.

  • A control on the page to display the data.

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

You can add these elements to your Web site separately. However, it is easiest to begin by visualizing the data display by using the GridView control, and then using wizards to create the connection and data source control. The following procedure explains how to create the elements that you need in order to display data on the page.

To add and configure a GridView control for displaying data

  1. Switch to or open the Default.aspx page, and then switch to Design view.

  2. In the Toolbox, from the Data group, drag a GridView control onto the page.

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

    The Data Source Configuration Wizard dialog box is displayed.

  4. Select Database, and then click OK.

    This specifies that you want to retrieve data from a SQL Server database.

    In the Specify an ID for the data source box, a default data source control name is displayed (SqlDataSource1). Leave this name.

    The Configure Data Source SqlDataSource1 wizard displays a page where you can select a connection.

  5. In the Which data connection should your application use to connect to the database? list, enter the connection that you created in the previous procedure, and then click Next.

    The wizard displays a page where you can select to store the connection string in a configuration file. Storing the connection string in the configuration file has two advantages:

    • It is more secure than storing it in the page.

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

  6. Select the Yes, save this connection as check box, and then click Next.

    The wizard displays a page where you can specify what data that you want to retrieve from the database.

  7. Select the Specify a custom SQL statement or stored procedure option, and then click Next.

  8. On the Define Custom Statements or Stored Procedures page, select Stored Procedure, and then select the stored procedure that you created earlier (GetEmployees).

  9. Click Next.

  10. Click Test Query to confirm that you are retrieving the data that you want.

  11. Click Finish.

Adding a DetailsView Control to Display Data from a Stored Procedure

You can display data from a stored procedure that takes parameters. In the following procedure, you will create a DetailsView control to display a computed value for a selected row on a GridView control. The GridView row will represent an Employee record, and the DetailsView control will show the total sales computed for the selected employee.

You need the following:

  • A stored procedure that takes the employee ID as a parameter and returns a value.

  • A second data source control on the page, which executes the stored procedure and manages the results. Both data source controls will represent connections to the same Northwind database. You will re-use the connection to the SQL Server Northwind database that you created previously in this walkthrough.

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

The first step is to create the stored procedure.

To create a stored procedure to return data for display in the DetailsView control

  1. In Server Explorer, under Data Connections, expand the data connection that you created earlier. If you are using Visual Web Developer Express, use Database Explorer to do this.

  2. Right-click Stored Procedures, and then click Add New Stored Procedure.

    Note

    The Add New Stored Procedure feature is not supported by Visual Web Developer Express.

  3. Add the following code in the new stored procedure, overwriting any existing code.

    CREATE PROCEDURE EmployeeSales
@Employee int AS
SELECT   SUM([Order Subtotals].Subtotal) AS Total
FROM Employees INNER JOIN
    Orders INNER JOIN
    [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
ON Employees.EmployeeID = Orders.EmployeeID
WHERE (Employees.EmployeeID = @Employee)

  4. Close the window and click Yes to create the stored procedure.

    Note

    The CREATE keyword changes to ALTER when the procedure is saved.

After you have created the stored procedure to provide data, you need the following:

  • A data source control on the page, which executes the stored procedure and manages the results.

  • A control on the page to display the data.

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

You can add these elements to your Web site separately. However, it is easiest to begin by visualizing the data display by using the DetailsView control, and then using wizards to create the connection and data source control. The following procedure explains how to create the elements that you need in order to display data on the page.

The next step is the enable row selection for the GridView control. The selected row will determine the EmployeeID parameter that is passed to the stored procedure.

To enable row selection for a GridView control

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

  2. In the GridView Tasks pane, check Enable Selection.

The next step is to create a control to display the results of the stored procedure.

To add and configure a DetailsView control for displaying data

  1. Switch to or open the Default.aspx page, and then switch to Design view.

  2. In the Toolbox, from the Data group, drag a DetailsView control onto the page.

  3. On the DetailsView Tasks menu, in the Choose Data Source list, click New data source.

    The Data Source Configuration Wizard dialog box is displayed.

  4. Select Database, and then click OK.

    This specifies that you want to retrieve data from a SQL Server database.

    In the Specify an ID for the data source box, a default data source control name is displayed (SqlDataSource2). Leave this name.

    The Configure Data Source SqlDataSource2 wizard displays a page where you can select a connection.

  5. In the Which data connection should your application use to connect to the database? list, enter the connection that you created earlier, and then click Next.

  6. Select the Specify a custom SQL statement or stored procedure option, and then click Next.

  7. On the Define Custom Statements or Stored Procedures page, select Stored Procedure, and then select the stored procedure that you created earlier (EmployeeSales).

  8. Click Next.

    The Define Parameters page is displayed.

  9. In the Parameter source list, select Control.

  10. In the ControlID list, select GridView1.

  11. Click Test Query to confirm that you are retrieving the data that you want. In the Parameter Values Editor dialog box, enter the value 1 and then click OK. The value 1 is the ID of an employee in the database. If data retrieval is working correctly, a value such as 192107.6000 is returned. (The value might be different if data in the database has been modified.)

  12. Click Finish.

Testing the Page

You can now run the page.

To test the page

  1. Press CTRL+F5 to run the page.

    The page is displayed in the browser. The GridView control displays all the data rows from the Employees table.

  2. Click the Select link on a data row on the GridView control.

    A DetailsView control displays details of the selected row, and the results of the stored procedure to calculate total sales for the selected employee.

  3. Close the browser.

Next Steps

This walkthrough has illustrated how you can use a SQL Server stored procedure with a data source control to display data on an ASP.NET Web page. You can use stored procedures in much the same way you use any SQL statement when you are working with the SqlDataSource control. Additionally, stored procedures can take parameters that you can associate with values on the Web page. You might want to explore the following scenarios with stored procedures, which perform data-related functions using SQL statements:

See Also

Tasks

How To: Secure Connection Strings when Using Data Source Controls

Walkthrough: Displaying a Drop-Down List While Editing in the GridView Web Server Control

Walkthrough: Basic Data Access in Web Pages