### Index # Dapper - a simple object mapper for .NET ## Overview A brief guide is available [on github](https://github.com/DapperLib/Dapper/blob/main/Readme.md) Questions on Stack Overflow should be tagged [`dapper`](https://stackoverflow.com/questions/tagged/dapper) ## Installation From NuGet: Install-Package Dapper or Install-Package Dapper.StrongName Note: to get the latest pre-release build, add ` -Pre` to the end of the command. ## Release Notes **RELEASE NOTE TRACKING HAS MOVED TO GITHUB** See: https://github.com/DapperLib/Dapper/releases Archive only (no new entries): ### 2.1.11 (note: new PRs will not be merged until they add release note wording here) - infer command text without any whitespace as stored-procedure (#1975 via @mgravell) - add global `SupportLegacyParameterTokens` setting to enable or disable single-character parameter tokens (#1974 via @Giorgi) - revert `$` addition for legacy parameter tokens (#1979 via @mgravell) - change NRT annotation on `GetConstructorParameter` (#1980 via @mgravell, fixes #1969) ### 2.1.4 - add untyped `GridReader.ReadUnbufferedAsync` API (#1958 via @mgravell) - tweak NRT annotations on type-handler API (#1960 via @mgravell, fixes #1959) ### 2.1.1 - add NRT annotations (#1928 via @mgravell) - extend `GridReader` API to allow it to be subclassed by external consumers (#1928 via @mgravell) - support `$` as a parameter prefix (#1952 via @Giorgi) - add public API tracking (#1948 via @mgravell) ### 2.0.151 - add global `FetchSize` setting for use with Oracle (#1946 via mgravell, fixes #1945) (also add some missing logic in `Settings.Reset()`) - add underscore handling with constructors (#1786 via @jo-goro, fixes #818; also #1947 via mgravell) ### 2.0.143 - add missing non-generic `AsyncEnumerable QueryUnbufferedAsync(...)` API (#1925 via mgravell, fixes #1922) - formally mark all `struct` types as `readonly` (#1925 via mgravell) - reinstate fallback support for `IDataReader`, and implement missing `DbDataReader` async APIs (#1913 via mgravell) ### 2.0.138 - (#1910 via mgravell, fix #1907, #1263) - add support for `SqlDecimal` and other types that need to be accessed via `DbDataReader.GetFieldValue` - add an overload of `AddTypeMap` that supports `DbDataReader.GetFieldValue` for additional types - acknowledge that in reality we only support `DbDataReader`; this has been true (via `DbConnection`) for `async` forever - (#1912 via mgravell) - add missing `AsyncEnumerable QueryUnbufferedAsync(...)` and `GridReader.ReadUnbufferedAsync(...)` APIs (.NET 5 and later) - implement `IAsyncDisposable` on `GridReader` (.NET 5 and later) ### 2.0.123 - Parameters can now be re-used on subsequent commands (#952 via jamescrowley) - Array query support (`.Query`) on supported platforms (e.g. Postgres) (#1598 via DarkWanderer) - `SqlMapper.HasTypeHandler` is made public for consumers (#1405 via brendangooden) - Improves multi-mapping error message when a specified column in splitOn can't be found (#1664 via NickCraver) - Improves `DbString.ToString()` (#1665 via NickCraver) - `DbType` for date/time types is no longer explicitly specified (resolves `Npgsql` v6 issue) - add `Settings.UseIncrementalPseudoPositionalParameterNames`, to support "snowflake" parameter naming conventions ### 2.0.90 - logo added; license updated to mention logo usage (via mgravell) - moved to DapperLib org; links updated (#1656) - RepoDb benchmark added (#1626 via stevedesmond-ca) - excise unrelated Soma tests (#1642 via kant2002) - SqlMarshl benchmark added (#1646 via kant2002) - documentation fixes (#1615 via Rollerss, #1604 via GitHubPang) ### 2.0.78 - fix `DynamicParameters` loop bug - wrong index (#1443 via DamirAinullin) - fix nullable tuple handling (#1400 via JulianRooze) - support update set in `SqlBuilder` (#1404 via Wei) - initialize collections with counts when possible (#1449 via DamirAinullin) - general code cleanup (#1452, #1457, #1458, #1459 all via DamirAinullin) - C# 9 and .NET 5 preparation/cleanup (#1572 via mgravell) - GitHub action, Docker, AppVeyor work (build/test) work (#1563 via Tyrrrz, #1559 via craver, #1450 via craver) - Test project rationalization (#1556 via craver) - ClickHouse detection (#1462 via DarkWanderer) - Switched to "main" branch (via craver) - documentation fixed (#1596 via royal, #1560 via wswind, #1558 via paul42, #1507 via imba-tjd, #1508 via dogac00, 899c9feb via BlackjacketMack, 0b17133 via BlackjacketMack, 9b6c8c7d via bryancrosby, #1202 via craver) - MightyOrm benchmark added (455b3f3b via cdonnellytx) - EF6 performance test updates (#1361 via AlexBagnolini) ### 2.0.35 - build tooling: enable "deterministic builds" and enable SDK roll-foward - fix culture related formatting/parsing issue with Sqlite (#1363 via sebastienros) - documentation fixes (#1357 via jawn) - add tests for `SqlBuilder` (#1369 via shps951023) ### 2.0.30 - upstream library updates; project (build) cleanup - reinstated net461 build target - add Dapper.ProviderTools library (to help with System vs Microsoft SqlClient migration, etc) - fix double dictionary lookup (#1339 via DamirAinullin) - fix bug with dynamic parameters accessing the wrong member (#1334 via DamirAinullin) - fix explicit-key issue with `DeleteAsync` (#1309 via james-hester-ah) - fix for `char` on Postgres (#1326 via jjonescz) - documentation fixes (#1340 via jawn) - test and benchmark fixes (#1337 via DamirAinullin, #1206 via yesmey, #1331 via andresrsanchez, #1335 via DamirAinullin) ### 2.0.4 Primary changes: - remove the System.Data.SqlClient dependency, allowing consumers to use System.Data.SqlClient or Microsoft.Data.SqlClient (or neither, or both) as they choose - this means that some users may need to *re-add* one of the above as a `` for their project to build, if they were previously relying on Dapper to provide System.Data.SqlClient - the `AsTableValuedParameter(this IEnumerable)` extension method is now `AsTableValuedParameter(this IEnumerable) where T : IDataRecord`; this is a breaking change but should be code-compatible and just requires a rebuild - unify the target platform at NetStandard2.0 (and .NET Framework 4.6.2 for the EF DB geometry/geography types) - fix bug with `Identity` not enforcing type identity of multi-mapped types Other changes merged: - fix #1242, #1280, #1282 - fix value-tuple mapping - fix #1295 - add `ExecuteReaderAsync` overload to expose `DbDataReader` - fix #569 - handing of `IN` and similar clauses in some scenarios - fix #1256 - make `Dispose()` polymorphic in "rainbow" - fix #1257 - make the `.Connection` available in "rainbow" ### 1.60.6 - improve performance of descriptor API ### 1.60.5 - add descriptor API to `DapperRow` (enables UI binding with non-generic `Query()` API) ### 1.60.1 - Fix [#1196](https://github.com/DapperLib/Dapper/issues/1196) - versioning fix only ([#1198](https://github.com/DapperLib/Dapper/pull/1198)) - assembly version is now locked at 1.60.0 to resolve some mismatch issues with .NET Core assembly loading/binding. ### 1.50.7 - Fix [#1190](https://github.com/DapperLib/Dapper/issues/1190) - incorrect unmanaged pointer when processing parameters that are a boxed struct (rare error relating to GC) - Fix [#1111](https://github.com/DapperLib/Dapper/issues/1111) - make `SqlMapper.Parse` consistent with `QueryImpl` - Fix #111- - improve error message for invalid literal types - Fix [#1149](https://github.com/DapperLib/Dapper/pull/1149) - improve error messages in "contrib" - Improved detection of empty table-valued-parameters ### 1.50.5 - Fixes empty result set hanging with `QueryAsync` - `DapperRow` now implements `IReadOnlyDictionary` - Improved error messages for `Async` when the provided `IDbConnection` is not a `DbConnection` - Contrib: `GetAll` now handles nullable types ### 1.50.4 - Added back missing .NET Standard functionality (restored in `netstandard2.0`) - Bumped `SqlClient` dependency to 4.4.0 (to help propagate the newer client) ### 1.50.2 - Fix issue [#569](https://github.com/DapperLib/Dapper/issues/569) (`in` expansions using ODBC pseudo-positional arguments) ### 1.50.1 - Change to how `string_split` is used for `InListStringSplitCount` ### 1.50.0 - No changes; stable release ### 1.50.0-rc3 - Updated for .Net Core RTM package dependencies ### 1.50.0-rc2b - New `InListStringSplitCount` global setting; if set (non-negative), `in @foo` expansions (of at least the specified size) of primitive types (`int`, `tinyint`, `smallint`, `bigint`) are implemented via the SQL Server 2016 (compat level 130) `STRING_SPLIT` function - Fix for incorrect conversions in `GridReader` ([#254](https://github.com/DapperLib/Dapper/issues/254)) ### 1.50.0-rc2 / 1.50.0-rc2a - Packaging for .NET Core rc2 ### 1.50-beta9 - Fix for `PadListExpansions` to work correctly with `not in` scenarios; now uses last non-null value instead of `null`; if none available, don't pad - Fix problems with single-result/single-row not being supported by all providers (basically: sqlite, [#466](https://github.com/DapperLib/Dapper/issues/466)) - Fix problems with enums - nulls ([#467](https://github.com/DapperLib/Dapper/issues/467)) and primitive values ([#468](https://github.com/DapperLib/Dapper/issues/468)) - Add support for C# 6 get-only properties ([#473](https://github.com/DapperLib/Dapper/issues/473)) - Add support for various xml types ([#427](https://github.com/DapperLib/Dapper/issues/427)) ### 1.50-beta8 - Addition of `GetRowParser` extension method on `IDataReader` API - allows manual construction of discriminated unions, etc - Addition of `Settings.PadListExpansions` - reduces query-plan saturation by padding list expansions with `null` values (opt-in, because on some DB configurations this could change the meaning) *(note: bad choice of `null` revised in 1.50-beta9)* - Addition of `Settings.ApplyNullValues` - assigns (rather than ignores) `null` values when possible - Fix for [#461](https://github.com/DapperLib/Dapper/issues/461) - ensure type-handlers work for constructor-based initialization - Fix for [#455](https://github.com/DapperLib/Dapper/issues/455) - make the `LookupDbType` method available again ### 1.50-beta7 - Addition of `GetRowParser(Type)` (and refactor the backing store for readers to suit) - Column hash should consider type, not just name ### 1.50-beta6 - Fix for issue [#424](https://github.com/DapperLib/Dapper/issues/424) - defensive `SqlDataRecord` handling ### 1.50-beta5 - Add "single", "first", "single or default" to complement the "first or default" options from 1.50-beta4 - Use single-row/single-result when possible - Fix for proxy-generator (issue #361) ### 1.50-beta4 - Add `QueryFirstOrDefault` / `ReadFirstOrDefault` methods that optimize the single-row scenario - Remove some legacy `dynamic` usage from the async API - Make `DynamicTypeMap` public again (error during core-clr migration) - Use `Hashtable` again on core-clr ### 1.50-beta3 - Core CLR support: add explicit `dnx451` support in addition to `dotnet5.4` (aka `netstandard1.4`) ### 1.50-beta2 - Core CLR now targets rc1 / 23516 - Various Core CLR fixes - Code cleanup and C# 6 usage (assorted) ### 1.50-beta1 - Split `SqlMapper.cs` as it was becoming too unmaintainable; NuGet is now the only supported deployment channel - Remove down-level C# requirements, as "drop in the file" is no longer the expected usage - `SqlMapper.Settings` added; provides high-level global configuration; initially `CommandTimeout` (@Irrational86) - improve error message if an array is used as a parameter in an invalid context - Add `Type[]` support for `GridReader.Read` scenarios (@NikolayGlynchak) - Support for custom type-maps in collection parameters (@gjsduarte) - Fix incorrect cast in `QueryAsync` (@phnx47, [#346](https://github.com/DapperLib/Dapper/issues/346)) - Fix incorrect null handling re `UdtTypeName` (@perliedman) - Support for `SqlDataRecord` (@sqmgh) - Allow `DbString` default for `IsAnsi` to be specified (@kppullin) - provide `TypeMapProvider` with lazy func-based initialization (@garyhuntddn) - Core-clr updated to beta-8 and various cleanups/fixes - Built using core-clr build tools ### 1.42 - Fix bug with dynamic parameters where `.Get` is called before the command is executed ### 1.41-beta5 - Core-clr packaging build and workarounds - Fix bug with literal `{=val}` boolean replacements ### 1.41-beta4 - Core-clr packaging build - Improve mapping to enum members (@BrianJolly) ### 1.41-beta - Core-clr packaging build ### 1.41-alpha - Introduces dnx (core-clr) experimental changes - Adds `SqlBuilder` project - Improve error message when incorrectly accessing parameter values ### 1.40 - Workaround for broken `GetValues()` on Mono; add `AsList()` ### 1.39 - Fix case on SQL CLR types; grid-reader should respect no-cache flags; make parameter inclusion case-insensitive ### 1.38 - Specify constructor explicitly; allow value-type parameters (albeit: boxed) ### 1.37 - Reuse StringBuilder instances when possible (list parameters in particular) ### 1.36 - Fix Issue [#192](https://github.com/DapperLib/Dapper/issues/192) (expanded parameter naming glitch) and Issue [#178](https://github.com/DapperLib/Dapper/issues/178) (execute reader now wraps the command/reader pair, to extend the command lifetime; note that the underlying command/reader are available by casting to `IWrappedDataReader`) ### 1.35 - Fix Issue [#151](https://github.com/DapperLib/Dapper/issues/151) (Execute should work with `ExpandoObject` etc); Fix Issue #182 (better support for db-type when using `object` values); - Output expressions / callbacks in dynamic args (via Derek); arbitrary number of types in multi-mapping (via James Holwell); - Fix `DbString`/Oracle bug (via Mauro Cerutti); new support for **named positional arguments** ### 1.34 - Support for `SqlHierarchyId` (core) ### 1.33 - Support for `SqlGeometry` (core) and `DbGeometry` (EF) ### 1.32 - Support for `SqlGeography` in core library ### 1.31 - Fix issue with error message when there is a column/type mismatch ### 1.30 - Better async cancellation ### 1.29 - Make underscore name matching optional (opt-in) - this can be a breaking change for some people ### 1.28 - Much better numeric type conversion; fix for large oracle strings; map `Foo_Bar` to `FooBar` (etc); `ExecuteScalar` added; stability fixes ### 1.27 - Fixes for type-handler parse; ensure type-handlers get last dibs on configuring parameters ### 1.26 - New type handler API for extension support ### 1.25 - Command recycling and disposing during pipelined async multi-exec; enable pipeline (via sync-over-async) for sync API" --- ### Dapperplus # Dapper and Dapper Plus Dapper is the micro-ORM developed initially by Stack Overflow and now maintained independently, that offers simple, high performance access to the ADO.NET API. Dapper Plus is a separate tool by ZZZ Projects, which builds on the path set by Dapper, offering features like bulk operations, and a range of [documentation for Dapper](https://www.learndapper.com/). From 2024, Dapper Plus is now a major sponsor of Dapper, helping to secure ongoing quality development and support of the Dapper platform. This sponsorship does not impact the ownership, license, or any other particulars of how Dapper operates. The core Dapper libraries continue to be freely available and fully open source. Dapper Plus logo --- ### Readme # Dapper Dapper is a simple micro-ORM used to simplify working with ADO.NET; if you like SQL but dislike the boilerplate of ADO.NET: Dapper is for you! As a simple example: ``` c# string region = ... var customers = connection.Query( "select * from Customers where Region = @region", // SQL new { region } // parameters ).AsList(); ``` But all the execute/single-row/scalar/async/etc functionality you would expect: is there as extension methods on your `DbConnection`. See [GitHub](https://github.com/DapperLib/Dapper) for more information and examples. 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](https://dapper-plus.net/) is a major sponsor and is proud to contribute to the development of Dapper ([read more](https://dapperlib.github.io/Dapper/dapperplus)) - [AWS](https://github.com/aws) who sponsored Dapper from Oct 2023 via the [.NET on AWS Open Source Software Fund](https://github.com/aws/dotnet-foss) [](https://dapper-plus.net/) --- ### Readme Dapper - a simple object mapper for .Net ======================================== [](https://ci.appveyor.com/project/StackExchange/dapper) Release Notes ------------- Located at [https://github.com/DapperLib/Dapper/releases](https://github.com/DapperLib/Dapper/releases/) Packages -------- MyGet Pre-release feed: https://www.myget.org/gallery/dapper | Package | NuGet Stable | NuGet Pre-release | Downloads | MyGet | | ------- | ------------ | ----------------- | --------- | ----- | | [Dapper](https://www.nuget.org/packages/Dapper/) | [](https://www.nuget.org/packages/Dapper/) | [](https://www.nuget.org/packages/Dapper/) | [](https://www.nuget.org/packages/Dapper/) | [](https://www.myget.org/feed/dapper/package/nuget/Dapper) | | [Dapper.EntityFramework](https://www.nuget.org/packages/Dapper.EntityFramework/) | [](https://www.nuget.org/packages/Dapper.EntityFramework/) | [](https://www.nuget.org/packages/Dapper.EntityFramework/) | [](https://www.nuget.org/packages/Dapper.EntityFramework/) | [](https://www.myget.org/feed/dapper/package/nuget/Dapper.EntityFramework) | | [Dapper.EntityFramework.StrongName](https://www.nuget.org/packages/Dapper.EntityFramework.StrongName/) | [](https://www.nuget.org/packages/Dapper.EntityFramework.StrongName/) | [](https://www.nuget.org/packages/Dapper.EntityFramework.StrongName/) | [](https://www.nuget.org/packages/Dapper.EntityFramework.StrongName/) | [](https://www.myget.org/feed/dapper/package/nuget/Dapper.EntityFramework.StrongName) | | [Dapper.Rainbow](https://www.nuget.org/packages/Dapper.Rainbow/) | [](https://www.nuget.org/packages/Dapper.Rainbow/) | [](https://www.nuget.org/packages/Dapper.Rainbow/) | [](https://www.nuget.org/packages/Dapper.Rainbow/) | [](https://www.myget.org/feed/dapper/package/nuget/Dapper.Rainbow) | | [Dapper.SqlBuilder](https://www.nuget.org/packages/Dapper.SqlBuilder/) | [](https://www.nuget.org/packages/Dapper.SqlBuilder/) | [](https://www.nuget.org/packages/Dapper.SqlBuilder/) | [](https://www.nuget.org/packages/Dapper.SqlBuilder/) | [](https://www.myget.org/feed/dapper/package/nuget/Dapper.SqlBuilder) | | [Dapper.StrongName](https://www.nuget.org/packages/Dapper.StrongName/) | [](https://www.nuget.org/packages/Dapper.StrongName/) | [](https://www.nuget.org/packages/Dapper.StrongName/) | [](https://www.nuget.org/packages/Dapper.StrongName/) | [](https://www.myget.org/feed/dapper/package/nuget/Dapper.StrongName) | Package Purposes: * Dapper * The core library * Dapper.EntityFramework * Extension handlers for EntityFramework * Dapper.EntityFramework.StrongName * Extension handlers for EntityFramework * Dapper.Rainbow * Micro-ORM implemented on Dapper, provides CRUD helpers ([readme](Dapper.Rainbow/readme.md)) * Dapper.SqlBuilder * Component for building SQL queries dynamically and composably 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](https://dapper-plus.net/) is a major sponsor and is proud to contribute to the development of Dapper ([read more](https://dapperlib.github.io/Dapper/dapperplus)) - [AWS](https://github.com/aws) who sponsored Dapper from Oct 2023 via the [.NET on AWS Open Source Software Fund](https://github.com/aws/dotnet-foss) Dapper Plus logo Features -------- Dapper is a [NuGet library](https://www.nuget.org/packages/Dapper) 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: ``` csharp // insert/update/delete etc var count = connection.Execute(sql [, args]); // multi-row query IEnumerable rows = connection.Query(sql [, args]); // single-row query ({Single|First}[OrDefault]) T row = connection.QuerySingle(sql [, args]); ``` where `args` can be (among other things): - a simple POCO (including anonyomous types) for named parameters - a `Dictionary` - a `DynamicParameters` instance Execute a query and map it to a list of typed objects ------------------------------------------------------- ``` csharp 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("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: ```csharp 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: ```csharp 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: ```csharp 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: ```csharp var foos = new List { { 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` 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](https://github.com/DapperLib/Dapper/tree/main/benchmarks/Dapper.Tests.Performance) (contributions welcome!) and can be run via: ```bash dotnet run --project .\benchmarks\Dapper.Tests.Performance\ -c Release -f net8.0 -- -f * --join ``` Output from the latest run is: ``` ini 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](https://github.com/FransBouma/RawDataAccessBencher) test suite or [OrmBenchmark](https://github.com/InfoTechBridge/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. ```csharp 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. ```csharp var sqlPredicates = new List(); 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. ```csharp 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` and will automatically parameterize your query. For example: ```csharp connection.Query("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: ```csharp 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. ```csharp 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`](https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/) 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` ```csharp 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: ```csharp (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 ```csharp ``` Everything put together, looks like this: ```csharp var sql = @"select * from #Posts p left join #Users u on u.Id = p.OwnerId Order by p.Id"; var data = connection.Query(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: ```csharp 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().Single(); var orders = multi.Read().ToList(); var returns = multi.Read().ToList(); ... } ``` Stored Procedures --------------------- Dapper fully supports stored procs: ```csharp var user = cnn.Query("spGetUser", new {Id = 1}, commandType: CommandType.StoredProcedure).SingleOrDefault(); ``` If you want something more fancy, you can do: ```csharp 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("@b"); int c = p.Get("@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: ```csharp Query("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. ```csharp var shapes = new List(); using (var reader = connection.ExecuteReader("select * from Shapes")) { // Generate a row parser for each type you expect. // The generic type is what the parser will return. // The argument (typeof(*)) is the concrete type to parse. var circleParser = reader.GetRowParser(typeof(Circle)); var squareParser = reader.GetRowParser(typeof(Square)); var triangleParser = reader.GetRowParser(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](https://www.sqlite.org/), 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](https://github.com/DapperLib/Dapper/tree/main/tests/Dapper.Tests). Who is using this? --------------------- Dapper is in production use at [Stack Overflow](https://stackoverflow.com/). ---