/// <summary>
/// US:1945 US:852 US:1883 US:834 helper to process checklist items, called from multiple places
/// </summary>
/// <param name="conn"></param>
/// <param name="data"></param>
/// <param name="dsChecklistItems"></param>
/// <returns></returns>
public CStatus RefreshPatientCheckList(CDataConnection conn,
CData data,
DataSet dsChecklistItems)
{
CStatus status = new CStatus();
//this class is used to do all transfers
//from MDWS to the VAPPCT database
CMDWSTransfer xfer = new CMDWSTransfer(data);
//2.loop over all items and process each one
foreach (DataTable table in dsChecklistItems.Tables)
{
foreach (DataRow dr in table.Rows)
{
if (dr["item_type_id"] != null)
{
switch (Convert.ToInt32(dr["item_type_id"]))
{
case (int)k_ITEM_TYPE_ID.Collection:
//WriteEvent(data, "Collection", "Collection");
break;
case (int)k_ITEM_TYPE_ID.Laboratory:
//WriteEvent(data, "Laboratory", "Laboratory");
status = ProcessLab(data, xfer, dr);
if (!status.Status)
{
//write the start event to the event table
WriteEvent(data, "ProcessLab", status.StatusComment);
return(status);
}
break;
case (int)k_ITEM_TYPE_ID.QuestionFreeText:
//WriteEvent(data, "QuestionFreeText", "QuestionFreeText");
break;
case (int)k_ITEM_TYPE_ID.QuestionSelection:
//WriteEvent(data, "QuestionSelection", "QuestionSelection");
break;
case (int)k_ITEM_TYPE_ID.NoteTitle:
status = ProcessNoteTitle(data, xfer, dr);
if (!status.Status)
{
//write the start event to the event table
WriteEvent(data, "ProcessNoteTitle", status.StatusComment);
return(status);
}
break;
}
}
}
}
return(status);
}
/// <summary>
/// US:1883 US:834 refresh a patient checklist
/// </summary>
/// <param name="strPatientID"></param>
/// <param name="strPatChecklistID"></param>
/// <returns></returns>
public CStatus RefreshPatientCheckList(CDataConnection conn,
CData data,
string strPatientID,
long lPatChecklistID)
{
//todo: check conn and data before continuing
//get all the items for this patient checklist
DataSet dsChecklistItems = null;
CVAPPCTCommData commData = new CVAPPCTCommData(data);
CStatus status = commData.GetPatChecklistItemDS(lPatChecklistID, out dsChecklistItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "GetPatChecklistItemDS", status.StatusComment);
return(status);
}
//refresh the checklist items
status = RefreshPatientCheckList(
conn,
data,
dsChecklistItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "RefreshPatientCheckList", status.StatusComment);
return(status);
}
//refresh the checklist collection items
DataSet dsCLCollectionItems = null;
status = commData.GetPatientCLCollectionItemDS(lPatChecklistID, out dsCLCollectionItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "GetPatientCLCollectionItemDS", status.StatusComment);
return(status);
}
//refresh the checklist items
status = RefreshPatientCheckList(
conn,
data,
dsCLCollectionItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "RefreshPatientCheckList", status.StatusComment);
return(status);
}
return(status);
}
/// <summary>
/// constructor
/// </summary>
public BaseMaster()
{
//create a new dataconnection object
m_DBConnection = new CDataConnection();
//clear status
m_strStatusComment = string.Empty;
m_lStatusCode = -1;
FXUserID = 0;
}
/// <summary>
/// get a data connection
/// </summary>
public CDataConnection GetDataConnection()
{
//get the connection string from the web.config file
//connection string is encrypted in the file using MS recommended procedures
//
//cd\
//cd windows
//cd microsoft.net
//cd framework
//cd v2.0.50727
//aspnet_regiis -pe "connectionStrings" -app "/PrimeCarePlus" -prov "RsaProtectedConfigurationProvider"
//
//look for connection strings in connection strings and app settings
string strConnectionString = "";
try
{
//try to get the connection string from the encrypted connectionstrings section
strConnectionString = ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString;
}
catch (Exception eee)
{
//pull from appsettings if failed, this lets developers connect from local boxes.
//strConnectionString = System.Configuration.ConfigurationManager.AppSettings["DBConnString"];
string strStatus = eee.Message;
}
bool bAudit = false;
string strAudit = "";
if (System.Configuration.ConfigurationManager.AppSettings["AUDIT"] != null)
{
strAudit = System.Configuration.ConfigurationManager.AppSettings["AUDIT"].ToString();
if (strAudit == "1")
{
bAudit = true;
}
}
CDataConnection DBConnection;
//create a new dataconnection object
DBConnection = new CDataConnection();
//Connect to the database, connection is housed in the master page
//so that all pages that use the master have access to it.
if (!DBConnection.Connect(strConnectionString, (int)DataConnectionType.Oracle, bAudit))
{
return(null);
}
return(DBConnection);
}
/// <summary>
/// constructor
/// </summary>
public BaseMaster()
{
//this puts us into a developer mode so
//that CAC stuff is bypassed
//etc....
//TODO: comment out before depoloying!!!
DEV_MODE = true;
//create a new dataconnection object
m_DBConnection = new CDataConnection();
//clear status
m_strStatusComment = "";
m_lStatusCode = -1;
FXUserID = 0;
m_lModuleID = 0;
m_lPageID = 0;
m_strRDS = "";
}
public bool GenerateProviderHTMLReport(CDataConnection DBConn,
BaseMaster Mastr,
string strPatientID,
string strEncounterID,
int nReportType,
bool bAltLang,
HtmlGenericControl divFlagReview)
{
CPatientPP cpt = new CPatientPP();
DataSet ds = cpt.GetReportDS(Mastr, strEncounterID, strPatientID, nReportType);
//this div holds all the flag info for the report
divFlagReview.InnerHtml = "";
if (this.HasProviderReportFlags(ds))//do not show flag stuff if no flags
{
#region build a header
//build a header.........
string strHeader = "";
//strHeader += "Report for " + strPatInfo;
strHeader += "This report is provided for your attention. ";
strHeader += "The HRA has gathered, scored, interpreted and is now reporting ";
strHeader += "your participant's self-reported responses. The patient reports the following:";
strHeader += "<br><br>";
divFlagReview.InnerHtml += strHeader;
#endregion
////////////////////////////////////////////////////
//critical - only show if we have good flags
//this will let us use the same report for all types
//of flags fired by assessments
///////////////////////////////////////////////////
#region Critical flags
string strCritFlags = GetFlags(ds, nReportType, 3);
if (strCritFlags.Length > 2)
{
divFlagReview.InnerHtml += GetProviderCriticalPara(bAltLang);
divFlagReview.InnerHtml += "<br><br><br>";
//critical
divFlagReview.InnerHtml += "<ul>";
divFlagReview.InnerHtml += "<li>" + strCritFlags + "</li>";
divFlagReview.InnerHtml += "</ul>";
divFlagReview.InnerHtml += "<br><br>";
}
#endregion
////////////////////////////////////////////////////
//cardiovasc - only show if we have good flags
//this will let us use the same report for all types
//of flags fired by assessments
///////////////////////////////////////////////////
#region Cardio flags
string strCVFlags = GetFlags(ds, nReportType, 4);
if (strCVFlags.Length > 2)
{
divFlagReview.InnerHtml += GetProviderCardiovascularRiskPara(bAltLang);
divFlagReview.InnerHtml += "<br><br><br>";
//CV
divFlagReview.InnerHtml += "<ul>";
divFlagReview.InnerHtml += "<li>" + strCVFlags + "</li>";
divFlagReview.InnerHtml += "</ul>";
divFlagReview.InnerHtml += "<br><br>";
}
#endregion
////////////////////////////////////////////////////
//high - only show if we have good flags
//this will let us use the same report for all types
//of flags fired by assessments
///////////////////////////////////////////////////
#region High flags
string strHIGHFlags = GetFlags(ds, nReportType, 2);
if (strHIGHFlags.Length > 2)
{
divFlagReview.InnerHtml += GetProviderHighPara(bAltLang);
divFlagReview.InnerHtml += "<br><br><br>";
//
divFlagReview.InnerHtml += "<ul>";
divFlagReview.InnerHtml += "<li>" + strHIGHFlags + "</li>";
divFlagReview.InnerHtml += "</ul>";
divFlagReview.InnerHtml += "<br><br>";
}
#endregion
///////////////////////////////////////////////////
//all flags by topic
///////////////////////////////////////////////////
divFlagReview.InnerHtml += GetProviderTopicFlags(ds, nReportType, bAltLang);
#region Privacy statement
divFlagReview.InnerHtml += "<font size=\"-2\"><br/>";
divFlagReview.InnerHtml += "Privacy Act of 1974, 5 U.S.C 552a, \"No agency shall disclose any record which is ";
divFlagReview.InnerHtml += "contained in a system of records by any means of communication to any person, or to ";
divFlagReview.InnerHtml += "another agency, except pursuant to a written request by, or with the prior written ";
divFlagReview.InnerHtml += "consent of, the individual to whom the record pertains.\"</font>";
#endregion
}
return(true);
}
/// <summary>
/// US:1883 US:834 refresh multipatient view
/// </summary>
/// <param name="plistPatChecklistIDs"></param>
/// <returns></returns>
public CStatus RefreshMultiPatientChecklists(CDataConnection conn,
CData data,
DateTime dtFrom,
DateTime dtTo,
long lChecklistID,
long lServiceID,
long lChecklistStatus)
{
//this class is used to do all transfers
//from MDWS to the VAPPCT database
CMDWSTransfer xfer = new CMDWSTransfer(data);
//get the multi patient checklist ds, uses the same filters
// as the website form does
DataSet dsMulti = null;
CPatientData pd = new CPatientData(data);
CStatus status = pd.GetMultiPatientSearchDS(
dtFrom,
dtTo,
lChecklistID,
lChecklistStatus,
lServiceID,
out dsMulti);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "GetMultiPatientSearchDS", status.StatusComment);
return(status);
}
foreach (DataTable table in dsMulti.Tables)
{
foreach (DataRow dr in table.Rows)
{
//checklist ID
long lPatChecklistID = Convert.ToInt64(dr["pat_cl_id"].ToString());
//get the items for this pat checklist id
DataSet dsChecklistItems = null;
CVAPPCTCommData commData = new CVAPPCTCommData(data);
status = commData.GetPatChecklistItemDS(lPatChecklistID,
out dsChecklistItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "GetPatChecklistItemDS", status.StatusComment);
return(status);
}
//refresh the checklist items
status = RefreshPatientCheckList(
conn,
data,
dsChecklistItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "RefreshPatientCheckList", status.StatusComment);
return(status);
}
//refresh the checklist collection items
DataSet dsCLCollectionItems = null;
status = commData.GetPatientCLCollectionItemDS(lPatChecklistID,
out dsCLCollectionItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "GetPatientCLCollectionItemDS", status.StatusComment);
return(status);
}
//refresh the checklist items
status = RefreshPatientCheckList(
conn,
data,
dsCLCollectionItems);
if (!status.Status)
{
//write the event to the event table
WriteEvent(data, "RefreshPatientCheckList", status.StatusComment);
return(status);
}
}
}
return(status);
}
/// <summary>
/// append all the columns and data from one ds (dsfrom) to another ds (dsto)
/// </summary>
/// <param name="conn"></param>
/// <param name="dsFrom"></param>
/// <param name="dsTo"></param>
/// <returns></returns>
public bool AppendData(CDataConnection conn,
DataSet dsFrom,
DataSet dsTo)
{
//keep the offeset of where the new columns start
int nStartIndex = dsTo.Tables[0].Columns.Count;
//loop over all the tables in the new ds and the cols to the original ds
foreach (System.Data.DataTable table in dsFrom.Tables)
{
foreach (System.Data.DataColumn col in table.Columns)
{
try
{
//add the new cols to the original dataset
System.Data.DataColumn colnew = new System.Data.DataColumn();
colnew.AllowDBNull = col.AllowDBNull;
colnew.AutoIncrement = col.AutoIncrement;
colnew.AutoIncrementSeed = col.AutoIncrementSeed;
colnew.AutoIncrementStep = col.AutoIncrementStep;
colnew.Caption = col.Caption;
colnew.ColumnMapping = col.ColumnMapping;
colnew.ColumnName = col.ColumnName;
//colnew.Container = col.Container;
colnew.DataType = col.DataType;
colnew.DateTimeMode = col.DateTimeMode;
colnew.DefaultValue = col.DefaultValue;
//colnew.DesignMode = col.DesignMode;
colnew.Expression = col.Expression;
//colnew.ExtendedProperties = col.ExtendedProperties;
colnew.MaxLength = col.MaxLength;
colnew.Namespace = col.Namespace;
//colnew.Ordinal = col.Ordinal;
colnew.Prefix = col.Prefix;
colnew.ReadOnly = col.ReadOnly;
colnew.Site = col.Site;
//colnew.Table = col.Table;
colnew.Unique = col.Unique;
//add the new column to the table
dsTo.Tables[0].Columns.Add(colnew);
}
catch (Exception ee)
{
string strError = ee.Message;
//ignore is already there
}
}
//accept any changes made
dsTo.AcceptChanges();
}
//loop over all the rows in the table of the new ds
//and add to original dataset
foreach (System.Data.DataTable table in dsFrom.Tables)
{
foreach (System.Data.DataRow row in table.Rows)
{
//loop over all the column values
for (int i = 0; i < row.ItemArray.Length; i++)
{
//loop over the "to" table and set the new col value = this col value
foreach (System.Data.DataTable table1 in dsTo.Tables)
{
foreach (System.Data.DataRow row1 in table1.Rows)
{
//loop over all the rows in the "to" table to find the new one
for (int ii = nStartIndex; ii < row1.ItemArray.Length; ii++)
{
if (row1.Table.Columns[ii].ColumnName == row.Table.Columns[i].ColumnName)
{
//set the rows value
row1[ii] = row[i];
//increment the start index to make the loop execute faster
nStartIndex++;
//break the for
break;
}
}
}
}
//accept any changes made
dsTo.AcceptChanges();
}
}
}
return(true);
}
/// <summary>
/// get a dataset from the connection and a stored proc
/// </summary>
/// <param name="conn"></param>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
/* public DataSet GetOracleDataSet(CDataConnection conn,
* string strSPName,
* CDataParameterList ParamList,
* out long lStatusCode,
* out string strStatus)
* {
* lStatusCode = 0;
* strStatus = "";
* m_lStatusCode = 0;
* m_strStatus = "";
*
* CDataUtils utils = new CDataUtils();
* string strAuditXML = "";
* strAuditXML += "<sp_name>" + strSPName + "</sp_name>";
*
* //return null if no conn
* if (conn == null)
* {
* m_lStatusCode = 1;
* m_strStatus = "Unable to connect to data source, CDataConnection is null";
* lStatusCode = m_lStatusCode;
* strStatus = m_strStatus;
* return null;
* }
*
* //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.GetOracleConnection();
* 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++)
* {
* CDataParameter 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;
*
* strAuditXML += "<" + oraParameter.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 == (int)DataParameterType.StringParameter)
* {
* oraParameter.Value = parameter.StringParameterValue;
*
* //audit value
* strAuditXML += parameter.StringParameterValue;
*
* }
* else if (parameter.ParameterType == (int)DataParameterType.LongParameter)
* {
* oraParameter.Value = parameter.LongParameterValue;
*
* //audit value
* strAuditXML += Convert.ToString(parameter.LongParameterValue);
* }
* else if (parameter.ParameterType == (int)DataParameterType.DateParameter)
* {
* oraParameter.Value = parameter.DateParameterValue;
*
* //audit value
* strAuditXML += utils.GetDateAsString(parameter.DateParameterValue);
* }
* else if (parameter.ParameterType == (int)DataParameterType.CLOBParameter)
* {
* oraParameter.Value = parameter.CLOBParameterValue;
*
* //audit value
* strAuditXML += parameter.CLOBParameterValue;
* }
* else
* {
* oraParameter.Value = parameter.StringParameterValue;
*
* //audit value
* strAuditXML += parameter.StringParameterValue;
* }
*
* strAuditXML += "</" + oraParameter.ParameterName + ">";
*
* 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.AddParameter("po_nStatusCode", 0, ParameterDirection.Output);
* OracleParameter oraStatusParameter = new OracleParameter("po_nStatusCode",
* OracleType.Int32);
* oraStatusParameter.Direction = ParameterDirection.Output;
* cmd.Parameters.Add(oraStatusParameter);
* //
* //comment
* ParamList.AddParameter("po_vStatusComment", "", ParameterDirection.Output);
* 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
* m_DataSet = new DataSet();
*
* //now audit the call.... ignore audit and get/set session values or
* //login is audited seperately
* if (conn.Audit)
* {
* if (strSPName.ToUpper().IndexOf("AUDIT") > -1 ||
* strSPName.ToUpper().IndexOf("GETSESSIONVALUE") > -1 ||
* strSPName.ToUpper().IndexOf("SETSESSIONVALUE") > -1 ||
* strSPName.ToUpper().IndexOf("LOGIN") > -1)
* {
* //ignore the audit
* }
* else
* {
* //audit the transaction
* CDataParameterList plistAudit = new CDataParameterList();
* plistAudit.AddInputParameter("pi_vSessionID", ParamList.GetItemByName("pi_vSessionID").StringParameterValue);
* plistAudit.AddInputParameter("pi_vSessionClientIP", ParamList.GetItemByName("pi_vSessionClientIP").StringParameterValue);
* plistAudit.AddInputParameter("pi_nUserID", ParamList.GetItemByName("pi_nUserID").LongParameterValue);
* plistAudit.AddInputParameter("pi_vSPName", strSPName);
* plistAudit.AddInputParameterCLOB("pi_clAuditXML", strAuditXML);
*
* long lStat = 0;
* string strStat = "";
* conn.ExecuteOracleSP("PCK_FX_SEC.AuditTransaction",
* plistAudit,
* out lStat,
* out strStat);
* }
* }
*
* //create an adapter and fill the dataset. I like datasets because they are completely
* //disconnected and provide the most flexibility for later porting to a web service etc.
* //It could be argued that a data reader is faster and offers easier movement back and forth
* //through a dataset. But for the web and the fact that we work from lists
* //I think a dataset is best. Concept is similar to current medbase architecture
* try
* {
* OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
* dataAdapter.Fill(m_DataSet);
* }
* catch (InvalidOperationException e)
* {
* m_strStatus = e.Message;
* m_lStatusCode = 1;
* m_DataSet = null;
* lStatusCode = m_lStatusCode;
* strStatus = m_strStatus;
* }
* catch (OracleException e)
* {
* m_strStatus = e.Message;
* m_lStatusCode = 1;
* m_DataSet = null;
* lStatusCode = m_lStatusCode;
* strStatus = m_strStatus;
* }
*
* if (m_lStatusCode == 0)
* {
* //now read back out params into our list
* for (int i = 0; i < ParamList.Count; i++)
* {
* CDataParameter 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 == (int)DataParameterType.StringParameter)
* {
* if (oP.Value != null)
* {
* parameter.StringParameterValue = oP.Value.ToString();
* }
* }
* else if (parameter.ParameterType == (int)DataParameterType.LongParameter)
* {
* if (oP.Value != null)
* {
* if (!oP.Value.ToString().Equals(""))
* {
* parameter.LongParameterValue = Convert.ToInt64(oP.Value);
* }
* }
* }
* else if (parameter.ParameterType == (int)DataParameterType.DateParameter)
* {
* if (oP.Value != null)
* {
* if (!oP.Value.ToString().Equals(""))
* {
* parameter.DateParameterValue = Convert.ToDateTime(oP.Value);
* }
* }
* }
* else
* {
* parameter.StringParameterValue = oP.Value.ToString();
* }
* }
* }
* }
* }
* }
*
* //set status code and text
* CDataParameter pStatusCode = ParamList.GetItemByName("po_nStatusCode");
* if (pStatusCode != null)
* {
* m_lStatusCode = pStatusCode.LongParameterValue;
*
* }
* CDataParameter pStatusComment = ParamList.GetItemByName("po_vStatusComment");
* if (pStatusComment != null)
* {
* m_strStatus = pStatusComment.StringParameterValue;
* }
* }
*
* lStatusCode = m_lStatusCode;
* strStatus = m_strStatus;
*
* return m_DataSet;
* }*/
/// <summary>
/// get a dataset from the connection and a stored proc
/// </summary>
/// <param name="conn"></param>
/// <param name="strSPName"></param>
/// <param name="ParamList"></param>
/// <param name="lStatusCode"></param>
/// <param name="strStatus"></param>
/// <returns></returns>
public DataSet GetOracleDataSet(CDataConnection conn,
string strSPName,
CDataParameterList ParamList,
out long lStatusCode,
out string strStatus)
{
lStatusCode = 0;
strStatus = "";
m_lStatusCode = 0;
m_strStatus = "";
CDataUtils utils = new CDataUtils();
//return null if no conn
if (conn == null)
{
m_lStatusCode = 1;
m_strStatus = "Unable to connect to data source, CDataConnection is null";
lStatusCode = m_lStatusCode;
strStatus = m_strStatus;
return(null);
}
//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.GetOracleConnection();
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++)
{
CDataParameter 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. Probably a better way than the
//if then else, but this works and we can find it later,
if (parameter.ParameterType == (int)DataParameterType.StringParameter)
{
oraParameter.Value = parameter.StringParameterValue;
}
else if (parameter.ParameterType == (int)DataParameterType.LongParameter)
{
oraParameter.Value = parameter.LongParameterValue;
}
else if (parameter.ParameterType == (int)DataParameterType.DateParameter)
{
oraParameter.Value = parameter.DateParameterValue;
}
else if (parameter.ParameterType == (int)DataParameterType.CLOBParameter)
{
oraParameter.Value = parameter.CLOBParameterValue;
}
else
{
oraParameter.Value = parameter.StringParameterValue;
}
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.AddParameter("po_nStatusCode", 0, ParameterDirection.Output);
OracleParameter oraStatusParameter = new OracleParameter("po_nStatusCode",
OracleType.Int32);
oraStatusParameter.Direction = ParameterDirection.Output;
cmd.Parameters.Add(oraStatusParameter);
//
//comment
ParamList.AddParameter("po_vStatusComment", "", ParameterDirection.Output);
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
m_DataSet = new DataSet();
//create an adapter and fill the dataset. I like datasets because they are completely
//disconnected and provide the most flexibility for later porting to a web service etc.
//It could be argued that a data reader is faster and offers easier movement back and forth
//through a dataset. But for the web and the fact that we work from lists
//I think a dataset is best. Concept is similar to current medbase architecture
try
{
OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
dataAdapter.Fill(m_DataSet);
}
catch (InvalidOperationException e)
{
m_strStatus = e.Message;
m_lStatusCode = 1;
m_DataSet = null;
lStatusCode = m_lStatusCode;
strStatus = m_strStatus;
}
catch (OracleException e)
{
m_strStatus = e.Message;
m_lStatusCode = 1;
m_DataSet = null;
lStatusCode = m_lStatusCode;
strStatus = m_strStatus;
}
if (m_lStatusCode == 0)
{
//now read back out params into our list
for (int i = 0; i < ParamList.Count; i++)
{
CDataParameter 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 == (int)DataParameterType.StringParameter)
{
if (oP.Value != null)
{
parameter.StringParameterValue = oP.Value.ToString();
}
}
else if (parameter.ParameterType == (int)DataParameterType.LongParameter)
{
if (oP.Value != null)
{
if (!oP.Value.ToString().Equals(""))
{
parameter.LongParameterValue = Convert.ToInt64(oP.Value);
}
}
}
else if (parameter.ParameterType == (int)DataParameterType.DateParameter)
{
if (oP.Value != null)
{
if (!oP.Value.ToString().Equals(""))
{
parameter.DateParameterValue = Convert.ToDateTime(oP.Value);
}
}
}
else
{
parameter.StringParameterValue = oP.Value.ToString();
}
}
}
}
}
}
//set status code and text
CDataParameter pStatusCode = ParamList.GetItemByName("po_nStatusCode");
if (pStatusCode != null)
{
m_lStatusCode = pStatusCode.LongParameterValue;
}
CDataParameter pStatusComment = ParamList.GetItemByName("po_vStatusComment");
if (pStatusComment != null)
{
m_strStatus = pStatusComment.StringParameterValue;
}
}
lStatusCode = m_lStatusCode;
strStatus = m_strStatus;
//now audit the call if needed....
if (conn.Audit)
{
long lAuditStatusCode = 0;
string strAuditStatus = String.Empty;
conn.AuditTransaction(strSPName,
ParamList,
lStatusCode,
strStatus,
out lAuditStatusCode,
out strAuditStatus);
}
return(m_DataSet);
}
