在线时间:8:00-16:00
迪恩网络APP
随时随地掌握行业动态
扫描二维码
关注迪恩网络微信公众号
开源软件名称:Dapper开源软件地址:https://gitee.com/stoneson/Dapper开源软件介绍:Dapper - a simple object mapper for .NetRelease NotesLocated at dapperlib.github.io/Dapper PackagesMyGet Pre-release feed: https://www.myget.org/gallery/dapper
FeaturesDapper is a NuGet library that you can add in to your project that will extend your It provides 3 helpers: Execute a query and map the results to a strongly typed Listpublic static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) Example 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 });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 objectspublic static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) 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 resultspublic static int Execute(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null) 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 timesThe 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 for some T. PerformanceA key feature of Dapper is performance. The following metrics show how long it takes to execute a The benchmarks can be found in Dapper.Tests.Performance (contributions welcome!) and can be run via: dotnet run -p .\benchmarks\Dapper.Tests.Performance\ -c Release -f netcoreapp3.1 -- -f * --join Output from the latest run is: BenchmarkDotNet=v0.12.1, OS=Windows 10.0.19041.208 (2004/?/20H1)Intel Core i7-7700HQ CPU 2.80GHz (Kaby Lake), 1 CPU, 8 logical and 4 physical cores.NET Core SDK=3.1.201 [Host] : .NET Core 3.1.3 (CoreCLR 4.700.20.11803, CoreFX 4.700.20.12001), X64 RyuJIT ShortRun : .NET Core 3.1.3 (CoreCLR 4.700.20.11803, CoreFX 4.700.20.12001), X64 RyuJIT
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 queriesParameters are passed in as anonymous classes. This allow 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 List SupportDapper allows you to pass in 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 replacementsDapper 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 injectedis 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 Buffered vs Unbuffered readersDapper'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, Multi MappingDapper 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: 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 This is the use case for multi mapping. You tell dapper that the query returns a (post, user) => { post.Owner = user; return post; } The 3 type arguments to the <Post, User, Post> Everything put together, looks like this: var sql =@"select * from #Posts pleft join #Users u on u.Id = p.OwnerIdOrder 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 Multiple ResultsDapper allows you to process multiple result grids in a single query. Example: var sql =@"select * from Customers where CustomerId = @idselect * from Orders where CustomerId = @idselect * 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 ProceduresDapper 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 varcharDapper 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 RowUsually 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 Imagine you have a database table named "Shapes" with the columns: 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 MySQLIn order to use Non-parameter SQL variables with MySql Connector, you have to add the following option to your connection string:
Make sure you don't provide Dapper with a property to map. Limitations and caveatsDapper 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 Dapper's simplicity means that many feature 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 |
请发表评论