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

  Public NotInheritable Class OdbcRoleProvider
    Inherits RoleProvider
  

    '
    ' Global OdbcConnection, generated password length, generic exception message, event log info.
    '

    Private conn As OdbcConnection 
    Private rolesTable As String = "Roles"
    Private usersInRolesTable As String = "UsersInRoles"

    Private eventSource As String = "OdbcRoleProvider"
    Private eventLog As String = "Application"
    Private exceptionMessage As String = "An exception occurred. Please check the Event Log."

    Private pConnectionStringSettings As ConnectionStringSettings
    Private connectionString As String


    '
    ' If false, exceptions are Thrown to the caller. If true,
    ' exceptions are written to the event log.
    '

    Private pWriteExceptionsToEventLog As Boolean = False

    Public Property WriteExceptionsToEventLog As Boolean
      Get
        Return pWriteExceptionsToEventLog
      End Get
      Set
        pWriteExceptionsToEventLog = value
      End Set
    End Property



    '
    ' System.Configuration.Provider.ProviderBase.Initialize Method
    '

    Public Overrides Sub Initialize(name As String, config As NameValueCollection)
    

      '
      ' Initialize values from web.config.
      '

      If config Is Nothing Then _
        Throw New ArgumentNullException("config")

      If name Is Nothing OrElse name.Length = 0 Then _
        name = "OdbcRoleProvider"

      If String.IsNullOrEmpty(config("description")) Then
        config.Remove("description")
        config.Add("description", "Sample ODBC Role provider")
      End If

      ' Initialize the abstract base class.
      MyBase.Initialize(name, config)


      If config("applicationName") Is Nothing OrElse config("applicationName").Trim() = "" Then
        pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
      Else
        pApplicationName = config("applicationName")
      End If


      If Not config("writeExceptionsToEventLog") Is Nothing Then      
        If config("writeExceptionsToEventLog").ToUpper() = "TRUE" Then
          pWriteExceptionsToEventLog = true
        End If
      End If


      '
      ' Initialize OdbcConnection.
      '

      pConnectionStringSettings = _
        ConfigurationManager.ConnectionStrings(config("connectionStringName"))

      If pConnectionStringSettings Is Nothing OrElse pConnectionStringSettings.ConnectionString.Trim() = "" Then
        Throw New ProviderException("Connection string cannot be blank.")
      End If

      connectionString = pConnectionStringSettings.ConnectionString
    End Sub



    '
    ' System.Web.Security.RoleProvider properties.
    '    

    Private pApplicationName As String

    Public Overrides Property ApplicationName As String
      Get
        Return pApplicationName
      End Get
      Set
        pApplicationName = value
      End Set
    End Property


    '
    ' System.Web.Security.RoleProvider methods.
    '

    '
    ' RoleProvider.AddUsersToRoles
    '

    Public Overrides Sub AddUsersToRoles(usernames As String(), rolenames As String())
    
      For Each rolename As String In rolenames
        If Not RoleExists(rolename) Then
          Throw New ProviderException("Role name not found.")
        End If
      Next

      For Each username As String In usernames      
        If username.IndexOf(",") > 0 Then        
          Throw New ArgumentException("User names cannot contain commas.")
        End If

        For Each rolename As String In rolenames        
          If IsUserInRole(username, rolename) Then          
            Throw New ProviderException("User is already in role.")
          End If
        Next
      Next


      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO [" & usersInRolesTable & "]" & _
              " (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 = Nothing

      Try      
        conn.Open()
        tran = conn.BeginTransaction()
        cmd.Transaction = tran

        For Each username As String In usernames
          For Each rolename As String In rolenames
            userParm.Value = username
            roleParm.Value = rolename
            cmd.ExecuteNonQuery()
          Next
        Next

        tran.Commit()
      Catch e As OdbcException
        Try
          tran.Rollback()
        Catch
        End Try


        If WriteExceptionsToEventLog Then        
          WriteToEventLog(e, "AddUsersToRoles")
        Else
          Throw e
        End If
      Finally
        conn.Close()      
      End Try
    End Sub


    '
    ' RoleProvider.CreateRole
    '

    Public Overrides Sub CreateRole(rolename As String)
     
      If rolename.IndexOf(",") > 0 Then
        Throw New ArgumentException("Role names cannot contain commas.")
      End If

      If RoleExists(rolename) Then      
        Throw New ProviderException("Role name already exists.")
      End If

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO [" & rolesTable & "]" & _
              " (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")
        Else
          Throw e
        End If
      Finally
        conn.Close()      
      End Try
    End Sub


    '
    ' RoleProvider.DeleteRole
    '

    Public Overrides Function DeleteRole(rolename As String, throwOnPopulatedRole As Boolean) As Boolean
    
      If Not RoleExists(rolename) Then      
        Throw New ProviderException("Role does not exist.")
      End If

      If throwOnPopulatedRole AndAlso GetUsersInRole(rolename).Length > 0 Then
        Throw New ProviderException("Cannot delete a populated role.")
      End If

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM [" & rolesTable & "]"  & _
              " 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 [" & usersInRolesTable & "]"  & _
              " 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 = Nothing

      Try
        conn.Open()
        tran = conn.BeginTransaction()
        cmd.Transaction = tran
        cmd2.Transaction = tran

        cmd2.ExecuteNonQuery()
        cmd.ExecuteNonQuery()

        tran.Commit()
      Catch e As OdbcException
        Try
          tran.Rollback()
        Catch
        End Try


        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "DeleteRole")

          Return False
        Else
          Throw e
        End If
      Finally
        conn.Close()      
      End Try

      Return True
    End Function


    '
    ' RoleProvider.GetAllRoles
    '

    Public Overrides Function GetAllRoles() As String()    
      Dim tmpRoleNames As String = ""

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("SELECT Rolename FROM [" & rolesTable & "]"  & _
                " WHERE ApplicationName = ?", conn)

      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName

      Dim reader As OdbcDataReader = Nothing

      Try
        conn.Open()

        reader = cmd.ExecuteReader()

        Do While reader.Read()
          tmpRoleNames &= reader.GetString(0) & ","
        Loop
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "GetAllRoles")
        Else
          Throw e
        End If
      Finally
        If Not reader Is Nothing Then reader.Close()
        conn.Close()      
      End Try

      If tmpRoleNames.Length > 0 Then
        ' Remove trailing comma.
        tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1)
        Return tmpRoleNames.Split(CChar(","))
      End If

      Return New String() {}
    End Function


    '
    ' RoleProvider.GetRolesForUser
    '

    Public Overrides Function GetRolesForUser(username As String) As String()
      Dim tmpRoleNames As String = ""

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("SELECT Rolename FROM [" & usersInRolesTable & "]"  & _
              " 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 = Nothing

      Try
        conn.Open()

        reader = cmd.ExecuteReader()

        Do While reader.Read()
          tmpRoleNames &= reader.GetString(0) & ","
        Loop
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "GetRolesForUser")
        Else
          Throw e
        End If
      Finally
        If Not reader Is Nothing Then reader.Close()
        conn.Close()      
      End Try

      If tmpRoleNames.Length > 0 Then
        ' Remove trailing comma.
        tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1)
        Return tmpRoleNames.Split(CChar(","))
      End If

      Return New String() {}
    End Function


    '
    ' RoleProvider.GetUsersInRole
    '

    Public Overrides Function GetUsersInRole(rolename As String) As String()
      Dim tmpUserNames As String = ""

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username FROM [" & usersInRolesTable & "]" & _
                " 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 = Nothing

      Try
        conn.Open()

        reader = cmd.ExecuteReader()

        Do While reader.Read()
          tmpUserNames &= reader.GetString(0) & ","
        Loop
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "GetUsersInRole")
        Else
          Throw e
        End If
      Finally
        If Not reader Is Nothing Then reader.Close()
        conn.Close()      
      End Try

      If tmpUserNames.Length > 0 Then
        ' Remove trailing comma.
        tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1)
        Return tmpUserNames.Split(CChar(","))
      End If

      Return New String() {}
    End Function


    '
    ' RoleProvider.IsUserInRole
    '

    Public Overrides Function IsUserInRole(username As String, rolename As String) As Boolean
    
      Dim userIsInRole As Boolean = False

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("SELECT COUNT(*) FROM [" & usersInRolesTable & "]"  & _
              " 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 As Integer = CType(cmd.ExecuteScalar(), Integer)

        If numRecs > 0 Then        
          userIsInRole = True
        End If
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "IsUserInRole")
        Else
          Throw e
        End If
      Finally
        conn.Close()      
      End Try

      Return userIsInRole
    End Function


    '
    ' RoleProvider.RemoveUsersFromRoles
    '

    Public Overrides Sub RemoveUsersFromRoles(usernames As String(), rolenames As String())
    
      For Each rolename As String In rolenames
        If Not RoleExists(rolename) Then        
          Throw New ProviderException("Role name not found.")
        End If
      Next

      For Each username As String In usernames
        For Each rolename As String In rolenames
          If Not IsUserInRole(username, rolename) Then
            Throw New ProviderException("User is not in role.")
          End If
        Next
      Next

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM [" & usersInRolesTable & "]"  & _
              " 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 = Nothing

      Try
        conn.Open()
        tran = conn.BeginTransaction
        cmd.Transaction = tran

        For Each username As String In usernames
          For Each rolename As String In rolenames
            userParm.Value = username
            roleParm.Value = rolename
            cmd.ExecuteNonQuery()
          Next
        Next
      
        tran.Commit()
      Catch e As OdbcException
        Try
          tran.Rollback()
        Catch
        End Try


        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "RemoveUsersFromRoles")
        Else
          Throw e
        End If
      Finally
        conn.Close()      
      End Try
    End Sub


    '
    ' RoleProvider.RoleExists
    '

    Public Overrides Function RoleExists(rolename As String) As Boolean
      Dim exists As Boolean = False

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("SELECT COUNT(*) FROM [" & rolesTable & "]"  & _
                " 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 As Integer = CType(cmd.ExecuteScalar(), Integer)

        If numRecs > 0 Then        
          exists = true
        End If
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "RoleExists")
        Else
          Throw e
        End If
      Finally
        conn.Close()      
      End Try

      Return exists
    End Function

    '
    ' RoleProvider.FindUsersInRole
    '

    Public Overrides Function FindUsersInRole(rolename As String, usernameToMatch As String) As String()
    
      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
      Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username FROM [" & usersInRolesTable & "] " & _
                "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 As String = ""
      Dim reader As OdbcDataReader = Nothing

      Try
        conn.Open()

        reader = cmd.ExecuteReader()

        Do While reader.Read()
          tmpUserNames &= reader.GetString(0) & ","
        Loop
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then
          WriteToEventLog(e, "FindUsersInRole")
        Else
          Throw e
        End If
      Finally
        If Not reader Is Nothing Then reader.Close()

        conn.Close()
      End Try

      If tmpUserNames.Length > 0 Then      
        ' Remove trailing comma.
        tmpUserNames = tmpUserNames.Substring(0, tmpUserNames.Length - 1)
        Return tmpUserNames.Split(CChar(","))
      End If

      Return New String() {}
    End Function

    '
    ' 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.
    '

    Private Sub WriteToEventLog(e As OdbcException, action As String)
      Dim log As EventLog = New EventLog()
      log.Source = eventSource
      log.Log = eventLog

      Dim message As String = exceptionMessage & vbCrLf & vbCrLf
      message &= "Action: " & action & vbCrLf & vbCrLf
      message &= "Exception: " & e.ToString()

      log.WriteEnTry(message)
    End Sub

  End Class
End Namespace

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
{

  public sealed class OdbcRoleProvider: RoleProvider
  {

    //
    // Global connection string, generic exception message, event log info.
    //

    private string rolesTable = "Roles";
    private string usersInRolesTable = "UsersInRoles";

    private string eventSource = "OdbcRoleProvider";
    private string eventLog = "Application";
    private string exceptionMessage = "An exception occurred. Please check the Event Log.";

    private ConnectionStringSettings pConnectionStringSettings;
    private string connectionString;


    //
    // 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 = "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() == "")
      {
        throw new ProviderException("Connection string cannot be blank.");
      }

      connectionString = pConnectionStringSettings.ConnectionString;
    }



    //
    // System.Web.Security.RoleProvider properties.
    //
    

    private string pApplicationName;


    public override string ApplicationName
    {
      get { return pApplicationName; }
      set { pApplicationName = value; }
    } 

    //
    // 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.");
          }
        }
      }


      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("INSERT INTO [" + usersInRolesTable + "]"  +
              " (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
    //

    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.");
      }

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("INSERT INTO [" + rolesTable + "]"  +
              " (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
    //

    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.");
      }

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("DELETE FROM [" + rolesTable + "]"  +
              " 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 [" + usersInRolesTable + "]"  +
              " 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");

          return false;
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();      
      }

      return true;
    }


    //
    // RoleProvider.GetAllRoles
    //

    public override string[] GetAllRoles()
    {
      string tmpRoleNames = "";

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Rolename FROM [" + rolesTable + "]"  +
                " 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(',');
      }

      return new string[0];
    }


    //
    // RoleProvider.GetRolesForUser
    //

    public override string[] GetRolesForUser(string username)
    {
      string tmpRoleNames = "";

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Rolename FROM [" + usersInRolesTable + "]"  +
              " 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(',');
      }

      return new string[0];
    }


    //
    // RoleProvider.GetUsersInRole
    //

    public override string[] GetUsersInRole(string rolename)
    {
      string tmpUserNames = "";

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Username FROM [" + usersInRolesTable + "]"  +
                " 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(',');
      }

      return new string[0];
    }


    //
    // RoleProvider.IsUserInRole
    //

    public override bool IsUserInRole(string username, string rolename)
    {
      bool userIsInRole = false;

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM [" + usersInRolesTable + "]"  +
              " 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
    //

    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.");
          }
        }
      }


      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("DELETE FROM [" + usersInRolesTable + "]"  +
              " 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
    //

    public override bool RoleExists(string rolename)
    {
      bool exists = false;

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT COUNT(*) FROM [" + rolesTable + "]"  +
                " 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
    //

    public override string[] FindUsersInRole(string rolename, string usernameToMatch)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Username FROM [" + usersInRolesTable + "] " +
                "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(',');
      }

      return new string[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.
    //

    private void 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);
    }

  }
}