December 31, 2012

C# - SQL Helper Class

Below I have mentioned my SQL Helper class that is used to communicate SQL in better way.

using System;
using System.Data;
using System.Data.SqlClient;
 
namespace Utility
{
    public static class SqlHelper
    {
        #region "FILL DATA TABLE"
 
        public static void Fill(DataTable dataTable, String procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataTable);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        public static void Fill(DataTable dataTable, String procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            if (parameters != null)
                oCommand.Parameters.AddRange(parameters);
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataTable);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        #endregion
 
        #region "FILL DATASET"
 
        public static void Fill(DataSet dataSet, String procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataSet);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        public static void Fill(DataSet dataSet, String procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
            oCommand.CommandType = CommandType.StoredProcedure;
 
            if (parameters != null)
                oCommand.Parameters.AddRange(parameters);
 
            SqlDataAdapter oAdapter = new SqlDataAdapter();
 
            oAdapter.SelectCommand = oCommand;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oAdapter.SelectCommand.Transaction = oTransaction;
                    oAdapter.Fill(dataSet);
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oAdapter.Dispose();
                }
            }
        }
 
        #endregion
 
        #region "EXECUTE SCALAR"
 
        public static object ExecuteScalar(String procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            object oReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oCommand.Transaction = oTransaction;
                    oReturnValue = oCommand.ExecuteScalar();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return oReturnValue;
        }
 
        public static object ExecuteScalar(String procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            object oReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    if (parameters != null)
                        oCommand.Parameters.AddRange(parameters);
 
                    oCommand.Transaction = oTransaction;
                    oReturnValue = oCommand.ExecuteScalar();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return oReturnValue;
        }
 
        #endregion
 
        #region "EXECUTE NON QUERY"
 
        public static int ExecuteNonQuery(string procedureName)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            int iReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    oCommand.Transaction = oTransaction;
                    iReturnValue = oCommand.ExecuteNonQuery();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return iReturnValue;
        }
 
        public static int ExecuteNonQuery(string procedureName, SqlParameter[] parameters)
        {
            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());
            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);
 
            oCommand.CommandType = CommandType.StoredProcedure;
            int iReturnValue;
            oConnection.Open();
            using (SqlTransaction oTransaction = oConnection.BeginTransaction())
            {
                try
                {
                    if (parameters != null)
                        oCommand.Parameters.AddRange(parameters);
 
                    oCommand.Transaction = oTransaction;
                    iReturnValue = oCommand.ExecuteNonQuery();
                    oTransaction.Commit();
                }
                catch
                {
                    oTransaction.Rollback();
                    throw;
                }
                finally
                {
                    if (oConnection.State == ConnectionState.Open)
                        oConnection.Close();
                    oConnection.Dispose();
                    oCommand.Dispose();
                }
            }
            return iReturnValue;
        }
 
        #endregion
 
    }
}


How to Use:

public static int YourFunction1(int param1, int param2)
{
    SqlParameter[] objParameter = new SqlParameter[2];
    objParameter[0] = new SqlParameter("@param1", param1);
    objParameter[1] = new SqlParameter("@param2", param2);
    return Convert.ToInt32(Data.ExecuteScalar("YourProcedureName", objParameter));
}
 
public static int YourFunction2()
{
    return Data.ExecuteNonQuery("YourProcedureName");
}
 
public static void YourFunction3(int param1, int param2, int param3, ref DataTable dt)
{
    SqlParameter[] objParameter = new SqlParameter[3];
    objParameter[0] = new SqlParameter("@param1", param1);
    objParameter[1] = new SqlParameter("@param2", param2);
    objParameter[2] = new SqlParameter("@param3", param3);
    dt.Fill("YourProcedureName", objParameter);
}
 

Feedback are accepted.

6 comments:

  1. How can we use out put parameter?

    ReplyDelete
    Replies
    1. We can get value of output param by the following way.

      objParameter[0].Direction = ParameterDirection.Output;
      objParameter[0].Value;

      Delete
  2. How do I pass Table type parameter

    ReplyDelete
  3. how can we get the value of multiple output parameters from the stored procedure.

    ReplyDelete
  4. There is no executereader in this. I do not want to use DataTable, I will fetch data directly to a Dropdown

    ReplyDelete

Recommended Post Slide Out For Blogger