private bool InsertDataFile(SQLOutboundChanel chn, string fname, DataSet ds, List <string> idList, int count)
{
int i = 1;
foreach (DataTable dt in ds.Tables)
{
foreach (DataRow dr in dt.Rows)
{
chn.OperationName = fname;
OleDbCommand cmd = Program.db.GetCommand();
MapingHelper.BuildInsertCmd(chn, dr, cmd);
cmd.CommandType = CommandType.Text;
Program.Log.Write(string.Format("Insert data into file. {0}/{1}", i++, count));
if (!Program.db.ExecCommand(cmd))
{
return(false);
}
string id = Convert.ToString(dr["data_id"]);
idList.Add(id);
}
}
return(true);
}
public ParameterPage(SQLOutboundChanel ch)
{
InitializeComponent();
channel = ch;
criteriaList = ch.Rule.QueryCriteria.MappingList;
resultList = ch.Rule.QueryResult.MappingList;
}
static public string BuildSQLStatement(SQLOutboundChanel ch, DataRow dr)
{
if (ch.OperationType != ThrPartyDBOperationType.SQLStatement)
{
return("");
}
Program.Log.Write("Start Build SQL Statement ");
StringBuilder sb = new StringBuilder();
sb.Append(ch.OperationName);
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
try
{
sb.Replace("{" + item.ThirdPartyDBPatamter.FieldName + "}", dr[item.TargetField.Trim()].ToString());
}catch (Exception ex)
{
Program.Log.Write(LogType.Error, "Replace parameter " + item.ThirdPartyDBPatamter.FieldName + " error, " + ex.Message);
}
}
Program.Log.Write(sb.ToString());
return(sb.ToString());
}
public HYS.Common.Objects.Rule.IOutboundRule[] GetRules()
{
if (SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.InteractType == InteractType.Active)
{
List <IOutboundRule> rules = new List <IOutboundRule>();
foreach (SQLOutboundChanel ch0 in SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels)
{
SQLOutboundChanel ch = ch0.Clone();
ch.Rule.RuleID = ch0.Rule.RuleID;
if (!IsExist(ch, GWDataDBField.i_IndexGuid.FieldName))
{
InsertDataID(ch);
}
if (!IsExist(ch, GWDataDBField.i_DataDateTime.FieldName))
{
InsertDataDT(ch);
}
rules.Add(ch.Rule);
}
return(rules.ToArray());
}
else
{
CreatPassiveSPScript(SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundPassiveChanels);
return(new IOutboundRule[] { });
}
}
private void LoadDefaultSetting()
{
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels.Clear();
string inboundName = this.textBoxIName.Text.Trim();
string tablePrefix = this.textBoxTP.Text;
SQLOutboundChanel chnPatient = SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels.Add(new SQLOutboundChanel());
chnPatient.ChannelName = GetSPName(inboundName, GWDataDBTable.Patient);
chnPatient.OperationName = GetSPName(inboundName, GWDataDBTable.Patient);
chnPatient.OperationType = ThrPartyDBOperationType.StorageProcedure;
chnPatient.Rule.RuleName = "sp_" + tablePrefix + "_Patient";
chnPatient.Rule.RuleID = "Patient";
chnPatient.Rule.CheckProcessFlag = true;
chnPatient.Enable = true;
//LoadEventTypeSetting(chnPatient, tablePrefix, new string[] { "00", "01", "02", "03" });
LoadEventTypeSetting(chnPatient, tablePrefix, this.textBoxEventTypePatient.Text.Trim().Split(','));
LoadTableSetting(chnPatient, GWDataDBTable.Index, inboundName, tablePrefix);
LoadTableSetting(chnPatient, GWDataDBTable.Patient, inboundName, tablePrefix);
SQLOutboundChanel chnOrder = SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels.Add(new SQLOutboundChanel());
chnOrder.ChannelName = GetSPName(inboundName, GWDataDBTable.Order);
chnOrder.OperationName = GetSPName(inboundName, GWDataDBTable.Order);
chnOrder.OperationType = ThrPartyDBOperationType.StorageProcedure;
chnOrder.Rule.RuleName = "sp_" + tablePrefix + "_Order";
chnOrder.Rule.RuleID = "Order" + this.textBoxRISOrderSPMultiSiteSuffix.Text.Trim();
chnOrder.Rule.CheckProcessFlag = true;
chnOrder.Enable = true;
//LoadEventTypeSetting(chnOrder, tablePrefix, new string[] { "10", "11", "12", "13", "20", "21", "22", "23" });
LoadEventTypeSetting(chnOrder, tablePrefix, this.textBoxEventTypeOrder.Text.Trim().Split(','));
LoadTableSetting(chnOrder, GWDataDBTable.Index, inboundName, tablePrefix);
LoadTableSetting(chnOrder, GWDataDBTable.Patient, inboundName, tablePrefix);
LoadTableSetting(chnOrder, GWDataDBTable.Order, inboundName, tablePrefix);
SQLOutboundChanel chnReport = SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels.Add(new SQLOutboundChanel());
chnReport.ChannelName = GetSPName(inboundName, GWDataDBTable.Report);
chnReport.OperationName = GetSPName(inboundName, GWDataDBTable.Report);
chnReport.OperationType = ThrPartyDBOperationType.StorageProcedure;
chnReport.Rule.RuleName = "sp_" + tablePrefix + "_Report";
chnReport.Rule.RuleID = "Report";
chnReport.Rule.CheckProcessFlag = true;
chnReport.Enable = true;
//LoadEventTypeSetting(chnReport, tablePrefix, new string[] { "30", "31", "32", "33" });
LoadEventTypeSetting(chnReport, tablePrefix, this.textBoxEventTypeReport.Text.Trim().Split(','));
LoadTableSetting(chnReport, GWDataDBTable.Index, inboundName, tablePrefix);
LoadTableSetting(chnReport, GWDataDBTable.Patient, inboundName, tablePrefix);
LoadTableSetting(chnReport, GWDataDBTable.Order, inboundName, tablePrefix);
LoadTableSetting(chnReport, GWDataDBTable.Report, inboundName, tablePrefix);
}
private void InsertDataDT(SQLOutboundChanel chn)
{
SQLOutQueryResultItem item = new SQLOutQueryResultItem();
item.ThirdPartyDBPatamter.FieldName = "";
item.TargetField = "data_dt";
item.GWDataDBField = GWDataDBField.i_DataDateTime;
item.SourceField = item.GWDataDBField.FieldName;
chn.Rule.QueryResult.MappingList.Add(item);
}
private bool IsExist(SQLOutboundChanel ch, string fieldname)
{
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.TargetField == fieldname)
{
return(true);
}
}
return(false);
}
public StorageProcedure(XCollection <SQLOutboundChanel> channels, int index)
{
InitializeComponent();
channelSet = channels;
channel = channels[index];
type = "Edit";
this.Text = "Edit Storage Procedure";
parameterPage = new ParameterPage(channel);
statementPage = new StatementPage(channel);
GetSPNameSet(channels);
ShowInformation();
}
public StorageProcedure(XCollection <SQLOutboundChanel> channels)
{
InitializeComponent();
channelSet = channels;
channel = new SQLOutboundChanel();
type = "Add";
this.Text = "Add Storage Procedure";
parameterPage = new ParameterPage(channel);
statementPage = new StatementPage(channel);
GetSPNameSet(channels);
Initialization();
}
public void ShowChannel(SQLOutboundChanel channel)
{
//SQLOutboundChanel channel = channelSet[channelIndex];
txtChannelName.Text = channel.ChannelName;
checkBoxStatus.Checked = channel.Enable;
enumCmbbxOperationType.Text = channel.OperationType.ToString();
txtModeName.Text = channel.OperationName;
//Show QueryCriteria ItemList
ShowCriteriaList();
//Show Mapping ItemList
ShowResultList();
}
/// <summary>
/// User for copy!
/// </summary>
/// <param name="channels"></param>
/// <param name="ft"></param>
public StorageProcedure(XCollection <SQLOutboundChanel> channels, SQLOutboundChanel ch)
{
InitializeComponent();
channelSet = channels;
channel = ch.Clone();
channel.Rule.RuleID = ch.Rule.RuleID;
type = "Edit";
this.Text = "Edit Storage Procedure";
parameterPage = new ParameterPage(channel);
statementPage = new StatementPage(channel);
GetSPNameSet(channels);
channel.SPName += "_Copy";
InitializationCopy();
//ShowInformation();
}
private void btnChannelCopy_Click(object sender, EventArgs e)
{
int count = channelSet.Count;
SQLOutboundChanel 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 bool RecordIsExist(DataRow dr, SQLOutboundChanel ch)
{
string strSQL = " select count(*) from " + ch.OperationName + MapingHelper.BuildWhereString(ch);
OleDbCommand cmd = Program.db.GetCommand();
cmd.CommandText = strSQL;
cmd.CommandType = CommandType.Text;
bool hasRedundancyFlag = false;
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
if (item.RedundancyFlag)
{
hasRedundancyFlag = true;
OleDbParameter parm = Program.db.GetParameter();
parm.ParameterName = "?";// + item.TargetField;
parm.OleDbType = item.ThirdPartyDBPatamter.FieldType;
parm.Direction = ParameterDirection.Input;
parm.Value = dr[item.TargetField];//Convert.ChangeType(dr[item.TargetField], item.ThirdPartyDBPatamter.FieldType.GetType());
cmd.Parameters.Add(parm);
}
}
if (hasRedundancyFlag == false)
{
return(false);
}
DataSet dsResult = new DataSet();
Program.db.ExecCommand(cmd, dsResult);
return(Convert.ToInt32(dsResult.Tables[0].Rows[0][0]) > 0);
}
public Channel(ActiveMode frm, XCollection <SQLOutboundChanel> channels)
{
InitializeComponent();
parentForm = frm;
channelSet = channels;
type = "Add";
this.Text = "Add Channel";
channel = new SQLOutboundChanel();
criteriaItemList = new XCollection <SQLOutQueryCriteriaItem>();
resultItemList = new XCollection <SQLOutQueryResultItem>();
GetChannelNameSet(channels);
if (parentForm.DBconfig.ConnectionParameter.FileConnection)
{
this.enumCmbbxOperationType.SelectedIndex = 1;
this.enumCmbbxOperationType.Enabled = false;
this.txtModeName.Enabled = false;
}
}
/// <summary>
///
/// </summary>
/// <param name="ds"></param>
/// <param name="ch"></param>
/// <returns></returns>
private bool WriteSPOracle(DataSet ds, SQLOutboundChanel ch)
{
// ------ 20070419 ------
string[] data_Id = new string[1];
using (OracleConnection conn = new OracleConnection(
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.ConnectionStr))
{
Program.Log.Write("--- begin connecting with 3rd party db ---");
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
cmd.Parameters.Clear();
cmd.CommandText = ch.OperationName;
cmd.CommandType = CommandType.StoredProcedure;
SetPamater(dr, cmd, ch);
cmd.ExecuteNonQuery();
data_Id[0] = Convert.ToString(dr["data_id"]);
if (OnDataDischarge != null)
{
OnDataDischarge(data_Id);
}
}
catch (Exception Ex)
{
Program.Log.Write(Ex);
Program.Log.Write(LogType.Info, Program.db.ConnectionString);
if (!ch.IgnoreDBException)
{
return(false);
}
}
}
}
return(true);
}
private void LoadEventTypeSetting(SQLOutboundChanel chn, string tablePrefix, string[] eventTypes)
{
chn.Rule.QueryCriteria.Type = QueryCriteriaRuleType.DataSet;
foreach (string str in eventTypes)
{
SQLOutQueryCriteriaItem item = new SQLOutQueryCriteriaItem();
item.TargetField = GWDataDBField.i_EventType.GetFullFieldName(tablePrefix);
item.GWDataDBField = GWDataDBField.i_EventType;
ThrPartyDBParamter p = new ThrPartyDBParamter();
p.FieldType = System.Data.OleDb.OleDbType.Empty;
item.ThirdPartyDBPatamter = p;
item.SourceField = "";
item.Translating.Type = TranslatingType.FixValue;
item.Translating.ConstValue = str;
item.Type = QueryCriteriaType.Or;
chn.Rule.QueryCriteria.MappingList.Add(item);
}
}
private void LoadTableSetting(SQLOutboundChanel chn, GWDataDBTable table, string inboundName, string tablePrefix)
{
GWDataDBField[] iFields = GWDataDBField.GetFields(table);
foreach (GWDataDBField f in iFields)
{
if (f.IsAuto)
{
continue;
}
string paramName = f.GetFullFieldName(inboundName).Replace(".", "_");
SQLOutQueryResultItem item = new SQLOutQueryResultItem();
item.SourceField = f.GetFullFieldName(tablePrefix);
item.GWDataDBField = f;
item.ThirdPartyDBPatamter.FieldType = System.Data.OleDb.OleDbType.VarChar;
item.ThirdPartyDBPatamter.FieldName = paramName;
item.TargetField = paramName;
chn.Rule.QueryResult.MappingList.Add(item);
}
}
private bool CreateDataFile(SQLOutboundChanel chn, string fname)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("CREATE TABLE [{0}] (", fname);
foreach (SQLOutQueryResultItem item in chn.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
sb.AppendFormat("[{0}] TEXT,", item.ThirdPartyDBPatamter.FieldName);
}
string sql = sb.ToString().TrimEnd(',') + ")";
OleDbCommand cmd = Program.db.GetCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
Program.Log.Write("Creating file: " + fname);
return(Program.db.ExecCommand(cmd));
}
static public string BuildWhereString(SQLOutboundChanel ch)
{
if (ch.OperationType == ThrPartyDBOperationType.StorageProcedure)
{
throw new Exception("StorgaeProcedure need not Build where string");
}
if (ch.Rule.QueryResult.MappingList.Count < 1)
{
return("");
}
StringBuilder sbWhere = new StringBuilder();
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
if (item.RedundancyFlag)
{
//sbWhere.Append(item.TargetField + "=@" + item.TargetField + " and ");
sbWhere.Append(item.TargetField + "=?" + " and ");
}
}
string sResult = "";
if (sbWhere.Length > 0)
{
sbWhere.Remove(sbWhere.Length - 4, 4);
sResult = " where " + sbWhere.ToString();
}
return(sResult);
}
public Channel(ActiveMode frm, XCollection <SQLOutboundChanel> channels, int index)
{
InitializeComponent();
parentForm = frm;
channelSet = channels;
type = "Edit";
this.Text = "Edit Channel";
channel = channelSet[index];
criteriaItemList = channelSet[index].Rule.QueryCriteria.MappingList;
resultItemList = channelSet[index].Rule.QueryResult.MappingList;
channelIndex = index;
GetChannelNameSet(channels);
ShowChannel(channel);
enumCmbbxOperationType.Enabled = false;
if (parentForm.DBconfig.ConnectionParameter.FileConnection)
{
this.enumCmbbxOperationType.SelectedIndex = 1;
this.enumCmbbxOperationType.Enabled = false;
this.txtModeName.Enabled = false;
}
}
/// <summary>
///
/// </summary>
/// <param name="ds"></param>
/// <param name="ch"></param>
/// <returns></returns>
private bool WriteSP(DataSet ds, SQLOutboundChanel ch)
{
// ------ 20070419 ------
string[] data_Id = new string[1];
OleDbCommand cmd = Program.db.GetCommand();
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
cmd.Parameters.Clear();
cmd.CommandText = ch.OperationName;
cmd.CommandType = CommandType.StoredProcedure;
SetPamater(dr, cmd, ch);
if (Program.db.ExecCommand(cmd))
{
data_Id[0] = Convert.ToString(dr["data_id"]);
if (OnDataDischarge != null)
{
OnDataDischarge(data_Id);
}
//TestCase.OnDataDischarge(data_Id); //DEBUG
}
}
catch (Exception Ex)
{
Program.Log.Write(Ex);
Program.Log.Write(LogType.Info, Program.db.ConnectionString);
if (!ch.IgnoreDBException)
{
return(false);
}
}
}
return(true);
// ----------------------
//try
//{
// string[] data_Id = new string[1];
// OleDbCommand cmd = Program.db.GetCommand();
// foreach (DataRow dr in ds.Tables[0].Rows)
// {
// cmd.Parameters.Clear();
// cmd.CommandText = ch.OperationName;
// cmd.CommandType = CommandType.StoredProcedure;
// SetPamater(dr, cmd, ch);
// if (Program.db.ExecCommand(cmd))
// {
// data_Id[0] = Convert.ToString(dr["data_id"]);
// if (OnDataDischarge != null) OnDataDischarge(data_Id);
// //TestCase.OnDataDischarge(data_Id); //DEBUG
// }
// }
// return true;
//}
//catch (Exception Ex)
//{
// Program.Log.Write(Ex);
// return false;
//}
}
//Build a test configuration file
static public bool BuildTestConfigFile()
{
#region 3rd database connection
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.Database = "GWDataDB";
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.Server = "CNSHW9RSZM1X";
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.User = "******";
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.Password = "******";
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.TimerEnable = true;
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.TimerInterval = 1000;
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.ThirdPartyInteractConfig.ConnectionParameter.ConnectionStr = "Provider=SQLNCLI.1;Data Source=CNSHW9RSZM1X;Password=123456;User ID=sa;Initial Catalog=GWDataDB";
#endregion
#region channel Patient_test_sp
SQLOutboundChanel ch = new SQLOutboundChanel();
ch.Enable = true;
ch.ChannelName = "Patient_test_SP";
ch.OperationType = ThrPartyDBOperationType.StorageProcedure;
ch.OperationName = "dbo.p_Patient_test";
ch.Rule.AutoUpdateProcessFlag = false;
ch.Rule.CheckProcessFlag = true;
ch.Rule.RuleName = "sp_testpatient";
// column Patient_test.patientid
SQLOutQueryResultItem map = new SQLOutQueryResultItem();
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 SQLOutQueryResultItem();
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 SQLOutQueryResultItem();
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 SQLOutQueryResultItem();
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);
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels.Add(ch);
#endregion
#region channel Patient_test_table
ch = new SQLOutboundChanel();
ch.ChannelName = "Patient_test_table";
ch.Enable = false;
ch.OperationType = ThrPartyDBOperationType.Table;
ch.OperationName = "dbo.p_Patient_test";
ch.Rule.AutoUpdateProcessFlag = false;
ch.Rule.CheckProcessFlag = true;
ch.Rule.RuleName = "sp_testpatient";
// column Patient_test.patientid
map = new SQLOutQueryResultItem();
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 SQLOutQueryResultItem();
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 SQLOutQueryResultItem();
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 SQLOutQueryResultItem();
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);
SQLOutAdapterConfigMgt.SQLOutAdapterConfig.OutboundChanels.Add(ch);
#endregion
//save
return(SQLOutAdapterConfigMgt.Save(SQLOutAdapterConfigMgt._FileName));
}
private void SetPamater(DataRow dr, OracleCommand cmd, SQLOutboundChanel ch)
{
Program.Log.Write("---- sp parameter ----");
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
OracleParameter parm = new OracleParameter();
parm.ParameterName = "@" + item.TargetField;
parm.DbType = OleDBType2DBType(item.ThirdPartyDBPatamter.FieldType);
parm.Direction = ParameterDirection.Input;
object obj = dr[item.TargetField.Trim()];
if (Convert.IsDBNull(obj))
{
if (item.Translating.Type == TranslatingType.DefaultValue)
{
parm.Value = item.Translating.ConstValue;
}
else
{
parm.Value = System.DBNull.Value;
}
}
else if (Convert.ToString(obj).Trim() == "")
{
if (item.ThirdPartyDBPatamter.FieldType == OleDbType.Date)
{
parm.Value = System.DBNull.Value;
}
else
{
parm.Value = Convert.ToString(obj);
}
}
else
{
//2007-3-23
if (item.ThirdPartyDBPatamter.FieldType == OleDbType.VarChar ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.VarWChar ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.Char ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.WChar)
{
parm.Value = MapingHelper.FixSigleQuoteInSQLStringValue(obj.ToString());
}
else
{
parm.Value = obj;
}
}
Program.Log.Write(parm.ParameterName + "=" + parm.Value);
cmd.Parameters.Add(parm);
}
Program.Log.Write("---------------------");
}
static public bool BuildUpdateCmd(SQLOutboundChanel ch, DataRow dr, OleDbCommand cmd)
{
if (ch.Rule.QueryResult.MappingList.Count < 1)
{
Program.Log.Write(LogType.Warning, "There is no mapinglist");
return(false);
}
ArrayList alSet = new System.Collections.ArrayList();
ArrayList alWhere = new System.Collections.ArrayList();
StringBuilder sbSet = new StringBuilder();
StringBuilder sbWhere = new StringBuilder();
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
if (item.RedundancyFlag)
{
//sbWhere.Append(item.TargetField + "=@" + item.TargetField + " and ");
sbWhere.Append(item.TargetField + "=?" + " and ");
OleDbParameter p = new OleDbParameter("@" + item.TargetField, item.ThirdPartyDBPatamter.FieldType);
p.Value = dr[item.TargetField.Trim()];
alWhere.Add(p);
}
else
{
//sbSet.Append(item.TargetField + "=@" + item.TargetField + ",");
sbSet.Append(item.TargetField + "=?" + " , ");
OleDbParameter p = new OleDbParameter("@" + item.TargetField, item.ThirdPartyDBPatamter.FieldType);
object obj = dr[item.TargetField.Trim()];
if (Convert.IsDBNull(obj))
{
if (item.Translating.Type == TranslatingType.DefaultValue)
{
p.Value = item.Translating.ConstValue;
}
else
{
p.Value = System.DBNull.Value;
}
}
else if (Convert.ToString(obj).Trim() == "")
{
if (item.ThirdPartyDBPatamter.FieldType == OleDbType.Date)
{
p.Value = System.DBNull.Value;
}
}
else
{
//2007-3-23
if (item.ThirdPartyDBPatamter.FieldType == OleDbType.VarChar ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.VarWChar ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.Char ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.WChar)
{
p.Value = MapingHelper.FixSigleQuoteInSQLStringValue(obj.ToString());
}
else
{
p.Value = obj;
}
}
alSet.Add(p);
}
}
if (sbWhere.Length > 0)
{
sbWhere.Remove(sbWhere.Length - 4, 4);
}
if (sbSet.Length > 0)
{
sbSet.Remove(sbSet.Length - 2, 2);
}
cmd.CommandText = " update " + ch.OperationName + " set " + sbSet.ToString() + " where " + sbWhere.ToString();
foreach (OleDbParameter item in alSet)
{
cmd.Parameters.Add(item);
}
foreach (OleDbParameter item in alWhere)
{
cmd.Parameters.Add(item);
}
return(true);
}
static public bool BuildInsertCmd(SQLOutboundChanel ch, DataRow dr, OleDbCommand cmd)
{
if (ch.Rule.QueryResult.MappingList.Count < 1)
{
Program.Log.Write(LogType.Warning, "There is no mapinglist");
return(false);
}
string sbFields = "";
string sbValues = "";
foreach (SQLOutQueryResultItem item in ch.Rule.QueryResult.MappingList)
{
if (item.ThirdPartyDBPatamter.FieldName == null || item.ThirdPartyDBPatamter.FieldName.Length < 1)
{
continue;
}
//if( item.ThirdPartyDBPatamter == Titem.Translating.Type = TranslatingType.
if (item.TargetField != "" && item.TargetField != null) //TODO: if 3rd database need not the field
{
sbFields = sbFields + item.TargetField + " , ";
//sbValues = sbValues + "@" + item.TargetField.Trim() + " , ";
sbValues = sbValues + "?" + " , ";
OleDbParameter p = cmd.Parameters.Add("@" + item.TargetField.Trim(), item.ThirdPartyDBPatamter.FieldType);
object obj = dr[item.TargetField.Trim()];
if (Convert.IsDBNull(obj))
{
if (item.Translating.Type == TranslatingType.DefaultValue)
{
p.Value = item.Translating.ConstValue;
}
else
{
p.Value = System.DBNull.Value;
}
}
else if (Convert.ToString(obj).Trim() == "")
{
if (item.ThirdPartyDBPatamter.FieldType == OleDbType.Date)
{
p.Value = System.DBNull.Value;
}
else
{
p.Value = "";
}
}
else
{
//2007-3-23
if (item.ThirdPartyDBPatamter.FieldType == OleDbType.VarChar ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.VarWChar ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.Char ||
item.ThirdPartyDBPatamter.FieldType == OleDbType.WChar)
{
p.Value = MapingHelper.FixSigleQuoteInSQLStringValue(obj.ToString());
}
else
{
p.Value = obj;
}
}
}
}
if (sbFields.Length > 0)
{
sbFields = sbFields.Substring(0, sbFields.Length - 2);
}
if (sbValues.Length > 0)
{
sbValues = sbValues.Substring(0, sbValues.Length - 2);
}
string sResult = " insert into " + ch.OperationName + "(" + sbFields + ")" + " values(" + sbValues + ")";
cmd.CommandText = sResult;
return(true);
}
public StatementPage(SQLOutboundChanel ch)
{
InitializeComponent();
channel = ch;
interfaceName = Program.DeviceMgt.DeviceDirInfor.Header.Name;
}
/// <summary>
/// Write logical:
/// 1.check PK field whether is existed or not
/// 2.insert new record where PK is not exist
/// 3.update record when PK is exist
/// 4.delete record TODO:???
///
/// Insert logical:
/// 1.select empty DataSet from 3rd database
/// 2.insert new record
/// 3.SqlAdapter.update
/// </summary>
/// <param name="ds"></param>
/// <param name="ch"></param>
/// <returns></returns>
private bool WriteTable(DataSet ds, SQLOutboundChanel ch)
{
//OleDbCommand cmd = Program.db.GetCommand();
// ------ 20070419 ------
string[] data_Id = new string[1];
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
OleDbCommand cmd = Program.db.GetCommand();
if (!RecordIsExist(dr, ch))
{
MapingHelper.BuildInsertCmd(ch, dr, cmd);
}
else
{
MapingHelper.BuildUpdateCmd(ch, dr, cmd);
}
cmd.CommandType = CommandType.Text;
if (Program.db.ExecCommand(cmd))
{
data_Id[0] = Convert.ToString(dr["data_id"]);
OnDataDischarge(data_Id);
//TestCase.OnDataDischarge(data_Id); //DEBUG
}
}
catch (Exception Ex)
{
Program.Log.Write(Ex);
Program.Log.Write(LogType.Info, Program.db.ConnectionString);
if (!ch.IgnoreDBException)
{
return(false);
}
}
}
return(true);
// ----------------------
//try
//{
// string[] data_Id =new string[1];
// foreach (DataRow dr in ds.Tables[0].Rows)
// {
// if (!RecordIsExist(dr, ch))
// MapingHelper.BuildInsertCmd(ch, dr, cmd);
// else
// MapingHelper.BuildUpdateCmd(ch, dr, cmd);
// cmd.CommandType = CommandType.Text;
// if (Program.db.ExecCommand(cmd))
// {
// data_Id[0] = Convert.ToString(dr["data_id"]);
// OnDataDischarge(data_Id);
// //TestCase.OnDataDischarge(data_Id); //DEBUG
// }
// }
// return true;
//}
//catch(Exception Ex)
//{
// Program.Log.Write(Ex);
// return false;
//}
}
private bool CallSQLStatement(DataSet ds, SQLOutboundChanel ch)
{
string sqlstatement = string.Empty;
if (ds == null)
{
try
{
OleDbCommand cmd = Program.db.GetCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstatement = MapingHelper.BuildSQLStatement(ch, null);
if (!Program.db.ExecCommand(cmd))
{
Program.Log.Write(LogType.Error, "Execute sql statement " + sqlstatement + " failed.");
}
}
catch (Exception Ex)
{
Program.Log.Write(LogType.Error, "Execute sql statement " + sqlstatement + " error.");
Program.Log.Write(Ex);
Program.Log.Write(LogType.Info, Program.db.ConnectionString);
if (!ch.IgnoreDBException)
{
return(false);
}
}
}
else
{
string[] data_Id = new string[1];
foreach (DataRow dr in ds.Tables[0].Rows)
{
try
{
OleDbCommand cmd = Program.db.GetCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstatement = MapingHelper.BuildSQLStatement(ch, dr);
if (Program.db.ExecCommand(cmd))
{
data_Id[0] = Convert.ToString(dr["data_id"]);
OnDataDischarge(data_Id);
//TestCase.OnDataDischarge(data_Id); //DEBUG
Program.Log.Write(LogType.Debug, "Execute sql statement " + sqlstatement + " successfully.");
}
else
{
Program.Log.Write(LogType.Error, "Execute sql statement " + sqlstatement + " failed.");
}
}
catch (Exception Ex)
{
Program.Log.Write(LogType.Error, "Execute sql statement " + sqlstatement + " error.");
Program.Log.Write(Ex);
Program.Log.Write(LogType.Info, Program.db.ConnectionString);
if (!ch.IgnoreDBException)
{
return(false);
}
}
}
}
return(true);
}
