Dapper README

The project's README from github.com/DapperLib/Dapper. As one markdown file: README.md.

Dapper - a simple object mapper for .Net

Build

Release Notes

Located at https://github.com/DapperLib/Dapper/releases

Packages

Package NuGet Stable NuGet Pre-release Downloads
Dapper Dapper Dapper Dapper
Dapper.EntityFramework Dapper.EntityFramework Dapper.EntityFramework Dapper.EntityFramework
Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName Dapper.EntityFramework.StrongName
Dapper.Rainbow Dapper.Rainbow Dapper.Rainbow Dapper.Rainbow
Dapper.SqlBuilder Dapper.SqlBuilder Dapper.SqlBuilder Dapper.SqlBuilder
Dapper.StrongName Dapper.StrongName Dapper.StrongName Dapper.StrongName

Package Purposes:

Sponsors

Dapper was originally developed for and by Stack Overflow, but is F/OSS. Sponsorship is welcome and invited - see the sponsor link at the top of the page. A huge thanks to everyone (individuals or organisations) who have sponsored Dapper, but a massive thanks in particular to:

Dapper Plus logo

Features

Dapper is a NuGet library that you can add in to your project that will enhance your ADO.NET connections via extension methods on your DbConnection instance. This provides a simple and efficient API for invoking SQL, with support for both synchronous and asynchronous data access, and allows both buffered and non-buffered queries.

It provides multiple helpers, but the key APIs are:

// insert/update/delete etc
var count  = connection.Execute(sql [, args]);

// multi-row query
IEnumerable<T> rows = connection.Query<T>(sql [, args]);

// single-row query ({Single|First}[OrDefault])
T row = connection.QuerySingle<T>(sql [, args]);

where args can be (among other things):

Execute a query and map it to a list of typed objects

public class Dog
{
    public int? Age { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
}

var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

Assert.Equal(1,dog.Count());
Assert.Null(dog.First().Age);
Assert.Equal(guid, dog.First().Id);

Execute a query and map it to a list of dynamic objects

This method will execute SQL and return a dynamic list.

Example usage:

var rows = connection.Query("select 1 A, 2 B union all select 3, 4").AsList();

Assert.Equal(1, (int)rows[0].A);
Assert.Equal(2, (int)rows[0].B);
Assert.Equal(3, (int)rows[1].A);
Assert.Equal(4, (int)rows[1].B);

Execute a Command that returns no results

Example usage:

var count = connection.Execute(@"
  set nocount on
  create table #t(i int)
  set nocount off
  insert #t
  select @a a union all select @b
  set nocount on
  drop table #t", new {a=1, b=2 });
Assert.Equal(2, count);

Execute a Command multiple times

The same signature also allows you to conveniently and efficiently execute a command multiple times (for example to bulk-load data)

Example usage:

var count = connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  );
Assert.Equal(3, count); // 3 rows inserted: "1,1", "2,2" and "3,3"

Another example usage when you already have an existing collection:

var foos = new List<Foo>
{
    { new Foo { A = 1, B = 1 } }
    { new Foo { A = 2, B = 2 } }
    { new Foo { A = 3, B = 3 } }
};

var count = connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)", foos);
Assert.Equal(foos.Count, count);

This works for any parameter that implements IEnumerable<T> for some T.

Performance

A key feature of Dapper is performance. The following metrics show how long it takes to execute a SELECT statement against a DB (in various config, each labeled) and map the data returned to objects.

The benchmarks can be found in Dapper.Tests.Performance (contributions welcome!) and can be run via:

dotnet run --project .\benchmarks\Dapper.Tests.Performance\ -c Release -f net8.0 -- -f * --join

Output from the latest run is:

BenchmarkDotNet v0.13.7, Windows 10 (10.0.19045.3693/22H2/2022Update)
Intel Core i7-3630QM CPU 2.40GHz (Ivy Bridge), 1 CPU, 8 logical and 4 physical cores
.NET SDK 8.0.100
  [Host]   : .NET 8.0.0 (8.0.23.53103), X64 RyuJIT AVX
  ShortRun : .NET 8.0.0 (8.0.23.53103), X64 RyuJIT AVX
ORM Method Return Mean StdDev Error Gen0 Gen1 Gen2 Allocated
Dapper cache impact ExecuteParameters_Cache Void 96.75 us 0.668 us 1.010 us 0.6250 - - 2184 B
Dapper cache impact QueryFirstParameters_Cache Void 96.86 us 0.493 us 0.746 us 0.8750 - - 2824 B
Hand Coded SqlCommand Post 119.70 us 0.706 us 1.067 us 1.3750 1.0000 0.1250 7584 B
Hand Coded DataTable dynamic 126.64 us 1.239 us 1.873 us 3.0000 - - 9576 B
SqlMarshal SqlCommand Post 132.36 us 1.008 us 1.523 us 2.0000 1.0000 0.2500 11529 B
Dapper QueryFirstOrDefault Post 133.73 us 1.301 us 2.186 us 1.7500 1.5000 - 11608 B
Mighty Query dynamic 133.92 us 1.075 us 1.806 us 2.0000 1.7500 - 12710 B
LINQ to DB Query Post 134.24 us 1.068 us 1.614 us 1.7500 1.2500 - 10904 B
RepoDB ExecuteQuery Post 135.83 us 1.839 us 3.091 us 1.7500 1.5000 - 11649 B
Dapper 'Query (buffered)' Post 136.14 us 1.755 us 2.653 us 2.0000 1.5000 - 11888 B
Mighty Query Post 137.96 us 1.485 us 2.244 us 2.2500 1.2500 - 12201 B
Dapper QueryFirstOrDefault dynamic 139.04 us 1.507 us 2.279 us 3.5000 - - 11648 B
Mighty SingleFromQuery dynamic 139.74 us 2.521 us 3.811 us 2.0000 1.7500 - 12710 B
Dapper 'Query (buffered)' dynamic 140.13 us 1.382 us 2.090 us 2.0000 1.5000 - 11968 B
ServiceStack SingleById Post 140.76 us 1.147 us 2.192 us 2.5000 1.2500 0.2500 15248 B
Dapper 'Contrib Get' Post 141.09 us 1.394 us 2.108 us 2.0000 1.5000 - 12440 B
Mighty SingleFromQuery Post 141.17 us 1.941 us 2.935 us 1.7500 1.5000 - 12201 B
Massive 'Query (dynamic)' dynamic 142.01 us 4.957 us 7.494 us 2.0000 1.5000 - 12342 B
LINQ to DB 'First (Compiled)' Post 144.59 us 1.295 us 1.958 us 1.7500 1.5000 - 12128 B
RepoDB QueryField Post 148.31 us 1.742 us 2.633 us 2.0000 1.5000 0.5000 13938 B
Norm 'Read<> (tuples)' ValueTuple`8 148.58 us 2.172 us 3.283 us 2.0000 1.7500 - 12745 B
Norm 'Read<()> (named tuples)' ValueTuple`8 150.60 us 0.658 us 1.106 us 2.2500 2.0000 1.2500 14562 B
RepoDB Query Post 152.34 us 2.164 us 3.271 us 2.2500 1.5000 0.2500 14106 B
RepoDB QueryDynamic Post 154.15 us 4.108 us 6.210 us 2.2500 1.7500 0.5000 13930 B
RepoDB QueryWhere Post 155.90 us 1.953 us 3.282 us 2.5000 0.5000 - 14858 B
Dapper cache impact ExecuteNoParameters_NoCache Void 162.35 us 1.584 us 2.394 us - - - 760 B
Dapper cache impact ExecuteNoParameters_Cache Void 162.42 us 2.740 us 4.142 us - - - 760 B
Dapper cache impact QueryFirstNoParameters_Cache Void 164.35 us 1.206 us 1.824 us 0.2500 - - 1520 B
DevExpress.XPO FindObject Post 165.87 us 1.012 us 1.934 us 8.5000 - - 28099 B
Dapper cache impact QueryFirstNoParameters_NoCache Void 173.87 us 1.178 us 1.781 us 0.5000 - - 1576 B
LINQ to DB First Post 175.21 us 2.292 us 3.851 us 2.0000 0.5000 - 14041 B
EF 6 SqlQuery Post 175.36 us 2.259 us 3.415 us 4.0000 0.7500 - 24209 B
Norm 'Read<> (class)' Post 186.37 us 1.305 us 2.496 us 3.0000 0.5000 - 17579 B
DevExpress.XPO GetObjectByKey Post 186.78 us 3.407 us 5.151 us 4.5000 1.0000 - 30114 B
Dapper 'Query (unbuffered)' dynamic 194.62 us 1.335 us 2.019 us 1.7500 1.5000 - 12048 B
Dapper 'Query (unbuffered)' Post 195.01 us 0.888 us 1.343 us 2.0000 1.5000 - 12008 B
DevExpress.XPO Query Post 199.46 us 5.500 us 9.243 us 10.0000 - - 32083 B
Belgrade FirstOrDefault Task`1 228.70 us 2.181 us 3.665 us 4.5000 0.5000 - 20555 B
EF Core 'First (Compiled)' Post 265.45 us 17.745 us 26.828 us 2.0000 - - 7521 B
NHibernate Get Post 276.02 us 8.029 us 12.139 us 6.5000 1.0000 - 29885 B
NHibernate HQL Post 277.74 us 13.032 us 19.703 us 8.0000 1.0000 - 31886 B
NHibernate Criteria Post 300.22 us 14.908 us 28.504 us 13.0000 1.0000 - 57562 B
EF 6 First Post 310.55 us 27.254 us 45.799 us 13.0000 - - 43309 B
EF Core First Post 317.12 us 1.354 us 2.046 us 3.5000 - - 11306 B
EF Core SqlQuery Post 322.34 us 23.990 us 40.314 us 5.0000 - - 18195 B
NHibernate SQL Post 325.54 us 3.937 us 7.527 us 22.0000 1.0000 - 80007 B
EF 6 'First (No Tracking)' Post 331.14 us 27.760 us 46.649 us 12.0000 1.0000 - 50237 B
EF Core 'First (No Tracking)' Post 337.82 us 27.814 us 46.740 us 3.0000 1.0000 - 17986 B
NHibernate LINQ Post 604.74 us 5.549 us 10.610 us 10.0000 - - 46061 B
Dapper cache impact ExecuteParameters_NoCache Void 623.42 us 3.978 us 6.684 us 3.0000 2.0000 - 10001 B
Dapper cache impact QueryFirstParameters_NoCache Void 630.77 us 3.027 us 4.576 us 3.0000 2.0000 - 10640 B

Feel free to submit patches that include other ORMs - when running benchmarks, be sure to compile in Release and not attach a debugger (Ctrl+F5).

Alternatively, you might prefer Frans Bouma's RawDataAccessBencher test suite or OrmBenchmark.

Parameterized queries

Parameters are usually passed in as anonymous classes. This allows you to name your parameters easily and gives you the ability to simply cut-and-paste SQL snippets and run them in your db platform's Query analyzer.

new {A = 1, B = "b"} // A will be mapped to the param @A, B to the param @B

Parameters can also be built up dynamically using the DynamicParameters class. This allows for building a dynamic SQL statement while still using parameters for safety and performance.

    var sqlPredicates = new List<string>();
    var queryParams = new DynamicParameters();
    if (boolExpression)
    {
        sqlPredicates.Add("column1 = @param1");
        queryParams.Add("param1", dynamicValue1, System.Data.DbType.Guid);
    } else {
        sqlPredicates.Add("column2 = @param2");
        queryParams.Add("param2", dynamicValue2, System.Data.DbType.String);
    }

DynamicParameters also supports copying multiple parameters from existing objects of different types.

    var queryParams = new DynamicParameters(objectOfType1);
    queryParams.AddDynamicParams(objectOfType2);

When an object that implements the IDynamicParameters interface passed into Execute or Query functions, parameter values will be extracted via this interface. Obviously, the most likely object class to use for this purpose would be the built-in DynamicParameters class.

List Support

Dapper allows you to pass in IEnumerable<int> and will automatically parameterize your query.

For example:

connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 } });

Will be translated to:

select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

Literal replacements

Dapper supports literal replacements for bool and numeric types.

connection.Query("select * from User where UserTypeId = {=Admin}", new { UserTypeId.Admin });

The literal replacement is not sent as a parameter; this allows better plans and filtered index usage but should usually be used sparingly and after testing. This feature is particularly useful when the value being injected is actually a fixed value (for example, a fixed "category id", "status code" or "region" that is specific to the query). For live data where you are considering literals, you might also want to consider and test provider-specific query hints like OPTIMIZE FOR UNKNOWN with regular parameters.

Buffered vs Unbuffered readers

Dapper's default behavior is to execute your SQL and buffer the entire reader on return. This is ideal in most cases as it minimizes shared locks in the db and cuts down on db network time.

However when executing huge queries you may need to minimize memory footprint and only load objects as needed. To do so pass, buffered: false into the Query method.

Multi Mapping

Dapper allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.

Example:

Consider 2 classes: Post and User

class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public User Owner { get; set; }
}

class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Now let us say that we want to map a query that joins both the posts and the users table. Until now if we needed to combine the result of 2 queries, we'd need a new object to express it but it makes more sense in this case to put the User object inside the Post object.

This is the use case for multi mapping. You tell dapper that the query returns a Post and a User object and then give it a function describing what you want to do with each of the rows containing both a Post and a User object. In our case, we want to take the user object and put it inside the post object. So we write the function:

(post, user) => { post.Owner = user; return post; }

The 3 type arguments to the Query method specify what objects dapper should use to deserialize the row and what is going to be returned. We're going to interpret both rows as a combination of Post and User and we're returning back a Post object. Hence the type declaration becomes

<Post, User, Post>

Everything put together, looks like this:

var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

Assert.Equal("Sams Post1", post.Content);
Assert.Equal(1, post.Id);
Assert.Equal("Sam", post.Owner.Name);
Assert.Equal(99, post.Owner.Id);

Dapper is able to split the returned row by making an assumption that your Id columns are named Id or id. If your primary key is different or you would like to split the row at a point other than Id, use the optional splitOn parameter.

Multiple Results

Dapper allows you to process multiple result grids in a single query.

Example:

var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
}

Stored Procedures

Dapper fully supports stored procs:

var user = cnn.Query<User>("spGetUser", new {Id = 1},
        commandType: CommandType.StoredProcedure).SingleOrDefault();

If you want something more fancy, you can do:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);

int b = p.Get<int>("@b");
int c = p.Get<int>("@c");

Ansi Strings and varchar

Dapper supports varchar params, if you are executing a where clause on a varchar column using a param be sure to pass it in this way:

Query<Thing>("select * from Thing where Name = @Name", new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true }});

On SQL Server it is crucial to use the unicode when querying unicode and ANSI when querying non unicode.

Type Switching Per Row

Usually you'll want to treat all rows from a given table as the same data type. However, there are some circumstances where it's useful to be able to parse different rows as different data types. This is where IDataReader.GetRowParser comes in handy.

Imagine you have a database table named "Shapes" with the columns: Id, Type, and Data, and you want to parse its rows into Circle, Square, or Triangle objects based on the value of the Type column.

var shapes = new List<IShape>();
using (var reader = connection.ExecuteReader("select * from Shapes"))
{
    // Generate a row parser for each type you expect.
    // The generic type <IShape> is what the parser will return.
    // The argument (typeof(*)) is the concrete type to parse.
    var circleParser = reader.GetRowParser<IShape>(typeof(Circle));
    var squareParser = reader.GetRowParser<IShape>(typeof(Square));
    var triangleParser = reader.GetRowParser<IShape>(typeof(Triangle));

    var typeColumnIndex = reader.GetOrdinal("Type");

    while (reader.Read())
    {
        IShape shape;
        var type = (ShapeType)reader.GetInt32(typeColumnIndex);
        switch (type)
        {
            case ShapeType.Circle:
            	shape = circleParser(reader);
            	break;
            case ShapeType.Square:
            	shape = squareParser(reader);
            	break;
            case ShapeType.Triangle:
            	shape = triangleParser(reader);
            	break;
            default:
            	throw new NotImplementedException();
        }

      	shapes.Add(shape);
    }
}

User Defined Variables in MySQL/MariaDB

In order to use Non-parameter SQL variables with MySql Connector, you have to add the following option to your connection string:

Allow User Variables=True

Make sure you don't provide Dapper with a property to map.

Limitations and caveats

Dapper caches information about every query it runs, this allows it to materialize objects quickly and process parameters quickly. The current implementation caches this information in a ConcurrentDictionary object. Statements that are only used once are routinely flushed from this cache. Still, if you are generating SQL strings on the fly without using parameters it is possible you may hit memory issues.

Dapper's simplicity means that many features that ORMs ship with are stripped out. It worries about the 95% scenario, and gives you the tools you need most of the time. It doesn't attempt to solve every problem.

Will Dapper work with my DB provider?

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MariaDB, MySQL, PostgreSQL and SQL Server.

Do you have a comprehensive list of examples?

Dapper has a comprehensive test suite in the test project.

Who is using this?

Dapper is in production use at Stack Overflow.

Dapper (DapperLib/Dapper)

Searching Database…

18.4K ·GitHub
Dapper
这是什么？Dapper 是一个简单的 .NET 对象映射器——一个 NuGet 库，它为你的 ADO.NET 连接添加扩展方法，让你执行 SQL 后直接把查询结果映射成对象。它最初是由 Stack Overflow 为自己开发并开源的。
它是像 Entity Framework 那样的完整 ORM，还是更轻量的东西？轻量得多——它是一个微 ORM（micro-ORM）：SQL 由你自己写，Dapper 负责处理参数和把行映射成对象。如果你想要自动生成的 CRUD，配套的 Dapper.Rainbow 包在它之上提供了辅助方法。
怎么把它加到我的项目里？从 NuGet 安装 Dapper 包即可，不需要配置其他任何东西。它的方法会以扩展方法的形式出现在你已创建的 DbConnection 对象上，所以现有的数据访问代码可以逐步引入它。
它可以搭配哪些数据库使用？任何能通过 ADO.NET 访问的数据库都可以：Dapper 只是对你的 DbConnection 做扩展，数据库提供程序（SQL Server、PostgreSQL、MySQL、SQLite 等）由你自己提供，它本身不绑定任何数据库。
有什么需要留意的坑吗？SQL 要自己写——Dapper 只负责映射行，不会帮你生成查询。针对布尔值和数字的字面量替换会绕过参数化，测试后也要尽量少用；另外多映射在拆分行时，默认假设你的主键列名为 Id 或 id。
它能在哪些平台上运行——是不是因为是 .NET 就只能跑在 Windows 上？只要 .NET 运行时能跑的地方它都能跑：Dapper 只是一个普通的库，不是独立应用，而且仓库的标签里列出了 Windows、macOS 和 Linux。
一次典型的调用是什么样子的？大致像 Query (sql, new { id = 7 }) 这样，返回一个 List，如果省略类型则返回动态行。Execute 用来执行命令，同一个调用也可以遍历 IEnumerable 来批量加载行。参数可以是匿名对象、字典或 DynamicParameters，查询默认会缓冲结果（buffered: false 则以流式处理超大数据集），而且每个 API 都有异步版本。
和手写的 ADO.NET 或更大的 ORM 相比，它有多快？这正是它最大的卖点。README 的基准测试中，同一条 SELECT 上，Dapper 的强类型查询约需 134 微秒，手写 ADO.NET 约 120 微秒，而 EF Core First 约 317 微秒，NHibernate LINQ 约 605 微秒——Dapper 在所有被测试的 ORM 中排第一。
一次查询能填充嵌套对象吗，比如 Post 连同它的作者一起？可以——多映射（multi-mapping）通过一个组合函数把一条连接查询的行映射到多个对象上（比如 Post 里包含它的 User）。Dapper 在对象之间拆分行时默认假设主键列名为 Id 或 id，不过你可以自行覆盖这个设置。
除了核心包之外，Dapper 家族还有哪些东西？Dapper.EntityFramework（为 Entity Framework 提供的扩展处理器）、Dapper.Rainbow（基于 Dapper 构建、带 CRUD 辅助方法的微 ORM）、Dapper.SqlBuilder（动态且可组合地拼装 SQL 查询），以及核心库的强签名（StrongName）版本。
What are similar projects?efcore, orm, server, diesel, sqlalchemy, mysql, nopCommerce, sequelize
Which topics is it in?query-builder, sql-server, integration, orm, async, dotnet, c#, sql, benchmark, developers, backend, database
Creator
DapperLib
Size
48.8 MB
Stars
18.4K
Updated
CommitSep 23Release2.1.89Sep 23
Age
15y
Commits · 90 Days