public int InsertUserTeamChip(UserTeamChips userTeamChips, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeamChips.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "UserTeamChipStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
//bulkCopy.ColumnMappings.Add("name", "chip_name");
bulkCopy.ColumnMappings.Add("time", "chip_time");
bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
bulkCopy.ColumnMappings.Add("event", "gameweekid");
bulkCopy.ColumnMappings.Add("chip", "chipid");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamChip Repository (insert) error: " + ex.Message);
return(rowsAffected);
//throw ex;
}
}
public int InsertUserTeam(UserTeams userTeams, SqlConnection db)
{
try
{
int rowsAffected = 0;
using (IDataReader reader = userTeams.GetDataReader())
//using (var db = new SqlConnection(ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString))
{
//db.Open();
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 1000;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "UserTeam";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "id");
bulkCopy.ColumnMappings.Add("player_first_name", "player_first_name");
bulkCopy.ColumnMappings.Add("player_last_name", "player_last_name");
bulkCopy.ColumnMappings.Add("player_region_id", "player_region_id");
bulkCopy.ColumnMappings.Add("player_region_name", "player_region_name");
bulkCopy.ColumnMappings.Add("player_region_iso_code_long", "player_region_iso_code");
bulkCopy.ColumnMappings.Add("summary_overall_points", "summary_overall_points");
bulkCopy.ColumnMappings.Add("summary_overall_rank", "summary_overall_rank");
bulkCopy.ColumnMappings.Add("summary_event_points", "summary_gameweek_points");
bulkCopy.ColumnMappings.Add("summary_event_rank", "summary_gameweek_rank");
bulkCopy.ColumnMappings.Add("current_event", "current_gameweekId");
bulkCopy.ColumnMappings.Add("joined_time", "joined_time");
bulkCopy.ColumnMappings.Add("name", "team_name");
bulkCopy.ColumnMappings.Add("last_deadline_bank", "team_bank");
bulkCopy.ColumnMappings.Add("last_deadline_value", "team_value");
bulkCopy.ColumnMappings.Add("last_deadline_total_transfers", "team_transfers");
bulkCopy.ColumnMappings.Add("kit", "kit");
bulkCopy.ColumnMappings.Add("favourite_team", "favourite_teamid");
bulkCopy.ColumnMappings.Add("started_event", "started_gameweekid");
//using (var dataReader = userTeamSeasons.ToDataReader())
//{
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
//}
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeam Repository (insert) error: " + ex.Message);
throw new Exception("UserTeam Repository (insert) error: " + ex.Message);
//throw ex;
}
}
public int InsertUserTeamCup(UserTeamCupMatches userTeamCupMatches, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeamCupMatches.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 1000;
bulkCopy.DestinationTableName = "UserTeamCupStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "id");
bulkCopy.ColumnMappings.Add("entry_1_entry", "homeTeam_userTeamid");
bulkCopy.ColumnMappings.Add("entry_1_name", "homeTeam_userTeamName");
bulkCopy.ColumnMappings.Add("entry_1_player_name", "homeTeam_playerName");
bulkCopy.ColumnMappings.Add("entry_1_points", "homeTeam_points");
bulkCopy.ColumnMappings.Add("entry_1_win", "homeTeam_win");
bulkCopy.ColumnMappings.Add("entry_1_draw", "homeTeam_draw");
bulkCopy.ColumnMappings.Add("entry_1_loss", "homeTeam_loss");
bulkCopy.ColumnMappings.Add("entry_1_total", "homeTeam_total");
bulkCopy.ColumnMappings.Add("entry_2_entry", "awayTeam_userTeamid");
bulkCopy.ColumnMappings.Add("entry_2_name", "awayTeam_userTeamName");
bulkCopy.ColumnMappings.Add("entry_2_player_name", "awayTeam_playerName");
bulkCopy.ColumnMappings.Add("entry_2_points", "awayTeam_points");
bulkCopy.ColumnMappings.Add("entry_2_win", "awayTeam_win");
bulkCopy.ColumnMappings.Add("entry_2_draw", "awayTeam_draw");
bulkCopy.ColumnMappings.Add("entry_2_loss", "awayTeam_loss");
bulkCopy.ColumnMappings.Add("entry_2_total", "awayTeam_total");
bulkCopy.ColumnMappings.Add("event", "gameweekid");
bulkCopy.ColumnMappings.Add("is_knockout", "is_knockout");
bulkCopy.ColumnMappings.Add("winner", "winner");
bulkCopy.ColumnMappings.Add("seed_value", "seed_value");
bulkCopy.ColumnMappings.Add("fromuserteamid", "fromuserteamid");
bulkCopy.ColumnMappings.Add("tiebreak", "tiebreak");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamCup Repository (insert) error: " + ex.Message);
throw ex;
}
}
public int UpdateUserTeam(UserTeams userTeams, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeams.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 1000;
bulkCopy.DestinationTableName = "UserTeamUpdateStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "id");
bulkCopy.ColumnMappings.Add("player_first_name", "player_first_name");
bulkCopy.ColumnMappings.Add("player_last_name", "player_last_name");
bulkCopy.ColumnMappings.Add("player_region_id", "player_region_id");
bulkCopy.ColumnMappings.Add("player_region_name", "player_region_name");
bulkCopy.ColumnMappings.Add("player_region_iso_code_long", "player_region_iso_code");
bulkCopy.ColumnMappings.Add("summary_overall_points", "summary_overall_points");
bulkCopy.ColumnMappings.Add("summary_overall_rank", "summary_overall_rank");
bulkCopy.ColumnMappings.Add("summary_event_points", "summary_gameweek_points");
bulkCopy.ColumnMappings.Add("summary_event_rank", "summary_gameweek_rank");
bulkCopy.ColumnMappings.Add("current_event", "current_gameweekId");
bulkCopy.ColumnMappings.Add("joined_time", "joined_time");
bulkCopy.ColumnMappings.Add("name", "team_name");
bulkCopy.ColumnMappings.Add("last_deadline_bank", "team_bank");
bulkCopy.ColumnMappings.Add("last_deadline_value", "team_value");
bulkCopy.ColumnMappings.Add("last_deadline_total_transfers", "team_transfers");
bulkCopy.ColumnMappings.Add("kit", "kit");
bulkCopy.ColumnMappings.Add("favourite_team", "favourite_teamid");
bulkCopy.ColumnMappings.Add("started_event", "started_gameweekid");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeam Repository (update) error: " + ex.Message);
throw ex;
}
}
public int InsertUserTeamClassicLeague(UserTeamClassicLeagues classicleagues, SqlConnection db)
{
try
{
int rowsAffected = 0;
using (IDataReader reader = classicleagues.GetDataReader())
//using (var db = new SqlConnection(ConfigurationManager.ConnectionStrings["FantasyPremierLeagueUserTeam"].ConnectionString))
{
//db.Open();
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 1000;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "UserTeamClassicLeague";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "leagueid");
bulkCopy.ColumnMappings.Add("entry_rank", "entry_rank");
bulkCopy.ColumnMappings.Add("entry_last_rank", "entry_last_rank");
bulkCopy.ColumnMappings.Add("entry_can_leave", "entry_can_leave");
bulkCopy.ColumnMappings.Add("entry_can_admin", "entry_can_admin");
bulkCopy.ColumnMappings.Add("entry_can_invite", "entry_can_invite");
bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
//using (var dataReader = userTeamChips.ToDataReader())
//{
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
//}
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamClassicLeague Repository (insert) error: " + ex.Message);
throw ex;
}
}
public int InsertUserTeamGameweekHistories(UserTeamGameweekHistories userTeamGameweekHistories, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeamGameweekHistories.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 1000;
bulkCopy.DestinationTableName = "UserTeamGameweekHistoryStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
//bulkCopy.ColumnMappings.Add("id", "id");
bulkCopy.ColumnMappings.Add("event", "gameweekid");
bulkCopy.ColumnMappings.Add("points", "points");
bulkCopy.ColumnMappings.Add("total_points", "total_points");
bulkCopy.ColumnMappings.Add("rank", "userteam_rank");
bulkCopy.ColumnMappings.Add("rank_sort", "userteam_rank_sort");
bulkCopy.ColumnMappings.Add("overall_rank", "userteam_overall_rank");
bulkCopy.ColumnMappings.Add("bank", "userteam_bank");
bulkCopy.ColumnMappings.Add("value", "userteam_value");
bulkCopy.ColumnMappings.Add("event_transfers", "userteam_gameweek_transfers");
bulkCopy.ColumnMappings.Add("event_transfers_cost", "userteam_gameweek_transfers_cost");
bulkCopy.ColumnMappings.Add("points_on_bench", "points_on_bench");
bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamGameweekHistory Repository (insert) error: " + ex.Message);
return(rowsAffected);
//throw ex;
}
}
public int InsertUserTeamPick(UserTeamPicks userTeamPicks, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeamPicks.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 1000;
bulkCopy.DestinationTableName = "UserTeamPickStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("element", "playerid");
bulkCopy.ColumnMappings.Add("position", "position");
bulkCopy.ColumnMappings.Add("is_captain", "is_captain");
bulkCopy.ColumnMappings.Add("is_vice_captain", "is_vice_captain");
bulkCopy.ColumnMappings.Add("multiplier", "multiplier");
bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
bulkCopy.ColumnMappings.Add("gameweekid", "gameweekid");
//using (var dataReader = userTeamPicks.ToDataReader())
//{
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
//}
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamPick Repository (insert) error: " + ex.Message);
return(rowsAffected);
//throw ex;
}
}
public int InsertUserTeamClassicLeague(UserTeamClassicLeagues classicleagues, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = classicleagues.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 500;
bulkCopy.DestinationTableName = "UserTeamClassicLeagueStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("id", "leagueid");
bulkCopy.ColumnMappings.Add("entry_rank", "entry_rank");
bulkCopy.ColumnMappings.Add("entry_last_rank", "entry_last_rank");
bulkCopy.ColumnMappings.Add("entry_can_leave", "entry_can_leave");
bulkCopy.ColumnMappings.Add("entry_can_admin", "entry_can_admin");
bulkCopy.ColumnMappings.Add("entry_can_invite", "entry_can_invite");
bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamClassicLeague Repository (insert) error: " + ex.Message);
return(rowsAffected);
//throw ex;
}
}
public int InsertUserTeamTransferHistories(UserTeamTransferHistoryData userTeamTransferData, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeamTransferData.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 1000;
bulkCopy.DestinationTableName = "UserTeamTransferHistoryStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("time", "transfer_time");
bulkCopy.ColumnMappings.Add("element_in", "playerid_in");
bulkCopy.ColumnMappings.Add("element_in_cost", "player_in_cost");
bulkCopy.ColumnMappings.Add("element_out", "playerid_out");
bulkCopy.ColumnMappings.Add("element_out_cost", "player_out_cost");
bulkCopy.ColumnMappings.Add("entry", "userteamid");
bulkCopy.ColumnMappings.Add("event", "gameweekid");
bulkCopy.ColumnMappings.Add("userteamtransferhistoryid", "userteamtransferhistoryid");
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamTransferHistory Repository (insert) error: " + ex.Message);
return(rowsAffected);
//throw ex;
}
}
public int InsertUserTeamSeason(UserTeamSeasons userTeamSeasons, SqlConnection db)
{
int rowsAffected = 0;
try
{
using (IDataReader reader = userTeamSeasons.GetDataReader())
{
using (var bulkCopy = new SqlBulkCopy(db))
{
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.BatchSize = 5000;
bulkCopy.DestinationTableName = "UserTeamSeasonStaging";
bulkCopy.EnableStreaming = true;
// Add your column mappings here
bulkCopy.ColumnMappings.Add("season_name", "season_name");
bulkCopy.ColumnMappings.Add("total_points", "total_points");
bulkCopy.ColumnMappings.Add("rank", "userteam_rank");
bulkCopy.ColumnMappings.Add("userteamid", "userteamid");
//using (var dataReader = userTeamSeasons.ToDataReader())
//{
bulkCopy.WriteToServer(reader);
rowsAffected = SqlBulkCopyExtension.RowsCopiedCount(bulkCopy);
//}
}
}
return(rowsAffected);
}
catch (Exception ex)
{
Logger.Error("UserTeamSeason Repository (insert) error: " + ex.Message);
return(rowsAffected);
//throw ex;
}
}
public async Task <bool> ImportTableAsync(
string tableName,
string primaryKeys,
bool HasIdentity,
bool HasLastSeen,
bool HasLastUpdated,
string Joins,
string UpdateColumns,
string AllColumns
)
{
string SqlInstance = this.SqlInstance;
Stopwatch tt = Stopwatch.StartNew();
using (SqlConnection connectionRepository = new SqlConnection(this.ConnectionStringRepository))
{
Stopwatch tc = Stopwatch.StartNew();
await connectionRepository.OpenAsync();
//Logger.LogVerbose($"Opened connection to the Central Repository in { tc.Elapsed.TotalMilliseconds }ms.");
string sql = "";
using (SqlConnection connectionRemote = new SqlConnection(this.ConnectionString))
{
try
{
Stopwatch t = Stopwatch.StartNew();
await connectionRemote.OpenAsync();
//Logger.LogVerbose($"Opened connection to \"{ SqlInstance }\" to import \"{tableName}\" in { t.Elapsed.TotalMilliseconds }ms.");
}
catch (SqlException e)
{
Logger.LogError($"Failed to open connection to { this.SqlInstance }", e.Errors[0].Message);
return(false);
}
string lastSeenInRepo = Config.fullLoad == true ? "1970-01-01" : "";
string lastUpdatedInRepo = Config.fullLoad == true ? "1970-01-01" : "";
if (HasLastSeen == true && lastSeenInRepo == "")
{
// The nolock here is safe as nothing is modifying or writing data for specific instance but it does not block other threads modifying their own instances
sql = $"select convert(varchar(30),isnull(max(date_last_seen),'1970-01-01'),121) " +
$"from { tableName } with (nolock) " +
$"where sql_instance = '{ this.SqlInstance }' ";
using (SqlCommand cmdGetRepoLastSeen = new SqlCommand(sql, connectionRepository))
{
Stopwatch t = Stopwatch.StartNew();
lastSeenInRepo = (await cmdGetRepoLastSeen.ExecuteScalarAsync()).ToString();
Logger.LogVerbose($"Fetched \"Last Seen\" (\"{lastSeenInRepo}\") from \"{ tableName }\" for \"[{ SqlInstance }]\" in { t.Elapsed.TotalMilliseconds }ms.");
}
}
else if (HasLastUpdated == true && lastUpdatedInRepo == "")
{
// The nolock here is safe as nothing is modifying or writing data for specific instance but it does not block other threads modifying their own instances
sql = $"select convert(varchar(30),isnull(max(date_updated),'1970-01-01'),121) " +
$"from { tableName } with (nolock) " +
$"where sql_instance = '{ this.SqlInstance }' ";
using (SqlCommand cmdGetRepoLastUpdated = new SqlCommand(sql, connectionRepository))
{
Stopwatch t = Stopwatch.StartNew();
lastUpdatedInRepo = (await cmdGetRepoLastUpdated.ExecuteScalarAsync()).ToString();
Logger.LogVerbose($"Fetched \"Last Updated\" (\"{lastUpdatedInRepo}\") from \"{ tableName }\" for \"[{ SqlInstance }]\" in { t.Elapsed.TotalMilliseconds }ms.");
}
}
// For the header table, we have to pull new headers for each snapshot
// We are going to build a where clause dynamically
if (tableName == "dbo.sqlwatch_logger_snapshot_header")
{
// The nolock here is safe as nothing is modifying or writing data for specific instance but it does not block other threads modifying their own instances
sql = $@"select 'case ' + char(10) + (
select 'when [snapshot_type_id] = ' + convert(varchar(10),[snapshot_type_id]) + ' then ''' + convert(varchar(23),max([snapshot_time])) + '''' + char(10)
from [dbo].[sqlwatch_logger_snapshot_header] with (nolock)
where sql_instance = '{ this.SqlInstance }'
group by [snapshot_type_id],[sql_instance]
for xml path('')
) + char(10) + ' else '''' end '" ;
string snapshotTimes = "";
// If we are doing a full load, we are going to skip the below and default to 1970-01-01 further below:
if (Config.fullLoad == false)
{
using (SqlCommand cmdGetLastHeaderTimes = new SqlCommand(sql, connectionRepository))
{
snapshotTimes = (await cmdGetLastHeaderTimes.ExecuteScalarAsync()).ToString();
}
}
sql = $@"select *
from [dbo].[sqlwatch_logger_snapshot_header] with (readpast)
where [snapshot_time] > { (snapshotTimes == "" ? "'1970-01-01'" : snapshotTimes) } " ;
}
// For logger tables excluding the snapshot header, check the last snapshot_time we have in the central respository
// and only import new records from remote
else if (tableName.Contains("sqlwatch_logger"))
{
// Check if we are running fullload and set the SnapshotTime way in the past if yes to force load all data from the remote:
string snapshotTime = Config.fullLoad == true ? "1970-01-01" : "";
if (snapshotTime == "")
{
// The nolock here is safe as nothing is modifying or writing data for specific instance but it does not block other threads modifying their own instances
sql = $"select [snapshot_time]=isnull(convert(varchar(23),max([snapshot_time]),121),'1970-01-01') " +
$"from { tableName } with (nolock)" +
$"where sql_instance = '{ this.SqlInstance }'";
using (SqlCommand cmdGetLastSnapshotTime = new SqlCommand(sql, connectionRepository))
{
Stopwatch t = Stopwatch.StartNew();
snapshotTime = (await cmdGetLastSnapshotTime.ExecuteScalarAsync()).ToString();
Logger.LogVerbose($"Fetched \"Last Snapshot Time\" (\"{ (snapshotTime == "" ? "1970-01-01" : snapshotTime) }\") from \"{ tableName }\" for \"[{ SqlInstance }]\" in { t.Elapsed.TotalMilliseconds }ms.");
}
}
// If we are not running full load, we can genuinely not have snapshottime if the tables are empty. In which case, again, we have to pull all data:
sql = $"select * from { tableName } with (readpast) where snapshot_time > '{ (snapshotTime == "" ? "1970-01-01" : snapshotTime) }'";
}
// For any other table, we are assuming they are meta tables:
else
{
sql = $"select * from { tableName } with (readpast)";
// Some tables may have both, the last seen and last updated fields.
// The last seen field takes precedence over the last udpated.
if (lastSeenInRepo != "" && HasLastSeen == true)
{
sql += $" where date_last_seen > '{ lastSeenInRepo }'";
}
else if (lastUpdatedInRepo != "" && HasLastUpdated == true)
{
sql += $" where date_updated > '{ lastUpdatedInRepo }'";
}
}
// ------------------------------------------------------------------------------------------------------------------------------
// BULK COPY
// ------------------------------------------------------------------------------------------------------------------------------
int rowsCopied = 0;
using (SqlCommand cmdGetData = new SqlCommand(sql, connectionRemote))
{
cmdGetData.CommandTimeout = Config.BulkCopyTimeout;
//import data into #t table
try
{
Stopwatch bk1 = Stopwatch.StartNew();
using (SqlDataReader reader = await cmdGetData.ExecuteReaderAsync())
{
if (reader.HasRows == false)
{
Logger.LogVerbose($"Nothing to import from \"[{ SqlInstance }].{ tableName }\"");
if (tableName == "dbo.sqlwatch_logger_snapshot_header")
{
Logger.LogWarning($"No new records in the snapshot header from \"{SqlInstance}\".");
// At this point we want to break importing of any child tables:
return(false);
}
else
{
return(true);
}
}
string PkColumns = primaryKeys;
sql = $"select top 0 * into [#{ tableName }] from { tableName } with (nolock);";
if (PkColumns != "")
{
sql += $"alter table [#{ tableName }] add primary key ({ PkColumns }); ";
}
using (SqlCommand commandRepository = new SqlCommand(sql, connectionRepository))
{
try
{
Stopwatch t = Stopwatch.StartNew();
await commandRepository.ExecuteNonQueryAsync();
Logger.LogVerbose($"Created landing table \"#{ tableName }\" for \"{ SqlInstance }\" in { t.Elapsed.TotalMilliseconds }ms.");
}
catch (SqlException e)
{
Logger.LogError($"Failed to prepare table for \"[{ SqlInstance }].{ tableName}\"", e.Errors[0].Message);
return(false);
}
}
var options = SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.TableLock;
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionRepository, options, null))
{
sqlBulkCopy.DestinationTableName = $"[#{ tableName }]";
sqlBulkCopy.BulkCopyTimeout = Config.BulkCopyTimeout;
sqlBulkCopy.EnableStreaming = Config.SqlBkEnableStreaming;
sqlBulkCopy.BatchSize = Config.SqlBkBatchSize;
try
{
await sqlBulkCopy.WriteToServerAsync(reader);
rowsCopied = SqlBulkCopyExtension.RowsCopiedCount(sqlBulkCopy);
t1 += bk1.Elapsed.TotalMilliseconds;
Logger.LogVerbose($"Copied { rowsCopied } { (rowsCopied == 1 ? "row" : "rows") } from \"[{ SqlInstance }].{ tableName }\" to \"{ (tableName == "dbo.sqlwatch_logger_snapshot_header" ? "dbo.sqlwatch_logger_snapshot_header" : $"#{ tableName }") }\" in { bk1.Elapsed.TotalMilliseconds }ms.");
}
catch (SqlException e)
{
Logger.LogError($"Failed to Bulk Copy data from \"[{ SqlInstance }].{ tableName }\"", e.Errors[0].Message);
return(false);
}
}
}
}
