Dapper-開源小型ORM

標籤：

 一些關於Dapper的介紹：

 1.Dapper是一個輕型的開源ORM類，代碼就一個SqlMapper.cs檔案，編譯後就40多K的一個很小的Dll. 

 2.Dapper支援Mysql,SqlLite,Mssql2000,Mssql2005,Oracle等一系列的資料庫

 3.Dapper的r支援多表並聯的對象。支援一對多 多對多的關係。並且沒侵入性。

 4.Dapper原理通過Emit反射IDataReader的序列隊列，來快速的得到和產生對象。效能提升了很多；（比採用常規的反射）並且無須遷就資料庫的設計。

 Dapper源碼下載連結：

 點擊這裡下載dapper原始碼。

 Demo:

           var connection = GetOpenConnection();            var guid = Guid.NewGuid();            string id = "6e2a106d-d838-48b9-ac74-ad604457bba2";            //1泛型            var dog = connection.Query<Dog>("select * from Dog where Id = @Id", new { Id = id });                        //2 動態解析            var rows = connection.Query("select * from Dog where Id = @Id", new { Id = id }).ToList();            foreach (dynamic item in rows)            {                 String 駭客 = item.Name;            }

Query

 

            //3.執行不返回結果            int result=connection.Execute("insert into Dog values(@age,@id,@name,@weight,@ignoredproperty)", new { age = 49, id = Guid.NewGuid(), name = "YZR", weight = 117.5, ignoredproperty = 1 });

Execute

            //4.批量Execute            List<Dog> list = new List<Dog>();            list.Add(new Dog() { Age = 9, Id = Guid.NewGuid(), Name = "ZXX", Weight = 120 });            list.Add(new Dog() { Age = 9, Id = Guid.NewGuid(), Name = "WMJ", Weight = 120 });            List<dynamic> paramsList = new List<dynamic>();            foreach (Dog item in list)            {                paramsList.Add(new { age = item.Age, id = item.Id, name = item.Name, weight = item.Weight, ignoredproperty = 1 });            }            int result = connection.Execute("insert into Dog values(@age,@id,@name,@weight,@ignoredproperty)", paramsList);

批量Execute

            //5.In操作            dog = connection.Query<Dog>("select * from Dog where id in @ids", new { ids = new String[] { "6e2a106d-d838-48b9-ac74-ad604457bba3", "6e2a106d-d838-48b9-ac74-ad604457bba2", "535dab3a-d3c1-4cb0-b8f7-63351f491056" } });            //等價於            dog = connection.Query<Dog>("select * from Dog where id in (@id1,@id2,@id3)", new { id1 = "6e2a106d-d838-48b9-ac74-ad604457bba3", id2 = "6e2a106d-d838-48b9-ac74-ad604457bba2", id3 = "535dab3a-d3c1-4cb0-b8f7-63351f491056" });

In操作

            //資料庫要建立主外鍵關係            var sql =                      @"select p.*,u.* from Dog p                                     left join Owner u on u.OwnerId = p.Id                                      where [email protected] Order by p.Id";            Dog d = null;            var t = connection.Query(sql, new { id = "6e2a106d-d838-48b9-ac74-ad604457bba2" });            var data = connection.Query<Dog, Owner, Dog>(sql, (post, user) =>            {                if (d == null || d.Id != post.Id)                {                    d = post;                }                if (user != null)                {                    d.user.Add(user);                }                return post;            }, new { id = "6e2a106d-d838-48b9-ac74-ad604457bba2" });

一對多關聯性

            //多查詢的結果集            sql =                 @"select * from Dog where Id = @id                   select * from Owner";            using (var multi = connection.QueryMultiple(sql, new { id = "6e2a106d-d838-48b9-ac74-ad604457bba3" }))            {                 var data = multi.Read<Dog>().ToList();                 var owner = multi.Read<Owner>().ToList();             }

QueryMultiple

            //事務            using (connection)            {                //開始事務                IDbTransaction transaction = connection.BeginTransaction();                try                {                    string query = "update Dog set Age=Age+1 where [email protected]";                    string query2 = "update Dog set Weight=Weight+1.0 where [email protected]";                    connection.Execute(query, new { Id = "71fff309-c7c9-4f64-b588-0a03e27459ba" }, transaction, null, null);                    connection.Execute(query2, new { Id = "71fff309-c7c9-4f64-b588-0a03e27459ba" }, transaction, null, null);                    //提交事務                    transaction.Commit();                }                catch (Exception ex)                {                    //出現異常，事務Rollback                    transaction.Rollback();                    throw new Exception(ex.Message);                }            }

IDbTransaction

            //預存程序            var p = new DynamicParameters();            //執行個體1            //p.Add("@result", dbType: DbType.Int32, direction: ParameterDirection.Output);            //var user = connection.Query("Test", p, commandType: CommandType.StoredProcedure);            //int totalCount = p.Get<int>("@result");            //執行個體2            //注意點：調用如果使用query，那麼預存程序需要有select結果集            //p.Add("@result", dbType: DbType.Int32, direction: ParameterDirection.Output);            //p.Add("@rowcount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);            //var user = connection.Query("TestValue", p, commandType: CommandType.StoredProcedure);            //int re = p.Get<int>("@result");            //int ro = p.Get<int>("@rowcount");            //執行個體3            //預存程序分頁需要row_number() over( order by id)這個排序的id需要指定            p.Add("@PageIndex", 1, dbType: DbType.Int32, direction: ParameterDirection.Input);            p.Add("@PageSize", 3, dbType: DbType.Int32, direction: ParameterDirection.Input);            p.Add("@TableName", "Dog", dbType: DbType.String, direction: ParameterDirection.Input);            p.Add("@Where", " 1=1 order by id asc ", dbType: DbType.String, direction: ParameterDirection.Input);            //p.Add("@rowcount", dbType: DbType.Int32, direction: ParameterDirection.Output);            p.Add("@rowcount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);            var result = connection.Query("SelectBase", p, commandType: CommandType.StoredProcedure);            int count = p.Get<int>("@rowcount");

Procedure

            //切換資料庫            connection.ChangeDatabase("資料庫名稱");

ChangeDatabase

 

Demo原始碼下載

點擊這裡下載

 

　

 

Dapper-開源小型ORM