Dapper.net Use (Turn)

Dapper.net use directory of this document

• Dapper.net use
• 1. Why Choose Dapper
• 2, take dapper (4.0) as an example.
• 2.1 Create several tables in the database.
• 2.2 Entity classes.
• 3. How to use
• 3.1 One-to-one mapping
• 3.2 One-to-many mappings
• 3.3 Inserting entities
• 3.4 Executing stored procedures

Dapper is a lightweight ORM tool (Github). If you are in a small project, using the entity Framework, NHibernate to handle big data access and relational mapping is a bit overkill. You think the ORM saves time and effort, then Dapper will be your choice.

1. Why Choose Dapper

1. Light weight. Only one file (SqlMapper.cs), only 120k after compilation (seems to be getting fatter)
2. Fast speed. The speed of the dapper is close to the IDataReader, and the data for the list exceeds the DataTable.
3. Supports multiple databases. Dapper can work with all ADO providers, including SQLite, SqlCe, Firebird, Oracle, MySQL, PostgreSQL and SQL Server
4. You can map one-to-one, one-to-many, many-to-many relationships.
5. High performance. By emit reflection IDataReader sequence queue, to quickly get and produce objects, performance is good.
6. Support framework2.0,3.0,3.5,4.0,4.5

2, take dapper (4.0) as an example. 2.1 Create several tables in the database.

CREATE TABLE [dbo]. [Cicuser] (    [UserId]                [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,    [Username]              [nvarchar] (a) not NULL,    [ PasswordHash]          [nvarchar] (+) NULL,    [Email]                 [nvarchar] (+) NULL,    [phonenumber]           [nvarchar] ( NULL,    [isfirsttimelogin]      [bit] Default (1) not NULL,    [Accessfailedcount]     [int.] Default (0) not NULL,    [CreationDate]          [datetime] Default (GETDATE ()) not NULL,    [IsActive]              [bit] Default (1) not NULL) CREATE TABLE [dbo]. [Cicrole] (    [Roleid]       [int] IDENTITY (1, 1) PRIMARY KEY NOT NULL,    [RoleName]     [nvarchar] (in) not NULL,) CREATE TABLE [dbo]. [Cicuserrole] (     [Id]   [int] IDENTITY (1, 1) PRIMARY key not NULL,     [UserId]  [int] FOREIGN KEY REFERENCES [dbo].[ Cicuser] ([UserId]) not NULL,     [Roleid]  [int] FOREIGN KEY REFERENCES [dbo].[ Cicrole] ([Roleid]) not NULL)

2.2 Entity classes.

When you create an entity class, the property name must correspond to the database field one by one.

entity class3. Use method 3.1 One-to-one mapping

 private static void Onetoone (String sqlconnectionstring) {list<customer> userlist = new list<            Customer> (); using (IDbConnection conn = getsqlconnection (sqlConnectionString)) {string sqlcommandtext = @ "S Elect C.userid,c.username as username,c.passwordhash as [Password],c.email,c.phonenumber,c.isfirsttimelogin,c. Accessfailedcount,c.creationdate,c.isactive,r.roleid,r.rolename from dbo. Cicuser c with (NOLOCK) INNER joins Cicuserrole CR on CR. UserId = C.userid INNER JOIN cicrole r on R.roleid = cr.                Roleid "; UserList = conn. Query<customer, Role, Customer> (Sqlcommandtext, (use R, role) = = {User. role = role; return user;                                                                }, NULL,            NULL, True,                                                     "Roleid", NULL, NULL).            ToList (); } if (Userlist.count > 0) {userlist.foreach (item) = Console.WriteLine ("UserN AME: "+ Item. UserName + "----Password:" + Item. Password + "-----Role:" + Item.                Role.rolename + "\ n"));            Console.ReadLine (); }        }

3.2 One-to-many mappings

private static void Onetomany (String sqlconnectionstring) {Console.WriteLine ("one to many");            list<user> userlist = new list<user> (); using (IDbConnection connection = getsqlconnection (sqlconnectionstring)) {string Sqlcommandtext 3 = @ "Select C.userid, C.username as Username, C.passwordhash as [Password], C.email, C.phon       ENumber, C.isfirsttimelogin, C.accessfailedcount, C.creationdate, c.isactive, R.roleid, R.ROLENAMEFROM dbo. Cicuser c with (NOLOCK) left joins Cicuserrole CR on CR. UserId = C.userid Left JOIN cicrole r on R.roleid = cr.                Roleid ";                var lookUp = new Dictionary<int, user> (); UserList = connection.                        Query<user, role, User> (SqlCommandText3, (User, role) = {                        User u; if (!lookup. TryGetValue (user. UserId, out u) {lookup.add (user.                        UserId, u = user);                        } u.role.add (Role);                    return user; }, NULL, NULL, True, "Roleid", NULL, NULL).                ToList ();            var result = Lookup.values; } if (Userlist.count > 0) {userlist.foreach (item) = Console.WriteLine ("UserN AME: "+ Item. UserName + "----Password:" + Item. Password + "-----Role:" + Item. Role.first ().                RoleName + "\ n"));            Console.ReadLine ();            } else {Console.WriteLine ("No Data in userlist!"); }        }

3.3 Inserting entities

public static void InsertObject (String sqlconnectionstring)        {            string sqlcommandtext = @ "INSERT into Cicuser ( Username,passwordhash,email,phonenumber) VALUES (    @UserName,    @Password,    @Email,    @PhoneNumber) ";            using (IDbConnection conn = getsqlconnection (sqlconnectionstring))            {                User user = new User ();                User. UserName = "Dapper";                User. Password = "654321";                User. email = "[email protected]";                User. PhoneNumber = "13795666243";                int result = Conn. Execute (sqlcommandtext, user);                if (Result > 0)                {                    Console.WriteLine ("Data has already inserted into db!");                }                else                {                    Console.WriteLine ("Insert failed!");                }                Console.ReadLine ();            }        }

3.4 Executing stored procedures

//<summary>//Execute StoredProcedure and map result to POCO//</summary> <param name= "sqlconnnectionstring" ></param> public static void Executestoredprocedure (string s            qlconnnectionstring) {list<user> users = new list<user> (); using (IDbConnection CNN = Getsqlconnection (sqlconnnectionstring)) {users = CNN.                                        Query<user> ("Dbo.p_getusers", new {UserId = 2},                                         NULL, True, NULL, CommandType.StoredProcedure).            ToList (); } if (users. Count > 0) {users. ForEach (user) = Console.WriteLine (user.            UserName + "\ n"));        } console.readline (); }

        <summary>//        Execute stroedprocedure and get result from return value//        </summary>/        < ;p Aram Name= "sqlconnnectionstring" ></param> public        static void Executestoredprocedurewithparms (string sqlconnnectionstring)        {            dynamicparameters p = new Dynamicparameters ();            P.add ("@UserName", "Cooper");            P.add ("@Password", "123456");            P.add ("@LoginActionType", NULL, Dbtype.int32, parameterdirection.returnvalue);            using (IDbConnection CNN = Getsqlconnection (sqlconnnectionstring))            {                CNN. Execute ("Dbo.p_validateuser", p, NULL, NULL, commandtype.storedprocedure);                int result = p.get<int> ("@LoginActionType");                Console.WriteLine (result);            }            Console.ReadLine ();        }

Dapper.net Use (Turn)