Lightweight, high-performance ORM framework: Dapper Advanced gameplay

Dapper Advanced Gameplay 1:

The underlined table fields in the database automatically match the non-underlined model field.

Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;

Note:

This is most helpful for friends using MySQL database, because MySQL is lowercase by default, and general fields are underlined, such as user_name.

Specific effects are shown below

1, first create a table and insert the data

2. Create model Models

public class User    {public        int UserID {get; set;}        public string UserName {get; set;}        public int Roleid {get; set;}    }

3. Write the extracted data logic code.

SELECT * FROM [user]

Static program ()        {            var config = new Configurationbuilder ()                . Setbasepath (Directory.GetCurrentDirectory ())                . Addjsonfile ("Appsettings.json", Optional:true, reloadonchange:true);            var data = config. Build ();            dapperextension.dbconnectionstring = data. GetConnectionString ("DefaultConnection");        }        static void Main (string[] args)        {            IDbConnection dbconnection = null;            using (DbConnection = DbConnection. OpenConnection ())            {                var users = dbconnection. List ("SELECT * from [user]", null);                foreach (var user in users)                {                    Console.WriteLine ($ "{user). Userid}-{user. Username}-{user. Roleid} ");                }            }            Console.readkey ();        }

4, data extraction without matchnameswithunderscores setting

No binding successful??

This is because the field that is taken out is underlined and does not match the model field because of the select * from.

5, set matchnameswithunderscores data pump again

static void Main (string[] args)        {            Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;            IDbConnection dbconnection = null;            using (DbConnection = DbConnection. OpenConnection ())            {                var users = dbconnection. List ("SELECT * from [user]", null);                foreach (var user in users)                {                    Console.WriteLine ($ "{user). Userid}-{user. Username}-{user. Roleid} ");                }            }            Console.readkey ();        }

Data binding succeeded.

Just one sentence Dapper.DefaultTypeMap.MatchNamesWithUnderscores = True, let's write a few as statements.

Dapper Advanced Gameplay 2:

Powerful query, with function functions, can set the model binding logic freely.

1, create two associated tables and fill in the data.

2, extracts user and its associated role data.

Select 1 as table1,t1.*,1 as table2,t2.* from [user] T1 inner join [role] T2 on t1.role_id = t2.role_id

Extension methods:

public static IEnumerable Queryt (this idbconnection dbconnection, string sql, Func map, object param = null, idbtransactio n transaction = null, String splitOn = "Id")        {            return dbconnection. Query (SQL, map, param, transaction, Spliton:spliton);        }

Use:

static void Querytest ()        {            Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;            IDbConnection dbconnection = null;            using (DbConnection = DbConnection. OpenConnection ())            {                var result = DbConnection. Queryt (                    @ "Select 1 as table1,t1.*,1 as table2,t2.* from [user] T1 inner joins [role] T2 on t1.role_id = t2.role_id", 
    (user, role) =                    {                        user. role = role;                        return user;                    },                    null,                    splitOn: "Table1,table2");                foreach (var user in result)                {                    Console.WriteLine ($ "{User. Userid}-{user. Username}-{user. Role.roleid}-{user. Role.rolename} ");                }            }            Console.readkey ();        }

The data was successfully taken.

Spliton explanation : the field split flag for model binding. Table fields table1 to table2 are bound to role after the table field is bound to user,table2.

3, special function logic. For example, extract role_id corresponding user list.

Select 1 as table1,t1.*,1 as table2,t2.* from [role] T1 left join [user] T2 on t1.role_id = t2.role_id

The external definition of a dictionary type, query internal model binding each time the function function is called, the function functions to add data to the external dictionary, which is useful in complex data processing.

static void QueryTest2 () {Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;            IDbConnection dbconnection = null; using (DbConnection = DbConnection.                OpenConnection ()) {dictionary> dic = new dictionary> (); DbConnection. Queryt (@ "Select 1 as table1,t1.*,1 as table2,t2.* from [role] T1 left joins [user] T2 on t1.role_id = T2 . role_id ", (role, user) = {if (dic. ContainsKey (role. Roleid)) {dic[role. Roleid].                       ADD (user); } else {dic. ADD (role.                       Roleid, new List {user});                   } return true;                }, NULL, SplitOn: "Table1,table2");                  foreach (var data in dic) {  Console.WriteLine ($ "role:{data.                    Key} "); foreach (var user in data. Value) {Console.WriteLine ($ "user:{user. Userid}-{user.                    UserName} ");        }}} Console.readkey (); }

Output Result:

Today introduced to this, follow-up time to add other play, personal preference dapper this freedom of writing.

Lightweight, high-performance ORM framework: Dapper Advanced gameplay