How to call Stored Procedure using C#

Stored Procedures are used to execute a specific set of commands. To use stored procedure in .Net we can use SqlCommandobject.  There are properties of SqlCommand which we need to set in order to call stored procedure.

First we need to set CommandText property we will assign this property with name of our stored procedure.

Then we need to tell CommandType of SqlCommand which will beCommandType.StoredProcedure in case of stored procedure, in case of simple query we will set this to CommandType.Text

DataTable dt = newDataTable();

using (SqlConnectioncon = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))

{

   using (SqlCommand cmd = new SqlCommand())

   {

      using (SqlDataAdapter adapter = new SqlDataAdapter())

      {

         con.Open();

         cmd.CommandText = "uspGetAllUsers";    //Name of stored procedure

         cmd.Connection = con;

         cmd.CommandType = CommandType.StoredProcedure;
         adapter.SelectCommand = cmd;

         adapter.Fill(dt);

       }

     }

 }