Dapper, dapperorm

Dapper, dapperorm
Content

• Features
• Performance
• Parameterized Query
• List support
• Cached and non-cached readers
• Multiple Mappings
• Multiple results
• Stored Procedure
• Ansi Strings and varchar
• Restrictions and precautions
• Can Dapper run on my db provider?
• Is there a complete list of examples?
• Who is using Dapper?
• Reference

The new company's database layer, to be precise, uses Dapper at the database layer and persistent layer, which is indeed very convenient.

Download Demo (this download contains the Dapper project, which has Dapper test examples and performance test examples) Features

Dapper has only one file. You can drag it to your project to expand your IDbConnection interface.

It provides three types of help:

Execute a query and map the result to a strong list

Note: All extension methods assume that the connection has been enabled. If the connection is closed, they will fail.

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

Usage:

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 });

dog.Count()

    .IsEqualTo(1);

dog.First().Age

    .IsNull();

dog.First().Id

    .IsEqualTo(guid);

Execute a query and map the result to the list of a dynamic object.

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

This method executes the SQL statement and returns a dynamic list.

Usage:

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

((int)rows[0].A)

   .IsEqualTo(1);

((int)rows[0].B)

   .IsEqualTo(2);

((int)rows[1].A)

   .IsEqualTo(3);

((int)rows[1].B)

    .IsEqualTo(4);

Execute a Command that does not return results

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

Usage:

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 })

   .IsEqualTo(2);

Execute a Command multiple times

The same signature also allows you to easily and efficiently execute a command multiple times, such as bulk-load data ).

Usage:

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 } }

  ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"

Implement for any typeTIsIEnumerableCan be executed.

Performance

Dapper features performance. The following data shows how long it takes to SELECT 500 rows for a database and map the data to an object.

Performance testing is divided into three aspects:

• POCO serialization framework, which supports retrieving static objects from the database. Use the original SQL.
  The dynamic serialization framework supports returning the dynamic list of objects.
  Typical framework usage. It usually does not involve writing SQL statements.

Performance of SELECT mapping over 500 iterations-POCO serialization

Method	Duration (MS)	Remarks
Hand coded (using a SqlDataReader)	47	Can be faster
Dapper ExecuteMapperQuery	49	Same as above
ServiceStack. OrmLite (QueryById)	50	Same as above
PetaPoco	52	Same as above
BLToolkit	80	Same as above
SubSonic CodingHorror	107	Same as above
Nhib1_ SQL	104	Same as above
Linq 2 SQL ExecuteQuery	181	Same as above
Entity framework ExecuteStoreQuery	631	Same as above

Performance of SELECT mapping over 500 iterations-dynamic serialization

Method	Duration (MS)	Remarks
Dapper ExecuteMapperQuery (dynamic)	48
Massive	52
Simple. Data	95

Performance of SELECT mapping over 500 iterations-typical usage

Method	Duration (MS)	Remarks
Linq 2 SQL CompiledQuery	81	Not super typical involves complex code
Nhib1_hql	118
Linq 2 SQL	559
Entity framework	859
SubSonic ActiveRecord. SingleOrDefault	3619

Parameterized Query

Parameters can be passed as anonymous classes. This allows you to easily name parameters by simply cutting and pasting SQL fragments and executing them in the query analyzer.

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

List support

Dapper running allows you to passIEnumerable, Automatically parameterized query.

For exampleInQuery:

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:

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

Cached and non-cached readers

The default action of Dapper is to execute the SQL statement and buffer the entire reader when returning it. In most cases, this is ideal because it can minimize the shared locks in the database and reduce the Network Time of the database.

However, when performing a large query, you may only load the required objects to reduce memory usage. To do this, cache the data to the query method.

Multiple Mappings

Dapper allows you to map a single row to multiple objects. This feature is critical if you want to avoid additional queries and loading associations.

For example:

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();

post.Content.IsEqualTo("Sams Post1");

post.Id.IsEqualTo(1);

post.Owner.Name.IsEqualTo("Sam");

post.Owner.Id.IsEqualTo(99);

Note: Dapper assumes that your ID column is named "ID" or "id". If your primary key is different, or you want to split a wide row on the vertex, instead of "ID", you can use the optional 'spliton' parameter.

Multiple results

Dapper allows you to process multiple result sets in one query.

For 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 Procedure

Dapper fully supports stored procedures:

var user = cnn.Query<User>("spGetUser", new {Id = 1}, 

        commandType: CommandType.StoredProcedure).First();}}}

If you want more flexible operations, you can do this:

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 parameters. If you execute a where statement on a varchar column, ensure that the parameters are passed as follows:

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

On SQL Server, when querying non-Unicode, Unicode and ANSI are required.

Restrictions and precautions

The cache information of each query executed by Dapper enables it to quickly materialized objects and process parameters. The current implementation caches information in a ConcurrentDictionary object. The objects it stores will never be refreshed. If the SQL string you generated does not use parameters, it may hit the memory. We convert the dictionary to the LRU (Least Recently Used) cache.

Many features of ORM are removed by Dapper, and no identity map (Identity Map), No updated/selected assistant.

Dapper does not manage the lifecycle of your connection. It assumes that the connection obtained by Dapper is open and does not have the DataReader enumeration (unless MARS is enabled ).

Can Dapper run on my db provider?

Dapper can run on all. net ado providers, including sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL, and SQL Server.

Is there a complete list of examples?

Dapper has a complete test suite in the test project.

Who is using Dapper?

Currently, Dapper has Stack Overflow and helpdesk.

(If you wowould like to be listed here let me know)

References

• Github Dapper
• Stackoverflow Dapper

 

Download Demo