This class contains methods for
1) For query execution
2) For Stored Procedures execution
3) Transaction
/*
* @desc This file contains generic class for SQL connection with ado.net
* @author NILAV PATEL <nilavpatel1992@gmail.com>
*/
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// SQL generic connection class
/// </summary>
public class
SqlGenericConnection : IDisposable
{
#region private variables
/// <summary>
/// Connection string to connect with database
/// </summary>
private static string connectionString { get; set; }
/// <summary>
/// SQL connection
/// </summary>
private SqlConnection connection { get; set; }
/// <summary>
/// SQL command
/// </summary>
private SqlCommand command { get; set; }
/// <summary>
/// SQL transaction
/// </summary>
private SqlTransaction transaction { get; set; }
/// <summary>
/// output parameters
/// </summary>
public List<DbParameter> outParameters { get; private set; }
/// <summary>
/// is object disposed ?
/// </summary>
private bool disposed = false;
#endregion
#region constructor
/// <summary>
/// SqlGenericConnection class constructor
/// </summary>
/// <param name="str">connection string</param>
/// <param name="oldConnection">pass connection if exist</param>
/// <param name="oldTransaction">pass transaction if exist</param>
public SqlGenericConnection(string str = "", SqlConnection oldConnection = null, SqlTransaction oldTransaction = null)
{
//create new connection if not exist
connection = oldConnection ?? new SqlConnection(connectionString);
connectionString = ConfigurationManager.ConnectionStrings[str].ConnectionString;
//assign transaction if exist
if (oldTransaction != null)
{
transaction = oldTransaction;
}
}
#endregion
#region private methods
/// <summary>
/// open connection
/// </summary>
private void Open()
{
try
{
if (connection != null && connection.State == ConnectionState.Closed)
{
connection.Open();
}
}
catch (Exception ex)
{
Close();
}
}
/// <summary>
/// close connection
/// </summary>
private void Close()
{
if (connection != null)
{
connection.Close();
}
}
/// <summary>
/// executes stored procedure with DB parameters if they are passed
/// </summary>
/// <param name="procedureName"></param>
/// <param name="executeType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private object ExecuteProcedure(string procedureName, ExecuteType executeType, List<DbParameter> parameters)
{
object returnObject = null;
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
command = new SqlCommand(procedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (transaction != null)
{
command.Transaction = transaction;
}
// pass stored procedure parameters to command
if (parameters != null)
{
command.Parameters.Clear();
foreach (DbParameter dbParameter in parameters)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@" + dbParameter.Name;
parameter.Direction = dbParameter.Direction;
parameter.Value = dbParameter.Value;
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case ExecuteType.ExecuteReader:
returnObject = command.ExecuteReader();
break;
case ExecuteType.ExecuteNonQuery:
returnObject = command.ExecuteNonQuery();
break;
case ExecuteType.ExecuteScalar:
returnObject = command.ExecuteScalar();
break;
default:
break;
}
}
}
return returnObject;
}
/// <summary>
/// execute query with DB parameters if they are passed
/// </summary>
/// <param name="text"></param>
/// <param name="executeType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private object ExecuteQuery(string text, ExecuteType executeType, List<DbParameter> parameters)
{
object returnObject = null;
if (connection != null)
{
if (connection.State == ConnectionState.Open)
{
command = new SqlCommand(text, connection);
command.CommandType = CommandType.Text;
if (transaction != null)
{
command.Transaction = transaction;
}
// pass stored procedure parameters to command
if (parameters != null)
{
command.Parameters.Clear();
foreach (DbParameter dbParameter in parameters)
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@" + dbParameter.Name;
parameter.Direction = dbParameter.Direction;
parameter.Value = dbParameter.Value;
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case ExecuteType.ExecuteReader:
returnObject = command.ExecuteReader();
break;
case ExecuteType.ExecuteNonQuery:
returnObject = command.ExecuteNonQuery();
break;
case ExecuteType.ExecuteScalar:
returnObject = command.ExecuteScalar();
break;
default:
break;
}
}
}
return returnObject;
}
/// <summary>
/// updates output parameters from stored procedure
/// </summary>
private void UpdateOutParameters()
{
if (command.Parameters.Count > 0)
{
outParameters = new List<DbParameter>();
outParameters.Clear();
for (int i = 0; i < command.Parameters.Count; i++)
{
if (command.Parameters[i].Direction == ParameterDirection.Output)
{
outParameters.Add(new DbParameter(command.Parameters[i].ParameterName,
ParameterDirection.Output,
command.Parameters[i].Value));
}
}
}
}
#endregion
#region protected methods
/// <summary>
/// Dispose SqlGenericConnection class object
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
transaction.Dispose();
command.Dispose();
connection.Dispose();
}
disposed = true;
}
}
#endregion
#region public methods
#region stored procedure methods
/// <summary>
/// executes scalar query stored procedure without parameters
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <returns></returns>
public T ExecuteSingleProc<T>(string procedureName) where T : new()
{
return ExecuteSingleProc<T>(procedureName, null);
}
/// <summary>
/// executes scalar query stored procedure and maps result to single object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public T ExecuteSingleProc<T>(string procedureName, List<DbParameter> parameters) where T : new()
{
Open();
IDataReader reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);
T tempObject = new T();
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
}
}
reader.Close();
UpdateOutParameters();
Close();
return tempObject;
}
/// <summary>
/// executes list query stored procedure without parameters (Select)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <returns></returns>
public List<T> ExecuteListProc<T>(string procedureName) where T : new()
{
return ExecuteListProc<T>(procedureName, null);
}
/// <summary>
/// executes list query stored procedure and maps result generic list of objects (Select with parameters)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public List<T> ExecuteListProc<T>(string procedureName, List<DbParameter> parameters) where T : new()
{
List<T> objects = new List<T>();
Open();
IDataReader reader = (IDataReader)ExecuteProcedure(procedureName, ExecuteType.ExecuteReader, parameters);
while (reader.Read())
{
T tempObject = new T();
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetValue(i) != DBNull.Value)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
}
}
objects.Add(tempObject);
}
reader.Close();
UpdateOutParameters();
Close();
return objects;
}
/// <summary>
/// executes non query stored procedure with parameters (Insert, Update, Delete)
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteNonQueryProc(string procedureName, List<DbParameter> parameters)
{
int returnValue;
Open();
returnValue = (int)ExecuteProcedure(procedureName, ExecuteType.ExecuteNonQuery, parameters);
UpdateOutParameters();
Close();
return returnValue;
}
/// <summary>
/// executes scalar query stored procedure without parameters (Count(), Sum(), Min(), Max() etc...)
/// </summary>
/// <param name="procedureName"></param>
/// <returns></returns>
public object ExecuteScalarProc(string procedureName)
{
return ExecuteScalarProc(procedureName, null);
}
/// <summary>
/// executes scalar query stored procedure with parameters (Count(), Sum(), Min(), Max() etc...)
/// </summary>
/// <param name="procedureName"></param>
/// <returns></returns>
public object ExecuteScalarProc(string procedureName, List<DbParameter> parameters)
{
object returnValue;
Open();
returnValue = ExecuteProcedure(procedureName, ExecuteType.ExecuteScalar, parameters);
UpdateOutParameters();
Close();
return returnValue;
}
#endregion
#region query methods
/// <summary>
/// executes scalar query stored procedure without parameters
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <returns></returns>
public T ExecuteSingle<T>(string text) where T : new()
{
return ExecuteSingle<T>(text, null);
}
/// <summary>
/// executes scalar query stored procedure and maps result to single object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public T ExecuteSingle<T>(string text, List<DbParameter> parameters) where T : new()
{
Open();
IDataReader reader = (IDataReader)ExecuteQuery(text, ExecuteType.ExecuteReader, parameters);
T tempObject = new T();
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
}
}
reader.Close();
UpdateOutParameters();
Close();
return tempObject;
}
/// <summary>
/// executes list query stored procedure without parameters (Select)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <returns></returns>
public List<T> ExecuteList<T>(string text) where T : new()
{
return ExecuteList<T>(text, null);
}
/// <summary>
/// executes list query stored procedure and maps result generic list of objects (Select with parameters)
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public List<T> ExecuteList<T>(string text, List<DbParameter> parameters) where T : new()
{
List<T> objects = new List<T>();
Open();
IDataReader reader = (IDataReader)ExecuteQuery(text, ExecuteType.ExecuteReader, parameters);
while (reader.Read())
{
T tempObject = new T();
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader.GetValue(i) != DBNull.Value)
{
PropertyInfo propertyInfo = typeof(T).GetProperty(reader.GetName(i));
propertyInfo.SetValue(tempObject, reader.GetValue(i), null);
}
}
objects.Add(tempObject);
}
reader.Close();
UpdateOutParameters();
Close();
return objects;
}
/// <summary>
/// executes non query stored procedure with parameters (Insert, Update, Delete)
/// </summary>
/// <param name="procedureName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int ExecuteNonQuery(string text, List<DbParameter> parameters)
{
int returnValue;
Open();
returnValue = (int)ExecuteQuery(text, ExecuteType.ExecuteNonQuery, parameters);
UpdateOutParameters();
Close();
return returnValue;
}
/// <summary>
/// executes scalar query stored procedure without parameters (Count(), Sum(), Min(), Max() etc...)
/// </summary>
/// <param name="procedureName"></param>
/// <returns></returns>
public object ExecuteScalar(string text)
{
return ExecuteScalar(text, null);
}
/// <summary>
/// executes scalar query stored procedure with parameters (Count(), Sum(), Min(), Max() etc...)
/// </summary>
/// <param name="procedureName"></param>
/// <returns></returns>
public object ExecuteScalar(string text, List<DbParameter> parameters)
{
object returnValue;
Open();
returnValue = ExecuteQuery(text, ExecuteType.ExecuteScalar, parameters);
UpdateOutParameters();
Close();
return returnValue;
}
#endregion
#region transaction methods
/// <summary>
/// begin transaction
/// </summary>
public void BeginTransaction()
{
if (connection != null)
{
transaction = connection.BeginTransaction();
}
}
/// <summary>
/// commit transaction
/// </summary>
public void CommitTransaction()
{
if (transaction != null)
{
transaction.Commit();
}
}
/// <summary>
/// rollback transaction
/// </summary>
public void RollbackTransaction()
{
if (transaction != null)
{
transaction.Rollback();
}
}
#endregion
#region dispose method
/// <summary>
/// Dispose SqlGenericConnection class object
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
#endregion
}
/// <summary>
/// execution type enumerations
/// </summary>
public enum ExecuteType
{
ExecuteReader,
ExecuteNonQuery,
ExecuteScalar
}
/// <summary>
/// Db parameter class
/// </summary>
public class DbParameter
{
public string Name { get; set; }
public ParameterDirection Direction { get; set; }
public object Value { get; set; }
public DbParameter(string paramName, ParameterDirection paramDirection, object paramValue)
{
Name = paramName;
Direction = paramDirection;
Value = paramValue;
}
}
