Dapper学习笔记（三）-增、删、改、查_C#

Dapper学习笔记（3）-增、删、改、查

一、建表

在数据库中建立如下三张表：

 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 )

在本篇中只会用到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 Role17     {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;[email protected]";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 [email protected] where [email protected]";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 [email protected]";6             return con.Execute(query, role);7         }8     }

1楼.Net菜鸟123: 看了这个框架，感觉没多大意义，还不如自己写一个呢