public void LinqGroupBy()
{
var sql = "SELECT * FROM matchevents ORDER BY idMatch LIMIT 100";
using (var c = new PostgresqlDataLayer(mConfig).GetConn())
{
var events = c.Query <MatchEvent>(sql);
var eventsByMatch = events.GroupBy(e => e.IdMatch, e => e, (key, group) => new { IdMatch = key, Events = group });
}
}
public void MultiMapping2()
{
var sql = "SELECT * FROM players p JOIN users u ON p.iduser = u.id";
using (var c = new PostgresqlDataLayer(mConfig).GetConn())
{
var players = c.Query <Player, User, Player>(sql, (player, user) =>
{
player.UserData = user;
return(player);
},
new { },
splitOn: "id").AsList();
Assert.IsTrue(players.Count > 0);
}
}
public static void RandomizePlayers()
{
if (mArgs.Count != 2)
{
Log("Usage: RandomizePlayers <idTournament>");
return;
}
if (!long.TryParse(mArgs[1], out long idTournament))
{
throw new ArgumentException("idTournament");
}
using (var c = new PostgresqlDataLayer(mDbConfig).GetConn())
{
var t = c.BeginTransaction();
try
{
var players = c.Query <Player>("SELECT id, idUser FROM players p JOIN teamplayers tp ON tp.idPlayer = p.id JOIN tournamentteams tt ON tt.idteam = tp.idTeam WHERE tt.idTournament = @idTournament", new { idTournament }, t);
var sql = new StringBuilder();
foreach (var player in players)
{
var name = NameGenerator.GetRandomName();
var surname = NameGenerator.GetRandomSurname();
sql.AppendFormat("UPDATE players SET name = '{0}', surname='{1}' WHERE id = {2};\r\n", name, surname, player.Id);
sql.AppendFormat("UPDATE users SET name = '{0}' WHERE id = {1};\r\n", name + " " + surname, player.IdUser);
}
var query = sql.ToString();
c.Execute(query, t);
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}
public void MultiMapping3()
{
var sql = "SELECT p.*, email, mobile, avatarimgurl, status FROM players p JOIN teamplayers t ON t.idplayer = p.id JOIN users u ON p.iduser = u.id WHERE t.idteam = @idteam";
using (var c = new PostgresqlDataLayer(mConfig).GetConn())
{
var players = c.Query <Player, User, TeamPlayer, Player>(
sql,
(player, user, teamPlayer) =>
{
player.UserData = user;
player.TeamData = teamPlayer;
return(player);
},
new { idteam = 9 },
splitOn: "email, status").AsList();
Assert.IsTrue(players.Count > 0);
}
}
public static void RandomizeTeams()
{
if (mArgs.Count != 2)
{
Log("Usage: RandomizeTeams <idTournament>");
return;
}
if (!long.TryParse(mArgs[1], out long idTournament))
{
throw new ArgumentException("idTournament");
}
using (var c = new PostgresqlDataLayer(mDbConfig).GetConn())
{
var t = c.BeginTransaction();
try
{
var teams = c.Query <Team>("SELECT id, name FROM teams t JOIN tournamentteams tt ON tt.idTeam = t.id WHERE tt.idTournament = @idTournament", new { idTournament }, t);
var sql = new StringBuilder();
foreach (var team in teams)
{
sql.AppendFormat("UPDATE teams SET name = '{0}', logoImgUrl = '' WHERE id = {1};\r\n", NameGenerator.GetTeamName(team.Id), team.Id);
}
var query = sql.ToString();
c.Execute(query, t);
t.Commit();
}
catch
{
t.Rollback();
throw;
}
}
}