How to execute a stored procedure within C# program or How to call stored procedure in asp.net c# code

In this article I will explain How to execute a stored procedure within C# program or How to call stored procedure in asp.net c# code In ASP.NET and C#.

In this session I will explain how establish a connection to sql server and How to execute stored procedure and How to declare sql command and how to fill sql data adapter code to dataset.

Below namespaces are for establishing a connection to Sql server.

using System.Data.SqlClient;

using System.Data;

Below is the Database connection string

SqlConnection con = new SqlConnection("server=Localhost;database=DBName;uid=sa1;pwd=g@123;MultipleActiveResultSets=True");

// Use GetRole is a Stored Procedure name

SqlCommand cmd = new SqlCommand("GetRole", con);

cmd.CommandType = CommandType.StoredProcedure;

// Use a SqlDataAdapter object to Execute the cmd

SqlDataAdapter daUserData = new SqlDataAdapter(cmd);

//Use a DataSet object to gather the returned rows and to work with these rows in addition to the return //values and the return parameters.

DataSet dsRole = new DataSet();

daUserData.Fill(dsRole);

Total Code:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

public partial class CreateNewEmployee : System.Web.UI.Page

{

    SqlConnection con = new SqlConnection("server=LocalHost;database=DBName;uid=sa1;pwd=g@123;MultipleActiveResultSets=True");

    DataSet dsRole = new DataSet();

    protected void Page_Load(object sender, EventArgs e)

    {

      if (!IsPostBack)

            {

SqlCommand cmd = new SqlCommand("GetRole", con);

cmd.CommandType = CommandType.StoredProcedure;

// Use a SqlDataAdapter object to Execute the cmd

SqlDataAdapter daUserData = new SqlDataAdapter(cmd);

//Use a DataSet object to gather the returned rows and to work with these rows in addition to the return //values and the return parameters.

DataSet dsRole = new DataSet();

daUserData.Fill(dsRole);

            }

      }

}