Calling stored procedure from ASP.net using C# or VB.net :

Dec 21, 2011 By Anil Gaikwad

Fetching data from stored procedure is better as compared to using Query as Stored procedure are pre-compiled and stored in the database. So they take less time as compared to a Query In this tutorial we will have a look at how to call stored procedure from C# or VB.net code and fetch and store data inside dataset.

To call a stored procedure you need to follow following steps:


  • Create a Sqlcommand object and pass name of the stored procedure to it
Dim oCommand As SqlCommand oCommand.CommandText = "Name_of_stored_Procedure"
  • Add input output parameters with value to the sqlcommand depending on the stored procedure paranmeters
oCommand.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10)).Value = "value"
  • Then you can execute that Procedure using DataAdapter, Executereader(),ExecuteNonQuery() etc.
Dim adpt As New SqlDataAdapter(oCommand) Dim ds As New DataSet() adpt.Fill(ds)

Complete Code


C#.net Example:

SqlConnection oConnection = new SqlConnection(“Connection_String”); SqlCommand oCommand = new SqlCommand(); oCommand.Connection = oConnection; oCommand.CommandText = “Name_of_stored_Procedure” oCommand.CommandType = CommandType.StoredProcedure; oCommand.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10)).Value = “value”; SqlDataAdapter adpt = new SqlDataAdapter(oCommand); DataSet ds = new DataSet(); adpt.Fill(ds);

VB.net Example:

Dim oConnection As SqlConnection = New SqlConnection("Connection_String") Dim oCommand As SqlCommand = New SqlCommand() oCommand.Connection = oConnection oCommand.CommandText = "Name_of_stored_Procedure" oCommand.CommandType = CommandType.StoredProcedure oCommand.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10)).Value = "value" Dim adpt As New SqlDataAdapter(oCommand) Dim ds As New DataSet()