public override string[] GetUsersInRole(string roleName)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT u.Username FROM dbo.SiteRole AS r");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t ON r.id = t.TechId");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.id = t.TypeId");
strSQL.Append(" WHERE r.RoleName = @rolename;");
strSQL.Append(" AND t.[Type] = 'UsersInRoles'");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
#endregion
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL.ToString(), parms);
StringCollection sc = new StringCollection();
String[] strReturn;
foreach (DataRow r in oTable.Rows)
{
sc.Add(r[0].ToString().Trim());
}
strReturn = new String[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
public override string[] FindUsersInRole(string roleName, string usernameToMatch)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT u.Username FROM dbo.SiteRole AS r");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t ON r.id = t.TechId");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.id = t.TypeId");
strSQL.Append(" WHERE r.RoleName = @rolename");
strSQL.Append(" AND u.Username LIKE @UserNameToMatch+'%'");
strSQL.Append(" AND t.[Type] = 'UsersInRoles'");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50),
new SqlParameter("@UserNameToMatch", SqlDbType.VarChar, 20)
};
parms[0].Value = roleName;
parms[1].Value = usernameToMatch;
#endregion
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL.ToString(), parms);
return(DataTable2Array(oTable));
}
/// <summary>
/// 删除角色
/// </summary>
/// <param name="roleName"></param>
/// <param name="throwOnPopulatedRole">是否确认用户</param>
/// <returns></returns>
public override bool DeleteRole(string roleName, bool throwOnPopulatedRole)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
StringBuilder strSQL = new StringBuilder();
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
if (throwOnPopulatedRole == true)
{
#region - T-SQL -
strSQL.Append(" SELECT COUNT(u.ID) FROM dbo.SiteRole AS r");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t ON r.id = t.TechId");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.id = t.TypeId");
strSQL.Append(" WHERE r.RoleName = @rolename");
strSQL.Append(" AND t.[Type] = 'UsersInRoles'");
#endregion
int i = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, strSQL.ToString(), parms));
if (i > 0)
{
throw new ProviderException("Role is not empty");
}
}
strSQL = new StringBuilder("DELETE FROM dbo.SiteRole WHERE RoleName = @rolename;");
int j = Convert.ToInt32(SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL.ToString(), parms));
return(j == 1);
}
internal static void CheckArrayParameter(ref string[] param, bool checkForNull, bool checkIfEmpty, bool checkForCommas, int maxSize, string paramName)
{
if (param == null)
{
throw new ArgumentNullException(paramName);
}
if (param.Length < 1)
{
throw new ArgumentException("The array parameter '" + paramName + "' should not be empty.", paramName);
}
for (int i = param.Length - 1; i >= 0; i--)
{
SecUtility.CheckParameter(ref param[i],
checkForNull,
checkIfEmpty,
checkForCommas,
maxSize,
paramName + "[ " + i.ToString(CultureInfo.InvariantCulture) + " ]");
}
for (int i = param.Length - 1; i >= 0; i--)
{
for (int j = i - 1; j >= 0; j--)
{
if (param[i].Equals(param[j]))
{
throw new ArgumentException("The array '" + paramName + "' should not contain duplicate values.",
paramName);
}
}
}
}
public override void CreateRole(string roleName)
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
string strSQL = "INSERT INTO dbo.SiteRole([RoleName]) SELECT @rolename WHERE NOT EXISTS(SELECT * FROM dbo.SiteRole WHERE RoleName=@rolename);";
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL, parms);
}
public override bool IsUserInRole(string username, string roleName)
{
string cachekey = "IsUserInRole_" + username + "_" + roleName;
if (HttpContext.Current.Cache[cachekey] == null)
{
SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
if (username.Length < 1)
{
return(false);
}
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT COUNT(*) FROM Members");
strSQL.Append(" WHERE EXISTS");
strSQL.Append(" (");
strSQL.Append(" SELECT * FROM MembersInRoles");
strSQL.Append(" WHERE MembersInRoles.UserId = Members.UserId");
strSQL.Append(" AND EXISTS");
strSQL.Append(" (");
strSQL.Append(" SELECT * FROM SiteRoles");
strSQL.Append(" WHERE SiteRoles.id = MembersInRoles.RoleId");
strSQL.Append(" AND SiteRoles.RoleName = @RoleName");
strSQL.Append(" )");
strSQL.Append(" )");
strSQL.Append(" AND UserName = @UserName");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@username", SqlDbType.VarChar, 20),
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = username;
parms[1].Value = roleName;
#endregion
bool b = (SQLPlus.ExecuteScalar(CommandType.Text, strSQL.ToString(), parms).ToString() != "0");
CacheInsert(cachekey, b);
return(b);
}
return(Convert.ToBoolean(HttpContext.Current.Cache[cachekey]));
}
public override void RemoveUsersFromRoles(string[] usernames, string[] roleNames)
{
SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames");
SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder(BuildTable(usernames, roleNames));
strSQL.Append(" DELETE dbo.Tech2Type");
strSQL.Append(" FROM @t AS t");
strSQL.Append(" INNER JOIN dbo.SiteRole AS r ON t.rolename = r.RoleName");
strSQL.Append(" INNER JOIN dbo.v_User2ID AS u ON u.Username= t.username");
strSQL.Append(" INNER JOIN dbo.Tech2Type AS t2 ON r.id = t2.TechId AND u.id = t2.TypeId");
strSQL.Append(" WHERE t2.[Type] = 'UsersInRoles'");
#endregion
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL.ToString());
}
public override string[] GetRolesForUser(string username)
{
string cachekey = "GetRolesForUser_" + username;
//if (HttpContext.Current.Cache[cachekey] == null)
{
SecUtility.CheckParameter(ref username, true, false, true, 255, "username");
if (username.Length < 1)
{
return(new string[0]);
}
#region - T-SQL -
StringBuilder strSQL = new StringBuilder();
strSQL.Append(" SELECT * FROM SiteRoles");
strSQL.Append(" WHERE EXISTS(");
strSQL.Append(" SELECT * FROM MembersInRoles");
strSQL.Append(" WHERE EXISTS(");
strSQL.Append(" SELECT * FROM Members");
strSQL.Append(" WHERE Members.UserId = MembersInRoles.UserId");
strSQL.Append(" AND Members.UserName = @UserName");
strSQL.Append(" ) AND SiteRoles.id = MembersInRoles.RoleId");
strSQL.Append(" )");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@username", SqlDbType.VarChar, 20)
};
parms[0].Value = username;
#endregion
DataTable oTable = SQLPlus.ExecuteDataTable(CommandType.Text, strSQL.ToString(), parms);
StringCollection sc = new StringCollection();
string[] strReturn;
foreach (DataRow r in oTable.Rows)
{
sc.Add(r["RoleName"].ToString().Trim());
}
strReturn = new string[sc.Count];
sc.CopyTo(strReturn, 0);
return(strReturn);
}
}
public override bool RoleExists(string roleName)
{
try
{
SecUtility.CheckParameter(ref roleName, true, true, true, 255, "roleName");
}
catch
{
return(false);
}
StringBuilder strSQL = new StringBuilder("SELECT COUNT(id) FROM dbo.SiteRole WHERE RoleName = @rolename;");
SqlParameter[] parms = new SqlParameter[] {
new SqlParameter("@rolename", SqlDbType.VarChar, 50)
};
parms[0].Value = roleName;
return(SQLPlus.ExecuteScalar(CommandType.Text, strSQL.ToString(), parms).ToString() != "0");
}
public override void Initialize(string name, NameValueCollection config)
{
if (config == null)
{
throw new ArgumentNullException("config");
}
if (String.IsNullOrEmpty(name))
{
name = "SiteRoleProvider";
}
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "Role Provider for our site");
}
base.Initialize(name, config);
_AppName = config["applicationName"];
if (string.IsNullOrEmpty(_AppName))
{
_AppName = SecUtility.GetDefaultAppName();
}
if (_AppName.Length > 255)
{
throw new ProviderException("Provider application name too long, max is 255.");
}
config.Remove("connectionStringName");
config.Remove("applicationName");
config.Remove("description");
if (config.Count > 0)
{
string attribUnrecognized = config.GetKey(0);
if (!String.IsNullOrEmpty(attribUnrecognized))
{
throw new ProviderException("Provider unrecognized attribute: " + attribUnrecognized);
}
}
}
public override void AddUsersToRoles(string[] usernames, string[] roleNames)
{
SecUtility.CheckArrayParameter(ref roleNames, true, true, true, 255, "roleNames");
SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");
#region - T-SQL -
StringBuilder strSQL = new StringBuilder(BuildTable(usernames, roleNames));
strSQL.Append(" INSERT INTO MembersInRoles(RoleId,UserId)");
strSQL.Append(" SELECT r.id,u.UserId FROM @t AS t");
strSQL.Append(" INNER JOIN dbo.SiteRoles AS r ON t.rolename = r.RoleName");
strSQL.Append(" INNER JOIN dbo.Members AS u ON u.Username= t.username");
strSQL.Append(" WHERE NOT EXISTS");
strSQL.Append(" (");
strSQL.Append(" SELECT * FROM dbo.MembersInRoles AS t2");
strSQL.Append(" INNER JOIN dbo.SiteRoles AS r1 ON r1.id = t2.RoleId");
strSQL.Append(" WHERE u.UserId = t2.UserId");
strSQL.Append(" AND r1.GroupName = r.GroupName");
strSQL.Append(" )");
#endregion
SQLPlus.ExecuteNonQuery(CommandType.Text, strSQL.ToString());
}
