Esempio n. 1
0
        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);
        }
Esempio n. 2
0
        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);
        }
Esempio n. 3
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("---------------------");
        }
Esempio n. 4
0
        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);
        }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        /// <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;
            //}
        }
Esempio n. 7
0
        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);
        }