public IEnumerable<Veto> GetAll(int userId)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Veto>(@"SELECT * FROM Vetoes WHERE Used = 0 AND UserId = @userId", new { userId }).ToList();
}
}
public IEnumerable<Veto> GetAllActive()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Veto>(@"SELECT * FROM Vetoes WHERE Used = 0").ToList();
}
}
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 DBVersion GetLastVersion()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
const string query = "SELECT TOP(1) * FROM DBVersions ORDER BY ID DESC";
return _connection.Query<DBVersion>(query).FirstOrDefault();
}
}
public IList<Vote> GetItemsByLast30Days(DateTime date)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes " +
"WHERE VoteDate > @date",
new {date = date.AddDays(-30)}).ToList();
}
}
public Veto Get(DateTime usedAt)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Veto>(@"SELECT * FROM Vetoes V WHERE
DATEPART(mm, V.UsedAt) = DATEPART(mm, @usedAt)
AND DATEPART(dd, V.UsedAt) = DATEPART(dd, @usedAt)
AND DATEPART(yyyy, V.UsedAt) = DATEPART(yyyy, @usedAt)", new { usedAt = usedAt }).FirstOrDefault();
}
}
public IList<SearchHistoryLog> GetItems(IList<long> searchIds)
{
IList<SearchHistoryLog> results;
using (_connection = Utilities.Database.GetProfiledOpenConnection())
{
const string query = "SELECT * FROM SearchHistoryLogs WHERE SearchId IN @SearchIds";
results = _connection.Query<SearchHistoryLog>(query, new {SearchIds = searchIds.ToArray()}).ToList();
}
return results;
}
public IList<Vote> GetItemsByMonthAndYear(int month, int year)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes " +
"WHERE DATEPART(mm, VoteDate) = @Month " +
"AND DATEPART(yyyy, VoteDate) = @Year",
new {Month = month, Year = year}).ToList();
}
}
public Vote GetItem(int userID, DateTime date)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes " +
"WHERE UserID = @UserID " +
"AND DATEPART(mm, VoteDate) = DATEPART(mm, @VoteDate) " +
"AND DATEPART(dd, VoteDate) = DATEPART(dd, @VoteDate) " +
"AND DATEPART(yyyy, VoteDate) = DATEPART(yyyy, @VoteDate)",
new Vote {UserId = userID, VoteDate = date}).FirstOrDefault();
}
}
public IEnumerable<RestaurantDetails> GetAllDetailed(int? categoryId)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var result =_connection.Query<RestaurantDetails>(
@"Select Restaurants.Id, Restaurants.RestaurantName, Restaurants.PreferredDayOfWeek, RestaurantTypes.Id as RestaurantTypeId, RestaurantTypes.TypeName as TypeName
from Restaurants
LEFT OUTER JOIN RestaurantTypes
ON Restaurants.RestaurantTypeId = RestaurantTypes.Id");
if (categoryId != null)
result = result.Where(f => f.RestaurantTypeId == categoryId);
return result;
}
}
public IEnumerable<Restaurant> GetAllByVote(DateTime dateTime)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var result = _connection.Query<Restaurant>(
@"SELECT R.*, (SELECT COUNT(*) AS Votes FROM Votes V WHERE V.RestaurantId = R.Id AND V.VoteDate > @start AND V.VoteDate < @end) Votes
FROM Restaurants R
ORDER BY Votes DESC
", new {start = dateTime.Date.AddDays(-1),
end = dateTime.Date.AddDays(1)});
return result;
}
}
public IEnumerable<RestaurantRating> GetAll()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var results =
_connection.Query<RestaurantRating>(
@"SELECT RR.Id, U.Id AS UserId, R.Id AS RestaurantId,
COALESCE((SELECT Rating FROM RestaurantRatings RR WHERE RR.UserId = U.Id and RR.RestaurantId = R.Id), 5) AS Rating
FROM Restaurants R
CROSS JOIN Users U
FULL OUTER JOIN RestaurantRatings RR ON RR.UserId = U.Id
ORDER BY R.Id, U.Id");
return results;
}
}
public static CySequenceCounterEntity GetCounterIdByKey(string sequenceKey, DbConnection conn)
{
const string sql = @"SELECT CounterId,UpdateTime
FROM [dbo].[CySequenceCounter](nolock)
WHERE SequenceKey = @SequenceKey";
try
{
var model = conn.Query<CySequenceCounterEntity>(sql, new { SequenceKey = sequenceKey }).FirstOrDefault<CySequenceCounterEntity>();
return model;
}
catch (Exception ex)
{
throw ;
}
}
public IEnumerable<RestaurantOption> GetAll()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
IEnumerable<RestaurantOption> results =
_connection.Query<RestaurantOption, Restaurant, RestaurantOption>(@"SELECT * FROM RestaurantOptions RO
INNER JOIN Restaurants R ON R.Id = RO.RestaurantId ",
(ro, r) =>
{
ro.Restaurant = r;
return ro;
});
return results;
}
}
public IEnumerable<RestaurantOption> GetAllByDate(DateTime dateTime)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
IEnumerable<RestaurantOption> results =
_connection.Query<RestaurantOption, Restaurant, RestaurantOption>(@"SELECT RO.*, (SELECT COUNT (*) FROM Votes WHERE Votes.RestaurantId = R.Id AND DATEPART(dd, VoteDate) = DATEPART(dd, @date) AND DATEPART(mm, VoteDate) = DATEPART(mm, @date) AND DATEPART(yyyy, VoteDate) = DATEPART(yyyy, @date)) AS Votes, R.* FROM RestaurantOptions RO
INNER JOIN Restaurants R ON R.Id = RO.RestaurantId
WHERE SelectedDate = @date",
(ro, r) =>
{
ro.Restaurant = r;
return ro;
}, new {date = dateTime.Date});
return results;
}
}
public IEnumerable<RestaurantOption> GetRecent()
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
IEnumerable<RestaurantOption> results =
_connection.Query<RestaurantOption, Restaurant, RestaurantOption>(@"SELECT * FROM RestaurantOptions RO
INNER JOIN Restaurants R ON R.Id = RO.RestaurantId
WHERE Selected = 1 AND SelectedDate > @startDate",
(ro, r) =>
{
ro.Restaurant = r;
return ro;
}, new {startDate = DateTime.Now.AddDays(-7)});
return results;
}
}
public int Insert(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, string tableName, string columnList, string parameterList, IEnumerable <PropertyInfo> keyProperties, object entityToInsert)
{
var sb = new StringBuilder();
sb.AppendFormat("insert into {0} ({1}) values ({2})", tableName, columnList, parameterList);
// If no primary key then safe to assume a join table with not too much data to return
var propertyInfos = keyProperties as PropertyInfo[] ?? keyProperties.ToArray();
if (!propertyInfos.Any())
{
sb.Append(" RETURNING *");
}
else
{
sb.Append(" RETURNING ");
var first = true;
foreach (var property in propertyInfos)
{
if (!first)
{
sb.Append(", ");
}
first = false;
sb.Append(property.Name);
}
}
var results = connection.Query(sb.ToString(), entityToInsert, transaction, commandTimeout: commandTimeout).ToList();
// Return the key by assinging the corresponding property in the object - by product is that it supports compound primary keys
var id = 0;
foreach (var p in propertyInfos)
{
var value = ((IDictionary <string, object>)results.First())[p.Name.ToLower()];
p.SetValue(entityToInsert, value, null);
if (id == 0)
{
id = Convert.ToInt32(value);
}
}
return(id);
}
public IEnumerable<RestaurantRating> GetAllByUser(int userId)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
var results =
_connection.Query<RestaurantRating, Restaurant, RestaurantRating>(
@"SELECT RR.Id, RR.UserId, R.Id AS RestaurantId,
COALESCE(RR.Rating, 5) AS Rating, R.*
FROM Restaurants R
left outer join RestaurantRatings RR on RR.RestaurantId = R.Id AND RR.UserId = @UserID ORDER BY R.RestaurantName", (rr, r) =>
{
rr.Restaurant = r;
return rr;
},
new { UserId = userId });
return results;
}
}
private void GetBrewers(DbConnection context, Beer beer)
{
var brewers = context.Query<UserBeer, User, UserBeer>(
"SELECT * FROM UserBeers ub " +
"LEFT JOIN Users u ON ub.Username = u.Username " +
"WHERE ub.BeerId = @BeerId;",
(userBeer, user) =>
{
userBeer.User = user;
return userBeer;
}, new { beer.BeerId }, splitOn: "Username");
beer.Brewers = brewers.ToList();
}
private void GetForkOf(DbConnection context, Beer beer)
{
var forkOf = context.Query<Beer, BeerStyle, SRM, ABV, IBU, Beer>(
"SELECT * FROM Beers b " +
"LEFT JOIN BeerStyles bs ON bs.BeerStyleId = b.BeerStyleId " +
"LEFT JOIN SRMs s ON s.SrmId = b.BeerId " +
"LEFT JOIN ABVs a ON a.AbvId = b.BeerId " +
"LEFT JOIN IBUs i ON i.IbuId = b.BeerId " +
"WHERE BeerId = @BeerId"
, (b, beerStyle, srm, abv, ibu) =>
{
if (beerStyle != null)
b.BeerStyle = beerStyle;
if (srm != null)
b.SRM = srm;
if (abv != null)
b.ABV = abv;
if (ibu != null)
b.IBU = ibu;
return b;
},
new { beer.BeerId },
splitOn: "BeerStyleId,SrmId,AbvId,IbuId"
);
beer.ForkeOf = forkOf.SingleOrDefault();
}
private void GetMashSteps(DbConnection context, Recipe recipe)
{
var mashSteps = context.Query<MashStep>(
"SELECT * FROM MashSteps WHERE RecipeId = @RecipeId;", new { recipe.RecipeId });
foreach (var mashStep in mashSteps)
{
var mashStepFermentables = context.Query<MashStepFermentable, Fermentable, MashStepFermentable>(
"SELECT * FROM MashStepFermentables m " +
"LEFT JOIN Fermentables f ON m.FermentableId = f.FermentableId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (mashStepFermentable, fermentable) =>
{
mashStepFermentable.Fermentable = fermentable;
return mashStepFermentable;
}, new { recipe.RecipeId, mashStep.StepNumber }, splitOn: "FermentableId");
mashStep.Fermentables = mashStepFermentables.ToList();
var mashStepHops = context.Query<MashStepHop, Hop, MashStepHop>(
"SELECT * FROM MashStepHops m " +
"LEFT JOIN Hops h ON m.HopId = h.HopId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (mashStepHop, hop) =>
{
mashStepHop.Hop = hop;
return mashStepHop;
}, new { recipe.RecipeId, mashStep.StepNumber }, splitOn: "HopId");
mashStep.Hops = mashStepHops.ToList();
var mashStepOthers = context.Query<MashStepOther, Other, MashStepOther>(
"SELECT * FROM MashStepOthers m " +
"LEFT JOIN Others o ON m.OtherId = o.OtherId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (mashStepOther, other) =>
{
mashStepOther.Other = other;
return mashStepOther;
}, new { recipe.RecipeId, mashStep.StepNumber }, splitOn: "OtherId");
mashStep.Others = mashStepOthers.ToList();
}
recipe.MashSteps = mashSteps.ToList();
}
private void GetBoilSteps(DbConnection context, Recipe recipe)
{
var boilSteps = context.Query<BoilStep>(
"SELECT * FROM BoilSteps WHERE RecipeId = @RecipeId;", new { recipe.RecipeId });
foreach (var boilStep in boilSteps)
{
var boilStepFermentables = context.Query<BoilStepFermentable, Fermentable, BoilStepFermentable>(
"SELECT * FROM BoilStepFermentables b " +
"LEFT JOIN Fermentables f ON b.FermentableId = f.FermentableId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (boilStepFermentable, fermentable) =>
{
boilStepFermentable.Fermentable = fermentable;
return boilStepFermentable;
}, new { recipe.RecipeId, boilStep.StepNumber }, splitOn: "FermentableId");
boilStep.Fermentables = boilStepFermentables.ToList();
var boilStepHops = context.Query<BoilStepHop, Hop, BoilStepHop>(
"SELECT * FROM BoilStepHops b " +
"LEFT JOIN Hops h ON b.HopId = h.HopId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (boilStepHop, hop) =>
{
boilStepHop.Hop = hop;
return boilStepHop;
}, new { recipe.RecipeId, boilStep.StepNumber }, splitOn: "HopId");
boilStep.Hops = boilStepHops.ToList();
var boilStepOthers = context.Query<BoilStepOther, Other, BoilStepOther>(
"SELECT * FROM BoilStepOthers b " +
"LEFT JOIN Others o ON b.OtherId = o.OtherId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (boilStepOther, other) =>
{
boilStepOther.Other = other;
return boilStepOther;
}, new { recipe.RecipeId, boilStep.StepNumber }, splitOn: "OtherId");
boilStep.Others = boilStepOthers.ToList();
}
recipe.BoilSteps = boilSteps.ToList();
}
private void GetFermentationSteps(DbConnection context, Recipe recipe)
{
var fermentationSteps = context.Query<FermentationStep>(
"SELECT * FROM FermentationSteps WHERE RecipeId = @RecipeId;", new { recipe.RecipeId });
foreach (var fermentationStep in fermentationSteps)
{
var fermentationStepFermentables = context.Query<FermentationStepFermentable, Fermentable, FermentationStepFermentable>(
"SELECT * FROM FermentationStepFermentables fs " +
"LEFT JOIN Fermentables f ON fs.FermentableId = f.FermentableId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (fermentationStepFermentable, fermentable) =>
{
fermentationStepFermentable.Fermentable = fermentable;
return fermentationStepFermentable;
}, new { recipe.RecipeId, fermentationStep.StepNumber }, splitOn: "FermentableId");
fermentationStep.Fermentables = fermentationStepFermentables.ToList();
var fermentationStepHops = context.Query<FermentationStepHop, Hop, FermentationStepHop>(
"SELECT * FROM FermentationStepHops fs " +
"LEFT JOIN Hops h ON fs.HopId = h.HopId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (fermentationStepHop, hop) =>
{
fermentationStepHop.Hop = hop;
return fermentationStepHop;
}, new { recipe.RecipeId, fermentationStep.StepNumber }, splitOn: "HopId");
fermentationStep.Hops = fermentationStepHops.ToList();
var fermentationStepOthers = context.Query<FermentationStepOther, Other, FermentationStepOther>(
"SELECT * FROM FermentationStepOthers fs " +
"LEFT JOIN Others o ON fs.OtherId = o.OtherId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (fermentationStepOther, other) =>
{
fermentationStepOther.Other = other;
return fermentationStepOther;
}, new { recipe.RecipeId, fermentationStep.StepNumber }, splitOn: "OtherId");
fermentationStep.Others = fermentationStepOthers.ToList();
var fermentationStepYeasts = context.Query<FermentationStepYeast, Yeast, FermentationStepYeast>(
"SELECT * FROM FermentationStepYeasts f " +
"LEFT JOIN Yeasts y ON f.YeastId = y.YeastId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (fermentationStepYeast, yeast) =>
{
fermentationStepYeast.Yeast = yeast;
return fermentationStepYeast;
}, new { recipe.RecipeId, fermentationStep.StepNumber }, splitOn: "YeastId");
fermentationStep.Yeasts = fermentationStepYeasts.ToList();
}
recipe.FermentationSteps = fermentationSteps.ToList();
}
private void GetSpargeStep(DbConnection context, Recipe recipe)
{
var spargeSteps = context.Query<SpargeStep>(
"SELECT * FROM SpargeSteps WHERE RecipeId = @RecipeId;", new { recipe.RecipeId });
var spargeStep = spargeSteps.SingleOrDefault();
if (spargeStep == null) return;
var spargeStepHops = context.Query<SpargeStepHop, Hop, SpargeStepHop>(
"SELECT * FROM SpargeStepHops fs " +
"LEFT JOIN Hops h ON fs.HopId = h.HopId " +
"WHERE RecipeId = @RecipeId and StepNumber = @StepNumber;", (spargeStepHop, hop) =>
{
spargeStepHop.Hop = hop;
return spargeStepHop;
}, new { recipe.RecipeId, spargeStep.StepNumber }, splitOn: "HopId");
spargeStep.Hops = spargeStepHops.ToList();
recipe.SpargeStep = spargeStep;
}
private void RecursiveMethod(ref int i, DbConnection conn, MiniProfiler profiler)
{
Thread.Sleep(5); // ensure we show up in the profiler
if (i >= 10) return;
using (profiler.Step("Nested call " + i))
{
// run some meaningless queries to illustrate formatting
conn.Query(
@"select *
from MiniProfilers
where Name like @name
or Name = @name
or DurationMilliseconds >= @duration
or HasSqlTimings = @hasSqlTimings
or Started > @yesterday ", new
{
name = "Home/Index",
duration = 100.5,
hasSqlTimings = true,
yesterday = DateTime.UtcNow.AddDays(-1)
});
conn.Query(@"select RouteName, HitCount from RouteHits where HitCount < 100000000 or HitCount > 0 order by HitCount, RouteName -- this should hopefully wrap");
// massive query to test if max-height is properly removed from <pre> stylings
conn.Query(
@"select *
from (select RouteName,
HitCount
from RouteHits
where HitCount between 0 and 9
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 10 and 19
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 20 and 29
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 30 and 39
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 40 and 49
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 50 and 59
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 60 and 69
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 70 and 79
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 80 and 89
union all
select RouteName,
HitCount
from RouteHits
where HitCount between 90 and 99
union all
select RouteName,
HitCount
from RouteHits
where HitCount > 100)
order by RouteName");
using (profiler.Step("Incrementing a reference parameter named i")) // need a long title to test max-width
{
i++;
}
RecursiveMethod(ref i, conn, profiler);
}
}
private IEnumerable<Reinstatement> GetReinstatements(IEnumerable<Guid> trainIds, DbConnection connection)
{
const string sql = @"
SELECT
[LiveTrainReinstatement].[TrainId]
,[LiveTrainReinstatement].[PlannedDepartureTime]
,[ReinstatementTiploc].[TiplocId]
,[ReinstatementTiploc].[Tiploc]
,[ReinstatementTiploc].[Nalco]
,[ReinstatementTiploc].[Description]
,[ReinstatementTiploc].[Stanox]
,[ReinstatementTiploc].[CRS]
,[Station].[StationName]
,[Station].[Location].[Lat] AS [Lat]
,[Station].[Location].[Long] AS [Lon]
FROM [LiveTrainReinstatement]
INNER JOIN [Tiploc] AS [ReinstatementTiploc] ON [LiveTrainReinstatement].[ReinstatedTiplocId] = [ReinstatementTiploc].[TiplocId]
LEFT JOIN [Station] ON [Station].[TiplocId] = [ReinstatementTiploc].[TiplocId]
WHERE [LiveTrainReinstatement].[TrainId] IN @trainIds";
return connection.Query<Reinstatement, StationTiploc, Reinstatement>(sql,
(r, t) =>
{
r.NewOrigin = t;
return r;
}, new { trainIds }, splitOn: "TiplocId");
}
public IList<Vote> GetItemsByRestaurant(int restaurantID, DateTime date)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote>("SELECT * FROM Votes WHERE RestaurantID = @RestaurantID " +
"AND DATEPART(mm, VoteDate) = DATEPART(mm, @VoteDate) " +
"AND DATEPART(dd, VoteDate) = DATEPART(dd, @VoteDate) " +
"AND DATEPART(yyyy, VoteDate) = DATEPART(yyyy, @VoteDate)",
new Vote {RestaurantId = restaurantID, VoteDate = date}).ToList();
}
}
private void GetBreweries(DbConnection context, Beer beer)
{
var breweries = context.Query<BreweryBeer, Brewery, BreweryBeer>(
"SELECT * " +
"FROM BreweryBeers bb " +
"LEFT JOIN Breweries b ON bb.BreweryId = b.BreweryId " +
"WHERE bb.BeerId = @BeerId", (breweryBeer, brewery) =>
{
breweryBeer.Brewery = brewery;
return breweryBeer;
}, new { beer.BeerId }, splitOn: "BreweryId");
beer.Breweries = breweries.ToList();
}
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 void GetForks(DbConnection context, Beer beer)
{
var forks = context.Query<Beer, SRM, ABV, IBU, BeerStyle, Beer>(
"SELECT * " +
"FROM Beers b " +
"LEFT JOIN SRMs s ON s.SrmId = b.BeerId " +
"LEFT JOIN ABVs a ON a.AbvId = b.BeerId " +
"LEFT JOIN IBUs i ON i.IbuId = b.BeerId " +
"LEFT JOIN BeerStyles bs ON bs.BeerStyleId = b.BeerStyleId " +
"WHERE b.ForkeOfId = @BeerId", (fork, srm, abv, ibu, beerStyle) =>
{
if (srm != null)
fork.SRM = srm;
if (abv != null)
fork.ABV = abv;
if (ibu != null)
fork.IBU = ibu;
if (beerStyle != null)
fork.BeerStyle = beerStyle;
return beer;
}, new { beer.BeerId }, splitOn: "SrmId,AbvId,IbuId,BeerStyleId");
beer.Forks = forks.ToList();
}
public IList<Vote> GetListForDate(DateTime date)
{
using (_connection = Utilities.GetProfiledOpenConnection())
{
return _connection.Query<Vote, User, Restaurant, Vote>(@"SELECT * FROM Votes V
INNER JOIN Users U ON U.Id = V.UserId
INNER JOIN Restaurants R ON R.Id = V.RestaurantId
WHERE
DATEPART(mm, VoteDate) = DATEPART(mm, @VoteDate)
AND DATEPART(dd, VoteDate) = DATEPART(dd, @VoteDate)
AND DATEPART(yyyy, VoteDate) = DATEPART(yyyy, @VoteDate)",
(v, u, r) =>
{
v.User = u;
v.Restaurant = r;
return v;
},
new Vote {VoteDate = date}).ToList();
}
}
