Walkthrough: Calling a Stored Procedure Using the SqlDataSource Web Server Control

Article
12/04/2014

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 Studio.
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. If you have SQL Server installed, you can use that instead, but you must make small adjustments to some of the procedures.
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

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

Open Visual Studio.
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.
Under Visual Studio installed templates, click ASP.NET Web Site.
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.
In the Language list, click the programming language you prefer to work in.
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

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 Studio, in the View menu, click Server Explorer. In Visual Web Developer Express, in the View menu, click Database Explorer
In the Add Connection box, enter your server name in the Server Name text box.
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.
If you entered a password, select the Save my Password check box.
Under Select or enter a database name, enter Northwind.
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.
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.
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.

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

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

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

Switch to or open the Default.aspx page, and then switch to Design view.
In the Toolbox, from the Data group, drag a GridView control onto the page.
On the GridView Tasks menu, in the Choose Data Source list, click New data source.

The Data Source Configuration Wizard dialog box is displayed.
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.
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.
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.
Select the Specify a custom SQL statement or stored procedure option, and then click Next.
On the Define Custom Statements or Stored Procedures page, select Stored Procedure, and then select the stored procedure that you created earlier (GetEmployees).
Click Next.
Click Test Query to confirm that you are retrieving the data that you want.
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

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

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)

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

Right-click the GridView control and then click Show Smart Tag.
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

Switch to or open the Default.aspx page, and then switch to Design view.
In the Toolbox, from the Data group, drag a DetailsView control onto the page.
On the DetailsView Tasks menu, in the Choose Data Source list, click New data source.

The Data Source Configuration Wizard dialog box is displayed.
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.
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.
Select the Specify a custom SQL statement or stored procedure option, and then click Next.
On the Define Custom Statements or Stored Procedures page, select Stored Procedure, and then select the stored procedure that you created earlier (EmployeeSales).
Click Next.

The Define Parameters page is displayed.
In the Parameter source list, select Control.
In the ControlID list, select GridView1.
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.)
Click Finish.

Testing the Page

You can now run the page.

To test the page

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

Partager via