C# Using Dapper as your SQL framework in .NET Core

Dapper is a easy to use object mapper for .NET and .NET Core, an it can be used a variety of ways. I use Dapper instead of Entity Framework because it makes my code less complex.

BASICS OF DAPPER: THE OBJECT MAPPER

Basically, Dapper is an object mapper. This means that Dapper will map SQL rows to C# model classes 1-1. So if you wish to select data from an SQL table, you create a class containing the exact same fields as the SQL table:

Contact Form Table

So for that contact form table above, I can create a corresponding ContactForm model class:

using System;
using Dapper.Contrib.Extensions;

namespace MyCode
{
  [Table("dbo.ContactForms")]
  public class ContactForm
  {
    [Key]
    public int Id { get; set; }

    public DateTime Created { get; set; }
	public string Name { get; set; }
	public string Phone { get; set; }
	public string Email { get; set; }
	public int? ZipCode { get; set; }
	public string Comment { get; set; }
	public string IpAddress { get; set; }
	public string UserAgent { get; set; }
  }
}

By including the Dapper.Contrib.Extensions, I can mark the table key in my code, and the table itself. Nullable fields like the zipcode are also nullable in my class.

SIMPLE SQL SELECT WITH DAPPER

Now with the mapping in place, selecting and returning a class is super easy:

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Dapper.Contrib.Extensions;

public class ContactFormRepository
{
  public IEnumerable<ContactForm> Get()
  {
    using var connection = new SqlConnection("some_sql_connection_string");
    return connection.Query<ContactForm>("select * from ContactForms").ToList();
  }
}

Dapper will map the ContactForms table to my ContactForm model class.

Selecting with parameters are equally easy, presented here in 2 different forms; one method returning only one row, another method returning all matching the parameter:

public ContactForm Get(int id)
{
    using var connection = new SqlConnection("some_sql_connection_string");
    return connection.QuerySingleOrDefault<ContactForm>("select * from ContactForms where id = @Id", 
      new { Id = id } 
    );
}

public IEnumerable<ContactForm> Get(string email)
{
    using var connection = new SqlConnection("some_sql_connection_string");
    return connection.Query<ContactForm>("select * from ContactForms where email = @Email", 
      new { Email = email } 
    ).ToList();
}

INSERT STATEMENT WITH DAPPER:

With inserting you decide if you wish to use your model class (great for exact inserts) or if you wish to use a dynamic class. The latter is great when you have fields that are autogenerated by the SQL server like auto-incrementing keys or dates that is set to GETDATE():

public void Insert(ContactForm contactForm)
{
    using var connection = new SqlConnection("some_sql_connection_string");
    connection.Insert(contactForm);
}

public void Insert(string name, string email)
{
    using var connection = new SqlConnection("some_sql_connection_string");
    connection.Execute("insert into ContactForms (name, email) values (@Name, @Email)", new { Name = name, Email = email });
}

USING STORED PROCEDURES:

This is also easy, just give the name of the stored procedure. In this example I will also use the DynamicParameters just to be fancy:

public IEnumerable<ContactForm> Get(string email)
{
    using var connection = new SqlConnection("some_sql_connection_string");
    var parameters = new DynamicParameters();
    parameters.Add("@Email", email);
    return connection.Query<ContactForm>("Stored_Procedure_Name", parameters, commandType: CommandType.StoredProcedure).ToList();
}

The same goes with insert using a stored procedure:

public void Insert(string name, string email)
{
    using var connection = new SqlConnection("some_sql_connection_string");
    var parameters = new DynamicParameters();
    parameters.Add("@Name", name);
    parameters.Add("@Email", email);
    connection.Execute("Stored_Procedure_Name", parameters, commandType: CommandType.StoredProcedure);
}

That’s basically it. Very easy to use.

MORE TO READ:

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in .net, .NET Core, c#, General .NET and tagged , , . Bookmark the permalink.

1 Response to C# Using Dapper as your SQL framework in .NET Core

  1. Pingback: Sitecore read from Custom Database | Brian Pedersen's Sitecore and .NET Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.