public static void ExecuteNonQuery(string procedureName, Common.ParameterList SQLParam, int timeOutDuration)
{
SqlConnection conn = new SqlConnection();
try
{
using (conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (SqlCommand cmd = BuildSqlCommand(conn, null, CommandType.StoredProcedure, procedureName, SQLParam))
{
cmd.CommandTimeout = timeOutDuration;
cmd.ExecuteNonQuery();
StoreParameterOutputValues(cmd, SQLParam);
}
conn.Close();
// Gurukul.Database.Common.SQLParameter.ConType = ConnectionType.WebConn;
}
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
//Gurukul.Database.Common.SQLParameter.ConType = ConnectionType.WebConn;
}
}
private static void StoreParameterOutputValues(SqlCommand cmd, Common.ParameterList SQLParam)
{
foreach (SqlParameter param in cmd.Parameters)
{
if (param.Direction != ParameterDirection.Input)
{
SQLParam.GetParameterByName(param.ParameterName).ParameterValue = param.Value;
}
}
}
public static DataTable ExecuteDataTable(string SQL, Common.ParameterList SQLParam)
{
SqlDataAdapter ad;
DataTable dt = new DataTable();
try
{
ad = ExecuteDataAdapter(SQL, SQLParam);
ad.Fill(dt);
return(dt);
}
catch (Exception)
{
throw;
}
}
public static DataSet ExecuteDataSet(string procedureName, Common.ParameterList SQLParam)
{
SqlDataAdapter ad;
DataSet ds = new DataSet();
try
{
ad = ExecuteDataAdapter(procedureName, SQLParam);
ad.Fill(ds);
return(ds);
}
catch (Exception)
{
throw;
}
}
public static DataTableReader ExecuteReader(string procedureName, Common.ParameterList SQLParam, int timeOut)
{
DataTableReader reader;
DataSet ds;
try
{
ds = ExecuteDataSet(procedureName, SQLParam, timeOut);
reader = ds.CreateDataReader();
return(reader);
}
catch (Exception)
{
throw;
}
}
public static SqlDataAdapter ExecuteDataAdapter(string procedureName, Common.ParameterList SQLParam)
{
SqlConnection conn = null;
SqlDataAdapter ad;
try
{
conn = new SqlConnection(ConnectionString);
conn.Open();
ad = new SqlDataAdapter();
ad.SelectCommand = BuildSqlCommand(conn, null, CommandType.StoredProcedure, procedureName, SQLParam);
conn.Close();
return(ad);
}
catch (Exception)
{
throw;
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
}
private static SqlCommand BuildSqlCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string procedureName, Common.ParameterList SQLParam, int timeOutDuration)
{
SqlCommand cmd = BuildBaseSqlCommand(conn, transaction, cmdType, procedureName, SQLParam);
cmd.CommandTimeout = timeOutDuration;
return(cmd);
}
//private static SqlCommand BuildSqlCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string procedureName, Common.SQLParameterList SQLParam)
//{
// SqlCommand cmd = new SqlCommand();
// cmd.CommandType = cmdType;
// cmd.Connection = conn;
// cmd.Transaction = transaction;
// cmd.CommandText = procedureName;
// foreach (SqlParameter param in SQLParam)
// {
// cmd.Parameters.Add(param);
// }
// return cmd;
//}
private static SqlCommand BuildBaseSqlCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string procedureName, Common.ParameterList SQLParam)
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = cmdType;
cmd.Connection = conn;
cmd.Transaction = transaction;
cmd.CommandText = procedureName;
foreach (Common.SQLParameter param in SQLParam)
{
cmd.Parameters.Add(new SqlParameter(param.ParameterName, param.ParameterValue));
if (param.DBType != null)
{
cmd.Parameters[cmd.Parameters.Count - 1].SqlDbType = (SqlDbType)param.DBType;
}
if (param.Size != -1)
{
cmd.Parameters[cmd.Parameters.Count - 1].Size = param.Size;
}
cmd.Parameters[cmd.Parameters.Count - 1].Direction = param.ParameterDirection;
}
return(cmd);
}
