Convert Figma logo to code with AI

DapperLib logoDapper

Dapper - a simple object mapper for .Net

17,439
3,667
17,439
494

Top Related Projects

3,263

Npgsql is the .NET data provider for PostgreSQL.

2,790

.NET Transactional Document DB and Event Store on PostgreSQL

Fast, Simple, Typed ORM for .NET

Fluent migrations framework for .NET

13,632

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.

Quick Overview

Dapper is a high-performance micro-ORM for .NET, providing a simple object mapper for ADO.NET. It offers fast execution of database queries and efficient mapping to .NET objects, making it an excellent choice for developers who need raw SQL performance with the convenience of an ORM.

Pros

  • Extremely fast performance, often outperforming other ORMs
  • Lightweight and simple to use, with a minimal learning curve
  • Supports multiple databases (SQL Server, PostgreSQL, SQLite, etc.)
  • Allows for fine-grained control over SQL queries

Cons

  • Limited automated query generation compared to full-featured ORMs
  • Requires more manual SQL writing, which can be error-prone
  • Lacks advanced features like change tracking or lazy loading
  • Not ideal for complex object graphs or heavily relational data models

Code Examples

Querying a single object:

using (var connection = new SqlConnection(connectionString))
{
    var user = connection.QuerySingle<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });
}

Executing a parameterized query:

using (var connection = new SqlConnection(connectionString))
{
    var users = connection.Query<User>("SELECT * FROM Users WHERE Age > @Age", new { Age = 18 });
}

Performing an insert operation:

using (var connection = new SqlConnection(connectionString))
{
    var affectedRows = connection.Execute("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", 
        new { Name = "John Doe", Email = "john@example.com" });
}

Getting Started

  1. Install Dapper via NuGet:

    Install-Package Dapper
    
  2. Add the following using statement to your C# file:

    using Dapper;
    
  3. Create a database connection and start querying:

    using System.Data.SqlClient;
    using Dapper;
    
    string connectionString = "Your connection string here";
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        var result = connection.Query<YourType>("SELECT * FROM YourTable");
    }
    

Competitor Comparisons

3,263

Npgsql is the .NET data provider for PostgreSQL.

Pros of Npgsql

  • Specialized for PostgreSQL, offering deep integration and support for PostgreSQL-specific features
  • Provides asynchronous operations out of the box, enhancing performance for I/O-bound operations
  • Includes built-in connection pooling, optimizing database connection management

Cons of Npgsql

  • Limited to PostgreSQL databases, lacking the multi-database support of Dapper
  • Steeper learning curve due to its focus on PostgreSQL-specific features
  • May require more boilerplate code for simple CRUD operations compared to Dapper's lightweight approach

Code Comparison

Npgsql:

using (var conn = new NpgsqlConnection(connectionString))
{
    await conn.OpenAsync();
    using (var cmd = new NpgsqlCommand("SELECT * FROM users WHERE id = @id", conn))
    {
        cmd.Parameters.AddWithValue("id", userId);
        using (var reader = await cmd.ExecuteReaderAsync())
        {
            // Process results
        }
    }
}

Dapper:

using (var conn = new SqlConnection(connectionString))
{
    var user = await conn.QuerySingleOrDefaultAsync<User>("SELECT * FROM users WHERE id = @id", new { id = userId });
    // Process results
}
2,790

.NET Transactional Document DB and Event Store on PostgreSQL

Pros of Marten

  • Built-in document storage and querying capabilities for PostgreSQL
  • Supports event sourcing and projections out of the box
  • Offers advanced features like multi-tenancy and batch operations

Cons of Marten

  • Limited to PostgreSQL, while Dapper supports multiple databases
  • Steeper learning curve due to more complex features
  • Potentially slower performance for simple CRUD operations

Code Comparison

Marten (Document storage):

var user = new User { Name = "John Doe", Email = "john@example.com" };
session.Store(user);
await session.SaveChangesAsync();

var retrievedUser = await session.LoadAsync<User>(user.Id);

Dapper (SQL query):

var user = new User { Name = "John Doe", Email = "john@example.com" };
await connection.ExecuteAsync("INSERT INTO Users (Name, Email) VALUES (@Name, @Email)", user);

var retrievedUser = await connection.QuerySingleOrDefaultAsync<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = user.Id });

Marten excels in document-based scenarios and complex domain models, while Dapper shines in performance-critical, SQL-centric applications. Choose based on your project's specific requirements and database preferences.

Fast, Simple, Typed ORM for .NET

Pros of ServiceStack.OrmLite

  • More feature-rich, offering a wider range of database operations and utilities
  • Supports multiple databases out of the box (SQLite, SQL Server, PostgreSQL, etc.)
  • Provides a fluent API for complex queries and joins

Cons of ServiceStack.OrmLite

  • Steeper learning curve due to its extensive feature set
  • Larger footprint and potentially slower performance for simple queries
  • Part of a larger framework, which may be overkill for small projects

Code Comparison

Dapper:

var user = connection.QuerySingle<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });

ServiceStack.OrmLite:

var user = db.Single<User>(u => u.Id == 1);

Both libraries offer simple ways to query data, but ServiceStack.OrmLite provides a more object-oriented approach with its fluent API. Dapper excels in raw SQL execution, while ServiceStack.OrmLite offers a higher level of abstraction.

ServiceStack.OrmLite is better suited for projects requiring a full-featured ORM with support for multiple databases, while Dapper is ideal for developers who prefer writing raw SQL and need high performance for simple queries.

Fluent migrations framework for .NET

Pros of FluentMigrator

  • Provides a fluent interface for database migrations, making them more readable and maintainable
  • Supports multiple database providers out of the box
  • Offers a command-line tool for executing migrations

Cons of FluentMigrator

  • Steeper learning curve compared to Dapper's simpler approach
  • May introduce additional complexity for smaller projects
  • Limited to database schema migrations, unlike Dapper's broader data access capabilities

Code Comparison

FluentMigrator:

public class AddUserTable : Migration
{
    public override void Up()
    {
        Create.Table("Users")
            .WithColumn("Id").AsInt32().PrimaryKey().Identity()
            .WithColumn("Name").AsString(100).NotNullable();
    }
}

Dapper:

connection.Execute(@"
    CREATE TABLE Users (
        Id INT PRIMARY KEY IDENTITY(1,1),
        Name NVARCHAR(100) NOT NULL
    )");

FluentMigrator focuses on defining database schema changes using a fluent API, while Dapper excels at executing raw SQL queries and mapping results to objects. FluentMigrator is specifically designed for managing database migrations, whereas Dapper is a more general-purpose micro-ORM for data access.

13,632

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.

Pros of Entity Framework Core

  • Rich ORM features including change tracking, lazy loading, and migrations
  • Supports multiple database providers (SQL Server, PostgreSQL, SQLite, etc.)
  • Seamless integration with ASP.NET Core and other .NET Core libraries

Cons of Entity Framework Core

  • Steeper learning curve compared to Dapper
  • Can be slower for simple queries due to overhead
  • More complex setup and configuration required

Code Comparison

Dapper:

var user = connection.QuerySingle<User>("SELECT * FROM Users WHERE Id = @Id", new { Id = 1 });

Entity Framework Core:

var user = dbContext.Users.FirstOrDefault(u => u.Id == 1);

Summary

Dapper is a lightweight, high-performance micro-ORM that excels in simplicity and raw SQL execution. It's ideal for developers who prefer writing SQL queries directly and need maximum performance.

Entity Framework Core is a full-featured ORM that provides a higher level of abstraction and more built-in features. It's better suited for complex data models and scenarios where developer productivity is prioritized over raw performance.

The choice between Dapper and Entity Framework Core depends on project requirements, performance needs, and developer preferences. Many projects benefit from using both in different parts of the application.

Convert Figma logo designs to code with AI

Visual Copilot

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

Dapper - a simple object mapper for .Net

Build status

Release Notes

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

Packages

MyGet Pre-release feed: https://www.myget.org/gallery/dapper

PackageNuGet StableNuGet Pre-releaseDownloadsMyGet
DapperDapperDapperDapperDapper MyGet
Dapper.EntityFrameworkDapper.EntityFrameworkDapper.EntityFrameworkDapper.EntityFrameworkDapper.EntityFramework MyGet
Dapper.EntityFramework.StrongNameDapper.EntityFramework.StrongNameDapper.EntityFramework.StrongNameDapper.EntityFramework.StrongNameDapper.EntityFramework.StrongName MyGet
Dapper.RainbowDapper.RainbowDapper.RainbowDapper.RainbowDapper.Rainbow MyGet
Dapper.SqlBuilderDapper.SqlBuilderDapper.SqlBuilderDapper.SqlBuilderDapper.SqlBuilder MyGet
Dapper.StrongNameDapper.StrongNameDapper.StrongNameDapper.StrongNameDapper.StrongName MyGet

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.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 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):

  • a simple POCO (including anonyomous types) for named parameters
  • a Dictionary<string,object>
  • a DynamicParameters instance

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

ORMMethodReturnMeanStdDevErrorGen0Gen1Gen2Allocated
Dapper cache impactExecuteParameters_CacheVoid96.75 us0.668 us1.010 us0.6250--2184 B
Dapper cache impactQueryFirstParameters_CacheVoid96.86 us0.493 us0.746 us0.8750--2824 B
Hand CodedSqlCommandPost119.70 us0.706 us1.067 us1.37501.00000.12507584 B
Hand CodedDataTabledynamic126.64 us1.239 us1.873 us3.0000--9576 B
SqlMarshalSqlCommandPost132.36 us1.008 us1.523 us2.00001.00000.250011529 B
DapperQueryFirstOrDefaultPost133.73 us1.301 us2.186 us1.75001.5000-11608 B
MightyQuerydynamic133.92 us1.075 us1.806 us2.00001.7500-12710 B
LINQ to DBQueryPost134.24 us1.068 us1.614 us1.75001.2500-10904 B
RepoDBExecuteQueryPost135.83 us1.839 us3.091 us1.75001.5000-11649 B
Dapper'Query (buffered)'Post136.14 us1.755 us2.653 us2.00001.5000-11888 B
MightyQueryPost137.96 us1.485 us2.244 us2.25001.2500-12201 B
DapperQueryFirstOrDefaultdynamic139.04 us1.507 us2.279 us3.5000--11648 B
MightySingleFromQuerydynamic139.74 us2.521 us3.811 us2.00001.7500-12710 B
Dapper'Query (buffered)'dynamic140.13 us1.382 us2.090 us2.00001.5000-11968 B
ServiceStackSingleByIdPost140.76 us1.147 us2.192 us2.50001.25000.250015248 B
Dapper'Contrib Get'Post141.09 us1.394 us2.108 us2.00001.5000-12440 B
MightySingleFromQueryPost141.17 us1.941 us2.935 us1.75001.5000-12201 B
Massive'Query (dynamic)'dynamic142.01 us4.957 us7.494 us2.00001.5000-12342 B
LINQ to DB'First (Compiled)'Post144.59 us1.295 us1.958 us1.75001.5000-12128 B
RepoDBQueryFieldPost148.31 us1.742 us2.633 us2.00001.50000.500013938 B
Norm'Read<> (tuples)'ValueTuple`8148.58 us2.172 us3.283 us2.00001.7500-12745 B
Norm'Read<()> (named tuples)'ValueTuple`8150.60 us0.658 us1.106 us2.25002.00001.250014562 B
RepoDBQueryPost152.34 us2.164 us3.271 us2.25001.50000.250014106 B
RepoDBQueryDynamicPost154.15 us4.108 us6.210 us2.25001.75000.500013930 B
RepoDBQueryWherePost155.90 us1.953 us3.282 us2.50000.5000-14858 B
Dapper cache impactExecuteNoParameters_NoCacheVoid162.35 us1.584 us2.394 us---760 B
Dapper cache impactExecuteNoParameters_CacheVoid162.42 us2.740 us4.142 us---760 B
Dapper cache impactQueryFirstNoParameters_CacheVoid164.35 us1.206 us1.824 us0.2500--1520 B
DevExpress.XPOFindObjectPost165.87 us1.012 us1.934 us8.5000--28099 B
Dapper cache impactQueryFirstNoParameters_NoCacheVoid173.87 us1.178 us1.781 us0.5000--1576 B
LINQ to DBFirstPost175.21 us2.292 us3.851 us2.00000.5000-14041 B
EF 6SqlQueryPost175.36 us2.259 us3.415 us4.00000.7500-24209 B
Norm'Read<> (class)'Post186.37 us1.305 us2.496 us3.00000.5000-17579 B
DevExpress.XPOGetObjectByKeyPost186.78 us3.407 us5.151 us4.50001.0000-30114 B
Dapper'Query (unbuffered)'dynamic194.62 us1.335 us2.019 us1.75001.5000-12048 B
Dapper'Query (unbuffered)'Post195.01 us0.888 us1.343 us2.00001.5000-12008 B
DevExpress.XPOQueryPost199.46 us5.500 us9.243 us10.0000--32083 B
BelgradeFirstOrDefaultTask`1228.70 us2.181 us3.665 us4.50000.5000-20555 B
EF Core'First (Compiled)'Post265.45 us17.745 us26.828 us2.0000--7521 B
NHibernateGetPost276.02 us8.029 us12.139 us6.50001.0000-29885 B
NHibernateHQLPost277.74 us13.032 us19.703 us8.00001.0000-31886 B
NHibernateCriteriaPost300.22 us14.908 us28.504 us13.00001.0000-57562 B
EF 6FirstPost310.55 us27.254 us45.799 us13.0000--43309 B
EF CoreFirstPost317.12 us1.354 us2.046 us3.5000--11306 B
EF CoreSqlQueryPost322.34 us23.990 us40.314 us5.0000--18195 B
NHibernateSQLPost325.54 us3.937 us7.527 us22.00001.0000-80007 B
EF 6'First (No Tracking)'Post331.14 us27.760 us46.649 us12.00001.0000-50237 B
EF Core'First (No Tracking)'Post337.82 us27.814 us46.740 us3.00001.0000-17986 B
NHibernateLINQPost604.74 us5.549 us10.610 us10.0000--46061 B
Dapper cache impactExecuteParameters_NoCacheVoid623.42 us3.978 us6.684 us3.00002.0000-10001 B
Dapper cache impactQueryFirstParameters_NoCacheVoid630.77 us3.027 us4.576 us3.00002.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

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, 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.