Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use Dapper to use Inner join operation, the article introduces in great detail, has a certain reference value, interested friends must read!

1. Create two new tables: Users table and Product table

The Users table is defined as follows:

CREATE TABLE [dbo]. [Users] ([UserId] [int] IDENTITY (1Power1) 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

The Product table is defined as follows:

CREATE TABLE [dbo]. [Product] ([ProductId] [int] IDENTITY (1Power1) 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

Looking at the Users table and the Product table, we will find that the two tables are associated with the UserId foreign key. Then we need to modify the Product entity class to add an entity attribute of User. The modified code is as follows:

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

The User entity class is defined as follows:

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;}} 2. The Main method is defined as follows: 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.ProductNameParticipe. PricePaperu.UserNamememe u.UserIdrecoveryu.EmailMere u.Address from Product as p join Users as u on p.UserId=u.UserId "; var result = connection.Query (sql, (product, users) = > {product.UserOwner = users; return product }, splitOn: "UserName"); var query = connection.Query (sql) / / output using dynamic type query.AsList () .ForEach (p = > {Console.WriteLine ("product name:" + p.ProductName + ", product price:" + p.Price + ", user name:" + p.UserName);} Console.ReadKey () ") }}}

The meaning of the splitOn parameter: the splitOn in the code is UserName. When running, it will match from the last field of the list of all fields in the query result until the UserName field is found (it doesn't matter if the case is ignored). If the first UserName field matches the UserName property of the Product class, then the previous fields belonging to Product,UserName from UserName to the last field are all mapped to Users through (T, P) = > {return T } to parse the instances of the two classes. The parsing of two pieces of data is implemented, otherwise dapper will not know which is Product and which is the attribute of Users.

In addition to the above method, you can also query the SQL statement directly:

Using (IDbConnection connection = new SqlConnection (conn)) {string sql = @ "select p.ProductNameParticipe. PriceRecol u.UserNameParticipe u.UserIdPoweru.email U.Address from Product as p join Users as u on p.UserId=u.UserId "; var query = connection.Query (sql) / / output using dynamic type query.AsList () .ForEach (p = > {Console.WriteLine ("product name:" + p.ProductName + ", product price:" + p.Price + ", user name:" + p.UserName);});}

The running results are as follows:

The above is all the contents of this article entitled "how to use Dapper to use Inner join". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.