本来打算写个SQLiteRolesProvider，但是写到差不多完成打算放弃。。改用SqlServer..这个东西放在这里存档。基本功能是可以使用咯。没有优化整理而已。
namespace God.Membership
{
public sealed class SQLiteRolesProvider : RoleProvider
{
#region Initialize
private string eventSource = "SQLiteRoleProvider";
private string eventLog = "Application";
private string exceptionMessage = "An exception occurred. Please check the Event Log.";
private Guid applicationId = Guid.Empty;
/// <summary>
/// The name of the application using the custom membership provider.
/// </summary>
public override string ApplicationName
{
get;set;
}
//
// If false, exceptions are thrown to the caller. If true,
// exceptions are written to the event log.
//
private bool pWriteExceptionsToEventLog = false;
public bool WriteExceptionsToEventLog
{
get { return pWriteExceptionsToEventLog; }
set { pWriteExceptionsToEventLog = value; }
}
//
// System.Configuration.Provider.ProviderBase.Initialize Method
//
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 = "SQLiteRolesProvider";
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "Sample SQLite Role provider");
}
// Initialize the abstract base class.
base.Initialize(name, config);
// Get application name
ApplicationName = config["applicationName"];
if (string.IsNullOrEmpty(ApplicationName))
ApplicationName = GetDefaultAppName();
if (ApplicationName.Length > 255)
throw new ProviderException("Provider application name is too long, max length is 255.");
// Get application ID
applicationId = GetApplicationID(ApplicationName, true);
if (applicationId.Equals(Guid.Empty))
throw new ProviderException("Provider application ID error.");
if (config["writeExceptionsToEventLog"] != null)
{
if (config["writeExceptionsToEventLog"].ToUpper() == "TRUE")
{
pWriteExceptionsToEventLog = true;
}
}
//
// Initialize SQLiteConnection.
//
//pConnectionStringSettings = ConfigurationManager.
// ConnectionStrings[config["connectionStringName"]];
//if (pConnectionStringSettings == null || pConnectionStringSettings.ConnectionString.Trim() == "")
//{
// throw new ProviderException("Connection string cannot be blank.");
//}
//connectionString = pConnectionStringSettings.ConnectionString;
}
#endregion
#region AddUsersToRoles
//
// System.Web.Security.RoleProvider methods.
//
//
// RoleProvider.AddUsersToRoles
//
public override void AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.IndexOf(',') > 0)
{
throw new ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
{
throw new ProviderException("User is already in role.");
}
}
}
string querystr = @"INSERT INTO aspnet_UsersInRoles(UserId,RoleId)
SELECT UserID,RoleId FROM aspnet_Roles r,aspnet_Users u,aspnet_Applications a
where a.ApplicationId = u.ApplicationId AND a.ApplicationId = r.ApplicationId AND a.ApplicationName=@ApplicationName AND r.RoleName = @RoleName AND u.UserName=@UserName";
SQLiteParameter[] parameters = { new SQLiteParameter("@Username", DbType.String, 255),
new SQLiteParameter("@Rolename", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[2].Value = ApplicationName;
SQLiteConnection connection = new SQLiteConnection(SQLiteHelper.ConnectionStringMembership);
connection.Open();
SQLiteTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
parameters[0].Value = username;
parameters[1].Value = rolename;
SQLiteHelper.ExecuteNonQuery(transaction, CommandType.Text, querystr, parameters);
}
}
transaction.Commit();
}
catch (SQLiteException e)
{
transaction.Rollback();
connection.Dispose();
throw e;
}
finally
{
connection.Dispose();
}
}
#endregion
#region CreateRole
//
// RoleProvider.CreateRole
//
public override void CreateRole(string rolename)
{
if (rolename.IndexOf(',') > 0)
{
throw new ArgumentException("Role names cannot contain commas.");
}
if (RoleExists(rolename))
{
throw new ProviderException("Role name already exists.");
}
//var applicationId = SQLiteApplication.EnsureApplicationExists(this.ApplicationName, this.connectionString);
string querystr = @"INSERT INTO aspnet_Roles (RoleId,Rolename,LoweredRoleName, ApplicationId)
SELECT @RoleId,@Rolename,@LoweredRoleName, ApplicationId
FROM aspnet_Applications WHERE ApplicationName=@ApplicationName";
SQLiteParameter[] parameters ={
new SQLiteParameter("@RoleId", DbType.String, 36),
new SQLiteParameter("@Rolename", DbType.String, 255),
new SQLiteParameter("@LoweredRoleName", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = Guid.NewGuid().ToString();
parameters[1].Value = rolename;
parameters[2].Value = rolename.ToLower();
parameters[3].Value = ApplicationName;
SQLiteHelper.ExecuteNonQuery(SQLiteHelper.ConnectionStringMembership,
CommandType.Text,
querystr,
parameters
);
}
#endregion
#region DeleteRole
//
// RoleProvider.DeleteRole
//
public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole >> GetUsersInRole(rolename).Length > 0)
{
throw new ProviderException("Cannot delete a populated role.");
}
string querystr = @"DELETE FROM aspnet_Roles
WHERE RoleId = (SELECT RoleId FROM aspnet_Applications a, aspnet_Roles r WHERE r.ApplicationId = a.ApplicationId AND a.ApplicationName=@ApplicationName AND r.RoleName = @RoleName)";
SQLiteParameter[] parameters ={
new SQLiteParameter("@Rolename", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = rolename;
parameters[1].Value = ApplicationName;
SQLiteConnection connection = new SQLiteConnection(SQLiteHelper.ConnectionStringMembership);
connection.Open();
SQLiteTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SQLiteHelper.ExecuteNonQuery(trans, CommandType.Text, "PRAGMA foreign_keys = ON", parameters);
SQLiteHelper.ExecuteNonQuery(trans, CommandType.Text, querystr, parameters);
}
catch (SQLiteException e)
{
trans.Rollback();
WriteToEventLog(e, "DeleteRole");
throw e;
}
finally
{
trans.Commit();
connection.Dispose();
}
return true;
}
#endregion
#region GetAllRoles
//
// RoleProvider.GetAllRoles
//
public override string[] GetAllRoles()
{
//StringBuilder tmpRoleNames = new StringBuilder();
List<string> tmpRoleNames = new List<string>();
string querystr = @"SELECT Rolename FROM aspnet_Roles r,aspnet_Applications a
WHERE r.ApplicationId = a.ApplicationId AND a.ApplicationName=@ApplicationName";
SQLiteParameter[] parameters = {
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = ApplicationName;
try
{
SQLiteDataReader reader = SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionStringMembership,CommandType.Text,querystr,parameters);
while (reader.Read())
{
tmpRoleNames.Add(reader.GetString(0));
}
reader.Dispose();
}
catch (SQLiteException e)
{
throw e;
}
if (tmpRoleNames.Count > 0)
{
// Remove trailing comma.
return tmpRoleNames.ToArray();
}
return new string[0];
}
#endregion
#region GetRolesForUser
//
// RoleProvider.GetRolesForUser
//
public override string[] GetRolesForUser(string username)
{
List<string> tmpRoleNames = new List<string>();
string query = @"SELECT Rolename FROM aspnet_Applications a,aspnet_Roles r,aspnet_Users u,aspnet_UsersInRoles uir
WHERE a.ApplicationId = r.ApplicationId AND a.ApplicationId = u.ApplicationId AND uir.UserId = u.UserId AND uir.RoleId = r.RoleId AND u.UserName =@UserName";
SQLiteParameter[] parameters = {
new SQLiteParameter("@Username", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = username;
parameters[1].Value = ApplicationName;
try
{
SQLiteDataReader reader = SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionStringMembership, CommandType.Text, query, parameters);
while (reader.Read())
{
tmpRoleNames.Add(reader.GetString(0));
}
reader.Dispose();
}
catch (SQLiteException e)
{
throw e;
}
if (tmpRoleNames.Count > 0)
{
return tmpRoleNames.ToArray();
}
return new string[0];
}
#endregion
#region GetUsersInRole
//
// RoleProvider.GetUsersInRole
//
public override string[] GetUsersInRole(string rolename)
{
List<string> tmpUserNames = new List<string>();
string query = @"SELECT UserName FROM aspnet_Applications a,aspnet_Roles r,aspnet_Users u,aspnet_UsersInRoles uir
WHERE a.ApplicationId = r.ApplicationId AND a.ApplicationId = u.ApplicationId AND uir.UserId = u.UserId AND uir.RoleId = r.RoleId AND r.RoleName =@RoleName";
SQLiteParameter[] parameters = {
new SQLiteParameter("@Rolename", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = rolename;
parameters[1].Value = ApplicationName;
try
{
SQLiteDataReader reader = SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionStringMembership, CommandType.Text, query, parameters);
while (reader.Read())
{
tmpUserNames.Add(reader.GetString(0));
}
reader.Dispose();
}
catch (SQLiteException e)
{
throw e;
}
if (tmpUserNames.Count > 0)
{
return tmpUserNames.ToArray();
}
return new string[0];
}
#endregion
#region IsUserInRole
//
// RoleProvider.IsUserInRole
//
public override bool IsUserInRole(string username, string rolename)
{
bool userIsInRole = false;
string query = @"SELECT COUNT(*) FROM aspnet_UsersInRoles
WHERE UserId = (SELECT UserId FROM aspnet_Applications a,aspnet_Users u WHERE a.ApplicationId = u.ApplicationId AND a.ApplicationName=@ApplicationName AND u.UserName=@UserName ) AND RoleId = (SELECT RoleId FROM aspnet_Applications a, aspnet_Roles r WHERE r.ApplicationId = a.ApplicationId AND a.ApplicationName=@ApplicationName AND r.RoleName = @RoleName)";
SQLiteParameter[] parameters = {
new SQLiteParameter("@Username", DbType.String, 255),
new SQLiteParameter("@Rolename", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
// cmd.Parameters.Add("@Username", DbType.String, 255).Value = username;
// cmd.Parameters.Add("@Rolename", DbType.String, 255).Value = rolename;
// cmd.Parameters.Add("@ApplicationName", DbType.String, 255).Value = ApplicationName;
parameters[0].Value = username;
parameters[1].Value = rolename;
parameters[2].Value = ApplicationName;
try
{
long numRecs = TypeConverter.StrToInt(
SQLiteHelper.ExecuteScalar(SQLiteHelper.ConnectionStringMembership,CommandType.Text,
query,
parameters)
);
if (numRecs > 0)
{
userIsInRole = true;
}
}
catch (SQLiteException e)
{
throw e;
}
return userIsInRole;
}
#endregion
#region RemoveUsersFromRoles
//
// RoleProvider.RemoveUsersFromRoles
//
public override void RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
throw new ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
{
throw new ProviderException("User is not in role.");
}
}
}
string query = @"DELETE FROM aspnet_UsersInRoles
WHERE UserId = (SELECT UserId FROM aspnet_Applications a,aspnet_Users u WHERE a.ApplicationId = u.ApplicationId AND a.ApplicationName=@ApplicationName AND u.UserName = @UserName)
AND RoleId = (SELECT RoleId FROM aspnet_Applications a,aspnet_Roles r WHERE a.ApplicationId = r.ApplicationId AND a.ApplicationName=@ApplicationName AND r.RoleName=@RoleName)";
SQLiteParameter[] parameters = {
new SQLiteParameter("@Username", DbType.String, 255),
new SQLiteParameter("@Rolename", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[2].Value = ApplicationName;
SQLiteConnection connection = new SQLiteConnection(SQLiteHelper.ConnectionStringMembership);
connection.Open();
SQLiteTransaction transactions = connection.BeginTransaction();
try
{
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
parameters[0].Value = username;
parameters[1].Value = rolename;
SQLiteHelper.ExecuteNonQuery(transactions, CommandType.Text, query, parameters);
}
}
transactions.Commit();
}
catch (SQLiteException e)
{
transactions.Rollback();
connection.Dispose();
throw e;
}
finally
{
connection.Dispose();
}
}
#endregion
#region RoleExists
//
// RoleProvider.RoleExists
//
/// <summary>
/// 权限是否存在
/// </summary>
/// <param name="rolename"></param>
/// <returns></returns>
public override bool RoleExists(string rolename)
{
bool exists = false;
string qurysrt = @"SELECT COUNT(*) FROM aspnet_Applications a,aspnet_Roles r
WHERE a.ApplicationId = r.ApplicationId AND Rolename = @RoleName AND ApplicationName = @ApplicationName";
SQLiteParameter[] parameters = { new SQLiteParameter("@RoleName", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = rolename;
parameters[1].Value = ApplicationName;
long numRecs = God.Common.TypeConverter.StrToInt(
SQLiteHelper.ExecuteScalar(SQLiteHelper.ConnectionStringMembership,
CommandType.Text,
qurysrt,
parameters
)
);
if (numRecs > 0)
{
exists = true;
}
return exists;
}
#endregion
#region FindUsersInRole
//
// RoleProvider.FindUsersInRole
//
public override string[] FindUsersInRole(string rolename, string usernameToMatch)
{
string query = @"SELECT UserName FROM aspnet_Applications a,aspnet_Roles r,aspnet_Users u,aspnet_UsersInRoles uir
WHERE a.ApplicationId = r.ApplicationId AND a.ApplicationId = u.ApplicationId AND uir.UserId = u.UserId AND uir.RoleId = r.RoleId AND r.RoleName =@RoleName AND u.UserName LIKE @UsernameSearch";
SQLiteParameter[] parameters = {
new SQLiteParameter("@UsernameSearch", DbType.String, 255),
new SQLiteParameter("@RoleName", DbType.String, 255),
new SQLiteParameter("@ApplicationName", DbType.String, 255)
};
parameters[0].Value = usernameToMatch;
parameters[1].Value = rolename;
parameters[2].Value = ApplicationName;
List<string> tmpUserNames = new List<string>();
try
{
SQLiteDataReader reader = SQLiteHelper.ExecuteReader(
SQLiteHelper.ConnectionStringMembership,
CommandType.Text,
query,
parameters
);
while (reader.Read())
{
tmpUserNames.Add(reader.GetString(0));
}
reader.Dispose();
}
catch (SQLiteException e)
{
throw e;
}
if (tmpUserNames.Count > 0)
{
return tmpUserNames.ToArray();
}
return new string[0];
}
#endregion
//
// WriteToEventLog
// A helper function that writes exception detail to the event log. Exceptions
// are written to the event log as a security measure to avoid private database
// details from being returned to the browser. If a method does not return a status
// or boolean indicating the action succeeded or failed, a generic exception is also
// thrown by the caller.
//
private void WriteToEventLog(SQLiteException e, string action)
{
EventLog log = new EventLog();
log.Source = eventSource;
log.Log = eventLog;
string message = exceptionMessage + "nn";
message += "Action: " + action + "nn";
message += "Exception: " + e.ToString();
log.WriteEntry(message);
}
private static string GetDefaultAppName()
{
string appName = System.Web.HttpRuntime.AppDomainAppVirtualPath;
if (string.IsNullOrEmpty(appName))
return "/";
return appName;
}
/// <summary>
///
/// </summary>
/// <param name="applicationName"></param>
/// <param name="createIfNeeded"></param>
/// <returns></returns>
private static Guid GetApplicationID(string applicationName, bool createIfNeeded)
{
string select = "SELECT ApplicationId FROM aspnet_Applications WHERE ApplicationName = @AppName";
string insert = "INSERT INTO aspnet_Applications (ApplicationId, ApplicationName) VALUES (@AppID, @AppName)";
SQLiteParameter[] parameters = {
new SQLiteParameter("@AppName", DbType.String, 255)};
parameters[0].Value = applicationName;
object lookupResult = SQLiteHelper.ExecuteScalar(SQLiteHelper.ConnectionStringMembership, CommandType.Text, select, parameters);
if (lookupResult != null)
return new Guid(lookupResult.ToString());
if (createIfNeeded)
{
Guid appId = Guid.NewGuid();
if (!appId.Equals(Guid.Empty))
{
SQLiteParameter[] _parameters = {new SQLiteParameter("@AppID", DbType.String, 36),
new SQLiteParameter("@AppName", DbType.String, 255)
};
_parameters[0].Value = appId.ToString();
_parameters[1].Value = applicationName;
if (SQLiteHelper.ExecuteNonQuery(SQLiteHelper.ConnectionStringMembership, CommandType.Text, insert, _parameters) == 1)
return appId;
}
}
return Guid.Empty;
}
}
}