您好,登錄后才能下訂單哦!
這篇文章主要介紹如何使用Dapper使用Inner join的操作,文中介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們一定要看完!
Users表定義如下:
CREATE TABLE [dbo].[Users]( [UserId] [int] IDENTITY(1,1) NOT NULL, [UserName] [varchar](16) NULL, [Email] [varchar](32) NULL, [Address] [varchar](128) NULL, PRIMARY KEY CLUSTERED ( [UserId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Product表定義如下:
CREATE TABLE [dbo].[Product]( [ProductId] [int] IDENTITY(1,1) NOT NULL, [ProductName] [varchar](16) NULL, [Price] [decimal](8, 2) NULL, [UserId] [int] NULL, PRIMARY KEY CLUSTERED ( [ProductId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
查看Users表和Product表會(huì)發(fā)現(xiàn)兩張表通過UserId外鍵關(guān)聯(lián)起來,然后我們需要在Product實(shí)體類上面進(jìn)行修改,添加一個(gè)User的實(shí)體屬性,修改后的代碼如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DapperApplicationJoin.Model { public class Product { public int ProductId { get; set; } public string ProductName { get; set; } public User UserOwner { get; set; } public string Price { get; set; } } }
User實(shí)體類定義如下:
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DapperApplicationJoin.Model { public class User { public int UserId { get; set; } public string UserName { get; set; } public string Email { get; set; } public string Address { get; set; } } }
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Configuration; using System.Data; using System.Data.SqlClient; using Dapper; using DapperApplicationJoin.Model; namespace DapperApplicationJoin { class Program { static void Main(string[] args) { string conn = ConfigurationManager.ConnectionStrings["AppConnection"].ConnectionString; using (IDbConnection connection = new SqlConnection(conn)) { string sql = @" select p.ProductName,p.Price,u.UserName,u.UserId,u.Email,u.Address from Product as p join Users as u on p.UserId=u.UserId; "; var result = connection.Query<Product, User, Product>(sql, (product, users) => { product.UserOwner = users; return product; }, splitOn: "UserName"); var query = connection.Query(sql); // 輸出 使用動(dòng)態(tài)類型 query.AsList().ForEach(p => { Console.WriteLine("產(chǎn)品名稱:" + p.ProductName + ",產(chǎn)品價(jià)格:" + p.Price + ",用戶姓名:" + p.UserName); }); } Console.ReadKey(); } } }
splitOn參數(shù)的含義:代碼中的splitOn是UserName,運(yùn)行時(shí),會(huì)從查詢結(jié)果所有字段列表的最后一個(gè)字段開始進(jìn)行匹配,一直到找到UserName這個(gè)字段(大小寫忽略無所謂),找到的第一個(gè)UserName字段匹配的Product類的UserName屬性,那么從UserName到最后一個(gè)字段都屬于Product,UserName以前的字段都被影射到Users,通過 (T, P) => {return T; },把兩個(gè)類的實(shí)例解析出來。就實(shí)現(xiàn)了兩個(gè)數(shù)據(jù)的解析,要不然dapper也不知道哪個(gè)是Product哪個(gè)是Users的屬性。
除了可以使用上面的方法以外,還可以使用直接查詢SQL語句的方式:
using (IDbConnection connection = new SqlConnection(conn)) { string sql = @" select p.ProductName,p.Price,u.UserName,u.UserId,u.Email,u.Address from Product as p join Users as u on p.UserId=u.UserId; "; var query = connection.Query(sql); // 輸出 使用動(dòng)態(tài)類型 query.AsList().ForEach(p => { Console.WriteLine("產(chǎn)品名稱:" + p.ProductName + ",產(chǎn)品價(jià)格:" + p.Price + ",用戶姓名:" + p.UserName); }); }
運(yùn)行結(jié)果如下:
以上是“如何使用Dapper使用Inner join的操作”這篇文章的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對(duì)大家有幫助,更多相關(guān)知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。