Export (0) Print
Expand All

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 using a stored procedure. A stored procedure is an SQL query stored in a Microsoft SQL Server database rather than in your application code. Stored procedures are more secure than query code and often run faster. The SqlDataSource control, created by a wizard in this walkthrough, retrieves the results of the stored procedure and acts as the data source for the GridView control. The GridView control examines the results and creates rows and tables as needed to display the data.

During this walkthrough, you will learn how to:

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

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

  • Add a stored procedure to return data for display in the GridView control.

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

Prerequisites

In order to complete this walkthrough, you will need:

  • Visual Web Developer (Visual Studio).

    NoteNote

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

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

    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 need to upgrade the MDAC already installed on your computer. For more information, see "Microsoft Data Access Components (MDAC) Installation" in the MSDN Library.

  • Access to the SQL Server Northwind database. For information about downloading and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

    NoteNote

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

Creating the 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 appears.

  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 creates the folder and a new page named Default.aspx.

Adding a Stored Procedure to Return Data for Display in the GridView Control

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 for display in the GridView control

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

    • 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 appears 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.

    NoteNote

    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 required, enter a user name and password.

  4. Select the Save my Password check box if you entered a password.

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

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

    Your new connection has been created under Data Connections in Server Explorer.

  7. In Server Explorer, under Data Connections, expand the data connection 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.

    NoteNote

    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.

    NoteNote

    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 will display data in a GridView control. The GridView 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 with 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 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 box, click New data source.

    The Data Source Configuration Wizard dialog box appears.

  4. Select Database, and then click OK.

    This specifies that you want to get 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 choose a connection.

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

    The wizard displays a page where you can choose 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 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 (GetEmployees) earlier.

  9. Click Next.

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

  11. 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 appears in the browser. The GridView control displays all the data rows from the Employees table.

  2. 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 way you use any SQL statement when working with the SqlDataSource control. Additionally, stored procedures can take parameters that you associated with values on the Web page. Other scenarios you might want to explore with stored procedures are the ones listed here, which perform data-related functions using SQL statements:

See Also

Community Additions

ADD
Show:
© 2014 Microsoft