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