Dapper

Dapper - a simple object mapper for .Net

And it is really fast!

What does dapper give us?

• Sql injection safe parameters
• Handles parameter list out of the box
• Maps SqlDataReader to C# types
• And did I say that it is fast
• Visible sql to easily test in SqlManagementStudio
• Use Sql Table-Valued parameters

Parameters

Parameters

string sql = @"
    INSERT INTO Customers (CustomerName) 
    Values (@CustomerName);";

using (var connection = new SqlConnection(cs))
{
	connection.Open();

	var affectedRows = connection.Execute(
            sql, 
            new {CustomerName = "Mark"}
        );

	Console.WriteLine(affectedRows);
}

Anonymous parameter class

Parameters

var sql = "Invoice_Insert";

using (var connection = My.ConnectionFactory())
{
	connection.Open();
	DynamicParameters parameter = new DynamicParameters();
	parameter.Add("@Code", "Many_Insert_0", DbType.String, ParameterDirection.Input);
	parameter.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

	connection.Execute(sql,
		parameter,
		commandType: CommandType.StoredProcedure);

	int rowCount = parameter.Get<int>("@RowCount");
}

DynamicParameters

Parameters

var sql = "SELECT * FROM Invoice WHERE Id IN @Ids;";

using (var connection = My.ConnectionFactory())
{
	connection.Open();

	var invoices = connection.Query<Invoice>(
        sql,
        new {Ids = new[] { 1, 2 }}
    ).ToList();
}

List

Parameters

public class JobCollectiveBargainRepository : IJobCollectiveBargainRepository
{
    private const string typeName = "COLLECTIVEBARGAIN_ARTICLE";
    private static readonly SqlMetaData[] ArticleRow = {
        new SqlMetaData("BisUniqueId", SqlDbType.BigInt),
        new SqlMetaData("PayAmount", SqlDbType.Decimal, 10,2),
        new SqlMetaData("PayPercent", SqlDbType.Decimal, 5,2),
    };

    public async Task UpdateArticles(List<Article> artList) {
        var parameters = new { articles = artList.Select(CreateRow)
                                                 .AsTableValuedParameter(typeName) };

        using (var conn = new SqlConnection(cs)) {
            await context.ExecuteAsync(
                    storeProcName, 
                    parameters, 
                    commandType: CommandType.StoredProcedure); 
        }
    }

    private static SqlDataRecord CreateRow(Article x)
    {
        var row = new SqlDataRecord(ArticleRow);
        row.SetInt64(0, x.BisUniqueId);
        row.SetDecimal(1, x.PayAmount);
        row.SetDecimal(2, x.PayPercent);
        return row;
    }
}

TABLE-VALUED

Returns

REsult

Strongly typed

REsult

public static string SqlGetRecordByUrlUniqueId = @"
SELECT r.[Id]
    ...
  FROM [dbo].[GflRecord] as r
  WHERE r.UrlUniqueId = @UrlUniqueId

SELECT si.[Id] AS Id
    ...
  FROM [dbo].[GflRecord] as r
       INNER JOIN [dbo].[SalaryInformation] as si on r.fkSalaryInformationId = si.Id
  WHERE r.UrlUniqueId = @UrlUniqueId

SELECT ci.[Id] AS Id
    ...
  FROM [dbo].[GflRecord] as r
       INNER JOIN [dbo].[ClientInformation] as ci on r.fkClientInformationId = ci.Id
  WHERE r.UrlUniqueId = @UrlUniqueId
";

using (var conn = new SqlConnection(_config.ConnectionString)) {
    var grid = await conn.QueryMultipleAsync(
                        SqlBuilder.SqlGetRecordByUrlUniqueId, 
                        new { UrlUniqueId = urlUniqueId });

    var record = (await grid.ReadAsync<GflRecordSql>()).SingleOrDefault();
    var salary = (await grid.ReadAsync<GflSalarySql>()).SingleOrDefault();
    var client = (await grid.ReadAsync<GflClientSql>()).SingleOrDefault();
    return record?.ToModel(salary, client);
}

Multi result

Multiple Resultsets

string sql = @"
SELECT * FROM Invoice WHERE InvoiceID = @InvoiceID; 
SELECT * FROM InvoiceItem WHERE InvoiceID = @InvoiceID;
";

using (var connection = My.ConnectionFactory())
{
    connection.Open();
    using (var multi = connection.QueryMultiple(sql, new {InvoiceID = 1}))
    {
        var invoice = multi.Read<Invoice>().First();
        var invoiceItems = multi.Read<InvoiceItem>().ToList();
    }
}

List

REsult

Multi mapping

Come talk to me

Multi type

Come talk to me