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


        }
     }

Code Snippet to Execute Stored Procedure (Which Returns int) from ASP.NET Application

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 int RunSpReturnint(string strSpName, string Param1, String Param2)
    {
        createConnection();
        DataSet ds = new DataSet();
        int result = 0;
        try
        {
            SqlCommand objCommand = new SqlCommand(strSpName, connObj);
            objCommand.CommandType = CommandType.StoredProcedure;
            objCommand.Parameters.Add("@Param1", SqlDbType.VarChar, 255);
            objCommand.Parameters["@Param1"].Value = Param1;


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




            objCommand.Parameters.Add("@Outparam", SqlDbType.Int);
            objCommand.Parameters["@Outparam"].Direction = ParameterDirection.Output;
            objCommand.ExecuteNonQuery();
            result = (int)objCommand.Parameters["@Outparam"].Value;
            closeConnection();


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


        return result;




    }




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


        }
        


    }