/// <summary>
/// Sets the record.
/// </summary>
/// <param name="type">The type.</param>
/// <param name="clob">The clob.</param>
/// <returns></returns>
private int SetRecord(string type, string clob)
{
var results = -1;
try
{
using (var conn = new OracleConnection(GeneralUtility.GetConnectStringFromInstance(_instance)))
{
var command = new OracleCommand("DBCLASSLIBRARY.UpdateClaimSubmissionLog", conn)
{
CommandType = CommandType.StoredProcedure
};
var oracleParameter1 = new OracleParameter();
var oracleParameter2 = new OracleParameter();
var oracleParameter3 = new OracleParameter();
var oracleParameter4 = new OracleParameter();
var oracleParameter5 = new OracleParameter();
oracleParameter1.Direction = ParameterDirection.Output;
oracleParameter1.OracleDbType = OracleDbType.Decimal;
oracleParameter1.ParameterName = "P_ID";
oracleParameter2.OracleDbType = OracleDbType.Varchar2;
oracleParameter2.ParameterName = "P_RQUID";
oracleParameter3.OracleDbType = OracleDbType.Varchar2;
oracleParameter3.ParameterName = "P_REQUESTTYPE";
oracleParameter4.OracleDbType = OracleDbType.Varchar2;
oracleParameter4.ParameterName = "P_TIMESTAMP";
oracleParameter5.OracleDbType = OracleDbType.Clob;
oracleParameter5.ParameterName = "CLOBDATA";
command.Parameters.Add(oracleParameter1);
command.Parameters.Add(oracleParameter2);
command.Parameters.Add(oracleParameter3);
command.Parameters.Add(oracleParameter4);
command.Parameters.Add(oracleParameter5);
command.Parameters[1].Value = _rquId;
command.Parameters[2].Value = type;
command.Parameters[3].Value = TimeStamp;
command.Parameters[4].Direction = ParameterDirection.Input;
command.Parameters[4].Size = clob.Length;
command.Parameters[4].Value = clob;
conn.Open();
command.ExecuteNonQuery();
results = Convert.ToInt32(command.Parameters[0].Value.ToString());
}
}
catch (Exception ex)
{
LastErrorMessage = ex.Message;
GeneralUtility.DebugLog(LastErrorMessage, Name);
}
return(results);
}
/// <summary>
/// Sets the source.
/// </summary>
public int SetSource(string source)
{
try
{
using (var conn = new OracleConnection(GeneralUtility.GetConnectStringFromInstance(Instance)))
{
var command = new OracleCommand("DBCLASSLIBRARY.CreateUpdateEntities", conn)
{
CommandType = CommandType.StoredProcedure
};
var parameters = new[]
{
new OracleParameter
{
Direction = ParameterDirection.Output,
OracleDbType = OracleDbType.Decimal,
ParameterName = "P_ID"
},
new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "P_RQUID",
Value = RquId
},
new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Clob,
ParameterName = "p_SOURCEDATA",
Value = source,
Size = source.Length
}
};
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
conn.Open();
command.ExecuteNonQuery();
return(Convert.ToInt32(command.Parameters[0].Value.ToString()));
}
}
catch (Exception ex)
{
LastError = ex.Message;
GeneralUtility.DebugLog(LastError, ToString());
return(-1);
}
}
/// <summary>
/// Gets the source.
/// </summary>
public string GetSource()
{
try
{
using (var conn = new OracleConnection(GeneralUtility.GetConnectStringFromInstance(Instance)))
{
var command = new OracleCommand("DBCLASSLIBRARY.GetEntity", conn)
{
CommandType = CommandType.StoredProcedure
};
var parameters = new[]
{
new OracleParameter
{
Direction = ParameterDirection.Input,
OracleDbType = OracleDbType.Varchar2,
ParameterName = "P_RQUID",
Value = RquId
},
new OracleParameter
{
Direction = ParameterDirection.Output,
OracleDbType = OracleDbType.Clob,
ParameterName = "p_SOURCEDATA",
}
};
foreach (var parameter in parameters)
{
command.Parameters.Add(parameter);
}
conn.Open();
command.ExecuteNonQuery();
var clobResults = (OracleClob)command.Parameters[1].Value;
return(clobResults.Value);
}
}
catch (Exception ex)
{
LastError = ex.Message;
GeneralUtility.DebugLog(LastError, ToString());
return(string.Empty);
}
}
/// <summary>
/// Gets the record.
/// </summary>
/// <param name="type">The type.</param>
/// <returns></returns>
private string GetRecord(string type)
{
var results = String.Empty;
try
{
using (var conn = new OracleConnection(GeneralUtility.GetConnectStringFromInstance(_instance)))
{
var command = new OracleCommand("DBCLASSLIBRARY.GetClaimSubLogData", conn)
{
CommandType = CommandType.StoredProcedure
};
var oracleParameter1 = new OracleParameter();
var oracleParameter2 = new OracleParameter();
var oracleParameter3 = new OracleParameter();
oracleParameter1.OracleDbType = OracleDbType.Varchar2;
oracleParameter1.ParameterName = "P_RQUID";
oracleParameter2.OracleDbType = OracleDbType.Varchar2;
oracleParameter2.ParameterName = "P_REQUESTTYPE";
oracleParameter3.OracleDbType = OracleDbType.Clob;
oracleParameter3.ParameterName = "CLOBDATA";
command.Parameters.Add(oracleParameter1);
command.Parameters.Add(oracleParameter2);
command.Parameters.Add(oracleParameter3);
command.Parameters[0].Value = _rquId;
command.Parameters[1].Value = type;
command.Parameters[2].Direction = ParameterDirection.Output;
conn.Open();
command.ExecuteNonQuery();
var clobResults = (OracleClob)command.Parameters[2].Value;
results = clobResults.Value;
}
}
catch (Exception ex)
{
LastErrorMessage = ex.Message;
GeneralUtility.DebugLog(LastErrorMessage, Name);
}
return(results);
}
/// <summary>
/// Resets the response.
/// </summary>
/// <returns></returns>
public bool ResetResponse()
{
var results = false;
try
{
using (var conn = new OracleConnection(GeneralUtility.GetConnectStringFromInstance(_instance)))
{
var command = new OracleCommand("DBCLASSLIBRARY.ResetClaimLogResponse", conn)
{
CommandType = CommandType.StoredProcedure
};
var oracleParameter1 = new OracleParameter();
var oracleParameter2 = new OracleParameter();
oracleParameter1.OracleDbType = OracleDbType.Varchar2;
oracleParameter1.ParameterName = "P_RQUID";
oracleParameter2.OracleDbType = OracleDbType.Int32;
oracleParameter2.ParameterName = "P_RESULTS";
command.Parameters.Add(oracleParameter1);
command.Parameters.Add(oracleParameter2);
command.Parameters[0].Value = _rquId;
command.Parameters[1].Direction = ParameterDirection.Output;
conn.Open();
command.ExecuteNonQuery();
results = 1 <= Convert.ToInt32(command.Parameters[1].Value);
}
}
catch (Exception ex)
{
LastErrorMessage = ex.Message;
GeneralUtility.DebugLog(LastErrorMessage, Name);
}
return(results);
}
/// <summary>
/// Executes this instance.
/// </summary>
/// <returns></returns>
public bool Execute()
{
try
{
_records.Clear();
if (_dbInstance.Length == 0)
{
_dbInstance = ApplicationConfiguration.Instance.DefaultInstance;
}
GeneralUtility.DebugLog("Execute entered", _activeRecordType);
var db = new OracleDatabase(GeneralUtility.GetConnectStringFromInstance(_dbInstance));
GeneralUtility.DebugLog("Database created", _activeRecordType);
return(ExecuteReader(db));
}
catch (Exception ex)
{
_lastErrorMessage = ex.Message;
GeneralUtility.LogError(_lastErrorMessage, _activeRecordType);
}
return(false);
}
/// <summary>
/// Execute stored procedure as defined by derived classes
/// </summary>
/// <returns>true if no errors</returns>
/// <remarks>submits parameters and executes target procedure</remarks>
/// <exception cref="FormatException"><c>FormatException</c>.</exception>
public bool Execute()
{
try
{
GeneralUtility.DebugLog("Execute entered", _name);
if (_databaseInstance.Length == 0)
{
_databaseInstance = ApplicationConfiguration.Instance.DefaultInstance;
}
var db = new OracleDatabase(GeneralUtility.GetConnectStringFromInstance(_databaseInstance));
GeneralUtility.DebugLog("Database created", _name);
var cmd = (OracleCommand)db.GetStoredProcCommand(_name);
if (_alParameters.Count > 0)
{
for (var i = 0; i < _alParameters.Count; i++)
{
var paramList = (SpParameters)_alParameters[_alParamNames[i]];
if (paramList.Direction == ParameterDirection.Input)
{
if (string.IsNullOrEmpty(paramList.Value))
{
db.AddInParameter(cmd, paramList.Name, paramList.DataType);
}
else
{
try
{
switch (paramList.DataType)
{
case DbType.Date:
GeneralUtility.DebugLog("converting date " + paramList.Value, _name);
var myDateParm = Convert.ToDateTime(paramList.Value);
db.AddInParameter(cmd, paramList.Name, paramList.DataType, myDateParm);
break;
case DbType.Int32:
var myInt32Parm = Convert.ToInt32(paramList.Value);
db.AddInParameter(cmd, paramList.Name, paramList.DataType, myInt32Parm);
break;
case DbType.Double:
var myDoubleParm = Convert.ToDouble(paramList.Value);
db.AddInParameter(cmd, paramList.Name, paramList.DataType, myDoubleParm);
break;
case DbType.Byte:
var val = Convert.ToByte(paramList.Value[0]);
db.AddInParameter(cmd, paramList.Name, paramList.DataType, val);
break;
default:
db.AddInParameter(cmd, paramList.Name, paramList.DataType, paramList.Value);
break;
}
}
catch (FormatException formatException)
{
throw new FormatException(string.Format("{0}'s value \"{1}\" is not of type {2}.", paramList.Name, paramList.Value, paramList.DataType), formatException);
}
}
}
else
{
if (paramList.DataType == DbType.Object)
{
_hasCursor = true;
db.AddParameter(cmd, paramList.Name, OracleDbType.RefCursor, 0, ParameterDirection.Output, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}
else if (paramList.Direction == ParameterDirection.ReturnValue)
{
db.AddParameter(cmd, paramList.Name, paramList.DataType, 4000, ParameterDirection.ReturnValue, true, 0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}
else
{
db.AddOutParameter(cmd, paramList.Name, paramList.DataType, 4000);
}
}
}
if (_hasCursor == false)
{
GeneralUtility.DebugLog("ExecuteNonQuery -start", _name);
db.ExecuteNonQuery(cmd);
GeneralUtility.DebugLog("ExecuteNonQuery -finish", _name);
for (var i = 0; i < _alParameters.Count; i++)
{
var paramList = (SpParameters)_alParameters[_alParamNames[i]];
if (paramList.Direction != ParameterDirection.Input)
{
paramList.Value = db.GetParameterValue(cmd, paramList.Name).ToString();
}
}
}
else
{
if (_data != null)
{
_data.Dispose();
_data = null;
GeneralUtility.DebugLog("Dataset reset", _name);
}
GeneralUtility.DebugLog("ExecuteDataSet -start", _name);
_data = db.ExecuteDataSet(cmd);
GeneralUtility.DebugLog("ExecuteDataSet -finish", _name);
}
}
return(true);
}
catch (Exception ex)
{
_lastErrorMessage = ex.Message;
GeneralUtility.DebugLog(_lastErrorMessage, _name);
return(false);
}
}
/// <summary>
/// Commits this instance.
/// </summary>
/// <returns></returns>
public virtual bool Commit()
{
bool results;
try
{
GeneralUtility.DebugLog("Transaction started", ToString());
using (var oTranScope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
if (DbInstance.Length == 0)
{
DbInstance = ApplicationConfiguration.Instance.DefaultInstance;
}
GeneralUtility.DebugLog("Execute entered", ToString());
var db = new Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase(GeneralUtility.GetConnectStringFromInstance(DbInstance));
GeneralUtility.DebugLog("Database created", ToString());
results = CommitInserted(db);
if (results)
{
if (CommitUpdated(db))
{
oTranScope.Complete();
GeneralUtility.DebugLog("Transaction complete ", ToString());
}
}
}
}
catch (Exception ex)
{
LastError = ex.Message;
GeneralUtility.LogError(LastError, ToString());
results = false;
}
ClearAll();
return(results);
}