/// <summary>
/// Used to display successful and unsuccessful status information
/// </summary>
/// <param name="lStatusCode"></param>
/// <param name="plistStatus"></param>
public void ShowStatusInfo(k_STATUS_CODE lStatusCode, CParameterList plistStatus)
{
if (plistStatus.Count == 0)
{
m_divStatus.InnerHtml = string.Empty;
return;
}
StringBuilder sbHTML = new StringBuilder();
sbHTML.Append("<table cellpadding=\"2\" width=\"99%\"><tr><td>");
sbHTML.Append("<span style=\"font-family: verdana,arial; color: ");
sbHTML.Append((lStatusCode == k_STATUS_CODE.Success) ? "darkgreen" : "darkred");
sbHTML.Append(";\">");
for (int i = 0; i < plistStatus.Count; i++)
{
sbHTML.Append(Server.HtmlEncode(plistStatus[i].ToString()));
sbHTML.Append("<br />");
}
sbHTML.Append("</span></td></tr></table><br />");
if (m_divStatus != null)
{
m_divStatus.InnerHtml = sbHTML.ToString();
}
}
/// <summary>
/// constructor
/// </summary>
public CStatus()
{
//initialize values
m_strStatusComment = String.Empty;
m_lStatusCode = k_STATUS_CODE.Success;
m_bStatus = true;
}
/// <summary>
/// method
/// shows status information
/// </summary>
/// <param name="divStatus"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatusComment"></param>
public void ShowStatusInfo(
HtmlGenericControl divStatus,
k_STATUS_CODE lStatusCode,
string strStatusComment)
{
StatusCode = lStatusCode;
StatusComment = strStatusComment;
if (string.IsNullOrEmpty(strStatusComment))
{
divStatus.InnerHtml = string.Empty;
return;
}
StringBuilder sbHTML = new StringBuilder();
sbHTML.Append("<span style=\"font-family: verdana,arial; color: ");
sbHTML.Append((lStatusCode == k_STATUS_CODE.Success) ? "darkgreen" : "darkred");
sbHTML.Append(";\">");
sbHTML.Append(Server.HtmlEncode(strStatusComment));
sbHTML.Append("</span><br /><br />");
if (divStatus != null)
{
divStatus.InnerHtml = sbHTML.ToString();
}
}
/// <summary>
/// method
/// shows status information
/// </summary>
/// <param name="divStatus"></param>
/// <param name="lStatusCode"></param>
/// <param name="plistStatus"></param>
public void ShowStatusInfo(
HtmlGenericControl divStatus,
k_STATUS_CODE lStatusCode,
CParameterList plistStatus)
{
StatusCode = lStatusCode;
StatusComment = string.Empty;
if (plistStatus.Count < 1)
{
divStatus.InnerHtml = string.Empty;
return;
}
StringBuilder sbHTML = new StringBuilder();
sbHTML.Append("<table cellpadding=\"2\" width=\"99%\"><tr><td>");
sbHTML.Append("<span style=\"font-family: verdana,arial; color: ");
sbHTML.Append((lStatusCode == k_STATUS_CODE.Success) ? "darkgreen" : "darkred");
sbHTML.Append(";\">");
for (int i = 0; i < plistStatus.Count; i++)
{
sbHTML.Append(Server.HtmlEncode(plistStatus[i].ToString()));
sbHTML.Append("<div class=\"app_horizontal_spacer\"></div>");
}
sbHTML.Append("</span></td></tr></table><div class=\"app_horizontal_spacer\"></div>");
if (divStatus != null)
{
divStatus.InnerHtml = sbHTML.ToString();
}
}
/// <summary>
/// clear the status, called by the masterpage after we
/// display status info
/// </summary>
public void ClearStatusInfo()
{
Session["StatusComment"] = string.Empty; //todo
Session["StatusCode"] = 0; //todo
m_strStatusComment = string.Empty;
m_lStatusCode = k_STATUS_CODE.Success;
}
//constructor
public CAppUserControlArgs(
k_EVENT lEvent,
k_STATUS_CODE lStatusCode,
string strStatusComment,
string strEventData)
{
Event = lEvent;
StatusCode = lStatusCode;
StatusComment = strStatusComment;
EventData = strEventData;
}
/// <summary>
/// NEW execute with status info returned
/// </summary>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <returns></returns>
public CStatus ExecuteOracleSP(string strSPName,
CParameterList ParamList)
{
k_STATUS_CODE lStatusCode = k_STATUS_CODE.Success;
string strStatus = String.Empty;
CStatus status = new CStatus();
status.Status = ExecuteOracleSP(strSPName,
ParamList,
out lStatusCode,
out strStatus);
status.StatusCode = lStatusCode;
status.StatusComment = strStatus;
return(status);
}
/// <summary>
/// Used to display successful and unsuccessful status information
/// </summary>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
public void ShowStatusInfo(k_STATUS_CODE lStatusCode, string strStatus)
{
if (string.IsNullOrEmpty(strStatus))
{
m_divStatus.InnerHtml = string.Empty;
return;
}
StringBuilder sbHTML = new StringBuilder();
sbHTML.Append("<table cellpadding=\"2\" width=\"99%\"><tr><td>");
sbHTML.Append("<span style=\"font-family: verdana,arial; color: ");
sbHTML.Append((lStatusCode == k_STATUS_CODE.Success) ? "darkgreen" : "darkred");
sbHTML.Append(";\">");
sbHTML.Append(Server.HtmlEncode(strStatus));
sbHTML.Append("</span></td></tr></table><br />");
if (m_divStatus != null)
{
m_divStatus.InnerHtml = sbHTML.ToString();
}
}
/// <summary>
/// writes to the FX_AUDIT table, used here and in CDataSet
/// </summary>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
public bool AuditTransaction(string strSPName,
CParameterList ParamList,
k_STATUS_CODE lInStatusCode,
string strInStatus,
out k_STATUS_CODE lStatusCode,
out string strStatus)
{
//System.Threading.Thread.Sleep(500);
lStatusCode = k_STATUS_CODE.Success;
strStatus = String.Empty;
//get the xml to audit
string strAuditXML = GetAuditXML(strSPName, ParamList);
//add status info
strAuditXML += "<status>" + strInStatus + "</status>";
//build the paramater list
CParameterList plistAudit = new CParameterList();
plistAudit.AddInputParameter("pi_vSessionClientIP", ParamList.GetItemByName("pi_vSessionClientIP").StringParameterValue);
plistAudit.AddInputParameter("pi_nUserID", ParamList.GetItemByName("pi_nUserID").LongParameterValue);
plistAudit.AddInputParameter("pi_vSPName", strSPName);
//audit data is a clob
plistAudit.AddInputParameterCLOB("pi_clAuditXML", strAuditXML);
//status is the status code
plistAudit.AddInputParameter("pi_nStatus", (long)lInStatusCode);
//execute sp and do not audit the audit
bool bStatus = ExecuteOracleSP(false,
"PCK_FX_SECURITY.AuditTransaction",
plistAudit,
out lStatusCode,
out strStatus);
return(bStatus);
}
/// <summary>
/// show status info scrollable
/// </summary>
/// <param name="lStatusCode"></param>
/// <param name="plistStatus"></param>
public void ShowStatusInfoScroll(k_STATUS_CODE lStatusCode, CParameterList plistStatus)
{
if (plistStatus.Count == 0)
{
m_divStatus.InnerHtml = string.Empty;
return;
}
StringBuilder sbHTML = new StringBuilder();
sbHTML.Append("<div style=\"width:100%; height:40px; overflow:auto;\">");
sbHTML.Append("<table cellpadding=\"2\" width=\"99%\"><tr><td>");
sbHTML.Append("<span style=\"font-family: verdana,arial; color: ");
sbHTML.Append((lStatusCode == k_STATUS_CODE.Success) ? "darkgreen" : "darkred");
sbHTML.Append(";\">");
for (int i = 0; i < plistStatus.Count; i++)
{
string strText = Server.HtmlEncode(plistStatus[i].ToString());
if (strText.Length > 80)
{
strText = strText.Substring(0, 79) + "...";
}
if (sbHTML.ToString().IndexOf(strText) < 0)
{
sbHTML.Append(strText);
sbHTML.Append("<br />");
}
}
sbHTML.Append("</span></td></tr></table></div><br />");
if (m_divStatus != null)
{
m_divStatus.InnerHtml = sbHTML.ToString();
}
}
/// <summary>
/// New call using CStatus, calls will be convertyed to this later
/// </summary>
/// <param name="conn"></param>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="ds"></param>
/// <returns></returns>
public CStatus GetOracleDataSet(CDataConnection conn,
string strSPName,
CParameterList ParamList,
out DataSet ds)
{
ds = null;
k_STATUS_CODE lStatusCode = k_STATUS_CODE.Success;
string strStatus = String.Empty;
CStatus status = new CStatus();
status.Status = GetOracleDataSet(conn,
strSPName,
ParamList,
out ds,
out lStatusCode,
out strStatus);
status.StatusCode = lStatusCode;
status.StatusComment = strStatus;
return(status);
}
/// <summary>
/// audit the query
/// </summary>
/// <param name="conn"></param>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lInStatusCode"></param>
/// <param name="strInStatue"></param>
/// <param name="lAuditStatusCode"></param>
/// <param name="strAuditStatus"></param>
/// <returns></returns>
public bool AuditTransaction(CDataConnection conn,
string strSPName,
CParameterList ParamList,
k_STATUS_CODE lInStatusCode,
string strInStatus,
out k_STATUS_CODE lAuditStatusCode,
out string strAuditStatus)
{
lAuditStatusCode = k_STATUS_CODE.Success;
strAuditStatus = String.Empty;
if (conn.Audit)
{
return(conn.AuditTransaction(strSPName,
ParamList,
lInStatusCode,
strInStatus,
out lAuditStatusCode,
out strAuditStatus));
}
return(true);
}
/// <summary>
/// Execute an oracle stored procedure using the
/// parameters passed in, using the connections audit property
/// </summary>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
public bool ExecuteOracleSP(string strSPName,
CParameterList ParamList,
out k_STATUS_CODE lStatusCode,
out string strStatus)
{
//execute the sp
bool bStatus = ExecuteOracleSP(m_bAudit,
strSPName,
ParamList,
out lStatusCode,
out strStatus);
//audit if auditing is turned on
if (m_bAudit)
{
k_STATUS_CODE lAuditStatusCode = k_STATUS_CODE.Success;
string strAuditStatus = String.Empty;
if (!AuditTransaction(strSPName,
ParamList,
lStatusCode,
strStatus,
out lAuditStatusCode,
out strAuditStatus))
{
//original error overrules audit error
if (lStatusCode == k_STATUS_CODE.Success)
{
lStatusCode = lAuditStatusCode;
strStatus = strAuditStatus;
}
}
}
return(bStatus);
}
/// <summary>
/// constructor
/// initializes the members of the instance with the passed values
/// </summary>
/// <param name="bStatus"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatusComment"></param>
public CStatus(bool bStatus, k_STATUS_CODE lStatusCode, string strStatusComment)
{
m_strStatusComment = strStatusComment;
m_lStatusCode = lStatusCode;
m_bStatus = bStatus;
}
/// <summary>
/// retrieve an Oracle dataset from an SP call
/// </summary>
/// <param name="conn"></param>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
public bool GetOracleDataSet(CDataConnection conn,
string strSPName,
CParameterList ParamList,
out DataSet ds,
out k_STATUS_CODE lStatusCode,
out string strStatus)
{
ds = null;
lStatusCode = k_STATUS_CODE.Success;
strStatus = String.Empty;
k_STATUS_CODE lAuditStatusCode = k_STATUS_CODE.Success;
string strAuditStatus = String.Empty;
//return null if no conn
if (conn == null)
{
ds = null;
lStatusCode = k_STATUS_CODE.Failed;
strStatus = "Unable to connect to data source, data connection is null!";
//audit the error if audting is on
AuditTransaction(conn,
strSPName,
ParamList,
lStatusCode,
strStatus,
out lAuditStatusCode,
out strAuditStatus);
return(false);
}
//create a new command object and set the command objects connection, text and type
//must use OracleCommand or you cannot get back a ref cur out param which is how
//we do things in medbase
OracleCommand cmd = new OracleCommand(); // OleDbCommand();
cmd.Connection = conn.Conn;
cmd.CommandText = strSPName;
cmd.CommandType = CommandType.StoredProcedure;
//add the parameters from the parameter list to the command parameter list
for (int i = 0; i < ParamList.Count; i++)
{
CParameter parameter = ParamList.GetItemByIndex(i);
if (parameter != null)
{
//create a new oledb param from our param and add it to the list
//this follows how we currently do it in medbase
//TODO: get direction, length etc from the parameter not hard coded below
OracleParameter oraParameter = new OracleParameter();
oraParameter.ParameterName = parameter.ParameterName;
//set the parameter value, default to string.
if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter)
{
oraParameter.Value = parameter.StringParameterValue;
oraParameter.OracleType = OracleType.VarChar;
oraParameter.Size = 4000;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter)
{
oraParameter.Value = parameter.LongParameterValue;
oraParameter.OracleType = OracleType.Int32;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter)
{
oraParameter.Value = parameter.DoubleParameterValue;
oraParameter.OracleType = OracleType.Double;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter)
{
if (!CDataUtils.IsDateNull(parameter.DateParameterValue))
{
//if the date is not null then set the value
oraParameter.Value = parameter.DateParameterValue;
}
else
{
//set value to DBNull if date is null
oraParameter.Value = DBNull.Value;
}
oraParameter.OracleType = OracleType.DateTime;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.CLOBParameter)
{
//You must begin a transaction before obtaining a temporary LOB.
//Otherwise, the OracleDataReader may fail to obtain data later.
OracleTransaction transaction = conn.Conn.BeginTransaction();
//make a new command
OracleCommand command = conn.Conn.CreateCommand();
command.Connection = conn.Conn;
command.Transaction = transaction;
command.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
command.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
//get a temp lob
OracleLob tempLob = (OracleLob)command.Parameters[0].Value;
//begin batch
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
//convert string to byte array and write to lob,
//note:encoding must be set to match oracle encoding
//default encoding for oracle can be found by running
//SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
System.Text.ASCIIEncoding encASCII = new System.Text.ASCIIEncoding();
//ascii chars
byte[] buffAsciiBytes = encASCII.GetBytes(parameter.CLOBParameterValue);
//destination to convert to. convert from ascii to unicode
UnicodeEncoding encDest = new UnicodeEncoding();
byte[] buffDest = Encoding.Convert(encASCII, encDest, buffAsciiBytes);
//write the converted data to the lob
tempLob.Write(buffDest,
0,
buffDest.Length);
//end batch
tempLob.EndBatch();
//set the value of the param = lob
oraParameter.OracleType = OracleType.Clob;
//oraParameter.OracleType = OracleType.NClob;
oraParameter.Value = tempLob;
//all done so commit;
transaction.Commit();
}
else
{
oraParameter.Value = parameter.StringParameterValue;
oraParameter.OracleType = OracleType.VarChar;
oraParameter.Size = 4000;
}
oraParameter.Direction = parameter.Direction;
cmd.Parameters.Add(oraParameter);
}
}
//add in out params for stored proc, all sp's will return a status 1 = good, 0 = bad
//status
ParamList.AddOutputParameter("po_nStatusCode", 0);
OracleParameter oraStatusParameter = new OracleParameter("po_nStatusCode",
OracleType.Int32);
oraStatusParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraStatusParameter);
//
//comment
ParamList.AddOutputParameter("po_vStatusComment", String.Empty);
OracleParameter oraCommentParameter = new OracleParameter("po_vStatusComment",
OracleType.VarChar,
4000);
oraCommentParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraCommentParameter);
//
//now add an out parameter to hold the ref cursor to the commands parameter list
//returned ref cursor must always be named "RS" because OracleClient binds these
//parameters by name, so you must name your parameter correctly
//so the OracleParameter must be named the same thing.
OracleParameter oraRSParameter = new OracleParameter("RS",
OracleType.Cursor);
//OracleType.Cursor
oraRSParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraRSParameter);
//create a new dataset to hold the conntent of the reference cursor
ds = new DataSet();
//create an adapter and fill the dataset.
//datasets are completely disconnected and provide
//the most flexibility for later porting to a web service etc.
try
{
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
dataAdapter.Fill(ds);
}
catch (InvalidOperationException e)
{
ds = null;
lStatusCode = k_STATUS_CODE.Failed;
strStatus = e.Message;
//audit if auditing is turned on
AuditTransaction(conn,
strSPName,
ParamList,
lStatusCode,
strStatus,
out lAuditStatusCode,
out strAuditStatus);
return(false);
}
catch (OracleException e)
{
ds = null;
lStatusCode = k_STATUS_CODE.Failed;
strStatus = e.Message;
//audit if auditing is turned on
AuditTransaction(conn,
strSPName,
ParamList,
lStatusCode,
strStatus,
out lAuditStatusCode,
out strAuditStatus);
return(false);
}
if (lStatusCode == k_STATUS_CODE.Success)
{
//now read back out params into our list
for (int i = 0; i < ParamList.Count; i++)
{
CParameter parameter = ParamList.GetItemByIndex(i);
if (parameter != null)
{
if (parameter.Direction == ParameterDirection.Output ||
parameter.Direction == ParameterDirection.InputOutput)
{
foreach (OracleParameter oP in cmd.Parameters)
{
if (oP.ParameterName.Equals(parameter.ParameterName))
{
if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter)
{
if (oP.Value != null)
{
parameter.StringParameterValue = oP.Value.ToString();
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter)
{
if (oP.Value != null)
{
if (!oP.Value.ToString().Equals(String.Empty))
{
parameter.LongParameterValue = Convert.ToInt64(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter)
{
if (oP.Value != null)
{
if (!oP.Value.ToString().Equals(String.Empty))
{
parameter.DateParameterValue = Convert.ToDateTime(oP.Value);
}
}
}
else
{
parameter.StringParameterValue = oP.Value.ToString();
}
}
}
}
}
}
}
//set status code and text
lStatusCode = ParamList.GetStatusCode();
strStatus = ParamList.GetStatusComment();
//audit if auditing is turned on
if (!AuditTransaction(conn,
strSPName,
ParamList,
lStatusCode,
strStatus,
out lAuditStatusCode,
out strAuditStatus))
{
//original error overrules audit error
if (lStatusCode == k_STATUS_CODE.Success)
{
lStatusCode = lAuditStatusCode;
strStatus = strAuditStatus;
}
}
if (lStatusCode != k_STATUS_CODE.Success)
{
return(false);
}
return(true);
}
/// <summary>
/// Execute an oracle stored procedure using the
/// parameters passed in
/// </summary>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
private bool ExecuteOracleSP(bool bAudit,
string strSPName,
CParameterList ParamList,
out k_STATUS_CODE lStatusCode,
out string strStatus)
{
lStatusCode = k_STATUS_CODE.Success;
strStatus = String.Empty;
//return null if no conn
if (m_OracleConnection == null)
{
lStatusCode = k_STATUS_CODE.Failed;
strStatus = "Unable to connect to data source, Data Connection is null";
return(false);
}
//create a new command object and set the command objects connection, text and type
//must use OracleCommand or you cannot get back a ref cur out param which is how
//we do things in medbase
OracleCommand cmd = new OracleCommand();
cmd.Connection = m_OracleConnection;
cmd.CommandText = strSPName;
cmd.CommandType = CommandType.StoredProcedure;
//add the parameters from the parameter list to the command parameter list
for (int i = 0; i < ParamList.Count; i++)
{
CParameter parameter = ParamList.GetItemByIndex(i);
if (parameter != null)
{
//create a new oledb param from our param and add it to the list
//this follows how we currently do it in medbase
OracleParameter oraParameter = new OracleParameter();
oraParameter.ParameterName = parameter.ParameterName;
//set the parameter value, default to string. Probably a better way than the
//if then else, but this works and we can find it later,
if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter)
{
oraParameter.Value = parameter.StringParameterValue;
oraParameter.OracleType = OracleType.VarChar;
oraParameter.Size = 4000;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter)
{
oraParameter.Value = parameter.LongParameterValue;
oraParameter.OracleType = OracleType.Int32;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter)
{
oraParameter.Value = parameter.DoubleParameterValue;
oraParameter.OracleType = OracleType.Double;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.IntParameter)
{
oraParameter.Value = parameter.IntParameterValue;
oraParameter.OracleType = OracleType.Int32;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter)
{
oraParameter.Value = parameter.DateParameterValue;
if (!CDataUtils.IsDateNull(parameter.DateParameterValue))
{
//if the date is not null then set the value
oraParameter.Value = parameter.DateParameterValue;
}
else
{
//set value to DBNull if date is null
oraParameter.Value = DBNull.Value;
}
oraParameter.OracleType = OracleType.DateTime;
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.CLOBParameter)
{
//You must begin a transaction before obtaining a temporary LOB.
//Otherwise, the OracleDataReader may fail to obtain data later.
OracleTransaction transaction = m_OracleConnection.BeginTransaction();
//make a new command
OracleCommand command = m_OracleConnection.CreateCommand();
command.Connection = m_OracleConnection;
command.Transaction = transaction;
command.CommandText = "declare xx clob; begin dbms_lob.createtemporary(xx, false, 0); :tempclob := xx; end;";
command.Parameters.Add(new OracleParameter("tempclob", OracleType.Clob)).Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
//get a temp lob
OracleLob tempLob = (OracleLob)command.Parameters[0].Value;
//begin batch
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
//convert string to byte array and write to lob,
//note:encoding must be set to match oracle encoding
//default encoding for oracle can be found by running
//SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
System.Text.ASCIIEncoding encASCII = new System.Text.ASCIIEncoding();
//ascii chars
byte[] buffAsciiBytes = encASCII.GetBytes(parameter.CLOBParameterValue);
//destination to convert to. convert from ascii to unicode
UnicodeEncoding encDest = new UnicodeEncoding();
byte[] buffDest = Encoding.Convert(encASCII, encDest, buffAsciiBytes);
//write the converted data to the lob
tempLob.Write(buffDest,
0,
buffDest.Length);
//end batch
tempLob.EndBatch();
//set the value of the param = lob
oraParameter.OracleType = OracleType.Clob;
//oraParameter.OracleType = OracleType.NClob;
oraParameter.Value = tempLob;
//all done so commit;
transaction.Commit();
}
else
{
oraParameter.Value = parameter.StringParameterValue;
oraParameter.OracleType = OracleType.VarChar;
oraParameter.Size = 4000;
}
oraParameter.Direction = parameter.Direction;
cmd.Parameters.Add(oraParameter);
}
}
//add in out params for stored proc, all sp's will return a status 0 = good, 1 = bad
//
//status
ParamList.AddOutputParameter("po_nStatusCode", 0);
OracleParameter oraStatusParameter = new OracleParameter("po_nStatusCode",
OracleType.Int32);
oraStatusParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraStatusParameter);
//
//comment
ParamList.AddOutputParameter("po_vStatusComment", String.Empty);
OracleParameter oraCommentParameter = new OracleParameter("po_vStatusComment",
OracleType.VarChar,
4000);
oraCommentParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraCommentParameter);
//
try
{
//execute the stored proc and move the out param values back into
//our list
cmd.ExecuteNonQuery();
for (int i = 0; i < ParamList.Count; i++)
{
CParameter parameter = ParamList.GetItemByIndex(i);
if (parameter != null)
{
if (parameter.Direction == ParameterDirection.Output ||
parameter.Direction == ParameterDirection.InputOutput)
{
foreach (OracleParameter oP in cmd.Parameters)
{
if (oP.ParameterName.Equals(parameter.ParameterName))
{
if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.StringParameter)
{
if (oP.Value != null)
{
parameter.StringParameterValue = oP.Value.ToString();
parameter.StringParameterValue = parameter.StringParameterValue.Trim();
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.LongParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.LongParameterValue = Convert.ToInt64(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DoubleParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.DoubleParameterValue = Convert.ToDouble(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.BoolParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.BoolParameterValue = Convert.ToBoolean(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.IntParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
parameter.IntParameterValue = Convert.ToInt32(oP.Value);
}
}
}
else if (parameter.ParameterType == k_DATA_PARAMETER_TYPE.DateParameter)
{
if (oP.Value != null)
{
if (oP.Value.ToString() != String.Empty)
{
if (!oP.Value.ToString().Equals(String.Empty))
{
parameter.DateParameterValue = Convert.ToDateTime(oP.Value);
}
}
}
}
else
{
if (oP.Value != null)
{
parameter.StringParameterValue = oP.Value.ToString();
parameter.StringParameterValue = parameter.StringParameterValue.Trim();
}
}
}
}
}
}
}
lStatusCode = ParamList.GetStatusCode();
strStatus = ParamList.GetStatusComment();
if (lStatusCode == k_STATUS_CODE.Success)
{
return(true);
}
return(false);
}
catch (InvalidOperationException e)
{
strStatus = e.Message;
lStatusCode = k_STATUS_CODE.Failed;
}
catch (OracleException e)
{
strStatus = e.Message;
lStatusCode = k_STATUS_CODE.Failed;
}
return(false);
}
/// <summary>
/// show status information
/// </summary>
/// <param name="lStatusCode"></param>
/// <param name="strStatusComment"></param>
public void ShowStatusInfo(k_STATUS_CODE lStatusCode, string strStatusComment)
{
ShowStatusInfo(divMasterStatus, lStatusCode, strStatusComment);
}
/// <summary>
/// Show Status Info
/// </summary>
/// <param name="lStatusCode"></param>
/// <param name="strStatusComment"></param>
public void ShowStatusInfo(k_STATUS_CODE lStatusCode, CParameterList pList)
{
ShowStatusInfo(divMasterStatus, lStatusCode, pList);
}
/// <summary>
/// constructor
/// initializes the members of the instance with the passes values
/// </summary>
/// <param name="bStatus"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatusComment"></param>
/// <param name="strValue"></param>
public CStringStatus(bool bStatus, k_STATUS_CODE lStatusCode, string strStatusComment, string strValue)
: base(bStatus, lStatusCode, strStatusComment)
{
m_strValue = strValue;
}