ssougnez

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])
)

ssougnez

public class Driver
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public int ChampionTitleCount { get; set; }
}

ssougnez

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);

ssougnez

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);";

ssougnez

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
});

ssougnez

string sql = $@"SELECT [Id], [FirstName], [LastName], [ChampionTitleCount] FROM [Drivers];";

IEnumerable<Driver> drivers = (await connection.QueryAsync<Driver>(sql));

ssougnez

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 });
}

ssougnez

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;

ssougnez

using Dapper.Mapper;

ssougnez

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();