Code Snippet to Execute Stored Procedure (Which Returns DataSet) in C#

Below is the code snippet to execute SP on SqlServer which is returning int

1.) Use the below namespace

using System.Data.SqlClient;

using System.Data.;

2.) Create Connection Object in your class in below manner

 SqlConnection connObj = new SqlConnection();

3.) Use the below method to execute SP and return the o/p

    public DataSet RunSpReturndataset(String param1, String strSpName)
    {
        createConnection();
        DataSet ds = new DataSet();
        try
        {
            SqlCommand objCommand = new SqlCommand(strSpName, connObj);
            objCommand.CommandType = CommandType.StoredProcedure;


            objCommand.Parameters.Add("@param1", SqlDbType.VarChar, 50);
            objCommand.Parameters["@param1"].Value = param1;


            objCommand.Parameters.Add("@Outparam", SqlDbType.Int);
            objCommand.Parameters["@Outparam"].Direction = ParameterDirection.Output;
            SqlDataAdapter sqldataAdpter = new SqlDataAdapter(objCommand);
            sqldataAdpter.Fill(ds);
            closeConnection();
        }
        catch (Exception ex)
        {
       
            evtlog.WriteError(ex.Message);
            HttpContext.Current.Response.Redirect("Error.aspx");
        }


        return ds;

    }



    public void createConnection()
    {
        try
        {
            connObj.ConnectionString = ConfigurationManager.ConnectionStrings["MyDbConn"].ToString();
            connObj.Open();
        }
        catch (Exception ex)
        {
            //evtlog.LogException(ex.Message, "createConnection");
            evtlog.WriteError(ex.Message);
            HttpContext.Current.Response.Redirect("Error.aspx");


        }


    }



 public void closeConnection()
    {
        try
        {


            connObj.Close();
        }
        catch (Exception ex)
        {
           // evtlog.LogException(ex.Message, "closeConnection");
            evtlog.WriteError(ex.Message);
            HttpContext.Current.Response.Redirect("Error.aspx");


        }
     }