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