private void LoadTableParameter(SQLInboundChanel chn, GWDataDBTable table, string interfaceName)
{
GWDataDBField[] iFields = GWDataDBField.GetFields(table);
foreach (GWDataDBField field in iFields)
{
if (field.IsAuto)
{
continue;
}
string paramName = field.GetFullFieldName(Program.DeviceMgt.DeviceDirInfor.Header.Name).Replace(".", "_");
SQLInQueryResultItem item = new SQLInQueryResultItem(field, paramName);
chn.Rule.QueryResult.MappingList.Add(item);
}
}
public StorageProcedure(XCollection <SQLInboundChanel> channels)
{
InitializeComponent();
channelSet = channels;
channel = new SQLInboundChanel();
type = "Add";
this.Text = "Add Storage Procedure";
parameterPage = new ParameterPage(channel);
statementPage = new StatementPage(channel);
GetSPNameSet(channels);
Initialization();
}
/// <summary>
/// Copy Channel
/// </summary>
/// <param name="frm"></param>
/// <param name="channels"></param>
/// <param name="copyChannel"></param>
public Channel(ActiveMode frm, XCollection <SQLInboundChanel> channels, SQLInboundChanel copyChannel)
{
InitializeComponent();
parentForm = frm;
channelSet = channels;
type = "Add";
channel = copyChannel;
criteriaItemList = channel.Rule.QueryCriteria.MappingList;
resultItemList = channel.Rule.QueryResult.MappingList;
this.Text = "Add Channel";
GetChannelNameSet(channels);
ShowChannel(channel);
enumCmbbxOperationType.Enabled = false;
}
public Channel(ActiveMode frm, XCollection <SQLInboundChanel> channels, int index)
{
InitializeComponent();
parentForm = frm;
channelSet = channels;
type = "Edit";
this.Text = "Edit Channel";
channel = channelSet[index];
channelIndex = index;
criteriaItemList = channelSet[index].Rule.QueryCriteria.MappingList;
resultItemList = channelSet[index].Rule.QueryResult.MappingList;
sqlStr = channelSet[index].Rule.QueryCriteria.SQLStatement;
GetChannelNameSet(channels);
ShowChannel(channel);
enumCmbbxOperationType.Enabled = false;
}
private void btnChannelCopy_Click(object sender, EventArgs e)
{
int count = channelSet.Count;
SQLInboundChanel copyChannel = channelSet[channelIndex].Clone();
copyChannel.ChannelName += "_copy";
Channel frm = new Channel(this, channelSet, copyChannel);
frm.ShowDialog(this);
if (channelSet.Count > count)
{ // Add successfully
channelIndex = count;
lstvChannel.Items[channelIndex].Selected = true;
}
}
private DataSet TranslateData(SQLInboundChanel ch, DataSet ds1)
{
// Build schema
DataSet ds2 = new DataSet();
DataTable table = new DataTable(ds1.Tables[0].TableName);
ds2.Tables.Add(table);
foreach (DataColumn col1 in ds1.Tables[0].Columns)
{
DataColumn col2 = new DataColumn(col1.ColumnName, typeof(System.String));
table.Columns.Add(col2);
}
// Copy Data
foreach (DataRow dr1 in ds1.Tables[0].Rows)
{
DataRow dr2 = table.NewRow();
foreach (DataColumn item in ds1.Tables[0].Columns)
{
object obj = dr1[item.ColumnName];
if (Convert.IsDBNull(obj))
{
dr2[item.ColumnName] = System.DBNull.Value;
continue;
}
if (item.DataType == typeof(System.DateTime))
{
//dr2[item.ColumnName] = Convert.ToDateTime(dr1[item.ColumnName]).ToString("yyyy-MM-dd hh:mm:ss");
dr2[item.ColumnName] = Convert.ToDateTime(obj).ToString(GWDataDB.DateTimeFormat);
}
else
{
dr2[item.ColumnName] = Convert.ToString(dr1[item.ColumnName]);
}
}
table.Rows.Add(dr2);
}
return(ds2);
}
private List <DataSet> ReadData2(OleDbConnection cnn, SQLInboundChanel ch)
{
List <DataSet> list = new List <DataSet>();
if (ch.OperationType == ThrPartyDBOperationType.StorageProcedure)
{
if (ch.Rule.IsInputParameterSPEnable())
{
DataSet dsParam = GetInputParameter(ch);
if (dsParam != null && dsParam.Tables.Count > 0)
{
foreach (DataRow drParam in dsParam.Tables[0].Rows)
{
OleDbCommand cmd = Program.db.GetCommand();
cmd.CommandText = ch.OperationName;
cmd.Connection = cnn;
SetParameter(ch, cmd, drParam);
DataSet ds = CallSP2(ch, cmd);
list.Add(ds);
}
}
}
else
{
OleDbCommand cmd = Program.db.GetCommand();
cmd.CommandText = ch.OperationName;
cmd.Connection = cnn;
SetParameter(ch, cmd, null);
DataSet ds = CallSP2(ch, cmd);
list.Add(ds);
}
}
else
{
DataSet ds = ExecuteSQL(ch);
list.Add(ds);
}
return(list);
}
private DataSet ExecuteSQL3(OracleConnection cnn, SQLInboundChanel ch)
{
OracleCommand cmd = new OracleCommand();
cmd.CommandText = ch.Rule.QueryCriteria.SQLStatement;
cmd.CommandType = CommandType.Text;
cmd.Connection = cnn;
Program.Log.Write("CommandText='" + cmd.CommandText + "'");
try
{
DataSet ds = new DataSet();
using (OracleDataAdapter da = new OracleDataAdapter(cmd))
{
da.Fill(ds);
return(ds);
}
}
catch (OracleException err)
{
string errMsg = err.ToString();
Program.Log.Write(LogType.Error, errMsg);
if (SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.SuicideWhenOleDbException)
{
string errCode = SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.SuicideWhenOleDbExceptionErrorCodeExclude;
if (!string.IsNullOrEmpty(errCode))
{
if (errMsg.IndexOf(errCode) >= 0)
{
Program.Log.Write(LogType.Error, "Ignore error code: " + errCode);
return(null);
}
}
Program.Log.Write(LogType.Error, "Killing myself now.");
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
return(null);
}
}
private void LoadDefaultInboundSP()
{
channelSet.Clear();
string interfaceName = Program.DeviceMgt.DeviceDirInfor.Header.Name;
//Patient Table
SQLInboundChanel chnPatient = channelSet.Add(new SQLInboundChanel());
chnPatient.Rule.RuleID = GWDataDBTable.Patient.ToString();
chnPatient.SPName = "sp_" + interfaceName + "_" + GWDataDBTable.Patient.ToString();
LoadTableParameter(chnPatient, GWDataDBTable.Index, interfaceName);
LoadTableParameter(chnPatient, GWDataDBTable.Patient, interfaceName);
chnPatient.SPStatement = RuleControl.GetInboundSP(interfaceName, chnPatient.Rule, false);
//Order Table
SQLInboundChanel chnOrder = channelSet.Add(new SQLInboundChanel());
chnOrder.Rule.RuleID = GWDataDBTable.Order.ToString();
chnOrder.SPName = "sp_" + interfaceName + "_" + GWDataDBTable.Order.ToString();
LoadTableParameter(chnOrder, GWDataDBTable.Index, interfaceName);
LoadTableParameter(chnOrder, GWDataDBTable.Patient, interfaceName);
LoadTableParameter(chnOrder, GWDataDBTable.Order, interfaceName);
chnOrder.SPStatement = RuleControl.GetInboundSP(interfaceName, chnOrder.Rule, false);
//Report Table
SQLInboundChanel chnReport = channelSet.Add(new SQLInboundChanel());
chnReport.Rule.RuleID = GWDataDBTable.Report.ToString();
chnReport.SPName = "sp_" + interfaceName + "_" + GWDataDBTable.Report.ToString();
LoadTableParameter(chnReport, GWDataDBTable.Index, interfaceName);
LoadTableParameter(chnReport, GWDataDBTable.Patient, interfaceName);
LoadTableParameter(chnReport, GWDataDBTable.Order, interfaceName);
LoadTableParameter(chnReport, GWDataDBTable.Report, interfaceName);
chnReport.SPStatement = RuleControl.GetInboundSP(interfaceName, chnReport.Rule, false);
}
/// <summary>
/// ch.OperationType = ThrPartyDBOperationType.StorageProcedure
/// </summary>
/// <param name="ch"></param>
/// <param name="cmd"></param>
/// <returns></returns>
private bool SetParameter(SQLInboundChanel ch, OracleCommand cmd, DataRow row)
{
foreach (SQLInQueryCriteriaItem item in ch.Rule.QueryCriteria.MappingList)
{
OracleParameter p = new OracleParameter();
p.DbType = OleDBType2DBType(item.ThirdPartyDBPatamter.FieldType);
p.ParameterName = "@" + item.ThirdPartyDBPatamter.FieldName;
if (item.IsNull)
{
p.Value = DBNull.Value;
}
else if (item.IsGetFromStorageProcedure)
{
string paramName = item.ThirdPartyDBPatamter.FieldName;
if (row.Table.Columns.Contains(paramName))
{
p.Value = row[paramName];
}
else
{
Program.Log.Write(LogType.Warning, "Cannot find input parameter from SP result set, parameter name: " + paramName);
p.Value = item.Translating.ConstValue;
}
}
else
{
p.Value = item.Translating.ConstValue;
//if (item.Translating.Type == TranslatingType.FixValue ||
// item.Translating.Type == TranslatingType.DefaultValue)
// cmd.Parameters.Add(p);
//else
// Program.Log.Write(LogType.Warning, item.ThirdPartyDBPatamter.FieldName + ": StorageProcedure Only Support FixValue or DefaultValue parameter!");
}
cmd.Parameters.Add(p);
}
return(true);
}
public void ShowChannel(SQLInboundChanel channel)
{
this.checkBoxSQLText.Checked = channel.CallSPAsSQLText;
//SQLInboundChanel channel = channelSet[channelIndex];
txtChannelName.Text = channel.ChannelName;
checkBoxStatus.Checked = channel.Enable;
enumCmbbxOperationType.Text = channel.OperationType.ToString();
txtModeName.Text = channel.OperationName;
//Show Criteria
if (enumCmbbxOperationType.Text == ThrPartyDBOperationType.StorageProcedure.ToString())
{
lblCriteria.Text = "Input parameters";
txtStatement.Visible = false;
lstvCriteria.Visible = true;
btnCriteriaAdd.Visible = true;
btnCriteriaModify.Visible = true;
btnCriteriaDelete.Visible = true;
txtStatement.Text = "";
ShowCriteriaList();
}
else
{
lblCriteria.Text = "SQL Statement";
txtStatement.Visible = true;
lstvCriteria.Visible = false;
btnCriteriaAdd.Visible = false;
btnCriteriaModify.Visible = false;
btnCriteriaDelete.Visible = false;
txtStatement.Text = channel.Rule.QueryCriteria.SQLStatement;
lstvCriteria.Items.Clear();
}
//Show Result
ShowResultList();
}
private DataSet CallSP(SQLInboundChanel ch, OleDbCommand cmd)
{
if (ch.CallSPAsSQLText)
{
cmd.CommandType = CommandType.Text;
}
else
{
cmd.CommandType = CommandType.StoredProcedure;
}
#region Log Prameter
Program.Log.Write(" ============ Storage Procedure Parameter===============");
foreach (OleDbParameter p in cmd.Parameters)
{
string str = "";
if (p.Value == null)
{
str = "(null)";
}
else if (p.Value == DBNull.Value)
{
str = "(dbnull)";
}
else
{
str = p.Value.ToString();
}
Program.Log.Write(p.ParameterName + "=" + str);
}
Program.Log.Write(" ========================= Parameter End ===============");
#endregion
Program.Log.Write("CommandText='" + cmd.CommandText + "'");
try
{
DataSet ds = new DataSet();
if (!Program.db.ExecCommand(cmd, ds))
{
return(null);
}
else
{
return(ds);
}
}
catch (OleDbException err)
{
//Program.Log.Write(err);
string errMsg = err.ToString();
Program.Log.Write(LogType.Error, errMsg);
if (SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.SuicideWhenOleDbException)
{
string errCode = SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.SuicideWhenOleDbExceptionErrorCodeExclude;
if (!string.IsNullOrEmpty(errCode))
{
if (errMsg.IndexOf(errCode) >= 0)
{
Program.Log.Write(LogType.Error, "Ignore error code: " + errCode);
return(null);
}
}
Program.Log.Write(LogType.Error, "Killing myself now.");
System.Diagnostics.Process.GetCurrentProcess().Kill();
}
return(null);
}
}
//Build a test configuration file
static public bool BuildTestConfigFile()
{
#region 3rd database connection
SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.OracleDriver = false;
SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.TimerEnable = true;
SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.TimerInterval = 1000;
SQLInAdapterConfigMgt.SQLInAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.ConnectionStr = "Provider=SQLNCLI.1;Data Source=CNSHW9RSZM1X;Password=123456;User ID=sa;Initial Catalog=GWDataDB";
#endregion
#region channel StorageProcedure
SQLInboundChanel ch = new SQLInboundChanel();
ch.OperationType = ThrPartyDBOperationType.StorageProcedure;
ch.OperationName = "p_Patient_3rdIN";
ch.Enable = true;
//ch.Rule.AutoUpdateProcessFlag = false;
//ch.Rule.CheckProcessFlag = true;
ch.Rule.RuleName = "p_patient_3rdIn";
ch.Rule.QueryCriteria.Type = QueryCriteriaRuleType.None; //base mapping list
#region SQLInQueryCriteria
SQLInQueryCriteriaItem ci = new SQLInQueryCriteriaItem();
ci.Type = QueryCriteriaType.And;
ci.Translating.Type = TranslatingType.DefaultValue;
ci.Translating.ConstValue = "0";
ci.RedundancyFlag = false;
ci.SourceField = "process_flag";
ci.ThirdPartyDBPatamter.FieldID = 0;
ci.ThirdPartyDBPatamter.FieldName = ci.SourceField;
ci.ThirdPartyDBPatamter.FieldType = OleDbType.Integer;
ch.Rule.QueryCriteria.MappingList.Add(ci);
ci = new SQLInQueryCriteriaItem();
ci.Type = QueryCriteriaType.And;
ci.Translating.Type = TranslatingType.FixValue;
ci.Translating.ConstValue = "1";
ci.RedundancyFlag = false;
ci.SourceField = "event_type";
ci.ThirdPartyDBPatamter.FieldID = 0;
ci.ThirdPartyDBPatamter.FieldName = ci.SourceField;
ci.ThirdPartyDBPatamter.FieldType = OleDbType.Integer;
ch.Rule.QueryCriteria.MappingList.Add(ci);
#endregion
#region SQLInQueryResultItem
// column Patient_test.patientid
SQLInQueryResultItem map = new SQLInQueryResultItem();
map.SourceField = "patientid";
map.TargetField = "patientid";
map.RedundancyFlag = true;
map.ThirdPartyDBPatamter.FieldID = 0;
map.ThirdPartyDBPatamter.FieldName = "PatientID";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.Integer;
ch.Rule.QueryResult.MappingList.Add(map);
// column Patient_test.patient_name
map = new SQLInQueryResultItem();
map.SourceField = "Patient_name";
map.TargetField = "Patient_name";
map.RedundancyFlag = false;
map.ThirdPartyDBPatamter.FieldID = 1;
map.ThirdPartyDBPatamter.FieldName = "Patient_name";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.VarChar;
ch.Rule.QueryResult.MappingList.Add(map);
// column Patient_test.address
map = new SQLInQueryResultItem();
map.SourceField = "Address";
map.TargetField = "Address";
map.RedundancyFlag = false;
map.ThirdPartyDBPatamter.FieldID = 2;
map.ThirdPartyDBPatamter.FieldName = "Address";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.VarChar;
ch.Rule.QueryResult.MappingList.Add(map);
// column patient_test.birthdate
map = new SQLInQueryResultItem();
map.SourceField = "BirthDate";
map.TargetField = "BirthDate";
map.RedundancyFlag = false;
map.ThirdPartyDBPatamter.FieldID = 3;
map.ThirdPartyDBPatamter.FieldName = "BirthDate";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.Date;
ch.Rule.QueryResult.MappingList.Add(map);
#endregion
SQLInAdapterConfigMgt.SQLInAdapterConfig.InboundChanels.Add(ch);
#endregion
#region channel Table
ch = new SQLInboundChanel();
ch.OperationType = ThrPartyDBOperationType.Table;
ch.OperationName = "dbo.Patient_3rdIN";
ch.Enable = false;
//ch.Rule.AutoUpdateProcessFlag = false;
//ch.Rule.CheckProcessFlag = true;
ch.Rule.RuleName = "patient_3rdIn";
ch.Rule.QueryCriteria.Type = QueryCriteriaRuleType.SQLStatement;
ch.Rule.QueryCriteria.SQLStatement = "process_flag=0 and event_type=1";
#region SQLInQueryResultItem
// column Patient_test.patientid
map = new SQLInQueryResultItem();
map.SourceField = "patientid";
map.TargetField = "patientid";
map.RedundancyFlag = true;
map.ThirdPartyDBPatamter.FieldID = 0;
map.ThirdPartyDBPatamter.FieldName = "PatientID";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.Integer;
ch.Rule.QueryResult.MappingList.Add(map);
// column Patient_test.patient_name
map = new SQLInQueryResultItem();
map.SourceField = "Patient_name";
map.TargetField = "Patient_name";
map.RedundancyFlag = false;
map.ThirdPartyDBPatamter.FieldID = 1;
map.ThirdPartyDBPatamter.FieldName = "Patient_name";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.VarChar;
ch.Rule.QueryResult.MappingList.Add(map);
// column Patient_test.address
map = new SQLInQueryResultItem();
map.SourceField = "Address";
map.TargetField = "Address";
map.RedundancyFlag = false;
map.ThirdPartyDBPatamter.FieldID = 2;
map.ThirdPartyDBPatamter.FieldName = "Address";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.VarChar;
ch.Rule.QueryResult.MappingList.Add(map);
// column patient_test.birthdate
map = new SQLInQueryResultItem();
map.SourceField = "BirthDate";
map.TargetField = "BirthDate";
map.RedundancyFlag = false;
map.ThirdPartyDBPatamter.FieldID = 3;
map.ThirdPartyDBPatamter.FieldName = "BirthDate";
map.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.Date;
ch.Rule.QueryResult.MappingList.Add(map);
#endregion
SQLInAdapterConfigMgt.SQLInAdapterConfig.InboundChanels.Add(ch);
#endregion
//save
return(SQLInAdapterConfigMgt.Save(SQLInAdapterConfigMgt._FileName));
}
public ParameterPage(SQLInboundChanel ch)
{
InitializeComponent();
channel = ch;
parameterList = ch.Rule.QueryResult.MappingList;
}
public StatementPage(SQLInboundChanel ch)
{
InitializeComponent();
channel = ch;
interfaceName = Program.DeviceMgt.DeviceDirInfor.Header.Name;
}
