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