你的位置:首页 > ASP.net教程

[ASP.net教程]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;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   }