Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

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.
 
  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual C# or Visual Basic > ASP.NET Empty Web Site > Click Ok
  4. Website > Add New Item > Web Form > Click Add
  5. Add a GridView control and a SqlDataSource control in your Web Form

     
    <asp:GridView ID="GridView1" runat="server">
     
    </asp:GridView>
     
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>
     
  6. We will need stored procedure to specify in SqlDataSource. For this example, we are using a stored procedure named “Ten Most Expensive Products” in NORTHWIND Sample database.

    ALTER procedure "Ten Most Expensive Products" AS
    SET ROWCOUNT 10
    SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
    FROM Products
    ORDER BY Products.UnitPrice DESC
     
    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.
          
  7. Configure SqlDataSource from the small arrow at the top in Design View of the page.

    1. Click on the Arrow
    2. Click Configure Data Source
    3. 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
    4. Select “Specify a custom SQL Statement or stored procedure” and Click Next
    5. 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.
    6. Test Query and click Finish to finish SqlDataSource configuration.
       
  8. Now click on small arrow at the top of GridView control and Choose Data Source as “SqlDataSource1”
  9. The source view of the aspx page will look like this:

    <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
     
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField DataField="TenMostExpensiveProducts"
                        HeaderText="TenMostExpensiveProducts"
                        SortExpression="TenMostExpensiveProducts" />
                    <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
                        SortExpression="UnitPrice" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                ConnectionString="<%$ ConnectionStrings:NORTHWNDConnectionString %>"
                SelectCommand="Ten Most Expensive Products" SelectCommandType="StoredProcedure">
            </asp:SqlDataSource>
        </div>
        </form>
    </body>
    </html>
     
  10. Press F5 to see the result in browser 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to Restore SQL Server Database in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net