Basic Select

• Query、QueryFirstOrDefault

  • Query: return an IEnumerable
  • QueryFirstOrDefault: return the first data

  using (var connection = new SqlConnection(connectionString)) {
    var sql = "SELECT * FROM Users WHERE Id = @Id"
  
    var user = connection.QueryFirstOrDefault<User>(sql, new { Id = 1 });
  
    var allUsers = connection.Query<User>("SELECt * FROM Users").ToList();
  }

Execute Sql Command

• Execute: exec INSERT、UPDATE、DELETE，would't return query result but return Rows Affected。

  var sql = "UPDATE Users SET Name = @Name WHERE Id = @Id";
  int rowsAffected = connection.Execute(sql, new { Name = "NewName", Id = 1 });

Bulk Operations

Dapper support IEnumerable to be param，Dapper will auto execute same sql command by param. It would be convenient when bulk update or insert。

var users = new [] {
  new { Id = 1, Name = "Alice" },
  new { Id = 2, Name = "Bob" }
};

var sql = "UPDATE Users SET Name = @Name WHERE Id = @Id";
connection.Execute(sql, users);

Multi-Mapping

Sometime we need to join multiple table, Dapper Query can split result to the difference object.

var sql = @"SELECT u.*, d.* FROM Users u 
              INNER JOIN Departments d ON u.DeptId = d.Id";

var users = connection.Query<User, Department, User>(
  sql,
  (user, dept) => {
    user.Department = dept;
    return user;
  },
  splitOn: "Id"
).ToList();

Multiple Results

if you want to exec multi query in one database session, you can use QueryMultiple.

var sql = "SELECT * FROM Users; SELECT * FROM Roles;";

using (var multi = connection.QueryMultiple(sql))
{
    var users = multi.Read<User>().ToList();
    var roles = multi.Read<Role>().ToList();
}

IN filter

var sql = "SELECT * FROM Users WHERE Id IN @Ids";
var users = connection.Query<User>(sql, new { Ids = new[] { 1, 2, 3, 4, 5 } });

Transactions

when you need to exec multi sql operate and require all success or all fail, use transactions.

using (var conn = new SqlConnection(_connString))
{
    conn.Open();
    using (var trans = conn.BeginTransaction())
    {
        try
        {
            conn.Execute("INSERT INTO Logs (Msg) VALUES (@Msg)", new { Msg = "Start" }, trans);
            conn.Execute("UPDATE Inventory SET Stock = Stock - 1 WHERE Id = 1", null, trans);
            
            trans.Commit();
        }
        catch
        {
            trans.Rollback();
            throw;
        }
    }
}

Call Stored Procedures

if you have stored procedure, just assign commandType.

var parameters = new DynamicParameters();
parameters.Add("@UserId", 1);
parameters.Add("@OutValue", dbType: DbType.Int32, direction: ParameterDirection.Output);

connection.Execute("GetUserDetails", parameters, commandType: CommandType.StoredProcedure);

int roleId = parameters.Get<int>("@OutValue");

Dynamic Parameters

if you need combine different sql filter, DynamicParameters can help you. It allow you dynamic add parameters.

var p = new DynamicParameters();
p.Add("@Name", "Alice");

if (searchAge.HasValue) 
{
  p.Add("@Age", searchAge.Value);
}

var sql = "SELECT * FROM Users WHERE Name = @Name" + (searchAge.HasValue ? " AND Age = @Age" : "");
var result = conn.Query<User>(sql, p);

Type Handler

Sometime db columns type would not match C# type, You can implement SqlMapper.TypeHandler.

public class JsonTypeHandler : SqlMapper.ITypeHandler
{
    public void SetValue(IDbDataParameter parameter, object value)
    {
        parameter.Value = JsonConvert.SerializeObject(value);
    }

    public object Parse(Type destinationType, object value)
    {
        return JsonConvert.DeserializeObject(value.ToString(), destinationType);
    }
}

// register
SqlMapper.AddTypeHandler(new JsonTypeHandler());

Pagination

In huge data table, you would never use SELECT *, you can use DynamicParameters implement high quality pagination.

var sql = @"SELECT * FROM Users 
            ORDER BY Id 
            OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
            SELECT COUNT(*) FROM Users;";

using (var multi = connection.QueryMultiple(sql, new { Offset = (page - 1) * pageSize, PageSize = pageSize }))
{
    var users = multi.Read<User>().ToList();
    var totalCount = multi.ReadFirst<int>();
}