How to: Sample Role-Provider Implementation
Illustrates a role-provider implementation.
The following code illustrates a role-provider implementation. For more information, see Sample Role-Provider Implementation.
Example
Imports System.Web.Security
Imports System.Configuration.Provider
Imports System.Collections.Specialized
Imports System
Imports System.Data
Imports System.Data.Odbc
Imports System.Configuration
Imports System.Diagnostics
Imports System.Web
Imports System.Globalization
Imports Microsoft.VisualBasic
''' This provider works with the following schema for the tables of role data.' ' CREATE TABLE Roles' (' Rolename Text (255) NOT NULL,' ApplicationName Text (255) NOT NULL,' CONSTRAINT PKRoles PRIMARY KEY (Rolename, ApplicationName)' )'' CREATE TABLE UsersInRoles' (' Username Text (255) NOT NULL,' Rolename Text (255) NOT NULL,' ApplicationName Text (255) NOT NULL,' CONSTRAINT PKUsersInRoles PRIMARY KEY (Username, Rolename, ApplicationName)' )''Namespace Samples.AspNet.Roles
PublicNotInheritableClass OdbcRoleProvider
Inherits RoleProvider
' ' Global OdbcConnection, generated password length, generic exception message, event log info. 'Private conn As OdbcConnection
Private eventSource AsString = "OdbcRoleProvider"Private eventLog AsString = "Application"Private exceptionMessage AsString = "An exception occurred. Please check the Event Log."Private pConnectionStringSettings As ConnectionStringSettings
Private connectionString AsString
' ' If false, exceptions are Thrown to the caller. If true, ' exceptions are written to the event log. 'Private pWriteExceptionsToEventLog AsBoolean = FalsePublicProperty WriteExceptionsToEventLog AsBooleanGetReturn pWriteExceptionsToEventLog
EndGetSet
pWriteExceptionsToEventLog = value
EndSetEndProperty
' ' System.Configuration.Provider.ProviderBase.Initialize Method 'PublicOverridesSub Initialize(name AsString, config As NameValueCollection)
' ' Initialize values from web.config. 'If config IsNothingThen _
ThrowNew ArgumentNullException("config")
If name IsNothingOrElse name.Length = 0 Then _
name = "OdbcRoleProvider"IfString.IsNullOrEmpty(config("description")) Then
config.Remove("description")
config.Add("description", "Sample ODBC Role provider")
EndIf
' Initialize the abstract base class.MyBase.Initialize(name, config)
If config("applicationName") IsNothingOrElse config("applicationName").Trim() = ""Then
pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
Else
pApplicationName = config("applicationName")
EndIfIfNot config("writeExceptionsToEventLog") IsNothingThenIf config("writeExceptionsToEventLog").ToUpper() = "TRUE"Then
pWriteExceptionsToEventLog = true
EndIfEndIf
' ' Initialize OdbcConnection. '
pConnectionStringSettings = _
ConfigurationManager.ConnectionStrings(config("connectionStringName"))
If pConnectionStringSettings IsNothingOrElse pConnectionStringSettings.ConnectionString.Trim() = ""ThenThrowNew ProviderException("Connection string cannot be blank.")
EndIf
connectionString = pConnectionStringSettings.ConnectionString
EndSub
' ' System.Web.Security.RoleProvider properties. ' Private pApplicationName AsStringPublicOverridesProperty ApplicationName AsStringGetReturn pApplicationName
EndGetSet
pApplicationName = value
EndSetEndProperty
' ' System.Web.Security.RoleProvider methods. '
' ' RoleProvider.AddUsersToRoles 'PublicOverridesSub AddUsersToRoles(usernames AsString(), rolenames AsString())
ForEach rolename AsStringIn rolenames
IfNot RoleExists(rolename) ThenThrowNew ProviderException("Role name not found.")
EndIfNextForEach username AsStringIn usernames
If username.Contains(",") ThenThrowNew ArgumentException("User names cannot contain commas.")
EndIfForEach rolename AsStringIn rolenames
If IsUserInRole(username, rolename) ThenThrowNew ProviderException("User is already in role.")
EndIfNextNextDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO UsersInRoles " & _
" (Username, Rolename, ApplicationName) " & _
" Values(?, ?, ?)", conn)
Dim userParm As OdbcParameter = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255)
Dim roleParm As OdbcParameter = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255)
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction()
cmd.Transaction = tran
ForEach username AsStringIn usernames
ForEach rolename AsStringIn rolenames
userParm.Value = username
roleParm.Value = rolename
cmd.ExecuteNonQuery()
NextNext
tran.Commit()
Catch e As OdbcException
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "AddUsersToRoles")
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' RoleProvider.CreateRole 'PublicOverridesSub CreateRole(rolename AsString)
If rolename.Contains(",") ThenThrowNew ArgumentException("Role names cannot contain commas.")
EndIfIf RoleExists(rolename) ThenThrowNew ProviderException("Role name already exists.")
EndIfDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO Roles " & _
" (Rolename, ApplicationName) " & _
" Values(?, ?)", conn)
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "CreateRole")
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' RoleProvider.DeleteRole 'PublicOverridesFunction DeleteRole(ByVal rolename AsString, ByVal throwOnPopulatedRole AsBoolean) AsBooleanIfNot RoleExists(rolename) ThenThrowNew ProviderException("Role does not exist.")
EndIfIf throwOnPopulatedRole AndAlso GetUsersInRole(rolename).Length > 0 ThenThrowNew ProviderException("Cannot delete a populated role.")
EndIfDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM Roles " & _
" WHERE Rolename = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim cmd2 As OdbcCommand = New OdbcCommand("DELETE FROM UsersInRoles " & _
" WHERE Rolename = ? AND ApplicationName = ?", conn)
cmd2.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction()
cmd.Transaction = tran
cmd2.Transaction = tran
cmd2.ExecuteNonQuery()
cmd.ExecuteNonQuery()
tran.Commit()
Catch e As OdbcException
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "DeleteRole")
ReturnFalseElseThrow e
EndIfFinally
conn.Close()
EndTryReturnTrueEndFunction
' ' RoleProvider.GetAllRoles 'PublicOverridesFunction GetAllRoles() AsString()
Dim tmpRoleNames AsString = ""Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Rolename FROM Roles " & _
" WHERE ApplicationName = ?", conn)
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
DoWhile reader.Read()
tmpRoleNames &= reader.GetString(0) & ","LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetAllRoles")
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf tmpRoleNames.Length > 0 Then ' Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1)
Return tmpRoleNames.Split(CChar(","))
EndIfReturnNewString() {}
EndFunction
' ' RoleProvider.GetRolesForUser 'PublicOverridesFunction GetRolesForUser(ByVal username AsString) AsString()
Dim tmpRoleNames AsString = ""Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Rolename FROM UsersInRoles " & _
" WHERE Username = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
DoWhile reader.Read()
tmpRoleNames &= reader.GetString(0) & ","LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetRolesForUser")
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf tmpRoleNames.Length > 0 Then ' Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1)
Return tmpRoleNames.Split(CChar(","))
EndIfReturnNewString() {}
EndFunction
' ' RoleProvider.GetUsersInRole 'PublicOverridesFunction GetUsersInRole(ByVal rolename AsString) AsString()
Dim tmpUserNames AsString = ""Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username FROM UsersInRoles " & _
" WHERE Rolename = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
DoWhile reader.Read()
tmpUserNames &= reader.GetString(0) & ","LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "GetUsersInRole")
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf tmpUserNames.Length > 0 Then ' Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1)
Return tmpUserNames.Split(CChar(","))
EndIfReturnNewString() {}
EndFunction
' ' RoleProvider.IsUserInRole 'PublicOverridesFunction IsUserInRole(ByVal username AsString, ByVal rolename AsString) AsBooleanDim userIsInRole AsBoolean = FalseDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT COUNT(*) FROM UsersInRoles " & _
" WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Try
conn.Open()
Dim numRecs AsInteger = CType(cmd.ExecuteScalar(), Integer)
If numRecs > 0 Then
userIsInRole = TrueEndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "IsUserInRole")
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn userIsInRole
EndFunction
' ' RoleProvider.RemoveUsersFromRoles 'PublicOverridesSub RemoveUsersFromRoles(ByVal usernames AsString(), ByVal rolenames AsString())
ForEach rolename AsStringIn rolenames
IfNot RoleExists(rolename) ThenThrowNew ProviderException("Role name not found.")
EndIfNextForEach username AsStringIn usernames
ForEach rolename AsStringIn rolenames
IfNot IsUserInRole(username, rolename) ThenThrowNew ProviderException("User is not in role.")
EndIfNextNextDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM UsersInRoles " & _
" WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn)
Dim userParm As OdbcParameter = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255)
Dim roleParm As OdbcParameter = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255)
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Dim tran As OdbcTransaction = NothingTry
conn.Open()
tran = conn.BeginTransaction
cmd.Transaction = tran
ForEach username AsStringIn usernames
ForEach rolename AsStringIn rolenames
userParm.Value = username
roleParm.Value = rolename
cmd.ExecuteNonQuery()
NextNext
tran.Commit()
Catch e As OdbcException
Try
tran.Rollback()
CatchEndTryIf WriteExceptionsToEventLog Then
WriteToEventLog(e, "RemoveUsersFromRoles")
ElseThrow e
EndIfFinally
conn.Close()
EndTryEndSub
' ' RoleProvider.RoleExists 'PublicOverridesFunction RoleExists(ByVal rolename AsString) AsBooleanDim exists AsBoolean = FalseDim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT COUNT(*) FROM Roles " & _
" WHERE Rolename = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName
Try
conn.Open()
Dim numRecs AsInteger = CType(cmd.ExecuteScalar(), Integer)
If numRecs > 0 Then
exists = TrueEndIfCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "RoleExists")
ElseThrow e
EndIfFinally
conn.Close()
EndTryReturn exists
EndFunction
' ' RoleProvider.FindUsersInRole 'PublicOverridesFunction FindUsersInRole(ByVal rolename AsString, ByVal usernameToMatch AsString) AsString()
Dim conn As OdbcConnection = New OdbcConnection(connectionString)
Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username FROM UsersInRoles " & _
"WHERE Username LIKE ? AND RoleName = ? AND ApplicationName = ?", conn)
cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch
cmd.Parameters.Add("@RoleName", OdbcType.VarChar, 255).Value = rolename
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
Dim tmpUserNames AsString = ""Dim reader As OdbcDataReader = NothingTry
conn.Open()
reader = cmd.ExecuteReader()
DoWhile reader.Read()
tmpUserNames &= reader.GetString(0) & ","LoopCatch e As OdbcException
If WriteExceptionsToEventLog Then
WriteToEventLog(e, "FindUsersInRole")
ElseThrow e
EndIfFinallyIfNot reader IsNothingThen reader.Close()
conn.Close()
EndTryIf tmpUserNames.Length > 0 Then ' Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1)
Return tmpUserNames.Split(CChar(","))
EndIfReturnNewString() {}
EndFunction
' ' WriteToEventLog ' A helper function that writes exception detail to the event log. Exceptions ' are written to the event log as a security measure to aSub 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. 'PrivateSub WriteToEventLog(e As OdbcException, action AsString)
Dim log As EventLog = New EventLog()
log.Source = eventSource
log.Log = eventLog
Dim message AsString = exceptionMessage & vbCrLf & vbCrLf
message &= "Action: " & action & vbCrLf & vbCrLf
message &= "Exception: " & e.ToString()
log.WriteEnTry(message)
EndSubEndClassEndNamespace
using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
/*
This provider works with the following schema for the tables of role data.
CREATE TABLE Roles
(
Rolename Text (255) NOT NULL,
ApplicationName Text (255) NOT NULL,
CONSTRAINT PKRoles PRIMARY KEY (Rolename, ApplicationName)
)
CREATE TABLE UsersInRoles
(
Username Text (255) NOT NULL,
Rolename Text (255) NOT NULL,
ApplicationName Text (255) NOT NULL,
CONSTRAINT PKUsersInRoles PRIMARY KEY (Username, Rolename, ApplicationName)
)
*/namespace Samples.AspNet.Roles
{
publicsealedclass OdbcRoleProvider: RoleProvider
{
//// Global connection string, generic exception message, event log info.//privatestring eventSource = "OdbcRoleProvider";
privatestring eventLog = "Application";
privatestring exceptionMessage = "An exception occurred. Please check the Event Log.";
private ConnectionStringSettings pConnectionStringSettings;
privatestring connectionString;
//// If false, exceptions are thrown to the caller. If true,// exceptions are written to the event log.//privatebool pWriteExceptionsToEventLog = false;
publicbool WriteExceptionsToEventLog
{
get { return pWriteExceptionsToEventLog; }
set { pWriteExceptionsToEventLog = value; }
}
//// System.Configuration.Provider.ProviderBase.Initialize Method//publicoverridevoid Initialize(string name, NameValueCollection config)
{
//// Initialize values from web.config.//if (config == null)
thrownew ArgumentNullException("config");
if (name == null || name.Length == 0)
name = "OdbcRoleProvider";
if (String.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "Sample ODBC Role provider");
}
// 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 OdbcConnection.//
pConnectionStringSettings = ConfigurationManager.
ConnectionStrings[config["connectionStringName"]];
if (pConnectionStringSettings == null || pConnectionStringSettings.ConnectionString.Trim() == "")
{
thrownew ProviderException("Connection string cannot be blank.");
}
connectionString = pConnectionStringSettings.ConnectionString;
}
//// System.Web.Security.RoleProvider properties.//privatestring pApplicationName;
publicoverridestring ApplicationName
{
get { return pApplicationName; }
set { pApplicationName = value; }
}
//// System.Web.Security.RoleProvider methods.////// RoleProvider.AddUsersToRoles//publicoverridevoid AddUsersToRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
thrownew ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
if (username.Contains(","))
{
thrownew ArgumentException("User names cannot contain commas.");
}
foreach (string rolename in rolenames)
{
if (IsUserInRole(username, rolename))
{
thrownew ProviderException("User is already in role.");
}
}
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("INSERT INTO UsersInRoles " +
" (Username, Rolename, ApplicationName) " +
" Values(?, ?, ?)", conn);
OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255);
OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcTransaction 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 (OdbcException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "AddUsersToRoles");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// RoleProvider.CreateRole//publicoverridevoid CreateRole(string rolename)
{
if (rolename.Contains(","))
{
thrownew ArgumentException("Role names cannot contain commas.");
}
if (RoleExists(rolename))
{
thrownew ProviderException("Role name already exists.");
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("INSERT INTO Roles " +
" (Rolename, ApplicationName) " +
" Values(?, ?)", conn);
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "CreateRole");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// RoleProvider.DeleteRole//publicoverridebool DeleteRole(string rolename, bool throwOnPopulatedRole)
{
if (!RoleExists(rolename))
{
thrownew ProviderException("Role does not exist.");
}
if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
{
thrownew ProviderException("Cannot delete a populated role.");
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM Roles " +
" WHERE Rolename = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcCommand cmd2 = new OdbcCommand("DELETE FROM UsersInRoles " +
" WHERE Rolename = ? AND ApplicationName = ?", conn);
cmd2.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd2.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcTransaction tran = null;
try
{
conn.Open();
tran = conn.BeginTransaction();
cmd.Transaction = tran;
cmd2.Transaction = tran;
cmd2.ExecuteNonQuery();
cmd.ExecuteNonQuery();
tran.Commit();
}
catch (OdbcException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "DeleteRole");
returnfalse;
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
returntrue;
}
//// RoleProvider.GetAllRoles//publicoverridestring[] GetAllRoles()
{
string tmpRoleNames = "";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Rolename FROM Roles " +
" WHERE ApplicationName = ?", conn);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
tmpRoleNames += reader.GetString(0) + ",";
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetAllRoles");
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpRoleNames.Length > 0)
{
// Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
returnnewstring[0];
}
//// RoleProvider.GetRolesForUser//publicoverridestring[] GetRolesForUser(string username)
{
string tmpRoleNames = "";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Rolename FROM UsersInRoles " +
" WHERE Username = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
tmpRoleNames += reader.GetString(0) + ",";
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetRolesForUser");
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpRoleNames.Length > 0)
{
// Remove trailing comma.
tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
return tmpRoleNames.Split(',');
}
returnnewstring[0];
}
//// RoleProvider.GetUsersInRole//publicoverridestring[] GetUsersInRole(string rolename)
{
string tmpUserNames = "";
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Username FROM UsersInRoles " +
" WHERE Rolename = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
tmpUserNames += reader.GetString(0) + ",";
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "GetUsersInRole");
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpUserNames.Length > 0)
{
// Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
returnnewstring[0];
}
//// RoleProvider.IsUserInRole//publicoverridebool IsUserInRole(string username, string rolename)
{
bool userIsInRole = false;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM UsersInRoles " +
" WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
try
{
conn.Open();
int numRecs = (int)cmd.ExecuteScalar();
if (numRecs > 0)
{
userIsInRole = true;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "IsUserInRole");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return userIsInRole;
}
//// RoleProvider.RemoveUsersFromRoles//publicoverridevoid RemoveUsersFromRoles(string[] usernames, string[] rolenames)
{
foreach (string rolename in rolenames)
{
if (!RoleExists(rolename))
{
thrownew ProviderException("Role name not found.");
}
}
foreach (string username in usernames)
{
foreach (string rolename in rolenames)
{
if (!IsUserInRole(username, rolename))
{
thrownew ProviderException("User is not in role.");
}
}
}
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("DELETE FROM UsersInRoles " +
" WHERE Username = ? AND Rolename = ? AND ApplicationName = ?", conn);
OdbcParameter userParm = cmd.Parameters.Add("@Username", OdbcType.VarChar, 255);
OdbcParameter roleParm = cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255);
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
OdbcTransaction 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 (OdbcException e)
{
try
{
tran.Rollback();
}
catch { }
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RemoveUsersFromRoles");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
}
//// RoleProvider.RoleExists//publicoverridebool RoleExists(string rolename)
{
bool exists = false;
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM Roles " +
" WHERE Rolename = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@Rolename", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;
try
{
conn.Open();
int numRecs = (int)cmd.ExecuteScalar();
if (numRecs > 0)
{
exists = true;
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "RoleExists");
}
else
{
throw e;
}
}
finally
{
conn.Close();
}
return exists;
}
//// RoleProvider.FindUsersInRole//publicoverridestring[] FindUsersInRole(string rolename, string usernameToMatch)
{
OdbcConnection conn = new OdbcConnection(connectionString);
OdbcCommand cmd = new OdbcCommand("SELECT Username FROM UsersInRoles " +
"WHERE Username LIKE ? AND RoleName = ? AND ApplicationName = ?", conn);
cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch;
cmd.Parameters.Add("@RoleName", OdbcType.VarChar, 255).Value = rolename;
cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
string tmpUserNames = "";
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
tmpUserNames += reader.GetString(0) + ",";
}
}
catch (OdbcException e)
{
if (WriteExceptionsToEventLog)
{
WriteToEventLog(e, "FindUsersInRole");
}
else
{
throw e;
}
}
finally
{
if (reader != null) { reader.Close(); }
conn.Close();
}
if (tmpUserNames.Length > 0)
{
// Remove trailing comma.
tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1);
return tmpUserNames.Split(',');
}
returnnewstring[0];
}
//// 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.//privatevoid WriteToEventLog(OdbcException e, string action)
{
EventLog log = new EventLog();
log.Source = eventSource;
log.Log = eventLog;
string message = exceptionMessage + "\n\n";
message += "Action: " + action + "\n\n";
message += "Exception: " + e.ToString();
log.WriteEntry(message);
}
}
}
See Also
Concepts
Sample Role-Provider Implementation