public static void ETLServiceTest()
{
Stopwatch watch = Stopwatch.StartNew();
#region table
Table table = new Table() { Name = "D_CLIENT" };
table.PrimaryKeys.Add(new PrimaryKey() { Name = "SYS_KEY", FieldType = DbType.String });
table.Fields.Add(new Field() { Name = "CLIENT_KEY", FieldType = DbType.String });
table.Fields.Add(new Field() { Name = "SYS_DISPLAY", FieldType = DbType.String });
table.Fields.Add(new Field() { Name = "SYS_LOAD_TIME", FieldType = DbType.DateTime });
table.Fields.Add(new Field() { Name = "SYS_END_TIME", FieldType = DbType.DateTime });
table.Fields.Add(new Field() { Name = "SYS_START_TIME", FieldType = DbType.DateTime });
#endregion
string sourceOleDbConnString = ConfigurationManager.ConnectionStrings["mssql"].ConnectionString;
string dstOleDbConnString = ConfigurationManager.ConnectionStrings["oracle"].ConnectionString;
string sql = "select CLIENT_KEY,SYS_DISPLAY,SYS_LOAD_TIME,SYS_END_TIME,SYS_START_TIME,SYS_KEY from D_CLIENT";
View view = new View(sql, sourceOleDbConnString);
view.OrderBy.Add(new Field() { Name = "SYS_KEY", FieldType = DbType.String });
ETLInfo etlInfo = new ETLInfo(table, "Client");
SerializeHelper.XmlSerializeToFile(etlInfo, "table.xml", true);
ETLInfo e2 = SerializeHelper.XmlDeserializeFromFile<ETLInfo>("table.xml");
SerializeHelper.XmlSerializeToFile(e2, "table2.xml", true);
new ETLService().Process("table.xml", sourceOleDbConnString, dstOleDbConnString, true, ShowMsg);
watch.Stop();
Console.WriteLine("耗时{0}毫秒", watch.ElapsedMilliseconds);
}
private int BulkCopyByPage(ETLInfo etlInfo, int pageSize, int pageIndex, BulkCopy bcp, OleDbConnection sourceConnection, DbConnection DestinationConnection)
{
string sql = etlInfo.SourceTable.ToQuerySQL(pageSize, pageIndex); ;
try
{
DataTable dt = OleDbHelper.ExecuteDataTable(sourceConnection, sql);
if (dt == null || dt.Rows.Count == 0)
return 0;
bcp.Insert(etlInfo.DestinationTableName, dt, etlInfo.ColumnMapping);
return dt.Rows.Count;
}
catch (Exception ex)
{
string errorString = string.Format("转移失败,数据源SQL:{0}...", sql);
File.AppendAllText(@"bulkcopy.log", errorString);
throw;
}
}
public void Process(ETLInfo etlInfo, string oleDbConnstring, string destinationOleDbConnectionString, bool clearDataBeforeETL = false, Action<int> callback = null, int pageSize = 10000)
{
int pageIndex = 0;
int result = 1;
BulkCopy bcp = new BulkCopy(oleDbConnstring);
OleDbConnection sourceConn = new OleDbConnection(oleDbConnstring);
OleDbConnection dstConnection = new OleDbConnection(destinationOleDbConnectionString);
if (clearDataBeforeETL)
OleDbHelper.TruncateTable(dstConnection, etlInfo.DestinationTableName);
int success = 0;
while (result > 0)
{
result = BulkCopyByPage(etlInfo, pageSize, pageIndex, bcp, sourceConn, dstConnection);
if (result > 0)
{
pageIndex++;
success += result;
if (callback != null)
callback(success);
}
}
}
