Dapper Study Notes (3)-add, delete, modify, and query dapper Study Notes

Dapper Study Notes (3)-add, delete, modify, and query dapper Study Notes

I. Create a table

Create the following three tables in the database:

 1 CREATE TABLE [dbo].[T_User] 2 ( 3     [UserId]           [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL, 4     [Username]         [nvarchar](256) NOT NULL, 5     [Password]         [nvarchar](500) NULL, 6     [Email]            [nvarchar](256) NULL, 7     [PhoneNumber]      [nvarchar](30) NULL, 8 ) 9 10 CREATE TABLE [dbo].[T_Role]11 (12     [RoleId]           [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,13     [RoleName]         [nvarchar](256) NOT NULL,14 )15 16 CREATE TABLE [dbo].[T_UserRole]17 (18      [Id]      [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,19      [UserId]  [int] FOREIGN KEY REFERENCES [dbo].[T_User] ([UserId]) NOT NULL,20      [RoleId]  [int] FOREIGN KEY REFERENCES [dbo].[T_Role] ([RoleId]) NOT NULL21 )

In this article, only the T_Role table is used, and the rest of the tables will be involved in later articles.

Ii. Create an object class

 1     public class User 2     { 3         public User() 4         { 5             Role = new List<Role>(); 6         } 7  8         public int UserId { get; set; } 9         public string UserName { get; set; }10         public string Password { get; set; }11         public string Email { get; set; }12         public string PhoneNumber { get; set; }13         public List<Role> Role { get; set; }14     }15 16     public class Role17     {18         public int RoleId { get; set; }19         public string RoleName { get; set; }20     }

When creating an object class, the attribute name must correspond to the database fields one by one. In this article, only the Role entity class will be used. The User entity class will be involved in subsequent articles and will be modified to a certain extent.

Iii. Operations

Before adding, deleting, modifying, and querying a database, you must establish a connection with the database. The specific code is as follows:

1     private static readonly string connectionString = @"Data Source=.;Initial Catalog=test;User Id=sa;Password=sa@126.com";2 3     private SqlConnection OpenConnection()4     {5         SqlConnection connection = new SqlConnection(connectionString);6         connection.Open();7         return connection;8     }

1. query the Object List

1     private List<Role> QueryRoleData()2     {3         using (IDbConnection con = OpenConnection())4         {5             string query = @"select * from T_Role";6             return con.Query<Role>(query, null).ToList<Role>();7         }8     }

2. Add an object

1 private int AddRole () 2 {3 using (IDbConnection con = OpenConnection () 4 {5 Role role = new Role (); 6 role. roleName = "Developer"; 7 string strSql = @ "insert into T_Role (RoleName) values (@ RoleName)"; 8 int result = con. execute (strSql, role); 9 return result; 10} 11}

3. Modify an object

1 private int UpdateRole (Role role) 2 {3 using (IDbConnection con = OpenConnection () 4 {5 role. roleName = "Development Director"; 6 string query = "update T_Role set RoleName = @ RoleName where RoleId = @ RoleId"; 7 return con. execute (query, role); 8} 9}

4. delete an object

1     private int DeleteRole(Role role)2     {3         using (IDbConnection con = OpenConnection())4         {5             string query = "delete from T_Role where RoleId=@RoleId";6             return con.Execute(query, role);7         }8     }