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