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; }