一、建表
在数据库中建立如下三张表：
CREATE TABLE [dbo].[T_User]
(
[UserId] [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[Username] [nvarchar](256) NOT NULL,
[Password] [nvarchar](500) NULL,
[Email] [nvarchar](256) NULL,
[PhoneNumber] [nvarchar](30) NULL,
) CREATE TABLE [dbo].[T_Role]
(
[RoleId] [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[RoleName] [nvarchar](256) NOT NULL,
) CREATE TABLE [dbo].[T_UserRole]
(
[Id] [int] IDENTITY(1, 1) PRIMARY KEY NOT NULL,
[UserId] [int] FOREIGN KEY REFERENCES [dbo].[T_User] ([UserId]) NOT NULL,
[RoleId] [int] FOREIGN KEY REFERENCES [dbo].[T_Role] ([RoleId]) NOT NULL
)
在本篇中只会用到T_Role表，剩下的表在后面的文章中将会涉及到。
二、创建实体类
public class User
{
public User()
{
Role = new List<Role>();
} public int UserId { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
public string Email { get; set; }
public string PhoneNumber { get; set; }
public List<Role> Role { get; set; }
} public class Role
{
public int RoleId { get; set; }
public string RoleName { get; set; }
}
在创建实体类时，属性名称一定要与数据库字段一一对应。在本篇中只会用到Role实体类，User实体类在后续文章中会涉及并且会有一定程度上的修改。
三、操作
在进行增、删、改、查操作之前，应先建立与数据库的连接，具体代码如下：
private static readonly string connectionString = @"Data Source=.;Initial Catalog=test;User Id=sa;Password=sa@126.com";
private SqlConnection OpenConnection()
{
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
return connection;
}
1、查询实体列表
private List<Role> QueryRoleData()
{
using (IDbConnection con = OpenConnection())
{
string query = @"select * from T_Role";
return con.Query<Role>(query, null).ToList<Role>();
}
}
2、添加实体
private int AddRole()
{
using (IDbConnection con = OpenConnection())
{
Role role = new Role();
role.RoleName = "开发人员";
string strSql = @"insert into T_Role(RoleName)values(@RoleName)";
int result = con.Execute(strSql, role);
return result;
}
}
3、修改实体
private int UpdateRole(Role role)
{
using (IDbConnection con = OpenConnection())
{
role.RoleName = "开发主管";
string query = "update T_Role set RoleName=@RoleName where RoleId=@RoleId";
return con.Execute(query, role);
}
}
4、删除实体
private int DeleteRole(Role role)
{
using (IDbConnection con = OpenConnection())
{
string query = "delete from T_Role where RoleId=@RoleId";
return con.Execute(query, role);
}
}