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; }