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

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.

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.

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.

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

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

➡️ Edit, update, delete records in GridView using SqlDataSource 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>

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

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.

← PreviousNext →