How to use SqlDataSource to bind Stored Procedure to GridView in ASP.NET

Author: Alanna Kremer

SqlDataSource can be configured to specify columns in a table or to specify custom SQL statement or stored procedure. We will see how we can bind stored procedure to GridView using SqlDataSource.

Visual Studio IDE provides rich data source web server controls like SqlDataSource and ObjectDataSource. SqlDataSource provides the facility to access and manipulate data in relational database. The data retrieved from database can be displayed using data controls like GridView or DetailsView. You can configure SqlDataSource with different options to manipulate data. You can specify columns from table in relational database or you can specify custom SQL statement or Stored Procedure. Stored procedures are better way to manipulate data in SQL server because it complied in SQL Server. So it will be a good practice in ASP.NET to write stored procedures and bind these stored procedures to data control.

Let’s see how we can bind stored procedures to GridView using SqlDataSource.

You can download NORTHWIND sample database and use this stored procedure for this example. If you want to use another stored procedure then write that stored procedure and specify it in configuration of SqlDataSource. I suppose you know how to write stored procedures in SQL Server. If you don’t know how to write stored procedures then refer to DevASP.NET articles on this topic.

Configure SqlDataSource from the small arrow at the top in Design View of the page.

Click on the Arrow

Click Configure Data Source

Create a new connection to the server or specify connection string in Choose your Data Connection option and Click Next. We are using NORTHWIND Connection String

Select “Specify a custom SQL Statement or stored procedure” and Click Next

Select “Stored procedure” Radio Button then Select “Ten Most Expensive Products” in DropDownList. Note that there are four tabs of SELECT, UPDATE, INSERT and DELETE. You can also specify stored procedures for update, insert or delete records in database. Now click Next.

Test Query and click Finish to finish SqlDataSource configuration.

Now click on small arrow at the top of GridView control and Choose Data Source as “SqlDataSource1”