public void BulkInsert(DataTable data, Table TargetTable, bool CreateTable,
SqlConnection _con)
{
SqlServer.SQLTempTableHelper.TemporarySQLDestinationTable DestTable = new SqlServer.SQLTempTableHelper.TemporarySQLDestinationTable();
if (CreateTable == true)
{
DestTable.CreateTable(SqlServer.SQLTempTableHelper.TemporarySQLDestinationTable.DropType.DropOnFirstCreateOnly, TargetTable.Schema, TargetTable.Name, data, _con, true, false);
}
DestTable.BulkInsertTableData(_con, TargetTable.QuotedSchemaAndName(), data);
}
public void BulkLoadDataToSQL(Logging LogHelper, DataTable Data, SyncTask s, bool DropTempAfterSuccess, bool ExecuteMerge, bool StringifyTargetTable)
{
bool NoData = false;
if (Data == null)
{
LogHelper.LogWarning("Table " + s.UniqueName + " has no Columns.");
NoData = true;
}
else
{
if ((Data.Columns.Count == 0))
{
LogHelper.LogWarning("Table " + s.UniqueName + " has no Columns.");
NoData = true;
}
}
if (NoData == false)
{
AdsGoFast.SqlServer.Table tbl = new AdsGoFast.SqlServer.Table
{
ConString = s.TargetConString,
Name = string.Format("{0}", s.TargetTableName),
Schema = s.TargetSchema
};
//SQLObjects.AzureSync.PerformSync_WriteToAzure(Data, tbl, SQLObjects.AzureSync.SyncType.Full);
SQLTempTableHelper.TemporarySQLDestinationTable DestTable = new SQLTempTableHelper.TemporarySQLDestinationTable();
Guid g = Guid.NewGuid();
AdsGoFast.SqlServer.Table TempTable = new AdsGoFast.SqlServer.Table
{
Schema = "",
Name = "#Temp_" + s.TargetTableName + "_" + g.ToString() + "",
ConString = tbl.ConString
};
//Check to see if final target exists.. if not then create temp as final target
if (tbl.ExistsInDB() == false)
{
TempTable.Schema = tbl.Schema;
TempTable.Name = tbl.Name;
DropTempAfterSuccess = false;
ExecuteMerge = false;
}
//Target
using (SqlConnection conn = new SqlConnection(tbl.ConString))
{
conn.Open();
DestTable.CreateTable(SQLTempTableHelper.TemporarySQLDestinationTable.DropType.DropOnFirstCreateOnly, TempTable.Schema, TempTable.Name, Data, conn, true, StringifyTargetTable);
TempTable.PersistedCon = conn;
int BatchSize = 1000;
int TotalRows = Data.Rows.Count;
int NumberOfLoops = (TotalRows / BatchSize) + 1;
for (int BatchCounter = 0; BatchCounter < NumberOfLoops; BatchCounter++)
{
int BatchEnd = ((BatchCounter + 1) * BatchSize);
if (BatchEnd >= TotalRows)
{
BatchEnd = TotalRows;
}
int BatchStart = BatchCounter * BatchSize;
LogHelper.LogDebug(string.Format("Writing {2} Records To Database for Table {1}. Total of {0} written.", BatchEnd.ToString(), tbl.QuotedSchemaAndName(), (BatchStart - BatchEnd).ToString()));
//using (SqlConnection conn = new SqlConnection(tbl.ConString))
//{
DataTable Data2 = Data.Clone();
for (int i = BatchStart; i < BatchEnd; ++i)
{
Data2.ImportRow(Data.Rows[i]);
}
//conn.Open();
LogHelper.LogDebug(DateTime.Now.ToString("yyyyMMdd hh:mm:ss") + " Bulk Insert " + TempTable.QuotedSchemaAndName() + " Started..");
DestTable.BulkInsertTableData(conn, TempTable.QuotedSchemaAndName(), Data2);
LogHelper.LogDebug(DateTime.Now.ToString("yyyyMMdd hh:mm:ss") + " Bulk Insert " + TempTable.QuotedSchemaAndName() + " Completed..");
Data2.Clear();
Data2.Dispose();
//}
}
if (ExecuteMerge)
{
//Generate generic merge
Console.WriteLine(DateTime.Now.ToString("yyyyMMdd hh:mm:ss") + " Executing Merge..");
//using (var Conn = new SqlConnection(tbl.ConString))
{
SqlTransaction trans = null;
string SQL = "";
try
{
//conn.Open();
string PrimaryKeyJoin = AdsGoFast.SqlServer.Snippets.GenerateColumnJoinOrUpdateSnippet(TempTable, tbl, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = AdsGoFast.SqlServer.Snippets.GenerateColumnJoinOrUpdateSnippet(TempTable, tbl, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = AdsGoFast.SqlServer.Snippets.GenerateColumnJoinOrUpdateSnippet(TempTable, tbl, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = AdsGoFast.SqlServer.Snippets.GenerateColumnJoinOrUpdateSnippet(TempTable, tbl, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = AdsGoFast.SqlServer.Snippets.GenerateColumnJoinOrUpdateSnippet(TempTable, tbl, "b", "", "=", ",", false, false, false, false, true, null, false, false);
if (s.SyncronisationType == SyncTask.SyncTypeEnum.Full)
{
//SQL = GenerateSQLStatementTemplates.GetSQL("SqlTemplates", "GenericMerge",);
}
if (s.SyncronisationType == SyncTask.SyncTypeEnum.Incremental)
{
//SQLT4Templates.IncrementalLoad GM = new SQLT4Templates.IncrementalLoad(TempTable.QuotedSchemaAndName(), tbl.Name, tbl.Schema, ColList, PrimaryKeyJoin, ColList, ColList, UpdateClause, InsertList, SelectListForInsert, ColList);
//SQL = GM.TransformText();
}
//PrprocessingSQL
if (s.PreProcessingQuery != null && s.PreProcessingQuery != "")
{
using (SqlCommand Com = new SqlCommand(s.PreProcessingQuery.Replace("<@TempTable/>", TempTable.QuotedSchemaAndName()), conn, trans))
{
Com.CommandTimeout = 600;
Com.ExecuteNonQuery();
}
}
//Main Merge
using (SqlCommand Com = new SqlCommand(SQL, conn, trans))
{
Com.CommandTimeout = 600;
Com.ExecuteNonQuery();
}
//Post Processing
if (s.PostProcessingQuery != null && s.PostProcessingQuery != "")
{
using (SqlCommand Com = new SqlCommand(s.PostProcessingQuery, conn, trans))
{
Com.CommandTimeout = 600;
Com.ExecuteNonQuery();
}
}
TempTable.DropTable();
}
catch (Exception Ex)
{
if (trans != null)
{
trans.Rollback();
}
throw new Exception("Error Executing SQL: " + SQL + " ErrorMessage: " + Ex.Message);
}
finally
{
//TempTable.DropTable();
}
Console.WriteLine(DateTime.Now.ToString("yyyyMMdd hh:mm:ss") + " Merge Completed..");
}
}
}
}//If Statement attached to Zero Col Datatable check
}