/* 数据表SQL脚本
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UsersInRoles_Roles]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UsersInRoles] DROP CONSTRAINT FK_UsersInRoles_Roles
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UsersInRoles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UsersInRoles]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Roles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Roles]
GO
CREATE TABLE [dbo].[Roles] (
[Rolename] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ApplicationName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UsersInRoles] (
[Username] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Rolename] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ApplicationName] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Roles] WITH NOCHECK ADD
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[Rolename],
[ApplicationName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UsersInRoles] WITH NOCHECK ADD
CONSTRAINT [PK_UsersInRoles] PRIMARY KEY CLUSTERED
(
[Username],
[Rolename],
[ApplicationName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UsersInRoles] ADD
CONSTRAINT [FK_UsersInRoles_Roles] FOREIGN KEY
(
[Rolename],
[ApplicationName]
) REFERENCES [dbo].[Roles] (
[Rolename],
[ApplicationName]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
*/
using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
using System.Web.Configuration;
namespace MyProvider
{
///
/// 角色提供程序
///
public class MyRoleProvider : RoleProvider
{
private string rolesTable = "Roles";
private string usersInRolesTable = "UsersInRoles";
private string eventSource = "SqlRoleProvider";
private string eventLog = "Application";
private string exceptionMessage = "一个异常被抛出，请查看事件日志。";
///
/// 连接字符串
///
private string connectionString;
public string ConnectionString
{
get { return connectionString; }
set { connectionString = value; }
}
//
// 如果false，有异常的话就抛出。如果true，有异常就写入日志。
//
private bool pWriteExceptionsToEventLog = false;
public bool WriteExceptionsToEventLog
{
get { return pWriteExceptionsToEventLog; }
set { pWriteExceptionsToEventLog = value; }
}
//
// System.Web.Security.RoleProvider properties.
// 使用配置文件 (Web.config) 中指定的角色信息的应用程序的名称。
private string pApplicationName;
public override string ApplicationName
{
get { return pApplicationName; }
set { pApplicationName = value; }
}
///
/// 构造函数
///
public MyRoleProvider()
{
}
///
/// 初始化角色提供程序
/// 接受提供程序的名称和配置设置的 NameValueCollection 作为输入。用于设置提供程序实例的属性值，其中包括特定于实现的值和配置文件（Machine.config 或 Web.config）中指定的选项。
///
/// 提供程序的名称
/// 配置设置
public override void Initialize(string name, NameValueCollection config)
{
//
// Initialize values from web.config.
//
if (config == null)
throw new ArgumentNullException("config");
if (name == null || name.Length == 0)
name = "MyRoleProvider";
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "LuoTong.MyRoleProvider");
}
// Initialize the abstract base class.
base.Initialize(name, config);
if (config["applicationName"] == null || config["applicationName"].Trim() == "")
pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath;
else
pApplicationName = config["applicationName"];
if (config["writeExceptionsToEventLog"] != null)
{
if (config["writeExceptionsToEventLog"].ToUpper() == "TRUE")
pWriteExceptionsToEventLog = true;
}
//
// Initialize SQLConnection.
//
if (string.IsNullOrEmpty(config["connectionStringName"]))
throw new ArgumentNullException("角色提供程序的数据库连接字符串未初始化");
ConnectionStringSettings css = ConfigurationManager.ConnectionStrings[config["connectionStringName"]];
connectionString = css.ConnectionString;
}
///
/// 用户添加到角色
///
/// 一个用户名
/// 一个角色名
public void AddUserToRole(string username, string rolename)
{
if (!RoleExists(rolename))
throw new ProviderException("没有此角色。");
if (rolename.IndexOf(',') > 0)
throw new ArgumentException("角色名中不能包含逗号。");
if (username.IndexOf(',') > 0)
throw new ArgumentException("用户名中不能包含逗号。");
if (IsUserInRole(username, rolename))
throw new ProviderException("用户已经在此角色中。");
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO [" + usersInRolesTable + "]" + " (Username, Rolename, ApplicationName) " + " Values(@Username, @Rolename, @ApplicationName)", conn);
cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = username;
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "AddUserToRole");
else
throw e;
}
finally
{
conn.Close();
}
}
///
/// 角色提供程序.多个用户添加到多个角色
/// 接受用户名列表和角色名列表作为输入，然后将指定的用户与在已配置的 ApplicationName 的数据源中指定的角色关联。
///
/// 用户名列表
/// 角色名列表
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
throw new ProviderException("没有此角色。");
if (rolename.IndexOf(',') > 0)
throw new ArgumentException("角色名中不能包含逗号。");
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
throw new ArgumentException("用户名中不能包含逗号。");
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
throw new ProviderException("用户已经在此角色中。");
}
}
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO [" + usersInRolesTable + "]" + " (Username, Rolename, ApplicationName) " + " Values(@Username, @Rolename, @ApplicationName)", conn);
SqlParameter userParm = cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20);
SqlParameter roleParm = cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (SqlException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "AddUsersToRoles");
else
throw e;
}
finally
{
conn.Close();
}
}
///
/// 角色提供程序.创建角色
/// 接受角色名作为输入，并将指定的角色添加到已配置的 ApplicationName 的数据源中。
///
/// 角色名
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') > 0)
throw new ArgumentException("角色名中不能包含逗号。");
if (RoleExists(rolename))
throw new ProviderException("角色名已经存在。");
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO [" + rolesTable + "]" + " (Rolename, ApplicationName) " + " Values(@Rolename, @ApplicationName)", conn);
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "CreateRole");
else
throw e;
}
finally
{
conn.Close();
}
}
///
/// 角色提供程序.删除角色
/// 接受角色名以及一个指示如果仍有用户与该角色关联时是否引发异常的布尔值作为输入。
///
/// 角色名
/// 如果有用户与此角色关联是否引发异常
/// 是否删除成功
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
throw new ProviderException("角色不存在。");
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
throw new ProviderException("不能删除含有用户的角色。");
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM [" + rolesTable + "]" + " WHERE Rolename = @Rolename AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
return false;
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return true;
}
///
/// 角色提供程序.返回所有角色名
/// 从数据源返回角色名的列表。
///
/// 角色名列表
public override string[] GetAllRoles()
{
string tmpRoleNames = "";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT Rolename FROM [" + rolesTable + "]" + " WHERE ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
SqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
// 用逗号将多个用户名分开。
tmpRoleNames += reader.GetString(0) + ",";
}
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetAllRoles");
else
throw e;
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpRoleNames.Length > 0)
{
// 删除最后一个多余的逗号。
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
return new string[0];
}
///
/// 角色提供程序.根据用户名返回角色
/// 接受用户名作为输入，并从数据源返回与指定的用户关联的角色名。
///
///
///
public override string[] GetRolesForUser(string username)
{
string tmpRoleNames = "";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT Rolename FROM [" + usersInRolesTable + "]" + " WHERE Username = @Username AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = username;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
SqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
// 将角色名用逗号分开。
tmpRoleNames += reader.GetString(0) + ",";
}
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetRolesForUser");
else
throw e;
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpRoleNames.Length > 0)
{
// 删除最后一个多余的逗号。
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
return new string[0];
}
///
/// 角色提供程序.返回角色中所有用户。
/// 接受角色名作为输入，并从数据源返回与角色关联的用户名。
///
/// 角色名
/// 用户名列表
public override string[] GetUsersInRole(string rolename)
{
string tmpUserNames = "";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT Username FROM [" + usersInRolesTable + "]" + " WHERE Rolename = @Rolename AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
SqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
// 用逗号将多个用户名分开。
tmpUserNames += reader.GetString(0) + ",";
}
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "GetUsersInRole");
else
throw e;
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpUserNames.Length > 0)
{
// 删除最后一个多余的逗号。
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
return new string[0];
}
///
/// 角色提供程序.叛断此用户是否属于此角色
/// 接受用户名和角色名作为输入，并确定当前登录用户是否与已配置的 ApplicationName 的数据源中的角色关联。
///
/// 用户名
/// 角色名
/// 是否有关联
public override bool IsUserInRole(string username, string rolename)
{
bool userIsInRole = false;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [" + usersInRolesTable + "]" + " WHERE Username = @Username AND Rolename = @Rolename AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = username;
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
try
{
conn.Open();
int numRecs = (int)cmd.ExecuteScalar();
if (numRecs > 0)
userIsInRole = true;
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "IsUserInRole");
else
throw e;
}
finally
{
conn.Close();
}
return userIsInRole;
}
///
/// 删除用户和角色的关联
///
/// 用户名
/// 角色名
public void RemoveUserFromRole(string username, string rolename)
{
if (!RoleExists(rolename))
throw new ProviderException("角色名不存在。");
if (!IsUserInRole(username, rolename))
throw new ProviderException("用户不在角色中。");
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM [" + usersInRolesTable + "]" + " WHERE Username = @Username AND Rolename = @Rolename AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20).Value = username;
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RemoveUserFromRole");
else
throw e;
}
finally
{
conn.Close();
}
}
///
/// 角色提供程序.删除用户和角色的关联
/// 接受用户名列表和角色名列表作为输入，然后删除指定用户与在已配置的 ApplicationName 的数据源中的指定角色的关联。
///
/// 用户名列表
/// 角色名列表
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
throw new ProviderException("角色名不存在。");
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
throw new ProviderException("用户不在角色中。");
}
}
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("DELETE FROM [" + usersInRolesTable + "]" + " WHERE Username = @Username AND Rolename = @Rolename AND ApplicationName = @ApplicationName", conn);
SqlParameter userParm = cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 20);
SqlParameter roleParm = cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
SqlTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
userParm.Value = username;
roleParm.Value = rolename;
cmd.ExecuteNonQuery();
}
}
tran.Commit();
}
catch (SqlException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RemoveUsersFromRoles");
else
throw e;
}
finally
{
conn.Close();
}
}
///
/// 角色提供程序.判断是否存在此角色
/// 接受角色名作为输入，并确定在已配置的 ApplicationName 的数据源中是否存在该角色名。
///
/// 角色名
/// 是否存在
public override bool RoleExists(string rolename)
{
bool exists = false;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM [" + rolesTable + "]" + " WHERE Rolename = @Rolename AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@Rolename", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = ApplicationName;
try
{
conn.Open();
int numRecs = (int)cmd.ExecuteScalar();
if (numRecs > 0)
exists = true;
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "RoleExists");
else
throw e;
}
finally
{
conn.Close();
}
return exists;
}
///
/// 角色提供程序.在角色中查找用户
/// 接受角色名和要搜索的用户名作为输入，并返回角色中的用户列表
///
/// 角色名
/// 要搜索的用户名
/// 用户列表
public override string[] FindUsersInRole(string rolename, string usernameToMatch)
{
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT Username FROM [" + usersInRolesTable + "] " + "WHERE Username LIKE @UsernameSearch AND RoleName = @RoleName AND ApplicationName = @ApplicationName", conn);
cmd.Parameters.Add("@UsernameSearch", SqlDbType.NVarChar, 20).Value = usernameToMatch;
cmd.Parameters.Add("@RoleName", SqlDbType.NVarChar, 20).Value = rolename;
cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 20).Value = pApplicationName;
string tmpUserNames = "";
SqlDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
// 将多个用户名用逗号分开。
tmpUserNames += reader.GetString(0) + ",";
}
}
catch (SqlException e)
{
if (WriteExceptionsToEventLog)
WriteToEventLog(e, "FindUsersInRole");
else
throw e;
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpUserNames.Length > 0)
{
// 删除最后一个多余的逗号。
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
return new string[0];
}
///
/// 写入事件日志
///
/// 异常
/// 操作
private void WriteToEventLog(SqlException e, string action)
{
EventLog log = new EventLog();
log.Source = eventSource;
log.Log = eventLog;
string message = exceptionMessage + "
";
message += "操作：" + action + "
";
message += "异常：" + e.ToString();
log.WriteEntry(message);
}
}
}
使用时，在web.config中添加如下配置：
<roleManager enabled="true" cacheRolesInCookie="true" cookieName=".ASPROLES" defaultProvider="MyRoleProvider">
<providers>
<clear />
<add applicationName="你的站点名" name="MyRoleProvider" type="MyRoleProvider" connectionStringName="ConnectionString1" writeExceptionsToEventLog="false" />
providers>
roleManager>
然后在你的代码中就可以直接使用Roles类了，静态的，很方便。比如添加一个用户到一个组中：Roles.AddUserToRole("张三", "管理员");