Skip to content

Instantly share code, notes, and snippets.

Avatar

Sébastien Sougnez ssougnez

View GitHub Profile
View wp-dapper-01.sql
CREATE DATABASE [FormulaOne];
CREATE TABLE [Drivers] (
[Id] int IDENTITY(1, 1) NOT NULL,
[FirstName] varchar(32) NOT NULL,
[LastName] varchar(32) NOT NULL,
[ChampionTitleCount] int NOT NULL CONSTRAINT [DF_Drivers_ChampionTitleCount] DEFAULT 0,
CONSTRAINT [PK_DriverId] PRIMARY KEY ([Id])
)
View wp-dapper-02.cs
public class Driver
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int ChampionTitleCount { get; set; }
}
View wp-dapper-03.cs
var alonso = new Driver
{
FirstName = "Fernando",
LastName = "Alonso",
ChampionTitleCount = 2
};
string sql = $"INSERT INTO [Drivers] ([FirstName], [LastName], [ChampionTitleCount]) VALUES (@FirstName, @LastName, @ChampionTitleCount);";
await connection.ExecuteAsync(sql, alonso);
View wp-dapper-04.cs
var hamilton = new Driver
{
FirstName = "Lewis",
LastName = "Hamilton",
ChampionTitleCount = 4
};
string sql = $@"INSERT INTO [Drivers] ([FirstName], [LastName], [ChampionTitleCount]) VALUES (@FirstName, @LastName, @ChampionTitleCount);
SELECT CAST(SCOPE_IDENTITY() AS int);";
View wp-dapper-05.cs
string sql = $@"INSERT INTO [Drivers] ([FirstName], [LastName], [ChampionTitleCount]) VALUES (@FirstName, @LastName, @ChampionTitleCount);
SELECT CAST(SCOPE_IDENTITY() AS int);";
await connection.ExecuteAsync(sql, new
{
FirstName = "Sebastian",
LastName = "Vettel",
ChampionTitleCount = 4
});
View wp-dapper-06.cs
string sql = $@"SELECT [Id], [FirstName], [LastName], [ChampionTitleCount] FROM [Drivers];";
IEnumerable<Driver> drivers = (await connection.QueryAsync<Driver>(sql));
View wp-dapper-07.cs
string sql = $@"SELECT [Id] FROM [Drivers] WHERE [LastName] = 'Alonso';";
int driverId = (await connection.QueryAsync<int>(sql)).FirstOrDefault();
if (driverId != default)
{
sql = $"INSERT INTO [Tracks] ([Title], [BestLapTimeDriverId]) VALUES ('Belgium', @DriverId);";
await connection.ExecuteAsync(sql, new { DriverId = driverId });
}
View wp-dapper-08.cs
string sql = $@"SELECT [Tracks].[Id], [Tracks].[Title],
[Drivers].[Id], [Drivers].[FirstName], [Drivers].[LastName], [Drivers].[ChampionTitleCount]
FROM [Tracks]
INNER JOIN [Drivers]
ON [Drivers].[Id] = [Tracks].[BestLapTimeDriverId]
WHERE [Tracks].[Id] = 1;";
Track track = (await connection.QueryAsync<Track, Driver, Track>(sql, (t, d) =>
{
t.BestLapTimeDriver = d;
View wp-dapper-10.cs
string sql = $@"SELECT [Tracks].[Id], [Tracks].[Title],
[Drivers].[Id], [Drivers].[FirstName], [Drivers].[LastName], [Drivers].[ChampionTitleCount]
FROM [Tracks]
INNER JOIN [Drivers]
ON [Drivers].[Id] = [Tracks].[BeastLapTimeDriverId]
WHERE [Tracks].[Id] = 1;";
Track track = (await connection.QueryAsync<Track, Driver>(sql)).FirstOrDefault();
You can’t perform that action at this time.