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




Picture showing the sample table schema

Click to Enlarge

Picture showing the data in the sample table

Click to Enlarge


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




Picture showing the output of calling stored procedure using SQL data source in asp.net

Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Saturday, July 12, 2014

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250