public void AutoBulkInsertAndMerge(DataTable dt, string StagingTableName, string TargetTableName)
{
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using (SqlConnection conn = TMD.GetSqlConnection())
{
Table SourceTable = new Table
{
Name = StagingTableName,
Schema = null,
PersistedCon = conn
};
Table TargetTable = new Table
{
Name = TargetTableName,
Schema = "dbo",
PersistedCon = conn
};
TargetTable.GetColumnsFromExistingDB(true);
TMD.BulkInsert(dt, SourceTable, true, conn);
SourceTable.GetColumnsFromExistingDB(true);
string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TargetFullName", TargetTable.QuotedSchemaAndName() },
{ "SourceFullName", SourceTable.QuotedSchemaAndName() },
{ "PrimaryKeyJoin_AB", PrimaryKeyJoin },
{ "UpdateClause", UpdateClause },
{ "SelectListForInsert", SelectListForInsert },
{ "InsertList", InsertList }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericMerge", SqlParams);
conn.Execute(MergeSQL);
}
}
public string GenerateInsertSQL(string StagingTableSchema, string StagingTableName, string TargetTableSchema, string TargetTableName, SqlConnection _con)
{
Table SourceTable = new Table
{
Name = StagingTableName,
Schema = StagingTableSchema,
PersistedCon = _con
};
Table TargetTable = new Table
{
Name = TargetTableName,
Schema = TargetTableSchema,
PersistedCon = _con
};
TargetTable.GetColumnsFromExistingDB(true);
SourceTable.GetColumnsFromExistingDB(true);
string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TargetFullName", TargetTable.SchemaAndName() },
{ "SourceFullName", SourceTable.SchemaAndName() },
{ "PrimaryKeyJoin_AB", PrimaryKeyJoin },
{ "UpdateClause", UpdateClause },
{ "SelectListForInsert", SelectListForInsert },
{ "InsertList", InsertList }
};
string MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericInsert", SqlParams);
return(MergeSQL);
}
public string GenerateMergeSQL(string StagingTableSchema, string StagingTableName, string TargetTableSchema, string TargetTableName, SqlConnection _con, bool CheckSchemaDrift, Logging logging)
{
if (CheckSchemaDrift)
{
SqlCommand cmd = new SqlCommand(string.Format("Select * from {0}.{1}", StagingTableSchema, StagingTableName), _con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable stagingDT = new DataTable();
da.Fill(stagingDT);
cmd = new SqlCommand(string.Format("Select * from {0}.{1}", TargetTableSchema, TargetTableName), _con);
da = new SqlDataAdapter(cmd);
DataTable targetDT = new DataTable();
da.Fill(targetDT);
bool schemaEqual = DataTableSchemaCompare.SchemaEquals(stagingDT, targetDT);
if (schemaEqual == false)
{
logging.LogWarning(string.Format("****Schema Drift for Table {0}.{1} to {2}.{3}", StagingTableSchema, StagingTableName.Replace("#Temp_", ""), TargetTableSchema, TargetTableName.Replace("#Temp_", "")));
}
}
Table SourceTable = new Table
{
Name = StagingTableName,
Schema = StagingTableSchema,
PersistedCon = _con
};
Table TargetTable = new Table
{
Name = TargetTableName,
Schema = TargetTableSchema,
PersistedCon = _con
};
TargetTable.GetColumnsFromExistingDB(true);
SourceTable.GetColumnsFromExistingDB(true);
string PrimaryKeyJoin = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "a", "b", "=", " and ", true, true, false, false, false, null, false, false);
string ColList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "=", ",", true, true, false, false, true, null, true, false);
string SelectListForInsert = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "", ",", true, false, false, false, true, null, true, false);
string InsertList = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "", "", "", ",", true, false, false, false, true, null, true, false);
string UpdateClause = Snippets.GenerateColumnJoinOrUpdateSnippet(SourceTable, TargetTable, "b", "", "=", ",", false, false, false, false, true, null, false, false);
Dictionary <string, string> SqlParams = new Dictionary <string, string>
{
{ "TargetFullName", TargetTable.SchemaAndName() },
{ "SourceFullName", SourceTable.SchemaAndName() },
{ "PrimaryKeyJoin_AB", PrimaryKeyJoin },
{ "UpdateClause", UpdateClause },
{ "SelectListForInsert", SelectListForInsert },
{ "InsertList", InsertList }
};
string MergeSQL = string.Empty;
if (PrimaryKeyJoin.Length >= 4)
{
MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericMerge", SqlParams);
}
else
{
MergeSQL = GenerateSQLStatementTemplates.GetSQL(Shared.GlobalConfigs.GetStringConfig("SQLTemplateLocation"), "GenericTruncateInsert", SqlParams);
}
return(MergeSQL);
}