Ejemplo n.º 1
0
        private string GetCondition(string temptable, ImportConfig config)
        {
            string condition = string.Join(" AND ", Keys.Select(p => $"{temptable}.{p}={config.TableName}.{p}"));

            if (!string.IsNullOrEmpty(condition))
            {
                condition = " AND " + condition;
            }
            return(condition);
        }
Ejemplo n.º 2
0
        protected override void CommonStrategy(string temptable, ImportConfig config, out int affectCount)
        {
            IDbCommand cmd = DBConn.CreateCommand();

            cmd.Transaction = DBTrans;
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = $"INSERT INTO {config.TableName} ({string.Join(',', Fields.ToArray())}) SELECT  {string.Join(',', Fields.ToArray())} FROM {temptable} ";
            affectCount     = cmd.ExecuteNonQuery();
        }
Ejemplo n.º 3
0
        protected override void SubmitDatabase(DataTable source, ImportConfig config, out string temptable)
        {
            temptable = $"{config.TableName}_Import";

            //创建临时表
            OracleCommand cmd = DBConn.CreateCommand() as OracleCommand;

            cmd.Transaction = DBTrans as OracleTransaction;
            cmd.CommandText = $"SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = UPPER('{temptable}')";
            if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
            {
                //删除已存在的临时表
                cmd.CommandText = $"DROP TABLE {temptable}";
                cmd.ExecuteNonQuery();
            }

            cmd.CommandText = $"CREATE GLOBAL TEMPORARY TABLE {temptable} ON COMMIT DELETE ROWS AS SELECT {string.Join(',', Fields.ToArray())}  FROM {config.TableName} WHERE 1=0";
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();


            //批量插入oracle
            cmd.ArrayBindCount = source.Rows.Count;
            cmd.CommandText    = $"INSERT INTO {temptable}({string.Join(',', Fields.ToArray())})VALUES({string.Join(',', Fields.Select(p => config.Mappings.Where(c=>c.DBColumn==p).FirstOrDefault().Type== DataType.Date?$"to_date(:{p},'yyyy-mm-dd hh24:mi:ss')":$":{p}"))})";


            for (int i = 0; i < source.Columns.Count; i++)
            {
                object[] value = new object[source.Rows.Count];
                for (int j = 0; j < source.Rows.Count; j++)
                {
                    try
                    {
                        value[j] = source.Rows[j][i];
                    }
                    catch (Exception ex)
                    {
                        throw new ImportException(ex.Message);
                    }
                }

                OracleParameter param = CreateParameter(config.Mappings[i]);
                param.Direction = ParameterDirection.Input;
                param.Value     = value;
                cmd.Parameters.Add(param);
            }

            cmd.ExecuteNonQuery();
        }
Ejemplo n.º 4
0
        protected override void UpdateStrategy(string temptable, ImportConfig config, out int affectCount)
        {
            IDbCommand cmd = DBConn.CreateCommand();

            cmd.Transaction = DBTrans;
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = $@"UPDATE {config.TableName} SET {string.Join(",", Fields.Except(Keys).Select(p => $"{p}={temptable}.{p}"))} FROM {config.TableName} 
                               INNER JOIN {temptable} ON 1=1 {GetCondition(temptable, config)}";

            affectCount = cmd.ExecuteNonQuery();

            cmd.CommandText = $@"INSERT INTO {config.TableName} ({string.Join(',', Fields.ToArray())}) 
                                  SELECT  {string.Join(',', Fields.ToArray())} FROM {temptable} WHERE NOT EXISTS (SELECT 1 FROM {config.TableName} WHERE 1=1 {GetCondition(temptable, config)})";
            cmd.ExecuteNonQuery();
        }
Ejemplo n.º 5
0
        protected override void AbortStrategy(string temptable, ImportConfig config, out int affectCount)
        {
            string checkSql = $@"SELECT COUNT(1) FROM {temptable} WHERE EXISTS (SELECT 1 FROM {config.TableName} WHERE 1=1 {GetCondition(temptable, config)})";

            IDbCommand cmd = DBConn.CreateCommand();

            cmd.Transaction = DBTrans;
            cmd.CommandType = CommandType.Text;

            cmd.CommandText = checkSql;

            if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)
            {
                affectCount = 0;
            }
            else
            {
                CommonStrategy(temptable, config, out affectCount);
            }
        }
Ejemplo n.º 6
0
        protected override void SubmitDatabase(DataTable source, ImportConfig config, out string temptable)
        {
            temptable = $"{config.TableName}_Import";

            string tmpPath = Path.GetTempFileName();
            string csv     = DataTableToCsv(source);

            System.IO.File.WriteAllText(tmpPath, csv);


            //创建临时表
            MySqlCommand cmd = DBConn.CreateCommand() as MySqlCommand;

            cmd.Transaction = DBTrans as MySqlTransaction;


            cmd.CommandText = $"CREATE TEMPORARY TABLE  IF NOT EXISTS {temptable}  SELECT {string.Join(',', Fields.ToArray())}  FROM {config.TableName} WHERE 1=0";
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();

            cmd.CommandText = $"TRUNCATE TABLE {temptable} ";
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();


            MySqlBulkLoader bulk = new MySqlBulkLoader(DBConn as MySqlConnection)
            {
                FieldTerminator         = ",",
                FieldQuotationCharacter = '"',
                EscapeCharacter         = '"',
                LineTerminator          = "\r\n",
                FileName            = tmpPath,
                NumberOfLinesToSkip = 0,
                TableName           = temptable,
            };

            bulk.Columns.AddRange(config.Mappings.Select(m => m.DBColumn).ToArray());
            bulk.Load();
            System.IO.File.Delete(tmpPath);
        }
Ejemplo n.º 7
0
 private string GetUpdateField(string temptable, ImportConfig config)
 {
     return(string.Join(",", Fields.Except(Keys).Select(p => $"{p}={temptable}.{p}")));
 }
Ejemplo n.º 8
0
 /// <summary>
 /// 更新策略提交
 /// </summary>
 protected abstract void UpdateStrategy(string temptable, ImportConfig config, out int affectCount);
Ejemplo n.º 9
0
 /// <summary>
 /// 提交到数据操作
 /// </summary>
 /// <param name="source"></param>
 protected abstract void SubmitDatabase(DataTable source, ImportConfig config, out string temptable);