一、建表
在数据库中建立如下三张表:
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 }