protected void BulkInsertToTable(List <ValidationResultsHashModel> validationResults, string tableName, string hashValidationIndex)
{
using (var dbConnection = new DbConnection.DbConnection())
{
var format = "yyyy-MM-ddTHH:mm:ss.fff";
var updatedOn = DateTime.UtcNow.ToString(format);
/*
* var queriesForDisable = new List<string>();
*
* foreach (var validationResult in validationResults)
* {
* if (validationResult.EventId <= 0 || validationResult.ValidationSettingId <= 0)
* continue;
*
* var hash = validationResult.EventId.ToString() + " " + validationResult.ValidationSettingId.ToString();
*
* queriesForDisable.Add(new StringBuilder("UPDATE IGNORE val_validationresult SET IsActive = b'0', LastUpdate = '" + updatedOn + "', UpdatedOn = '" + updatedOn +
* "' WHERE HashByEventSetting = '" + hash + "'"
+ " AND IsActive=b'1'").ToString());
+ }
+
+ dbConnection.BulkExecuteList(queriesForDisable);
*/
for (int i = 0; i <= validationResults.Count() / InsertCount; i++)
{
var subResults = validationResults.Skip(i * InsertCount).Take(InsertCount).ToList();
if (subResults.Any())
{
var query =
new StringBuilder(
"INSERT DELAYED INTO " + tableName + " (ValidationSettingId, EventId, EventName, SportName, LeagueName, " +
"LocationName, Market, Provider, Status, LastUpdate, ProductId, CreatedOn, CreatedBy, IsActive, " +
"UpdatedOn, UpdatedBy, LeagueId, SportId, LocationId, PointsToHighline, HashIndexByEventProduct, HashUniqueIndex, " +
"Counter, HashValidationIndex) VALUES ");
var counter = 0;
for (var resI = 0; resI < subResults.Count; resI++)
{
var model = subResults[resI];
var validationResult = model.Result;
query.Append("(");
if (validationResult.PointsToHighline.Contains("'"))
{
validationResult.PointsToHighline = validationResult.PointsToHighline.Replace("'", "\\'");
}
query.Append(validationResult.ValidationSettingId);
query.Append("," + validationResult.EventId);
query.Append(",'" + validationResult.EventName + "'");
query.Append(",'" + validationResult.SportName + "'");
query.Append(",'" + validationResult.LeagueName + "'");
query.Append(",'" + validationResult.LocationName + "'");
query.Append(",'" + validationResult.Market + "'");
query.Append(",'" + validationResult.Provider + "'");
query.Append(",'" + validationResult.Status + "'");
query.Append(",'" + validationResult.LastUpdate.ToString(format) + "'");
// query.Append(",'" + validationResult.XmlMessage + "'");
query.Append(",'" + validationResult.ProductId + "'");
query.Append(",'" + validationResult.CreatedOn.ToString(format) + "'");
query.Append(",'" + validationResult.CreatedBy + "'");
query.Append(", b'1'");
query.Append(",'" + updatedOn + "'");
query.Append(",'" + validationResult.UpdatedBy + "'");
query.Append(",'" + validationResult.LeagueId + "'");
query.Append(",'" + validationResult.SportId + "'");
query.Append(",'" + validationResult.LocationId + "'");
query.Append(",'" + validationResult.PointsToHighline + "'");
query.Append(",'" + model.IndexEventProductHash + "'");
query.Append(",'" + model.UniqueHash + "'");
query.Append("," + 1);
query.Append(",'" + hashValidationIndex + "'");
query.Append(")");
if (counter != subResults.Count - 1)
{
query.Append(",");
}
subResults[resI] = null;
counter++;
}
query.Append(" ON DUPLICATE KEY UPDATE");
query.Append(" Status = VALUES(Status)");
query.Append(", LastUpdate = VALUES(LastUpdate)");
query.Append(", UpdatedOn = VALUES(UpdatedOn)");
query.Append(", Market = VALUES(Market)");
query.Append(", Provider = VALUES(Provider)");
query.Append(", IsActive = b'1'");
// query.Append(", UpdatedOn = VALUES(UpdatedOn)");
query.Append(", HashIndexByEventProduct = VALUES(HashIndexByEventProduct)");
query.Append(", PointsToHighline = VALUES(PointsToHighline)");
query.Append(", Counter = Counter + 1");
query.Append(";");
dbConnection.BulkExecute(query.ToString());
}
}
}
}
public void BulkInsert(IList <Event> events)
{
// lock (this.thisLock)
{
using (var dbConnection = new DbConnection.DbConnection())
{
var updatedOn = DateTime.UtcNow.ToString("yy-MM-dd HH:mm:ss");
for (int i = 0; i <= events.Count / INSERT_COUNT; i++)
{
var subEvents = events.Skip(i * INSERT_COUNT).Take(INSERT_COUNT).ToList();
if (subEvents.Any())
{
var query = new
StringBuilder(
"INSERT DELAYED INTO dvs_event (EventId,EventName,StartDate,SportId,SportName,LeagueId, " +
"LeagueName,LocationId,LocationName,Status,LastUpdate,HomeTeamId,HomeTeamName,AwayTeamId,AwayTeamName," +
"CreatedOn,CreatedBy,IsActive,UpdatedOn,UpdatedBy,MarketId,MarketName,ProviderId,ProviderName,ProductId) VALUES ");
var counter = 0;
foreach (var @event in subEvents)
{
//@event.XmlText = "blabla";
query.Append("(");
query.Append(@event.EventId);
query.Append(",'" + @event.BuildEventName() + "'");
query.Append(",'" + @event.StartDate + "'");
query.Append(",'" + (@event.SportId ?? 0) + "'");
query.Append(",'" + @event.SportName + "'");
query.Append(",'" + (@event.LeagueId ?? 0) + "'");
query.Append(",'" + @event.LeagueName + "'");
query.Append(",'" + (@event.LocationId ?? 0) + "'");
query.Append(",'" + @event.LocationName + "'");
query.Append(",'" + @event.Status + "'");
query.Append(",'" + @event.LastUpdate.ToString("yyyy-MM-ddTHH:mm:ss.fff") + "'");
query.Append(",'" + (@event.HomeTeamId ?? 0) + "'");
query.Append(",'" + @event.HomeTeamName + "'");
query.Append(",'" + (@event.AwayTeamId ?? 0) + "'");
query.Append(",'" + @event.AwayTeamName + "'");
query.Append(",'" + @event.CreatedOn.ToString("yyyy-MM-ddTHH:mm:ss.fff") + "'");
query.Append(",'" + @event.CreatedBy + "'");
query.Append(", b'1'");
query.Append(",'" + updatedOn + "'");
query.Append(",'" + @event.UpdatedBy + "'");
query.Append(",'" + (@event.MarketId ?? 0) + "'");
query.Append(",'" + @event.MarketName + "'");
query.Append(",'" + (@event.ProviderId ?? 0) + "'");
query.Append(",'" + @event.ProviderName + "'");
query.Append(",'" + @event.ProductId + "'");
query.Append(")");
if (counter != subEvents.Count - 1)
{
query.Append(",");
}
counter++;
}
query.Append(" ON DUPLICATE KEY UPDATE");
query.Append(" UpdatedOn = '" + updatedOn + "'");
query.Append(";");
//throw new Exception(query.ToString());
dbConnection.BulkExecute(query.ToString());
}// else pass
}
}
}
}