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

In this article I will explain How to execute a stored procedure with Parameters in C# program or How to call stored procedure with Parameters in in asp.net c# code.

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 and How to pass parameters to Database.

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;

//Here we can add parameter

//Check syntax of the passing parameters

cmd.Parameters.Add(new SqlParameter("@UserID", txtUserID.Text));

cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text));

// 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;

cmd.Parameters.Add(new SqlParameter("@UserID", txtUserID.Text));

cmd.Parameters.Add(new SqlParameter("@Password", txtPassword.Text));

// 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);

            }

      }

}