Lightweight ORM Framework: Some complex operations in dapper and inner joins should be noted for pits

In the last blog post we quickly introduced some of the basic curd operations of dapper, which is also the smallest unit of our manipulate DB, which we introduced a relatively complex

A bit of operation, source analysis is not here for the time being introduced.

One: Table sql

For convenience, here we generate two tables, one users, and one product,sql as follows:

<1> Users Table

CREATE TABLE [dbo]. [Users] (    [UserID] [int] IDENTITY () not NULL,    [UserName] [varchar] () NULL,    [Email] [varchar] (+) NULL,    [ Address] [varchar] (+) NULL, CONSTRAINT [pk_users] PRIMARY KEY CLUSTERED (    [UserID] ASC) with (pad_index  = OFF, ST Atistics_norecompute  = off, Ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [PRIMARY]) On [PRIMARY]

<2> Product Table

CREATE TABLE [dbo]. [Product] (    [ProductID] [int] IDENTITY () not NULL,    [ProductName] [varchar] (+) NULL,    [PRODUCTDESC] [varchar] ( ) NULL,    [UserID] [int] null,    [createtime] [datetime] NULL, CONSTRAINT [pk_product] 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]
   

Two: in operation

Many times when we manipulate table, more or less will use the "in keyword", for example: I want to find the user table in the email in (' 5qq.com ', ' 8qq.com ')

Users record ...

static void Main (string[] args)        {            var connection = new SqlConnection ("Data source=.;i Nitial catalog=datamip;integrated security=true; Multipleactiveresultsets=true ");            var sql = "Select * from Users where e-mail in @emails";            var info = connection. query<users> (SQL, new {emails = new string[2] {"5qq.com", "7qq.com"}});        

Look at the above operation, is not very simple, as long as our parameter type is an array, Dappper will automatically convert it ...

Three: Multiple SQL execute together

Sometimes we want to pour a lot of snippet SQL into one SQL and then let it execute together, which reminds me of an operation that I'll write when I load data in db

Select ... from marketing where ID in (...); Select .... from Eventmarketing, where in (...) A statement like this, and then merge the results, this

To facilitate the demonstration, do a * operation on the user and do a * operation on product, such as the following:

static void Main (string[] args)        {            var connection = new SqlConnection ("Data source=.;i Nitial catalog=datamip;integrated security=true; Multipleactiveresultsets=true ");            var sql = "Select * from Product; SELECT * from Users ";            var Multireader = connection. Querymultiple (SQL);            var productlist = multireader.read<product> ();            var userlist = multireader.read<users> ();            Multireader.dispose ();        }

Four: Multi-table JOIN operation

No matter how good or bad SQL write, contact one months or contact 10 years, are bound to run more than a table query, then on the multi-table query dapper How to use it??? Like what

Say I want to find the product information and personal information after 2015-12-12, obviously this is a multi-table query, you can first look at the relationship between users and product.

It can be found that they have a foreign key relationship, and then we make a small change to the product entity, and the users as an entity property of product ...

   public class Product    {public        int ProductID {get; set;}        public string ProductName {get; set;}        public string Productdesc {get; set;}        Public Users Userowner {get; set;}        public string Createtime {get; set;}    }

With these reserves, we can probably write the following SQL.

static void Main (string[] args)        {            var connection = new SqlConnection ("Data source=.;i Nitial catalog=datamip;integrated security=true; Multipleactiveresultsets=true ");            var sql = @ "Select  p.productname,p.createtime,u.username from                        Product as P                        join Users as u on                        p.userid = u . UserID                        where p.createtime > ' 2015-12-12 '; ";            var result = connection. Query<product, users, product> (SQL,                                    (product, users) =                                    {                                        product. Userowner = users; return product;                                    });        

The result is "Damn!!!" “。。。。。。。。。。。。

As you can see from the error message: When you use multi-mapping, be sure to set the Spliton parameter, in addition to the ID ... It seems that there is nothing to be seen in this sentence, that is to say

In addition to the ID, you need to set the Spliton parameter, OK, this is forcing the brother to see the source code .... See what kind of bird Spliton is. And then I went up from call stack.

And found a very "critical" paragraph.

Then Spliton is dapper to DataReader "right-to-left" scanning, so that you can get a subsequent from sequent, and then encounter the settings Spliton

Just stop ... Yes, that's it, haha ... This time I know, set the Spliton to "userName" just fine ... such as the following ...

   static void Main (string[] args)        {            var connection = new SqlConnection ("Data source=.;i Nitial catalog=datamip;integrated security=true; Multipleactiveresultsets=true ");            var sql = @ "Select  p.productname,p.createtime,u.username from                        Product as P                        join Users as u on                        p.userid = u . UserID                        where p.createtime > ' 2015-12-12 '; ";            var result = connection. Query<product, users, product> (SQL,                                    (product, users) =                                    {                                        product. Userowner = users; return product;                                    },spliton: "UserName");        }

Of course, if you think I said above is too verbose, notice also more TMD, is a generic type, but also lambda ... You can also not specify these specific type, and use the default

Dynamic is also possible, such as the following:

V: Supports stored procedures

For stored procedures, is also a topic to be said, our dapper can also be executed, just need to be in the query in the CommandType to mark the current is a

StoredProcedure is sorta, such as creating a simple storedprocedure on the users table now.

Use [datamip]go/****** Object:  StoredProcedure [dbo].[ Sp_getusers]    Script date:09/02/2016 09:14:04 ******/set ansi_nulls ongoset quoted_identifier ONGOCreate proc [dbo].[ Sp_getusers]   @id int   as  begin   SELECT * from Users where UserID = @id;   

Here, we need to plug a @id parameter into the stored procedure and return to the specific users entitylist, so let's look at how query is structured.

  static void Main (string[] args)        {            var connection = new SqlConnection ("Data source=.;i Nitial catalog=datamip;integrated security=true; Multipleactiveresultsets=true ");            var info = connection. Query<users> ("Sp_getusers", new {id = 5},                                   commandType:CommandType.StoredProcedure);        }

Fix, feel with dapper is not so simple, first said here, hope to everyone helpful.

Lightweight ORM Framework: Some complex operations in dapper and inner joins should be noted for pits