Articles → ADO.NET → How To Call Stored Procedure Using Sqldatasource In Asp.Net

How To Call Stored Procedure Using Sqldatasource In Asp.Net

Software Requirements

1. Visual studio 2005 (or above) is installed on your machine
2. SQL server 2005 (or above) is installed on your machine.

Technical Knowledge

1. How to create a table in SQL server?
2. How to create a stored procedure in SQL server?
3. How to create a web based application using Visual Studio?
4. Basics of asp.net controls like button, textbox, gridview etc.
5. What is the purpose of SQLDataSource in asp.net?

Scenario

Create A New Table

Create A New Stored Procedure

Create Proc GetMyTask
@TaskId int
As
Select TaskId,TaskName from MyTask where TaskId = @TaskId

Create A Web Application And Add Controls In Web Form

<%--Controls--%>
<asp:TextBox ID="txtTaskId" runat="server"></asp:TextBox>
<asp:Button ID="btnTest" runat="server" Text="Click" />
<%--GridView--%>
<asp:GridView ID="gvTask" runat="server">
</asp:GridView>

Add Sqldatasource In The Web Form

<asp:SqlDataSource ID="source" runat="server" ConnectionString=""
        SelectCommand="GetMyTask" SelectCommandType="StoredProcedure">
<SelectParameters>
   <asp:ControlParameter ControlID="txtTaskId" PropertyName="Text" Name="TaskId"      Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<%--Controls--%>
<asp:TextBox ID="txtTaskId" runat="server"></asp:TextBox>
<asp:Button ID="btnTest" runat="server" Text="Click" />
    <%--GridView--%>
<asp:GridView ID="gvTask" runat="server" DataSourceID="source"></asp:GridView>

1. ID – unique id of SQLDataSource
2. ConnectionString – Connection string of database from where we are calling stored procedure
3. SelectCommand – Name of the stored procedure

1. ControlID – Name of the control from where we get the value of input parameter task id
2. PropertyName – Property of control that gives the value.
3. Name – Name of the input parameter of stored procedure
4. Type – Data type of input parameter

Output