Dapper學習筆記（3）-增、刪、改、查，dapper學習筆記

Dapper學習筆記（3）-增、刪、改、查，dapper學習筆記

一、建表

在數據庫中建立如下三張表：

 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 NULL
21 )

在本篇中只會用到T_Role表，剩下的表在後面的文章中將會涉及到。

二、創建實體類

 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 Role
17     {
18         public int RoleId { get; set; }
19         public string RoleName { get; set; }
20     }

在創建實體類時，屬性名稱一定要與數據庫字段一一對應。在本篇中只會用到Role實體類，User實體類在後續文章中會涉及並且會有一定程度上的修改。

三、操作

在進行增、刪、改、查操作之前，應先建立與數據庫的連接，具體代碼如下：

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、查詢實體列表

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、添加實體

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

3、修改實體

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

4、刪除實體

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     }