Below I have mentioned my SQL Helper class that is used to communicate SQL in better way.
How to Use:
Feedback are accepted.
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.
NICE JOB
ReplyDeleteHow can we use out put parameter?
ReplyDeleteWe can get value of output param by the following way.
DeleteobjParameter[0].Direction = ParameterDirection.Output;
objParameter[0].Value;
How do I pass Table type parameter
ReplyDeletehow can we get the value of multiple output parameters from the stored procedure.
ReplyDeletehttps://www.c-sharpcorner.com/blogs/userdefined-table-type-and-table-valued-parameters-in-stored-procedure-to-reduce-the-code-size-in-code-behind-file
DeleteThere is no executereader in this. I do not want to use DataTable, I will fetch data directly to a Dropdown
ReplyDeletePlease find below link for sql helper class
ReplyDeletehttp://www.codingjugaad.com/code-snippest/c-sharp/database-helper-in-csharp