34,838
社区成员




----权限表
CREATE TABLE [dbo].[Table_Role](
[RoleId] [int] IDENTITY(1,1) primary key NOT NULL,
[Title] [nvarchar](50) NULL
)
---账户表
CREATE TABLE [dbo].[Table_User](
[UserID] [int] IDENTITY(1,1) primary key NOT NULL,
[LoginName] [nvarchar](50) NULL,
[Password] [nvarchar](50) NULL,
[UserName] [nvarchar](50) NULL
)
----账号权限表
CREATE TABLE [dbo].[Table_UserRole](
[URID] [int] IDENTITY(1,1) Primary key NOT NULL,
[UserID] [int] NULL,
[RoleID] [int] NULL
)
-----为了省事,我用了视图
-----视图
CREATE VIEW [dbo].[View_UserRole]
AS
SELECT dbo.Table_Role.RoleId, dbo.Table_Role.Title, dbo.Table_UserRole.URID, dbo.Table_UserRole.UserID,
dbo.Table_User.LoginName, dbo.Table_User.Password, dbo.Table_User.UserName
FROM dbo.Table_Role INNER JOIN
dbo.Table_UserRole ON dbo.Table_Role.RoleId = dbo.Table_UserRole.RoleID
INNER JOIN
dbo.Table_User ON dbo.Table_UserRole.UserID = dbo.Table_User.UserID
//----------------------------
/// <summary>
/// 数据库连接
/// </summary>
public class SqlConn
{
SqlConnection connection;
public SqlConnection Connection { get { return connection; } }
public SqlConn()
{
string connectionstring = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
connection = new SqlConnection(connectionstring);
}
}
//---------------
//SqlHelper类 太长,省略。。。。
//----------------------------------
/// <summary>
/// 权限类
/// </summary>
public class Role
{
public string RoleID { get; set; }
public string Title { get; set; }
}
///---------------------------
/// <summary>
/// 账号类
/// </summary>
public class Account
{
/// <summary>
/// 账号ID
/// </summary>
public string UserID { get; set; }
/// <summary>
/// 登录账号
/// </summary>
public string LoginName { get; set; }
/// <summary>
/// 密码
/// </summary>
public string Password { get; set; }
/// <summary>
/// 姓名
/// </summary>
public string UserName { get; set; }
private List<Role> roleList;
/// <summary>
/// 权限
/// </summary>
public List<Role> RoleList { get { return roleList; } }
/// <summary>
/// 获取账号信息
/// </summary>
/// <param name="loginName"></param>
/// <param name="password"></param>
/// <returns></returns>
public Account GetAccount(string loginName, string password)
{
Account account = null;
roleList = new List<Role>();
string sql = @"SELECT
[RoleId]
,[Title]
,[URID]
,[UserID]
,[LoginName]
,[Password]
,[UserName]
FROM [View_UserRole]
WHERE LoginName=@loginName and Password=@password";
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@loginName",loginName),
new SqlParameter("@password",password)
};
/// SqlHelper查询,获取DataTable
using (DataTable dt = SqlHelper.ExecuteDatatable(new SqlConn().Connection, CommandType.Text, sql, parameters))
{
if (dt != null && dt.Rows.Count > 0)
{
account = new Account();
account.UserID = dt.Rows[0]["UserID"].ToString();
account.LoginName = dt.Rows[0]["LoginName"].ToString();
account.Password = dt.Rows[0]["Password"].ToString();
account.UserName = dt.Rows[0]["UserName"].ToString();
for(int i=0;i<dt.Rows.Count;i++)
{
// 权限
Role role = new Role();
role.RoleID = dt.Rows[i]["RoleID"].ToString();
role.Title = dt.Rows[i]["Title"].ToString();
account.roleList.Add(role);
}
}
return account;
}
}
}
/// <summary>
/// 登录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
string loginname = textBox1.Text.Trim(); //账号
string password = textBox2.Text.Trim(); //密码
if (loginname == "" || password == "")
{
MessageBox.Show("用户名或密码不能为空");
return;
}
// 通过登录名及密码获取账号信息
Account account = new Account().GetAccount(loginname, password);
string message = string.Empty;
if (account != null)
{
if (account.RoleList != null)
{
if (account.RoleList.Count > 0)
{
var list = from m in account.RoleList select m.Title;
message = string.Join(",", list);
MessageBox.Show("当前登录员工的权限如下:" + message);
//登录成功,按权限处理
}
else
{
MessageBox.Show("该账号无权限");
//按无权限处理
}
}
else
{
MessageBox.Show("该账号权限为NULL,请联系管理员处理");
}
}
else
{
MessageBox.Show("账号或密码错误,登录失败");
}
}
///-------winform调用
/// <summary>
/// 调用存储过程
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
string id = textBox3.Text.Trim();
if(id!="")
{
Account account = new Account();
int ret=account.DelAccountWithStoredProcedure(id);
string msg = string.Format("删除了{0}个员工",ret);
MessageBox.Show(msg);
}
}
/// <summary>
/// SQL语句
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
string id = textBox3.Text.Trim();
if (id != "")
{
Account account = new Account();
int ret = account.DelAccount(id);
string msg = string.Format("删除了{0}个员工", ret);
MessageBox.Show(msg);
}
}
//-----------------------
/// <summary>
/// 方法1:删除账号,SQL
/// </summary>
/// <param name="id"></param>
/// <returns>影响行数</returns>
public int DelAccount(string id)
{
string sql = @"DELETE FROM[Table_User]
WHERE UserID = @UserID";
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@UserID",id)
};
// 执行命令,返回影响行数
int retval = SqlHelper.ExecuteNonQuery(new SqlConn().Connection, CommandType.Text, sql, parameters);
return retval;
}
/// <summary>
/// 方法2:使用存储过程删除账号
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public int DelAccountWithStoredProcedure(string id)
{
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@UserID",id)
};
// 执行命令,返回影响行数
int retval = SqlHelper.ExecuteNonQuery(new SqlConn().Connection, CommandType.StoredProcedure, "P_DelUser", parameters);
return retval;
}
-----C#使用存储过程删除数据,用到的存储过程
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_DelUser
-- Add the parameters for the stored procedure here
@UserID int
AS
BEGIN
DELETE FROM Table_User WHERE UserID=@UserID
END