public static void BulkUpdate(DbConnection con, List<AttributeDefinition> models)
{
StringBuilder queryBuilder = new StringBuilder();
string updateAttributeDefinitionQuery = @"
UPDATE AttributeDefinitions
SET IsIdentifier = @IsIdentifier,
IsAutoEncoding = @IsAutoEncoding,
IsDecision = @IsDecision,
AttributeDataType = @AttributeDataType,
Description = @Description,
ValidationStatus = @ValidationStatus
WHERE Id = @Id;
";
string deleteMapRuleQuery = @"
DELETE FROM MapRules WHERE AttributeDefinitionId = @AttributeDefinitionId;
";
string insertMapRuleQuery = @"
INSERT INTO MapRules(AttributeDefinitionId, RuleContent, RuleType) VALUES(@AttributeDefinitionId, @RuleContent, @RuleType);
";
con.Execute(updateAttributeDefinitionQuery, models);
var mapRuleModels = new List<MapRule>();
models.ForEach(p => {
mapRuleModels.AddRange(p.MapRules);
});
if (mapRuleModels.Count > 0)
{
con.Execute(deleteMapRuleQuery, mapRuleModels);
con.Execute(insertMapRuleQuery, mapRuleModels);
}
}
public void CheckDbAccess(string connectionstring)
{
using (_connection = Utilities.GetOpenConnection(connectionstring))
{
_connection.Execute("CREATE TABLE testdb (Id int)");
_connection.Execute("DROP TABLE testdb");
}
}
public static void BulkInsert(DbConnection con, List<AttributeDefinition> models)
{
con.Execute(@"
INSERT INTO AttributeDefinitions(name, rawName, dataFileId, attributeIndex)
VALUES(@Name,@RawName, @DataFileId, @AttributeIndex);
", models);
}
public int Insert(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, string tableName, string columnList, string parameterList, IEnumerable <PropertyInfo> keyProperties, object entityToInsert)
{
var cmd = $"insert into {tableName} ({columnList}) values ({parameterList})";
connection.Execute(cmd, entityToInsert, transaction, commandTimeout);
var r = connection.Query("Select LAST_INSERT_ID() id", transaction: transaction, commandTimeout: commandTimeout);
var id = r.First().id;
if (id == null)
{
return(0);
}
var propertyInfos = keyProperties as PropertyInfo[] ?? keyProperties.ToArray();
if (!propertyInfos.Any())
{
return(Convert.ToInt32(id));
}
var idp = propertyInfos.First();
idp.SetValue(entityToInsert, Convert.ChangeType(id, idp.PropertyType), null);
return(Convert.ToInt32(id));
}
public void GenerateInitialDatabase(string connectionstring)
{
//if database has versioninfo table ABORT
using (_connection = Utilities.GetOpenConnection(connectionstring))
{
if (_connection.Query<string>("SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'dbversions'").Any())
throw new Exception("What are you doing? The database already exists!");
}
//Regular expression that finds multiline block comments.
Regex findComments = new Regex(@"\/\*.*?\*\/", RegexOptions.Singleline | RegexOptions.Compiled);
using (_connection = Utilities.GetOpenConnection(connectionstring))
{
var statements = Lunch.Data.Install.SQLresources._0001_InitialLoad;
if (string.IsNullOrEmpty(statements))
{
throw new Exception("The sql statement to execute is empty.");
}
string sqlBatch = string.Empty;
foreach (string line in statements.Split(new string[2] {"\n", "\r"}, StringSplitOptions.RemoveEmptyEntries))
{
if (line.ToUpperInvariant().Trim() == "GO")
{
_connection.Execute(sqlBatch);
sqlBatch = string.Empty;
}
else
{
sqlBatch += line + "\n";
}
}
}
}
public RestaurantRating Delete(RestaurantRating entity)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
_connection.Execute("Delete from RestaurantRatings where UserID = @UserID and RestaurantID = @RestaurantID", entity);
}
return entity;
}
public int Insert(SearchHistoryLog entity)
{
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "INSERT INTO SearchHistoryLogs (SearchId, SearchDate, LastTweetId, TweetCount) VALUES (@SearchId, @SearchDate, @LastTweetId,@TweetCount)";
return _connection.Execute(query, entity);
}
}
public void Delete(long id)
{
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "DELETE FROM Searches WHERE SearchId = @SearchId";
_connection.Execute(query, new { SearchId = id });
}
}
public int Insert(IgnoreWord item)
{
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "INSERT INTO IgnoreWords (IgnoreWordId, WordText) VALUES (@IgnoreWordId, @WordText)";
return _connection.Execute(query, item);
}
}
public static void Install(DbConnection connection, string schema)
{
if (connection == null) throw new ArgumentNullException(nameof(connection));
Log.Info("Start installing Hangfire SQL objects...");
if (!IsSqlEditionSupported(connection))
{
throw new PlatformNotSupportedException("The SQL Server edition of the target server is unsupported, e.g. SQL Azure.");
}
var script = GetStringResource(
typeof(SqlServerObjectsInstaller).GetTypeInfo().Assembly,
"Hangfire.SqlServer.Install.sql");
script = script.Replace("SET @TARGET_SCHEMA_VERSION = 5;", "SET @TARGET_SCHEMA_VERSION = " + RequiredSchemaVersion + ";");
script = script.Replace("$(HangFireSchema)", !string.IsNullOrWhiteSpace(schema) ? schema : Constants.DefaultSchema);
#if NETFULL
for (var i = 0; i < RetryAttempts; i++)
{
try
{
connection.Execute(script);
break;
}
catch (DbException ex)
{
if (ex.ErrorCode == 1205)
{
Log.WarnException("Deadlock occurred during automatic migration execution. Retrying...", ex);
}
else
{
throw;
}
}
}
#else
connection.Execute(script);
#endif
Log.Info("Hangfire SQL objects installed.");
}
public static void BulkUpdate(DbConnection con, List<DataFile> models)
{
StringBuilder queryBuilder = new StringBuilder();
string updateDataFileQuery = @"
UPDATE DataFiles
SET Description = @Description
WHERE Id = @Id;
";
con.Execute(updateDataFileQuery, models);
}
public static int Add(CySequenceCounterEntity entity, DbConnection conn)
{
const string sql = @"INSERT INTO [dbo].[CySequenceCounter]
([SequenceKey]
,[CounterId]
,UpdateTime)
VALUES
(@SequenceKey
,@CounterId
,@UpdateTime)";
try
{
int result = conn.Execute(sql, entity);
return result;
}
catch (Exception ex)
{
throw ;
}
}
public void DropTableIfExists(DbConnection db, TableName table)
{
var name = _utils.EscapeTableName(table);
db.Execute($"IF OBJECT_ID('{name}', 'U') IS NOT NULL DROP TABLE {name}");
}
private void SaveTimings(List<Timing> timings, DbConnection conn)
{
const string sql = @"INSERT INTO MiniProfilerTimings
(Id,
MiniProfilerId,
ParentTimingId,
Name,
DurationMilliseconds,
StartMilliseconds,
IsRoot,
Depth,
CustomTimingsJson)
SELECT @Id,
@MiniProfilerId,
@ParentTimingId,
@Name,
@DurationMilliseconds,
@StartMilliseconds,
@IsRoot,
@Depth,
@CustomTimingsJson
WHERE NOT EXISTS (SELECT 1 FROM MiniProfilerTimings WHERE Id = @Id)";
foreach (var timing in timings)
{
conn.Execute(
sql,
new
{
timing.Id,
timing.MiniProfilerId,
timing.ParentTimingId,
Name = timing.Name.Truncate(200),
timing.DurationMilliseconds,
timing.StartMilliseconds,
timing.IsRoot,
timing.Depth,
timing.CustomTimingsJson
});
}
}
private void SaveClientTimings(List<ClientTimings.ClientTiming> timings, DbConnection conn)
{
const string sql = @"INSERT INTO MiniProfilerClientTimings
( Id,
MiniProfilerId,
Name,
Start,
Duration)
SELECT @Id,
@MiniProfilerId,
@Name,
@Start,
@Duration
WHERE NOT EXISTS (SELECT 1 FROM MiniProfilerClientTimings WHERE Id = @Id)";
foreach (var timing in timings)
{
conn.Execute(
sql,
new
{
timing.Id,
timing.MiniProfilerId,
Name = timing.Name.Truncate(200),
timing.Start,
timing.Duration
});
}
}
private static void InitTablesData( DbConnection connection )
{
connection.Execute( @"INSERT INTO [test_table] (Id, name) VALUES (1, 'Grinka')" );
connection.Execute( @"INSERT INTO [test_table] (Id, name) VALUES (2, 'Lenk')" );
connection.Execute( @"INSERT INTO [test_table] (Id, name) VALUES (3, 'Gina')" );
}
/// <summary>
/// Saves parameter Timing to the dbo.MiniProfilerTimings table.
/// </summary>
protected virtual void SaveTiming(DbConnection conn, MiniProfiler profiler, Timing t)
{
const string sql =
@"insert into MiniProfilerTimings
(Id,
MiniProfilerId,
ParentTimingId,
Name,
Depth,
StartMilliseconds,
DurationMilliseconds,
DurationWithoutChildrenMilliseconds,
SqlTimingsDurationMilliseconds,
IsRoot,
HasChildren,
IsTrivial,
HasSqlTimings,
HasDuplicateSqlTimings,
ExecutedReaders,
ExecutedScalars,
ExecutedNonQueries)
values (@Id,
@MiniProfilerId,
@ParentTimingId,
@Name,
@Depth,
@StartMilliseconds,
@DurationMilliseconds,
@DurationWithoutChildrenMilliseconds,
@SqlTimingsDurationMilliseconds,
@IsRoot,
@HasChildren,
@IsTrivial,
@HasSqlTimings,
@HasDuplicateSqlTimings,
@ExecutedReaders,
@ExecutedScalars,
@ExecutedNonQueries)";
conn.Execute(sql, new
{
Id = t.Id,
MiniProfilerId = profiler.Id,
ParentTimingId = t.IsRoot ? (Guid?)null : t.ParentTiming.Id,
Name = t.Name.Truncate(200),
Depth = t.Depth,
StartMilliseconds = t.StartMilliseconds,
DurationMilliseconds = t.DurationMilliseconds,
DurationWithoutChildrenMilliseconds = t.DurationWithoutChildrenMilliseconds,
SqlTimingsDurationMilliseconds = t.SqlTimingsDurationMilliseconds,
IsRoot = t.IsRoot,
HasChildren = t.HasChildren,
IsTrivial = t.IsTrivial,
HasSqlTimings = t.HasSqlTimings,
HasDuplicateSqlTimings = t.HasDuplicateSqlTimings,
ExecutedReaders = t.ExecutedReaders,
ExecutedScalars = t.ExecutedScalars,
ExecutedNonQueries = t.ExecutedNonQueries
});
if (t.HasSqlTimings)
{
foreach (var st in t.SqlTimings)
{
SaveSqlTiming(conn, profiler, st);
}
}
if (t.HasChildren)
{
foreach (var child in t.Children)
{
SaveTiming(conn, profiler, child);
}
}
}
/// <summary>
/// Saves any SqlTimingParameters used in the profiled SqlTiming to the dbo.MiniProfilerSqlTimingParameters table.
/// </summary>
protected virtual void SaveSqlTimingParameters(DbConnection conn, MiniProfiler profiler, SqlTiming st)
{
const string sql =
@"insert into MiniProfilerSqlTimingParameters
(MiniProfilerId,
ParentSqlTimingId,
Name,
DbType,
Size,
Value)
values (@MiniProfilerId,
@ParentSqlTimingId,
@Name,
@DbType,
@Size,
@Value)";
foreach (var p in st.Parameters)
{
conn.Execute(sql, new
{
MiniProfilerId = profiler.Id,
ParentSqlTimingId = st.Id,
Name = p.Name.Truncate(130),
DbType = p.DbType.Truncate(50),
Size = p.Size,
Value = p.Value
});
}
}
public void Update(Search entity)
{
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "UPDATE Searches SET Title = @Title, AllOfTheseWords = @AllOfTheseWords, ThisExactPhrase = @ThisExactPhrase, AnyOfTheseWords = @AnyOfTheseWords, NoneOfTheseWords = @NoneOfTheseWords, NearThisPlace = @NearThisPlace, Radius = @Radius, ResultType = @ResultType WHERE SearchId = @SearchId";
_connection.Execute(query, entity);
}
}
private static void UpdateFermentationSteps(DbConnection context, DbTransaction transaction, Recipe recipe)
{
//Remove mash steps
context.Execute("DELETE FROM FermentationSteps WHERE RecipeId = @RecipeId;", new { recipe.RecipeId }, transaction);
context.Execute("DELETE FROM FermentationStepFermentables WHERE RecipeId = @RecipeId;", new { recipe.RecipeId }, transaction);
context.Execute("DELETE FROM FermentationStepHops WHERE RecipeId = @RecipeId;", new { recipe.RecipeId }, transaction);
context.Execute("DELETE FROM FermentationStepOthers WHERE RecipeId = @RecipeId ;", new { recipe.RecipeId }, transaction);
//Add mash steps
foreach (var fermentationStep in recipe.FermentationSteps)
{
fermentationStep.RecipeId = recipe.RecipeId;
AddFermentationStep(context, transaction, fermentationStep);
}
}
private void UpdateUserSocials(DbConnection context, DbTransaction transaction, User user)
{
var userSocials = context.Query<UserSocial>("SELECT * FROM UserSocials WHERE Username = @Username",
new { user.Username }, transaction);
context.Execute("DELETE FROM UserSocials WHERE Username = @Username and SocialId = @SocialId;",
userSocials.Where(
u => user.Socials.All(s => u.SocialId != s.SocialId)),
transaction);
context.Execute(
"UPDATE UserSocials set Site = @Site, Url = @Url WHERE Username = @Username and SocialId = @SocialId;",
user.Socials, transaction);
context.Execute("INSERT UserSocials(Username,Site,Url) VALUES(@Username,@Site,@Url);",
user.Socials.Where(
s => userSocials.All(u => s.Username != u.Username && u.SocialId != s.SocialId)).Select(s => new { user.Username, s.Site, s.Url }),
transaction);
}
private static void AddRecipe(Recipe recipe, DbConnection context, DbTransaction transaction)
{
context.Execute("INSERT Recipes(RecipeId,Volume,Notes,OG,FG,Efficiency,TotalBoilTime) " +
"VALUES(@RecipeId,@Volume,@Notes,@OG,@FG,@Efficiency,@TotalBoilTime);",
new
{
recipe.RecipeId,
recipe.Volume,
recipe.Notes,
recipe.OG,
recipe.FG,
recipe.Efficiency,
recipe.TotalBoilTime
}, transaction);
SetStepNumber(recipe);
if (recipe.MashSteps != null)
{
foreach (var mashStep in recipe.MashSteps)
{
mashStep.RecipeId = recipe.RecipeId;
AddMashStep(context, transaction, mashStep);
}
}
if (recipe.BoilSteps != null)
{
foreach (var boilStep in recipe.BoilSteps)
{
boilStep.RecipeId = recipe.RecipeId;
AddBoilStep(context, transaction, boilStep);
}
}
if (recipe.FermentationSteps != null)
{
foreach (var fermentationStep in recipe.FermentationSteps)
{
fermentationStep.RecipeId = recipe.RecipeId;
AddFermentationStep(context, transaction, fermentationStep);
}
}
}
public int Insert(Search entity)
{
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "INSERT INTO Searches (Title, AllOfTheseWords, ThisExactPhrase, AnyOfTheseWords, NoneOfTheseWords, NearThisPlace, Radius, ResultType) " +
"VALUES (@Title, @AllOfTheseWords, @ThisExactPhrase, @AnyOfTheseWords, @NoneOfTheseWords, @NearThisPlace, @Radius, @ResultType)";
return _connection.Execute(query, entity);
}
}
private void UpdateRsvp(RSVP rsvp, DbConnection connection)
{
// Future has IsDirtyProperty...
connection.Execute(@"update Rsvp set " +
"DinnerID = @DinnerID, " +
"AttendeeName = @AttendeeName, " +
"AttendeeNameId = @AttendeeNameId " +
"WHERE " +
"DinnerId = @id ",
new
{
id = rsvp.RsvpID,
rsvp.DinnerID,
rsvp.AttendeeName,
rsvp.AttendeeNameId,
});
}
private void UpdateDinner(Dinner dinner, DbConnection connection)
{
// Future has IsDirtyProperty...
connection.Execute(@"update Dinners set " +
"Title = @Title, " +
"EventDate = @EventDate, " +
"Description = @Description, " +
"HostedBy = @HostedBy, " +
"ContactPhone = @ContactPhone, " +
"Address = @Address, " +
"Country = @Country, " +
"Latitude = @Latitude, " +
"Longitude = @Longitude, " +
"HostedById = @HostedById " +
"WHERE " +
"DinnerId = @id ",
new
{
id = dinner.DinnerID,
dinner.Title,
dinner.EventDate,
dinner.Description,
dinner.HostedBy,
dinner.ContactPhone,
dinner.Address,
dinner.Country,
dinner.Latitude,
dinner.Longitude,
dinner.HostedById
});
}
private static void UpdateRecipe(DbConnection context, DbTransaction transaction, Recipe recipe)
{
context.Execute("UPDATE Recipes set Volume = @Volume, Notes = @Notes,OG = @OG,FG = @FG, Efficiency = @Efficiency, TotalBoilTime = @TotalBoilTime " +
"WHERE RecipeId = @RecipeId", recipe, transaction);
SetStepNumber(recipe);
UpdateMashSteps(context, transaction, recipe);
UpdateBoilSteps(context, transaction, recipe);
UpdateFermentationSteps(context, transaction, recipe);
}
/// <summary>
/// Saves parameter SqlTiming to the dbo.MiniProfilerSqlTimings table.
/// </summary>
protected virtual void SaveSqlTiming(DbConnection conn, MiniProfiler profiler, SqlTiming st)
{
const string sql =
@"insert into MiniProfilerSqlTimings
(Id,
MiniProfilerId,
ParentTimingId,
ExecuteType,
StartMilliseconds,
DurationMilliseconds,
FirstFetchDurationMilliseconds,
IsDuplicate,
StackTraceSnippet,
CommandString)
values (@Id,
@MiniProfilerId,
@ParentTimingId,
@ExecuteType,
@StartMilliseconds,
@DurationMilliseconds,
@FirstFetchDurationMilliseconds,
@IsDuplicate,
@StackTraceSnippet,
@CommandString)";
conn.Execute(sql, new
{
Id = st.Id,
MiniProfilerId = profiler.Id,
ParentTimingId = st.ParentTiming.Id,
ExecuteType = st.ExecuteType,
StartMilliseconds = st.StartMilliseconds,
DurationMilliseconds = st.DurationMilliseconds,
FirstFetchDurationMilliseconds = st.FirstFetchDurationMilliseconds,
IsDuplicate = st.IsDuplicate,
StackTraceSnippet = st.StackTraceSnippet.Truncate(200),
CommandString = st.CommandString
});
if (st.Parameters != null && st.Parameters.Count > 0)
{
SaveSqlTimingParameters(conn, profiler, st);
}
}
private void AddBrewers(Beer beer, DbConnection context, DbTransaction transaction)
{
//TODO: FIX THIS.
if (beer.Brewers == null || !beer.Brewers.Any()) return;
//foreach (var userBeer in beer.Brewers)
//{
// userBeer.Username = userBeer.Username.ToLower();
//}
var brewers = beer.Brewers.DistinctBy(u => u.Username.ToLower());
var distinct = brewers.Select(b => new {beer.BeerId, b.Username, b.Confirmed});
context.Execute("INSERT UserBeers(BeerId,Username,Confirmed) VALUES(@BeerId,@Username,@Confirmed);", distinct, transaction);
}
private void UpdateBreweryMembers(DbConnection context, DbTransaction transaction, Brewery brewery)
{
var breweryMembers = context.Query<BreweryMember>(
"SELECT * FROM BreweryMembers WHERE BreweryId = @BreweryId", new { brewery.BreweryId }, transaction);
context.Execute("DELETE FROM BreweryMembers WHERE BreweryId = @BreweryId and MemberUsername = @MemberUsername;",
breweryMembers.Where(bm => brewery.Members.All(m => bm.MemberUsername != m.MemberUsername)).Select(bm => new { brewery.BreweryId, bm.MemberUsername }), transaction);
context.Execute(
"UPDATE BreweryMembers set Role = @Role, Confirmed = @Confirmed WHERE BreweryId = @BreweryId and MemberUsername = @MemberUsername;",
brewery.Members.Where(m => breweryMembers.Any(bm => m.MemberUsername == bm.MemberUsername)), transaction);
context.Execute("INSERT BreweryMembers(BreweryId,MemberUsername,Role,Confirmed) VALUES(@BreweryId,@MemberUsername,@Role,@Confirmed);",
brewery.Members.Where(m => breweryMembers.All(bm => m.MemberUsername != bm.MemberUsername)).Select(bm => new { brewery.BreweryId, bm.MemberUsername, bm.Role, bm.Confirmed }), transaction);
}
private static void AddBoilStep(DbConnection context, DbTransaction transaction, BoilStep boilStep)
{
context.Execute(
"INSERT BoilSteps(Length,Volume,Notes,RecipeId,StepNumber)" +
"VALUES(@Length,@Volume,@Notes,@RecipeId,@StepNumber)", boilStep, transaction);
if (boilStep.Fermentables != null)
{
var duplicates = boilStep.Fermentables.GroupBy(f => f.FermentableId).Where(g => g.Count() > 1).Select(g => g.Key);
if (duplicates.Any())
{
boilStep.Fermentables = boilStep.Fermentables.GroupBy(o => o.FermentableId)
.Select(g => g.Skip(1).Aggregate(
g.First(), (a, o) => { a.Amount += o.Amount; return a; })).ToList();
}
context.Execute(
"INSERT BoilStepFermentables(RecipeId,StepNumber,FermentableId,Amount,Lovibond,PPG) " +
"VALUES(@RecipeId,@StepNumber,@FermentableId,@Amount,@Lovibond,@PPG);",
boilStep.Fermentables.Select(f => new
{
boilStep.RecipeId,
boilStep.StepNumber,
f.FermentableId,
f.Amount,
f.Lovibond,
f.PPG,
}), transaction);
}
if (boilStep.Hops != null)
{
var duplicates = boilStep.Hops.GroupBy(f => f.HopId).Where(g => g.Count() > 1).Select(g => g.Key);
if (duplicates.Any())
{
boilStep.Hops = boilStep.Hops.GroupBy(o => o.Hop)
.Select(g => g.Skip(1).Aggregate(
g.First(), (a, o) => { a.Amount += o.Amount; return a; })).ToList();
}
context.Execute(
"INSERT BoilStepHops(RecipeId,StepNumber,HopId,Amount,AAValue,HopFormId) " +
"VALUES(@RecipeId,@StepNumber,@HopId,@Amount,@AAValue,@HopFormId);",
boilStep.Hops.Select(h => new
{
boilStep.RecipeId,
boilStep.StepNumber,
h.HopId,
h.HopFormId,
h.AAValue,
h.Amount
}), transaction);
}
if (boilStep.Others != null)
{
var duplicates = boilStep.Others.GroupBy(f => f.OtherId).Where(g => g.Count() > 1).Select(g => g.Key);
if (duplicates.Any())
{
boilStep.Others = boilStep.Others.GroupBy(o => o.OtherId)
.Select(g => g.Skip(1).Aggregate(
g.First(), (a, o) => { a.Amount += o.Amount; return a; })).ToList();
}
context.Execute(
"INSERT BoilStepOthers(RecipeId,StepNumber,OtherId,Amount) " +
"VALUES(@RecipeId,@StepNumber,@OtherId,@Amount);",
boilStep.Others.Select(o => new
{
boilStep.RecipeId,
boilStep.StepNumber,
o.Amount,
o.OtherId
}), transaction);
}
}
public void DropTableIfExists(DbConnection db, TableName table)
{
db.Execute($"drop table if exists {_utils.EscapeTableName(table)}");
}