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); }
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); }
private string GetUpdateField(string temptable, ImportConfig config) { return(string.Join(",", Fields.Except(Keys).Select(p => $"{p}={temptable}.{p}"))); }
/// <summary> /// 更新策略提交 /// </summary> protected abstract void UpdateStrategy(string temptable, ImportConfig config, out int affectCount);
/// <summary> /// 提交到数据操作 /// </summary> /// <param name="source"></param> protected abstract void SubmitDatabase(DataTable source, ImportConfig config, out string temptable);