Dapper-a Simple Object Mapper for. Net

features

Dapper is a separate file that can be put into your project to expand your IDbConnection interface.

It offers three assistants:

executes a query and maps the results to a strongly typed collection

Note: All extension methods assume that the connection is open and they will fail if the connection is closed.

 Public Static Ienumerable<t> query<t> (Thisstringobjectnullnull  BOOLtrue)

Examples of Use:

 Public classdog{ Public int? Age {Get;Set; }  PublicGuid Id {Get;Set; }  Public stringName {Get;Set; }  Public float? Weight {Get;Set; }  Public intIgnoredproperty {Get{return 1; } }}            varGUID =Guid.NewGuid ();varDog = connection. Query<dog> ("Select age = @Age, Id = @Id",New{age = (int?)NULL, Id =guid}); Dog. Count (). Isequalto (1);d og. First (). Age. IsNull ();d og. First (). Id. Isequalto (GUID);

executes a query and maps the results to a dynamic type collection

The method executes the SQL and returns a dynamic collection.

 Public Static ienumerable<Dynamic> Query (Thisstringobjectnullnull BOOL true)

Examples of Use:

varrows = 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);

executes a command that does not return a result

 Public Static int Execute (Thisstringobjectnullnull)

Examples of Use:

Connection. Execute (@ "  SET NOCOUNT on   CREATE TABLE #t (i int)   SET NOCOUNT off   insert #t   Select @a a U Nion All Select @b   set NOCOUNT on   drop table #t"new {a=1, b=2  })   . Isequalto (2);

execute a command multiple times

The same placeholder allows you to easily and efficiently execute a command multiple times.

Examples of Use:

Connection. Execute (@ "insert MyTable (ColA, ColB) VALUES (@a, @b)",    newNew {a=1, b=1new {a=2, b=2new {a=3, b=  3  }}  ). Isequalto (3//  3 rows inserted: "2,2", "3,3" and "

Performance

A key feature of Dapper is performance. The following indicator shows how long it will take to execute 500 SELECT statements against the database and return the data to the object.

A performance test is divided into three tables:

• p The OCO serialization framework supports the use of raw SQL from a database to a statically typed object, through
• Dynamic serialization Framework supports returning a list of dynamic objects.
• Typical use of the framework. Usually typical frameworks are used differently than the best use of performance wise. Usually it doesn't involve writing SQL.

Performance-poco serialization for 500 query mappings

Method	Duration	Remarks
Hand coded (using a SqlDataReader )	47ms	Can be faster
DapperExecuteMapperQuery	49ms
Servicestack.ormlite (Querybyid)	50ms
Petapoco	52ms
Bltoolkit	80ms
Subsonic Codinghorror	107ms
NHibernate SQL	104ms
Linq 2 SQLExecuteQuery	181ms
Entity FrameworkExecuteStoreQuery	631ms

performance of 500 Query mappings-Dynamic type serialization

Method	Duration	Remarks
Dapper ExecuteMapperQuery (Dynamic)	48ms
Massive	52ms
Simple.data

Performance of 500 query mappings-Classic frame usage

Method	Duration	Remarks
Linq 2 SQL CompiledQuery	81ms	Not super typical involves complex code
NHibernate HQL	118ms
Linq 2 SQL	559ms
Entity Framework	859ms
Subsonic Activerecord.singleordefault	3619ms

Parameterized Queries

The parameter passes the anonymous class. This allows you to name parameters easily and enables you to simply cut and paste SQL snippets in Query Analyzer and run them.

New 1 " b " //

Support for collection parameters

Dapper allows you to automate your query parameters through IEnumerable.

Examples of Use:

Connection.  query<int> ("select * from" (select 1 as ID UNION ALL SELECT 2 UNION ALL select 3) as X where ID In @Ids"newnewint123 });

will be parsed into this way:

Select  from (Select1asselect2Select3aswhere   in (@Ids1, @Ids2, @Ids3)"  //@Ids1 = 1, @Ids2 = 2, @Ids2 = 3

buffered and unbuffered readers

The default behavior of dapper is to execute SQL and buffer the entire reader in return. This is ideal in most cases because it minimizes shared locks in the database and reduces the database time on the network.

When executing a query, however, you may need to reduce memory consumption and load only the objects as needed.

Multiple Mappings

Dapper allows a single record to be mapped to multiple objects. This is a key feature if you want to avoid irrelevant queries and desire to load associated objects.

Examples of Use:

varsql =@"select * from #Posts p left joins #Users u on u.id = P.ownerid Order by p.id";vardata = connection. Query<post, user, post> (SQL, (post, User) = {post. Owner = user;returnPost;}); /The last parameter of the generic is the type to be returnedvarPost =data. First ();p OST. Content.isequalto ("Sams Post1");p Ost. Id.isequalto (1);p Ost. Owner.Name.IsEqualTo ("Sam");p Ost. Owner.Id.IsEqualTo ( About);

Important Note Dapper assume that your ID column is named "id" or "id", if your primary key is not, or you want to split the "id" of a wide line, use the optional "splitOn" parameter.

Multiple Results

Dapper allows you to process multiple results in a single query.

Example:

var @" SELECT * from Customers where CustomerId = @idselect * from the Orders where CustomerId = @idselect * from Returns where C Ustomerid = @id"; using (varnew {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 procedures

var user = cnn. Query<user> ("spgetuser"new1},         CommandType: CommandType.StoredProcedure). Singleordefault ();

If you want to be more beautiful you can do this:

varp =Newdynamicparameters ();p. ADD ("@a", One);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); intb = p.get<int> ("@b");intc = p.get<int> ("@c");

ANSI Strings and varchar

Dapper supports varchar params if you are executing a WHERE clause on a varchar column using the parameter must be passed it in this way:

Query<thing> ("select * from Thing" where Name = @Name"newnew"  abcde"truetotrue "});

It is critical to use Unicode Unicode and ANSI when querying non-Unicode on SQL Server.

Limitations and Warnings

Dapper cache information each query runs on it, which enables it to quickly implement object and process parameters. The current implementation caches this information in the Concurrentdictionary last object. The object It stores is not refreshed. If you are dynamically generating SQL strings without using parameters it is possible that you will experience memory problems. we can convert the dictionary to an LRU cache.

can I use it in my database?

Dapper does not have DB specific implementation details, it works at all. net ADO provider includingsqlite, SQL CE, Fire bird, Oracle, MySQL, PostgreSQL, and SQL Server.

Dapper-a Simple Object Mapper for. Net