public void SaveTableAsCsv(FetchTables TableToFetch, string CsvFolder, string csvSeparator)
{
foreach (Shard shard in TableToFetch.Shards)
{
SaveShardAsCsv(shard, CsvFolder, csvSeparator);
}
}
bool CheckIfTempTableExists(FetchTables ft)
{
string Sql = "select case when OBJECT_ID('" + ft.TargetSchema + "." + ft.SourceSchema + "_" + ft.SourceTable + "_tmp', 'U') is not null then 'true' else 'false' end";
bool r = bool.Parse(TargetDataAccess.GetSingleValue(Sql).ToString());
return(r);
}
void BulkInsert(FetchTables ft, bool ParallelExecution, int MaxThreads, bool OracleSpool)
{
// If table is not incrementally loaded, all data is loaded to a temp table which is then switched
// If incremental load, data is loaded into table
// create target schema if not exists
TargetDataAccess.ExecSqlNonQuery("IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '" + ft.TargetSchema + "') BEGIN EXEC('CREATE SCHEMA " + ft.TargetSchema + "') END");
// 1. Create temp table
if (!ft.Incremental)
{
TargetDataAccess.ExecSqlNonQuery(ft.DropTempTableSql);
TargetDataAccess.ExecSqlNonQuery(ft.CreateTempTableSql);
}
else
{
// if incremental, check if target table exists otherwise create it
if (!CheckIfTableExists(ft))
{
TargetDataAccess.ExecSqlNonQuery(ft.CreateTableSql);
}
}
// 2. Bulk insert data to temp table
Parallel.ForEach(ft.Shards, new ParallelOptions {
MaxDegreeOfParallelism = MaxThreads / 10
}, (shard) =>
//foreach(Shard shard in ft.Shards)
{
Console.WriteLine($"Bulk inserting {shard.Name} on thread {Thread.CurrentThread.ManagedThreadId}");
if (BulkInsert(shard, ft.Incremental, OracleSpool))
{
ft.NumShardsInsertedSuccessfully++;
}
}
);
if (!ft.Incremental)
{
// if all shards where inserted successfully
if (ft.AllShardsBulkInsertedSuccessfully)
{
// 3. Drop existsing table
TargetDataAccess.ExecSqlNonQuery(ft.DropTableSql);
// 4. Rename temp table
TargetDataAccess.ExecSqlNonQuery(ft.SwitchTableSql);
}
}
}
public override List <FetchTables> LoadTablesFromConfig(int MaxRowLimit)
{
List <FetchTables> fetchTables = new List <FetchTables>();
string[] lines = File.ReadAllLines("Config.txt");
List <string> linesWithoutComments = new List <string>();
foreach (string line in lines)
{
if (line.Length > 2 && line.Substring(0, 2) != "//" && line.Substring(0, 2) != "--")
{
linesWithoutComments.Add(line);
}
}
for (int i = 0; i < linesWithoutComments.Count; i++)
{
string line = linesWithoutComments[i];
if (line.Split(':')[0] == "fetchtable")
{
string sourcechema = "", sourcetable = "", shardmethod = "", shardcolumn = "", incrementalcolumn = "", incrementalcolumntype = "", where = "", targetschema = "";
bool loadtotarget = false, sharding = false, incremental = false;
int counter = 0;
for (int j = 1; j <= 10; j++)
{
if (i + j < linesWithoutComments.Count)
{
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "sourceschema")
{
sourcechema = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "sourcetable")
{
sourcetable = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "targetschema")
{
targetschema = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "loadtotarget")
{
loadtotarget = bool.Parse(linesWithoutComments[i + j].Split(':')[1].Trim());
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "sharding")
{
sharding = bool.Parse(linesWithoutComments[i + j].Split(':')[1].Trim());
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "shardmethod")
{
shardmethod = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "shardcolumn")
{
shardcolumn = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "incremental")
{
incremental = bool.Parse(linesWithoutComments[i + j].Split(':')[1].Trim());
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "incrementalcolumn")
{
incrementalcolumn = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "incrementalcolumntype")
{
incrementalcolumntype = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "where")
{
where = linesWithoutComments[i + j].Split(':')[1].Trim();
counter++;
}
if (linesWithoutComments[i + j].Split(':')[0].Trim() == "fetchtable")
{
break;
}
}
}
i = i + counter;
// if no targetschema is set set dlt as default
if (targetschema == "")
{
targetschema = "dlt";
}
FetchTables ft = new FetchTables(sourcechema, sourcetable, targetschema, loadtotarget, "SqlServer");
ft.Sharding = sharding;
ft.ShardMethod = shardmethod;
ft.ShardColumn = shardcolumn;
ft.Incremental = incremental;
ft.IncrementalColumn = incrementalcolumn;
ft.IncrementalColumnType = incrementalcolumntype;
ft.Where = where;
ft.LimitRowsForTest = MaxRowLimit;
fetchTables.Add(ft);
}
}
// Populate each fetchtable with table creation sql´s by fetching metadata from data source
foreach (FetchTables fetchTable in fetchTables)
{
fetchTable.CreateTableSql = GetCreateTableSql(fetchTable.TargetSchema, fetchTable.SourceSchema, fetchTable.SourceTable, false);
fetchTable.CreateTempTableSql = GetCreateTableSql(fetchTable.TargetSchema, fetchTable.SourceSchema, fetchTable.SourceTable, true);
fetchTable.DropTableSql = "IF OBJECT_ID('" + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "', 'U') IS NOT NULL DROP TABLE " + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + ";";
fetchTable.DropTempTableSql = "IF OBJECT_ID('" + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "_tmp', 'U') IS NOT NULL DROP TABLE " + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "_tmp;";
fetchTable.SwitchTableSql = "EXEC sp_rename '" + fetchTable.TargetSchema + "." + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "_tmp', '" + fetchTable.SourceSchema + "_" + fetchTable.SourceTable + "';";
}
return(fetchTables);
}