public void SendMutilDb()
{
var dbProvider = DbConnectionProvider.CreateDbProvider(MyDataConfigger.DbKey);
var data = new DataTable();
data.Columns.Add("id", typeof(long));
data.Columns.Add("name", typeof(string));
data.Columns.Add("sex", typeof(bool));
data.Columns.Add("money", typeof(decimal));
data.Columns.Add("create_time", typeof(DateTime));
for (int i = 0; i < 10; i++)
{
var dr = data.NewRow();
dr["id"] = i + 1;
dr["name"] = "晨test_" + i;
dr["sex"] = 1;
dr["money"] = 45.23;
dr["create_time"] = DateTime.Now;
data.Rows.Add(dr);
}
DbBulkOption option = new DbBulkOption()
{
};
var watch = Stopwatch.StartNew();
var result = dbProvider.ExecuteBulkQuery("bulk_temp", data, option);
Trace.WriteLine("result:" + result);
WaitEnd(watch);
}
/// <summary>
///
/// </summary>
/// <param name="tabkeName"></param>
/// <param name="dataTable"></param>
/// <param name="option"></param>
/// <returns></returns>
public override long ExecuteBulkQuery(string tabkeName, DataTable dataTable, DbBulkOption option)
{
long result = 0;
OpenConnection(conn =>
{
var tran = conn.BeginTransaction();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, option.CopyOptions, tran))
{
SetBulkOption(bulkCopy, tabkeName, option);
foreach (DataColumn col in dataTable.Columns)
{
bulkCopy.ColumnMappings.Add(col.ColumnName, option.GetColumnMapping(col.ColumnName));
}
try
{
bulkCopy.WriteToServer(dataTable);
tran.Commit();
result = bulkCopy.NotifyAfter;
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
}
});
return(result);
}
private static void SetBulkOption(SqlBulkCopy bulkCopy, string tabkeName, DbBulkOption option)
{
bulkCopy.DestinationTableName = tabkeName;
if (option.Timeout.HasValue)
{
bulkCopy.BulkCopyTimeout = option.Timeout.Value;
}
if (option.BashSize.HasValue)
{
bulkCopy.BatchSize = option.BashSize.Value;
}
bulkCopy.SqlRowsCopied += (object sender, SqlRowsCopiedEventArgs e) =>
{
option.OnRowsCopied(e.RowsCopied);
};
}
/// <summary>
///
/// </summary>
/// <param name="tabkeName"></param>
/// <param name="dataTable"></param>
/// <param name="option"></param>
/// <returns></returns>
public override long ExecuteBulkQuery(string tabkeName, DataTable dataTable, DbBulkOption option)
{
long result = 0;
OpenConnection(conn =>
{
MySqlBulkLoader bulkCopy = new MySqlBulkLoader(conn);
SetBulkOption(bulkCopy, tabkeName, option);
var columns = new List <string>();
foreach (DataColumn col in dataTable.Columns)
{
string column = option.GetColumnMapping(col.ColumnName);
columns.Add(column);
bulkCopy.Columns.Add(column);
}
BuildTempFile(columns, dataTable, option);
FileInfo fi = new FileInfo(option.TempPath);
FileAttributes oldAttr = fi.Attributes;
try
{
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
result = bulkCopy.Load();
}
finally
{
fi.Attributes = oldAttr;
try
{
fi.Delete();
}
catch
{
}
}
});
return(result);
}
private static void SetBulkOption(MySqlBulkLoader bulkCopy, string tabkeName, DbBulkOption option)
{
if (string.IsNullOrEmpty(tabkeName))
{
throw new ArgumentNullException(nameof(tabkeName));
}
if (string.IsNullOrEmpty(option.TempPath))
{
option.TempPath = Path.GetTempFileName();
}
bulkCopy.TableName = tabkeName;
bulkCopy.FileName = option.TempPath;
if (option.Timeout.HasValue)
{
bulkCopy.Timeout = option.Timeout.Value;
}
if (!string.IsNullOrEmpty(option.LinePrefix))
{
bulkCopy.LinePrefix = option.LinePrefix;
}
if (!string.IsNullOrEmpty(option.FieldTerminator))
{
bulkCopy.FieldTerminator = option.FieldTerminator;
}
if (!string.IsNullOrEmpty(option.LineTerminator))
{
bulkCopy.LineTerminator = option.LineTerminator;
}
if (option.ConflictOption.HasValue)
{
bulkCopy.ConflictOption = option.ConflictOption.Value;
}
if (option.FieldQuotationCharacter.HasValue)
{
bulkCopy.FieldQuotationCharacter = option.FieldQuotationCharacter.Value;
bulkCopy.FieldQuotationOptional = true;
}
if (option.NumberOfLinesToSkip.HasValue)
{
bulkCopy.NumberOfLinesToSkip = option.NumberOfLinesToSkip.Value;
}
if (option.EscapeCharacter.HasValue)
{
bulkCopy.EscapeCharacter = option.EscapeCharacter.Value;
}
}
/// <summary>
///
/// </summary>
/// <param name="tabkeName"></param>
/// <param name="columns"></param>
/// <param name="dataReader"></param>
/// <param name="option"></param>
/// <returns></returns>
public override long ExecuteBulkQuery(string tabkeName, IEnumerable <string> columns, IDataReader dataReader, DbBulkOption option)
{
long result = 0;
OpenConnection(conn =>
{
MySqlBulkLoader bulkCopy = new MySqlBulkLoader(conn);
SetBulkOption(bulkCopy, tabkeName, option);
foreach (var column in columns)
{
bulkCopy.Columns.Add(option.GetColumnMapping(column));
}
BuildTempFile(columns, dataReader, option);
FileInfo fi = new FileInfo(option.TempPath);
FileAttributes oldAttr = fi.Attributes;
try
{
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
result = bulkCopy.Load();
}
finally
{
fi.Attributes = oldAttr;
try
{
fi.Delete();
}
catch
{
}
}
});
return(result);
}
private void BuildTempFile(IEnumerable <string> columns, IDataReader dataReader, DbBulkOption option)
{
using (StreamWriter sw = new StreamWriter(File.OpenWrite(option.TempPath)))
{
while (dataReader.Read())
{
sw.Write($"{option.LinePrefix ?? ""}");
int columnIndex = 0;
foreach (var column in columns)
{
columnIndex++;
var index = dataReader.GetOrdinal(column);
Type fieldType = dataReader.GetFieldType(index);
if (option.ColumnValueFormat != null)
{
option.ColumnValueFormat(sw, column, fieldType, dataReader[column]);
//处理结束符
if (columnIndex > 1)
{
sw.Write(option.FieldTerminator);
}
continue;
}
//默认值处理
if (typeof(DateTime) == fieldType)
{
sw.Write($"{dataReader.GetDateTime(index).ToString(option.DateTimeFormat)}");
}
else
{
sw.Write($"{dataReader.GetString(index)}");
}
//处理结束符
if (columnIndex > 1)
{
sw.Write(option.FieldTerminator);
}
}
sw.Write(option.LineTerminator);
}
sw.Flush();
}
}
private void BuildTempFile(IEnumerable <string> columns, DataTable dataTable, DbBulkOption option)
{
using (StreamWriter sw = new StreamWriter(File.OpenWrite(option.TempPath)))
{
foreach (DataRow row in dataTable.Rows)
{
sw.Write($"{option.LinePrefix ?? ""}");
int columnIndex = 0;
foreach (var column in columns)
{
columnIndex++;
//处理结束符
if (columnIndex > 1)
{
sw.Write(option.FieldTerminator);
}
Type fieldType = row.Table.Columns[column].DataType;
if (option.ColumnValueFormat != null)
{
option.ColumnValueFormat(sw, column, fieldType, row[column]);
continue;
}
//默认值处理
if (typeof(DateTime) == fieldType)
{
sw.Write($"{row[column].ToDateTime().ToString(option.DateTimeFormat)}");
}
else
{
sw.Write($"{row[column].ToNotNullString()}");
}
}
sw.Write(option.LineTerminator);
}
sw.Flush();
}
}
