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);
}
}
}
}
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(string etlInfoFilePath, string oleDbConnstring, string destinationOleDbConnectionString, bool clearDataBeforeETL = false, Action <int> callback = null, int pageSize = 10000)
{
ETLInfo etlInfo = SerializeHelper.XmlDeserializeFromFile <ETLInfo>(etlInfoFilePath);
this.Process(etlInfo, oleDbConnstring, destinationOleDbConnectionString, clearDataBeforeETL, callback, pageSize);
}