internal void DeriveParameters()
{
if (commandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(String.Format("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
}
ValidateCommand("DeriveParameters");
SqlParameterCollection localParameters = new SqlParameterCollection(this);
localParameters.Add("@procedure_name", SqlDbType.NVarChar, CommandText.Length).Value = CommandText;
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc(sql, localParameters.MetaParameters, 0, true);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
SqlDataReader reader = new SqlDataReader(this);
parameters.Clear();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(dbValues);
parameters.Add(new SqlParameter(dbValues));
}
reader.Close();
}
internal void DeriveParameters()
{
if (commandType != CommandType.StoredProcedure)
{
throw new InvalidOperationException(String.Format("SqlCommand DeriveParameters only supports CommandType.StoredProcedure, not CommandType.{0}", commandType));
}
ValidateCommand("DeriveParameters", false);
string procName = CommandText;
string schemaName = String.Empty;
int dotPosition = procName.IndexOf('.');
if (dotPosition >= 0)
{
schemaName = procName.Substring(0, dotPosition);
procName = procName.Substring(dotPosition + 1);
}
procName = EscapeProcName(procName, false);
schemaName = EscapeProcName(schemaName, true);
SqlParameterCollection localParameters = new SqlParameterCollection(this);
localParameters.Add("@procedure_name", SqlDbType.NVarChar, procName.Length).Value = procName;
if (schemaName.Length > 0)
{
localParameters.Add("@procedure_schema", SqlDbType.NVarChar, schemaName.Length).Value = schemaName;
}
string sql = "sp_procedure_params_rowset";
try {
Connection.Tds.ExecProc(sql, localParameters.MetaParameters, 0, true);
} catch (TdsTimeoutException ex) {
Connection.Tds.Reset();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
} catch (TdsInternalException ex) {
Connection.Close();
throw SqlException.FromTdsInternalException((TdsInternalException)ex);
}
SqlDataReader reader = new SqlDataReader(this);
parameters.Clear();
object[] dbValues = new object[reader.FieldCount];
while (reader.Read())
{
reader.GetValues(dbValues);
parameters.Add(new SqlParameter(dbValues));
}
reader.Close();
if (parameters.Count == 0)
{
throw new InvalidOperationException("Stored procedure '" + procName + "' does not exist.");
}
}
public override void SetParameters( SqlParameterCollection parameters )
{
parameters.Clear();
parameters.Add( "@id", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@itemId", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@source", SqlDbType.VarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@bankId", SqlDbType.NVarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@date", SqlDbType.DateTime ).Value = DBNull.Value;
parameters.Add( "@amount", SqlDbType.Decimal ).Value = DBNull.Value;
parameters.Add( "@number", SqlDbType.VarChar, 20 ).Value = DBNull.Value;
parameters.Add( "@description", SqlDbType.NVarChar, 200 ).Value = DBNull.Value;
parameters.Add( "@machine", SqlDbType.NVarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@now", SqlDbType.DateTime ).Value = DBNull.Value;
}
/// <summary>
/// Executes any SQL statement on the database.
/// </summary>
/// <param name="Parameters" >The Input/Output Parameters</param>
/// <param name="ResultSetType" >The Desired Result Set Type</param>
/// <param name="StoredProcedure" >A Command Object encapsulating the Stored Procedure Call</param>
/// <param name="SQLCommandType">The SQL command type.</param>
/// <value>An Object containing the Desired Result Set Type</value>
private object executeSQL(SqlCommand SqlCommand, TResultSetTypes ResultSetType, CommandType SQLCommandType, SqlParameterCollection Parameters)
{
SqlDataAdapter da = null;
DataTable returnDt = null;
DataSet returnDs = null;
Exception SaveException = null;
if (mTimeout != 0) {
SqlCommand.CommandTimeout = mTimeout;
mTimeout = 0;
}
lock (mSynchLock) {
try {
OpenIfClosed(SqlCommand);
SqlCommand.CommandType = SQLCommandType;
if (Parameters != null) {
List<SqlParameter> plist = new List<SqlParameter>();
foreach (SqlParameter p in Parameters) {
if (!this.NVarcharAllowed && p.SqlDbType == SqlDbType.NVarChar)
p.SqlDbType = SqlDbType.VarChar;
plist.Add(p);
}
Parameters.Clear();
SqlCommand.Parameters.AddRange(plist.ToArray());
}
if (ResultSetType != TResultSetTypes.None && ResultSetType != TResultSetTypes.Scalar) {
da = new SqlDataAdapter(SqlCommand);
}
for (int i = 1; i <= mnMAX_COMMAND_EXECUTION_RETRIES; i++) {
OpenIfClosed(SqlCommand);
try {
switch (ResultSetType) {
case TResultSetTypes.Scalar:
return SqlCommand.ExecuteScalar();
case TResultSetTypes.None:
return SqlCommand.ExecuteNonQuery();
case TResultSetTypes.Single:
returnDt = new DataTable();
fillFromAdapter(da, returnDt);
return returnDt;
case TResultSetTypes.Multiple:
returnDs = new DataSet();
fillFromAdapter(da, returnDs);
return returnDs;
default:
throw new EnumerationValueException(ResultSetType);
}
} catch (SqlException sqlEx) {
if (InTransaction() != null)
throw sqlEx;
if ((isTimeRelatedError(sqlEx.ToString()) ||
sqlEx.Message == "Unknown error." ||
sqlEx.Message == "A severe error occurred on the current command. The results, if any, should be discarded.") && i < mnMAX_COMMAND_EXECUTION_RETRIES) {
AuditLog.AuditException((Exception)sqlEx, "sql will retry", null);
Thread.Sleep(TimeSpan.FromSeconds(SECONDS_TO_WAIT_BEFORE_A_COMMAND_RETRY));
continue;
} else
throw sqlEx;
} catch (InvalidOperationException iEx) {
if (InTransaction() != null)
throw iEx;
if ((isTimeRelatedError(iEx.ToString()) ||
iEx.Message == "Unknown error." ||
iEx.Message == "A severe error occurred on the current command. The results, if any, should be discarded.") && i < mnMAX_COMMAND_EXECUTION_RETRIES) {
AuditLog.AuditException((Exception)iEx, "sql will retry", null);
Thread.Sleep(TimeSpan.FromSeconds(SECONDS_TO_WAIT_BEFORE_A_COMMAND_RETRY));
continue;
} else
throw iEx;
}
}
} catch (SqlException sqlEx) {
SaveException = (Exception)sqlEx;
//Lets Use The MS Application Block here w/a custom publisher that sends out an eMail & Publishes exception data to a structure in the Shared Database.
//If Shared DB is not available then publish exception data to the event log.
throw sqlEx;
} catch (Exception ex) {
SaveException = ex;
//Lets Use The MS Application Block here w/a custom publisher that sends out an eMail & Publishes exception data to a structure in the Shared Database.
//If Shared DB is not available then publish exception data to the event log.
throw new Exception(ex.Message, ex);
} finally {
if (SaveException != null)
AuditLog.AuditException(SaveException, "sqlutil failure", null);
if (da != null)
da.Dispose();
if (returnDt != null)
returnDt.Dispose();
if (returnDs != null)
returnDs.Dispose();
if (SqlCommand != null)
SqlCommand.Dispose();
// Close Active Connection to Database
CloseActiveConnection();
}
return null;
}
}
public override void SetParameters( SqlParameterCollection parameters )
{
parameters.Clear();
parameters.Add( "@id", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@date", SqlDbType.DateTime ).Value = DBNull.Value;
parameters.Add( "@amount", SqlDbType.Decimal ).Value = DBNull.Value;
parameters.Add( "@payee", SqlDbType.NVarChar, 200 ).Value = DBNull.Value;
parameters.Add( "@memo", SqlDbType.NVarChar, 200 ).Value = DBNull.Value;
parameters.Add( "@generated", SqlDbType.Bit ).Value = DBNull.Value;
parameters.Add( "@voided", SqlDbType.Bit ).Value = DBNull.Value;
parameters.Add( "@machine", SqlDbType.NVarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@now", SqlDbType.DateTime ).Value = DBNull.Value;
}
public override void SetParameters( SqlParameterCollection parameters )
{
parameters.Clear();
parameters.Add( "@id", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@name", SqlDbType.NVarChar, 50 ).Value = DBNull.Value;
parameters.Add( "@type", SqlDbType.VarChar, 20 ).Value = DBNull.Value;
parameters.Add( "@source", SqlDbType.VarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@startingBalance", SqlDbType.Decimal ).Value = DBNull.Value;
parameters.Add( "@startingDate", SqlDbType.DateTime ).Value = DBNull.Value;
parameters.Add( "@machine", SqlDbType.NVarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@now", SqlDbType.DateTime ).Value = DBNull.Value;
}
public override void SetParameters( SqlParameterCollection parameters )
{
parameters.Clear();
parameters.Add( "@id", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@transId", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@onlineId", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@accountId", SqlDbType.Int ).Value = DBNull.Value;
parameters.Add( "@date", SqlDbType.DateTime ).Value = DBNull.Value;
parameters.Add( "@type", SqlDbType.Char, 1 ).Value = DBNull.Value;
parameters.Add( "@number", SqlDbType.VarChar, 20 ).Value = DBNull.Value;
parameters.Add( "@amount", SqlDbType.Decimal ).Value = DBNull.Value;
parameters.Add( "@memo", SqlDbType.NVarChar, 200 ).Value = DBNull.Value;
parameters.Add( "@category", SqlDbType.NVarChar, 20 ).Value = DBNull.Value;
parameters.Add( "@reconciled", SqlDbType.Bit ).Value = DBNull.Value;
parameters.Add( "@machine", SqlDbType.NVarChar, 100 ).Value = DBNull.Value;
parameters.Add( "@now", SqlDbType.DateTime ).Value = DBNull.Value;
}