protected override void SubmitDatabase(DataTable source, ImportConfig config, out string temptable) { temptable = $"#{config.TableName}_Import_tmp"; //创建临时表 IDbCommand cmd = DBConn.CreateCommand(); cmd.Transaction = DBTrans; cmd.CommandText = $"SELECT {string.Join(',', Fields.ToArray())} INTO {temptable} FROM {config.TableName} WHERE 1=0"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); //使用SqlBulkCopy批量提交 using (SqlBulkCopy sqlBC = new SqlBulkCopy(DBConn as SqlConnection, SqlBulkCopyOptions.CheckConstraints, DBTrans as SqlTransaction)) { sqlBC.BatchSize = source.Rows.Count; sqlBC.BulkCopyTimeout = 60; sqlBC.DestinationTableName = temptable; foreach (var item in config.Mappings) { sqlBC.ColumnMappings.Add(item.FileColumn, item.DBColumn); } sqlBC.WriteToServer(source); } }
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(); }
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(); }
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(); }
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); } }
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); }