猛士

 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 )

 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     }

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     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     }

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

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

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     }