public SqlReader ExecuteReader(SpCall spCall)
{
if (logger.IsDebugEnabled)
{
LogCommandInfo("Select", spCall);
}
SetConnection();
SqlCommand cmd = null;
try
{
cmd = CreateCommand(spCall);
SqlReader reader = new SqlReader(cmd, spCall);
readers.Add(reader);
return(reader);
}
catch (SqlException e)
{
logger.Fatal(e, string.Format("Exception while executing spCall{0}{1}", Environment.NewLine, spCall.ToString()));
throw;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
}
}
public T ExecuteScalar <T>(SpCall spCall)
{
if (logger.IsDebugEnabled)
{
LogCommandInfo("ExecuteScalar", spCall);
}
SetConnection();
using (SqlCommand cmd = CreateCommand(spCall))
{
DateTime start = DateTime.Now;
try
{
object result = cmd.ExecuteScalar();
return((T)Convert.ChangeType(result, typeof(T)));
}
catch (SqlException e)
{
logger.Fatal(e, string.Format("Exception while executing spCall{0}{1}", Environment.NewLine, spCall.ToString()));
throw;
}
finally
{
TimeSpan span = DateTime.Now - start;
if (!InTransaction)
{
CloseDbConnection();
}
cmd.Parameters.Clear();
}
}
}
SqlCommand CreateCommand(SpCall spCall)
{
SqlCommand cmd = new SqlCommand(spCall.ProcName, connection);
cmd.CommandType = spCall.CommandType;
cmd.CommandTimeout = commandTimeout;
if (InTransaction)
{
cmd.Transaction = transaction;
}
foreach (SqlParameter parameter in spCall.Parameters.Values)
{
if (parameter.DbType == DbType.Date || parameter.DbType == DbType.DateTime || parameter.DbType == DbType.DateTime2)
{
object o = parameter.Value;
if (o != null && Type.GetTypeCode(o.GetType()) == TypeCode.DateTime)
{
if (minimumDBDate > (DateTime)o)
{
parameter.Value = DBNull.Value;
}
}
}
cmd.Parameters.Add(parameter);
}
return(cmd);
}
public int ExecuteNonQuery(SpCall spCall)
{
SetConnection();
SqlCommand command = CreateCommand(spCall);
if (logger.IsDebugEnabled)
{
LogCommandInfo("ExecuteNonQuery", spCall);
}
if (command == null)
{
throw new ArgumentNullException("command");
}
try
{
DateTime start = DateTime.Now;
try
{
return(command.ExecuteNonQuery());
}
catch (SqlException e)
{
logger.Fatal(e, string.Format("Exception while executing spCall{0}{1}", Environment.NewLine, spCall.ToString()));
throw;
}
finally
{
TimeSpan span = DateTime.Now - start;
if (!InTransaction)
{
CloseDbConnection();
}
command.Parameters.Clear();
/*
* if (span.TotalMilliseconds > thresholdTimeValue)
* {
* LogLongRunningCall(span, sp);
* }*/
}
}
finally
{
command.Dispose();
}
}
public DataTable ExecuteDataTable(SpCall spCall)
{
if (logger.IsDebugEnabled)
{
LogCommandInfo("ExecuteDataTable", spCall);
}
SetConnection();
DataTable dt = CreateDataTable(spCall);
if (!InTransaction)
{
CloseDbConnection();
}
return(dt);
}
protected void LogCommandInfo(string method, SpCall sp)
{
//if (!Configuration.ConfigManager.LogDbCommands)
//return;
List <string> args = new List <string>();
foreach (SqlParameter parameter in sp.Parameters.Values)
{
if (parameter.Value == null || parameter.Value == DBNull.Value)
{
args.Add(string.Format("{0}=NULL", parameter.ParameterName));
}
else
{
switch (parameter.SqlDbType)
{
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.NText:
case SqlDbType.NVarChar:
case SqlDbType.VarChar:
case SqlDbType.Timestamp:
case SqlDbType.Text:
case SqlDbType.Xml:
args.Add(string.Format("{0}='{1}'", parameter.ParameterName, parameter.Value));
break;
case SqlDbType.DateTime:
case SqlDbType.SmallDateTime:
DateTime dt = (DateTime)parameter.Value;
args.Add(string.Format("{0}='{1}'", parameter.ParameterName, dt.ToString("yyyyMMdd")));
break;
default:
args.Add(string.Format("{0}={1}", parameter.ParameterName, parameter.Value));
break;
}
}
}
StringBuilder sb = new StringBuilder();
sb.AppendFormat("{0} : {1} ", method, sp.ProcName);
sb.Append(string.Join(", ", args.ToArray()));
logger.Debug(sb.ToString());
}
protected virtual SqlCommand CreateCommand(SpCall sp, int timeout)
{
SqlCommand cmd = new SqlCommand(sp.ProcName, connection);
cmd.CommandType = sp.CommandType;
cmd.CommandTimeout = timeout;
if (InTransaction)
{
cmd.Transaction = transaction;
}
foreach (SqlParameter parameter in sp.Parameters.Values)
{
if (parameter.DbType == DbType.Date || parameter.DbType == DbType.DateTime || parameter.DbType == DbType.DateTime2)
{
object o = parameter.Value;
if (o != null && Type.GetTypeCode(o.GetType()) == TypeCode.DateTime)
{
if (minimumDBDate > (DateTime)o)
{
parameter.Value = DBNull.Value;
}
}
}
/*
* if ((parameter.SqlDbType == SqlDbType.Char || parameter.SqlDbType == SqlDbType.NChar || parameter.SqlDbType == SqlDbType.NVarChar || parameter.SqlDbType == SqlDbType.VarChar)
* && privityService.IsPrivateInformation(parameter.ParameterName))
* {
* var parameterValue = privityService.Encrypt(parameter.Value.ToString());
* parameter.Value = parameterValue;
* if (parameterValue != null && parameter.Size < parameterValue.Length)
* {
* parameter.Size = parameterValue.Length;
* }
* }*/
cmd.Parameters.Add(parameter);
}
return(cmd);
}
protected virtual DataTable CreateDataTable(SpCall spCall)
{
using (SqlCommand command = CreateCommand(spCall))
{
using (SqlDataReader reader = command.ExecuteReader())
{
DataTable dt = new DataTable();
int fieldCount = reader.FieldCount;
for (int i = 0; i < fieldCount; i++)
{
dt.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
}
while (reader.Read())
{
object[] values = new object[fieldCount];
reader.GetValues(values);
dt.Rows.Add(values);
}
return(dt);
}
}
}
public DataSet ExecuteDataSet(SpCall spCall)
{
if (logger.IsDebugEnabled)
{
LogCommandInfo("ExecuteDataSet", spCall);
}
SetConnection();
using (SqlCommand cmd = CreateCommand(spCall))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DateTime start = DateTime.Now;
try
{
DataSet ds = new DataSet();
da.Fill(ds);
return(ds);
}
catch (SqlException e)
{
logger.Fatal(e, string.Format("Exception while executing spCall{0}{1}", Environment.NewLine, spCall.ToString()));
throw;
}
finally
{
TimeSpan span = DateTime.Now - start;
if (!InTransaction)
{
CloseDbConnection();
}
cmd.Parameters.Clear();
}
}
}
}
internal SqlReader(SqlCommand command, SpCall sp)
{
this.sp = sp;
ContainedReader = command.ExecuteReader();
rowIndex = 0;
}
internal SqlReader(SqlDataReader reader, SpCall sp)
{
ContainedReader = reader;
this.sp = sp;
rowIndex = 0;
}
