How to bind an SQL Server Stored Procedure with SqlDataSource or GridView Control in Asp.Net

← PrevNext →

You can bind an SQL Server stored procedure with Asp.Net SqlDataSource control. The SqlDataSource Web Server control, as you know, allows you to access data from a remote database, using either a simple SELECT query or a Stored Procedure. I’ll show you how you can bind data from a Stored Procedure to an SqlDataSource and bind the control to a variety of data-bound controls, such as a Repeater control or a GridView control.

If you are new to Asp.Net, I would recommend reading my previous article on binding data to a GridView control using SqlDataSource . It covers a comprehensive data binding procedure with SqlDataSource control using a SELECT query.

Before we move on with our example, we’ll first create a simple Stored Procedure in SQL Server.

I am using the Books table for the data. I have already created the table here... dbo.Books.

All I need now is a Stored Procedure.

CREATE PROCEDURE GetBookDetails 
AS
    BEGIN
        SELECT *FROM Books
    END
GO

Execute the procedure and you will see the list of books. Now, I’ll bind this procedure to the SqlDataSource web control in Asp.Net.

Bind the Store Procedure to SqlDataSource Control

Create a new web site, go to the Design mode and click the Toolbox (Ctrl+Alt+x). In the toolbox window, find Data and expand the tree. Choose SqlDataSource from the list.

Now, you need to configure a data source to the control. Click the control and choose Configure Data Source… option. This will open the configuration window. Set a New Connection and click Next button. You will see two options in the next window, choose the first option, so you can retrieve data from a Stored Procedure.

Configure SqlDataSource

Click Next and now you can choose the Stored Procedure that you have created. Choose the Stored procedure: option and select the procedure from the dropdown list. Click the next button to finish it.

Configure SqlDataSource with Stored Procedure

Well, now you have access to the data that you have created in your SQL Server. All, you need now is hook (bind) this data to any data-bound control in Asp.Net.

Bind SqlDataSource to a GridView Control

Your application now has the data required as you have successfully bound the Stored Procedure to an SqlDataSource web control. However, to display or manipulate the data, you will need to bind the control with a data-bound control, such as, the GridView.

Its very simple to bind it with a GridView control. Go to the Design mode again follow the steps that I have mentioned above for SqlDataSource. In the Toolbar, expand the Data list and choose GridView.

Click the GridView and you will find Choose Data Source option followed by a dropdown control. Select the newly created SqlDataSource1 from list. That is it. Go back to the Source mode of your page. You will see the markup with the GridView followed by SqlDataSource web control.

The Markup
<asp:GridView ID="GridView1" runat="server" 
    AutoGenerateColumns="False" 
    AllowPaging="True" 
    PageSize="5"
    DataKeyNames="BookID" DataSourceID="SqlDataSource1">

    <Columns>
        <asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" 
            ReadOnly="True" SortExpression="BookID" />
        <asp:BoundField DataField="BookName" HeaderText="BookName" 
            SortExpression="BookName" />
        <asp:BoundField DataField="Category" HeaderText="Category" 
            SortExpression="Category" />
        <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
        <asp:BoundField DataField="Price_Range" HeaderText="Price_Range" 
            SortExpression="Price_Range" />
    </Columns>
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DNAConnectionString %>" 
    SelectCommand="GetBookDetails" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>

I have added two more properties to the GridView. One, I have set AllowPaging = "True" and two set the PageSize = "5". This will enable paging to the GridView. I have removed any column from the list. You can however, choose the fields as per your requirement. Run the application.

Related: Export Data from a Paging Enabled GridView to Excel in Asp.Net

Bind SqlDataSource to a Repeater Control

The Asp.Net Repeater control is another popular data-bound control that you can bind with the SqlDataSource web control. You will find the Repeater control too, in the “Data” tree in the Toolbar window. Add it to your web page from the design mode and bind it with the SqlDataSource. Get back to the Source mode, as we now need to add some items, with a header and footer, in the Repeater control.

Once you have bind the data source to the repeater control, it would look like this. The control is bound with SqlDataSource.

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
</asp:Repeater>
    
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:DNAConnectionString %>" 
    SelectCommand="GetBookDetails" SelectCommandType="StoredProcedure">
</asp:SqlDataSource>

A repeater will have a Header, some Items and a Footer section. So, let's add it.

<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
    <%--THE HEADER--%>
    <HeaderTemplate>
        <table>
            <tr>
                <td>Book ID</td>
                <td>Book Name</td>
            </tr>
    </HeaderTemplate>

    <%--THE ITEMS (FIELDS)--%>
    <ItemTemplate>      
        <tr>
            <td><%#Eval("BookID")%></td>
            <td><%#Eval("BookName") %></td>
        </tr>
    </ItemTemplate>

    <%--THE FOOTER SECTION--%>
    <FooterTemplate>
        </table>
    </FooterTemplate>
</asp:Repeater>

Binding Repeater control in Asp.Net with a Stored Procedure

Must Read: What is a Repeater control in Asp.Net and how to use it in a Web Application

Run the application and you will see records showing the Book ID and Book Name in the repeater control. You can add more headers and fields to the control.

Conclusion

Simple tools with simple procedures and that’s we need to give our users a cool experience. Here, we have how we can bind an SQL Server Stored Procedure to an Asp.Net SqlDataSource web control by following few simple procedures. Along with it, I have shown how you can later bind the SqlDataSource to two popular data-bound controls, such as, a GridView and a Repeater control.

Thanks for reading.

← PreviousNext →