Udostępnij za pośrednictwem


How to: Sample Membership Provider Implementation

Illustrates a complete ODBC-based membership provider.

The following code illustrates a complete ODBC-based membership provider.

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 System.Security.Cryptography
Imports System.Text
Imports System.Web.Configuration

'
' This provider works with the following schema for the table of user data.
' 
' CREATE TABLE Users
' (
'   PKID Guid NOT NULL PRIMARY KEY,
'   Username Text (255) NOT NULL,
'   ApplicationName Text (255) NOT NULL,
'   Email Text (128) NOT NULL,
'   Comment Text (255),
'   Password Text (128) NOT NULL,
'   PasswordQuestion Text (255),
'   PasswordAnswer Text (255),
'   IsApproved YesNo, 
'   LastActivityDate DateTime,
'   LastLoginDate DateTime,
'   LastPasswordChangedDate DateTime,
'   CreationDate DateTime, 
'   IsOnLine YesNo,
'   IsLockedOut YesNo,
'   LastLockedOutDate DateTime,
'   FailedPasswordAttemptCount Integer,
'   FailedPasswordAttemptWindowStart DateTime,
'   FailedPasswordAnswerAttemptCount Integer,
'   FailedPasswordAnswerAttemptWindowStart DateTime
' )
' 

Namespace Samples.AspNet.Membership


  Public NotInheritable Class OdbcMembershipProvider
    Inherits MembershipProvider

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

    Private newPasswordLength As Integer = 8
    Private eventSource As String = "OdbcMembershipProvider"
    Private eventLog As String = "Application"
    Private exceptionMessage As String = "An exception occurred. Please check the Event Log."
        Private connectionString As String

    '
    ' Used when determining encryption key values.
    '

    Private machineKey As MachineKeySection


    '
    ' If False, exceptions are thrown to the caller. If True,
    ' exceptions are written to the event log.
    '

    Private pWriteExceptionsToEventLog As Boolean

    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 = "OdbcMembershipProvider"

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

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


      pApplicationName            = GetConfigValue(config("applicationName"), _
                                      System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath)
      pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config("maxInvalidPasswordAttempts"), "5"))
      pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config("passwordAttemptWindow"), "10"))
      pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config("minRequiredAlphaNumericCharacters"), "1"))
      pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config("minRequiredPasswordLength"), "7"))
      pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config("passwordStrengthRegularExpression"), ""))
      pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config("enablePasswordReset"), "True"))
      pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config("enablePasswordRetrieval"), "True"))
      pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config("requiresQuestionAndAnswer"), "False"))
      pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config("requiresUniqueEmail"), "True"))
      pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config("writeExceptionsToEventLog"), "True"))

      Dim temp_format As String = config("passwordFormat")
      If temp_format Is Nothing Then      
        temp_format = "Hashed"
      End If

      Select Case temp_format      
        Case "Hashed"
          pPasswordFormat = MembershipPasswordFormat.Hashed
        Case "Encrypted"
          pPasswordFormat = MembershipPasswordFormat.Encrypted
        Case "Clear"
          pPasswordFormat = MembershipPasswordFormat.Clear
        Case Else
          Throw New ProviderException("Password format not supported.")
      End Select

      '
      ' Initialize OdbcConnection.
      '

      Dim ConnectionStringSettings As ConnectionStringSettings = _
        ConfigurationManager.ConnectionStrings(config("connectionStringName"))

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

      connectionString = COnnectionStringSettings.ConnectionString


      ' Get encryption and decryption key information from the configuration.
      Dim cfg As System.Configuration.Configuration = _
        WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath)
      machineKey = CType(cfg.GetSection("system.web/machineKey"), MachineKeySection)

      If machineKey.ValidationKey.Contains("AutoGenerate") Then _
        If PasswordFormat <> MembershipPasswordFormat.Clear Then _
          Throw New ProviderException("Hashed or Encrypted passwords " & _
                                      "are not supported with auto-generated keys.")
    End Sub


    '
    ' A helper function to retrieve config values from the configuration file.
    '

    Private Function GetConfigValue(configValue As String, defaultValue As String) As String
      If String.IsNullOrEmpty(configValue) Then _
        Return defaultValue

      Return configValue
    End Function


    '
    ' System.Web.Security.MembershipProvider properties.
    '


    Private pApplicationName           As String
    Private pEnablePasswordReset       As Boolean
    Private pEnablePasswordRetrieval   As Boolean
    Private pRequiresQuestionAndAnswer As Boolean
    Private pRequiresUniqueEmail       As Boolean
    Private pMaxInvalidPasswordAttempts  As Integer
    Private pPasswordAttemptWindow     As Integer
    Private pPasswordFormat            As MembershipPasswordFormat

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

    Public Overrides ReadOnly Property EnablePasswordReset As Boolean    
      Get
        Return pEnablePasswordReset
      End Get
    End Property


    Public Overrides ReadOnly Property EnablePasswordRetrieval As Boolean    
      Get
        Return pEnablePasswordRetrieval
      End Get
    End Property


    Public Overrides ReadOnly Property RequiresQuestionAndAnswer As Boolean    
      Get
        Return pRequiresQuestionAndAnswer
      End Get
    End Property


    Public Overrides ReadOnly Property RequiresUniqueEmail As Boolean    
      Get
        Return pRequiresUniqueEmail
      End Get
    End Property


    Public Overrides ReadOnly Property MaxInvalidPasswordAttempts As Integer
      Get
        Return pMaxInvalidPasswordAttempts
      End Get
    End Property


    Public Overrides ReadOnly Property PasswordAttemptWindow As Integer
      Get
        Return pPasswordAttemptWindow
      End Get
    End Property


    Public Overrides ReadOnly Property PasswordFormat As MembershipPasswordFormat
      Get
        Return pPasswordFormat
      End Get
    End Property

    Private pMinRequiredNonAlphanumericCharacters As Integer

    Public Overrides ReadOnly Property MinRequiredNonAlphanumericCharacters() As Integer
      Get
        Return pMinRequiredNonAlphanumericCharacters
      End Get
    End Property

    Private pMinRequiredPasswordLength As Integer

    Public Overrides ReadOnly Property MinRequiredPasswordLength() As Integer
      Get
        Return pMinRequiredPasswordLength
      End Get
    End Property

    Private pPasswordStrengthRegularExpression As String

    Public Overrides ReadOnly Property PasswordStrengthRegularExpression() As String
      Get
        Return pPasswordStrengthRegularExpression
      End Get
    End Property

    '
    ' System.Web.Security.MembershipProvider methods.
    '

    '
    ' MembershipProvider.ChangePassword
    '

    Public Overrides Function ChangePassword(username As String, _
                                             oldPwd As String, _
                                             newPwd As String) As Boolean    
      If Not ValidateUser(username, oldPwd) Then _
        Return False


      Dim args As ValidatePasswordEventArgs = _
        New ValidatePasswordEventArgs(username, newPwd, True)

      OnValidatingPassword(args)

      If args.Cancel Then
        If Not args.FailureInformation Is Nothing Then
          Throw args.FailureInformation
        Else
          Throw New ProviderException("Change password canceled due to New password validation failure.")
        End If
      End If


      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
              " SET Password = ?, LastPasswordChangedDate = ? " & _
              " WHERE Username = ? AND ApplicationName = ?", conn)

      cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPwd)
      cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now
      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName


      Dim rowsAffected As Integer = 0

      Try
        conn.Open()

        rowsAffected = cmd.ExecuteNonQuery()
      Catch e As OdbcException
        If WriteExceptionsToEventLog Then

          WriteToEventLog(e, "ChangePassword")

          Throw New ProviderException(exceptionMessage)
        Else
          Throw e
        End If
      Finally      
        conn.Close()
      End Try

      If rowsAffected > 0 Then      
        Return True
      End If

      Return False
    End Function



    '
    ' MembershipProvider.ChangePasswordQuestionAndAnswer
    '

    Public Overrides Function ChangePasswordQuestionAndAnswer(username As String, _
                  password As String, _
                  newPwdQuestion As String, _
                  newPwdAnswer As String) As Boolean

      If Not ValidateUser(username, password) Then _
        Return False

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
                    " SET PasswordQuestion = ?, PasswordAnswer = ?" & _
                    " WHERE Username = ? AND ApplicationName = ?", conn)

            cmd.Parameters.Add("@Question", OdbcType.VarChar, 255).Value = newPwdQuestion
            cmd.Parameters.Add("@Answer", OdbcType.VarChar, 255).Value = EncodePassword(newPwdAnswer)
            cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName


            Dim rowsAffected As Integer = 0

            Try
                conn.Open()

                rowsAffected = cmd.ExecuteNonQuery()
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "ChangePasswordQuestionAndAnswer")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try

            If rowsAffected > 0 Then
                Return True
            End If

            Return False
        End Function



        '
        ' MembershipProvider.CreateUser
        '

        Public Overrides Function CreateUser(ByVal username As String, _
        ByVal password As String, _
        ByVal email As String, _
        ByVal passwordQuestion As String, _
        ByVal passwordAnswer As String, _
        ByVal isApproved As Boolean, _
        ByVal providerUserKey As Object, _
                 ByRef status As MembershipCreateStatus) As MembershipUser

            Dim Args As ValidatePasswordEventArgs = _
              New ValidatePasswordEventArgs(username, password, True)

            OnValidatingPassword(args)

            If args.Cancel Then
                status = MembershipCreateStatus.InvalidPassword
                Return Nothing
            End If


            If RequiresUniqueEmail AndAlso GetUserNameByEmail(email) <> "" Then
                status = MembershipCreateStatus.DuplicateEmail
                Return Nothing
            End If

            Dim u As MembershipUser = GetUser(username, False)

            If u Is Nothing Then
                Dim createDate As DateTime = DateTime.Now

                If providerUserKey Is Nothing Then
                    providerUserKey = Guid.NewGuid()
                Else
                    If Not TypeOf providerUserKey Is Guid Then
                        status = MembershipCreateStatus.InvalidProviderUserKey
                        Return Nothing
                    End If
                End If

                Dim conn As OdbcConnection = New OdbcConnection(connectionString)
                Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO Users " & _
                       " (PKID, Username, Password, Email, PasswordQuestion, " & _
                       " PasswordAnswer, IsApproved," & _
                       " Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," & _
                       " ApplicationName, IsLockedOut, LastLockedOutDate," & _
                       " FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " & _
                       " FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)" & _
                       " Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn)

                cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey
                cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(password)
                cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email
                cmd.Parameters.Add("@PasswordQuestion", OdbcType.VarChar, 255).Value = passwordQuestion
                cmd.Parameters.Add("@PasswordAnswer", OdbcType.VarChar, 255).Value = EncodePassword(passwordAnswer)
                cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = isApproved
                cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = ""
                cmd.Parameters.Add("@CreationDate", OdbcType.DateTime).Value = createDate
                cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = createDate
                cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = createDate
                cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName
                cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = False
                cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = createDate
                cmd.Parameters.Add("@FailedPasswordAttemptCount", OdbcType.Int).Value = 0
                cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", OdbcType.DateTime).Value = createDate
                cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", OdbcType.Int).Value = 0
                cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", OdbcType.DateTime).Value = createDate

                Try
                    conn.Open()

                    Dim recAdded As Integer = cmd.ExecuteNonQuery()

                    If recAdded > 0 Then
                        status = MembershipCreateStatus.Success
                    Else
                        status = MembershipCreateStatus.UserRejected
                    End If
                Catch e As OdbcException
                    If WriteExceptionsToEventLog Then
                        WriteToEventLog(e, "CreateUser")
                    End If

                    status = MembershipCreateStatus.ProviderError
                Finally
                    conn.Close()
                End Try


                Return GetUser(username, False)
            Else
                status = MembershipCreateStatus.DuplicateUserName
            End If

            Return Nothing
        End Function



    '
    ' MembershipProvider.DeleteUser
    '

    Public Overrides Function DeleteUser(username As String, _
                                         deleteAllRelatedData As Boolean) As Boolean

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("DELETE FROM Users " & _
                    " WHERE Username = ? AND Applicationname = ?", conn)

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

            Dim rowsAffected As Integer = 0

            Try
                conn.Open()

                rowsAffected = cmd.ExecuteNonQuery()

                If deleteAllRelatedData Then
                    ' Process commands to delete all data for the user in the database.
                End If
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "DeleteUser")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try

            If rowsAffected > 0 Then _
              Return True

            Return False
        End Function



        '
        ' MembershipProvider.GetAllUsers
        '

        Public Overrides Function GetAllUsers(ByVal pageIndex As Integer, _
        ByVal pageSize As Integer, _
                                              ByRef totalRecords As Integer) _
                                  As MembershipUserCollection

            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users  " & _
                                              "WHERE ApplicationName = ?", conn)
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName

            Dim users As MembershipUserCollection = New MembershipUserCollection()

            Dim reader As OdbcDataReader = Nothing
            totalRecords = 0

            Try
                conn.Open()
                totalRecords = CInt(cmd.ExecuteScalar())

                If totalRecords <= 0 Then Return users

                cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
                         " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
                         " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
                         " FROM Users  " & _
                         " WHERE ApplicationName = ? " & _
                         " ORDER BY Username Asc"

                reader = cmd.ExecuteReader()

                Dim counter As Integer = 0
                Dim startIndex As Integer = pageSize * pageIndex
                Dim endIndex As Integer = startIndex + pageSize - 1

                Do While reader.Read()
                    If counter >= startIndex Then
                        Dim u As MembershipUser = GetUserFromReader(reader)
                        users.Add(u)
                    End If

                    If counter >= endIndex Then cmd.Cancel()

                    counter += 1
                Loop
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetAllUsers")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()
                conn.Close()
            End Try

            Return users
        End Function


        '
        ' MembershipProvider.GetNumberOfUsersOnline
        '

        Public Overrides Function GetNumberOfUsersOnline() As Integer

            Dim onlineSpan As TimeSpan = New TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0)
            Dim compareTime As DateTime = DateTime.Now.Subtract(onlineSpan)

            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users " & _
                    " WHERE LastActivityDate > ? AND ApplicationName = ?", conn)

            cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = compareTime
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

            Dim numOnline As Integer = 0

            Try
                conn.Open()

                numOnline = CInt(cmd.ExecuteScalar())
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetNumberOfUsersOnline")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try

            Return numOnline
        End Function



    '
    ' MembershipProvider.GetPassword
    '

    Public Overrides Function GetPassword(username As String, answer As String) As String

      If Not EnablePasswordRetrieval Then      
        Throw New ProviderException("Password Retrieval Not Enabled.")
      End If

      If PasswordFormat = MembershipPasswordFormat.Hashed Then      
        Throw New ProviderException("Cannot retrieve Hashed passwords.")
      End If

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Password, PasswordAnswer, IsLockedOut FROM Users " & _
                  " WHERE Username = ? AND ApplicationName = ?", conn)

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

            Dim password As String = ""
            Dim passwordAnswer As String = ""
            Dim reader As OdbcDataReader = Nothing

            Try
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()

                    If reader.GetBoolean(2) Then _
                      Throw New MembershipPasswordException("The supplied user is locked out.")

                    password = reader.GetString(0)
                    passwordAnswer = reader.GetString(1)
                Else
                    Throw New MembershipPasswordException("The supplied user name is not found.")
                End If
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetPassword")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()
                conn.Close()
            End Try


            If RequiresQuestionAndAnswer AndAlso Not CheckPassword(answer, passwordAnswer) Then
                UpdateFailureCount(username, "passwordAnswer")

                Throw New MembershipPasswordException("Incorrect password answer.")
            End If


            If PasswordFormat = MembershipPasswordFormat.Encrypted Then
                password = UnEncodePassword(password)
            End If

            Return password
        End Function



        '
        ' MembershipProvider.GetUser(String, Boolean)
        '

        Public Overrides Function GetUser(ByVal username As String, _
        ByVal userIsOnline As Boolean) As MembershipUser

            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," & _
                  " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
                  " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" & _
                  " FROM Users  WHERE Username = ? AND ApplicationName = ?", conn)

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

            Dim u As MembershipUser = Nothing
            Dim reader As OdbcDataReader = Nothing

            Try
                conn.Open()

                reader = cmd.ExecuteReader()

                If reader.HasRows Then
                    reader.Read()
                    u = GetUserFromReader(reader)

                    If userIsOnline Then
                        Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users  " & _
                                  "SET LastActivityDate = ? " & _
                                  "WHERE Username = ? AND Applicationname = ?", conn)

                        updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now
                        updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                        updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

                        updateCmd.ExecuteNonQuery()
                    End If
                End If
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetUser(String, Boolean)")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()

                conn.Close()
            End Try

            Return u
        End Function


        '
        ' MembershipProvider.GetUser(Object, Boolean)
        '

        Public Overrides Function GetUser(ByVal providerUserKey As Object, _
        ByVal userIsOnline As Boolean) As MembershipUser

            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," & _
                  " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
                  " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" & _
                  " FROM Users  WHERE PKID = ?", conn)

            cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey

            Dim u As MembershipUser = Nothing
            Dim reader As OdbcDataReader = Nothing

            Try
                conn.Open()

                reader = cmd.ExecuteReader()

                If reader.HasRows Then
                    reader.Read()
                    u = GetUserFromReader(reader)

                    If userIsOnline Then
                        Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users  " & _
                                  "SET LastActivityDate = ? " & _
                                  "WHERE PKID = ?", conn)

                        updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now
                        updateCmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey

                        updateCmd.ExecuteNonQuery()
                    End If
                End If
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetUser(Object, Boolean)")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()

                conn.Close()
            End Try

            Return u
        End Function


        '
        ' GetUserFromReader
        '    A helper function that takes the current row from the OdbcDataReader
        ' and hydrates a MembershiUser from the values. Called by the 
        ' MembershipUser.GetUser implementation.
        '

        Private Function GetUserFromReader(ByVal reader As OdbcDataReader) As MembershipUser
            Dim providerUserKey As Object = reader.GetValue(0)
            Dim username As String = reader.GetString(1)
            Dim email As String = reader.GetString(2)

            Dim passwordQuestion As String = ""
            If Not reader.GetValue(3) Is DBNull.Value Then _
              passwordQuestion = reader.GetString(3)

            Dim comment As String = ""
            If Not reader.GetValue(4) Is DBNull.Value Then _
              comment = reader.GetString(4)

            Dim isApproved As Boolean = reader.GetBoolean(5)
            Dim isLockedOut As Boolean = reader.GetBoolean(6)
            Dim creationDate As DateTime = reader.GetDateTime(7)

            Dim lastLoginDate As DateTime = New DateTime()
            If Not reader.GetValue(8) Is DBNull.Value Then _
              lastLoginDate = reader.GetDateTime(8)

            Dim lastActivityDate As DateTime = reader.GetDateTime(9)
            Dim lastPasswordChangedDate As DateTime = reader.GetDateTime(10)

            Dim lastLockedOutDate As DateTime = New DateTime()
            If Not reader.GetValue(11) Is DBNull.Value Then _
              lastLockedOutDate = reader.GetDateTime(11)

            Dim u As MembershipUser = New MembershipUser(Me.Name, _
                                                  username, _
                                                  providerUserKey, _
                                                  email, _
                                                  passwordQuestion, _
                                                  comment, _
                                                  isApproved, _
                                                  isLockedOut, _
                                                  creationDate, _
                                                  lastLoginDate, _
                                                  lastActivityDate, _
                                                  lastPasswordChangedDate, _
                                                  lastLockedOutDate)

            Return u
        End Function


        '
        ' MembershipProvider.UnlockUser
        '

        Public Overrides Function UnlockUser(ByVal username As String) As Boolean
            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users  " & _
                                              " SET IsLockedOut = False, LastLockedOutDate = ? " & _
                                              " WHERE Username = ? AND ApplicationName = ?", conn)

            cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now
            cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

            Dim rowsAffected As Integer = 0

            Try
                conn.Open()

                rowsAffected = cmd.ExecuteNonQuery()
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "UnlockUser")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try

            If rowsAffected > 0 Then _
              Return True

            Return False
        End Function


    '
    ' MembershipProvider.GetUserNameByEmail
    '

    Public Overrides Function GetUserNameByEmail(email As String) As String    
      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Username" & _
                  " FROM Users  WHERE Email = ? AND ApplicationName = ?", conn)

            cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

            Dim username As String = ""

            Try
                conn.Open()

                username = cmd.ExecuteScalar().ToString()
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetUserNameByEmail")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try

            If username Is Nothing Then _
              username = ""

            Return username
        End Function




        '
        ' MembershipProvider.ResetPassword
        '

        Public Overrides Function ResetPassword(ByVal username As String, ByVal answer As String) As String

            If Not EnablePasswordReset Then
                Throw New NotSupportedException("Password Reset is not enabled.")
            End If

            If answer Is Nothing AndAlso RequiresQuestionAndAnswer Then
                UpdateFailureCount(username, "passwordAnswer")

                Throw New ProviderException("Password answer required for password Reset.")
            End If

            Dim newPassword As String = _
              System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters)


            Dim Args As ValidatePasswordEventArgs = _
              New ValidatePasswordEventArgs(username, newPassword, True)

            OnValidatingPassword(args)

            If args.Cancel Then
                If Not args.FailureInformation Is Nothing Then
                    Throw args.FailureInformation
                Else
                    Throw New MembershipPasswordException("Reset password canceled due to password validation failure.")
                End If
            End If


            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT PasswordAnswer, IsLockedOut FROM Users " & _
                  " WHERE Username = ? AND ApplicationName = ?", conn)

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

            Dim rowsAffected As Integer = 0
            Dim passwordAnswer As String = ""
            Dim reader As OdbcDataReader = Nothing

            Try
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()

                    If reader.GetBoolean(1) Then _
                      Throw New MembershipPasswordException("The supplied user is locked out.")

                    passwordAnswer = reader.GetString(0)
                Else
                    Throw New MembershipPasswordException("The supplied user name is not found.")
                End If

                If RequiresQuestionAndAnswer AndAlso Not CheckPassword(answer, passwordAnswer) Then
                    UpdateFailureCount(username, "passwordAnswer")

                    Throw New MembershipPasswordException("Incorrect password answer.")
                End If

                Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
                    " SET Password = ?, LastPasswordChangedDate = ?" & _
                    " WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn)

                updateCmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPassword)
                updateCmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now
                updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

                rowsAffected = updateCmd.ExecuteNonQuery()
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "ResetPassword")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()
                conn.Close()
            End Try

            If rowsAffected > 0 Then
                Return newPassword
            Else
                Throw New MembershipPasswordException("User not found, or user is locked out. Password not Reset.")
            End If
        End Function


        '
        ' MembershipProvider.UpdateUser
        '

        Public Overrides Sub UpdateUser(ByVal user As MembershipUser)

            Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("UPDATE Users " & _
                    " SET Email = ?, Comment = ?," & _
                    " IsApproved = ?" & _
                    " WHERE Username = ? AND ApplicationName = ?", conn)

            cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = user.Email
            cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = user.Comment
            cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = user.IsApproved
            cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = user.UserName
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName


            Try
                conn.Open()

                cmd.ExecuteNonQuery()
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "UpdateUser")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                conn.Close()
            End Try
        End Sub


    '
    ' MembershipProvider.ValidateUser
    '

    Public Overrides Function ValidateUser(username As String, password As String) As Boolean
      Dim isValid As Boolean = False

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Password, IsApproved FROM Users " & _
                    " WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn)

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

            Dim reader As OdbcDataReader = Nothing
            Dim isApproved As Boolean = False
            Dim pwd As String = ""

            Try
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()
                    pwd = reader.GetString(0)
                    isApproved = reader.GetBoolean(1)
                Else
                    Return False
                End If

                reader.Close()

                If CheckPassword(password, pwd) Then
                    If isApproved Then
                        isValid = True

                        Dim updateCmd As OdbcCommand = New OdbcCommand("UPDATE Users  SET LastLoginDate = ?" & _
                                                                " WHERE Username = ? AND ApplicationName = ?", conn)

                        updateCmd.Parameters.Add("@LastLoginDate", OdbcType.DateTime).Value = DateTime.Now
                        updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                        updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

                        updateCmd.ExecuteNonQuery()
                    End If
                Else
                    conn.Close()

                    UpdateFailureCount(username, "password")
                End If
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "ValidateUser")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()
                conn.Close()
            End Try

      Return isValid
    End Function


    '
    ' UpdateFailureCount
    '   A helper method that performs the checks and updates associated with
    ' password failure tracking.
    '

    Private Sub UpdateFailureCount(username As String, failureType As String)

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT FailedPasswordAttemptCount, " & _
                                              "  FailedPasswordAttemptWindowStart, " & _
                                              "  FailedPasswordAnswerAttemptCount, " & _
                                              "  FailedPasswordAnswerAttemptWindowStart " & _
                                              "  FROM Users  " & _
                                              "  WHERE Username = ? AND ApplicationName = ?", conn)

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

            Dim reader As OdbcDataReader = Nothing
            Dim windowStart As DateTime = New DateTime()
            Dim failureCount As Integer = 0

            Try
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()

                    If failureType = "password" Then
                        failureCount = reader.GetInt32(0)
                        windowStart = reader.GetDateTime(1)
                    End If

                    If failureType = "passwordAnswer" Then
                        failureCount = reader.GetInt32(2)
                        windowStart = reader.GetDateTime(3)
                    End If
                End If

                reader.Close()

                Dim windowEnd As DateTime = windowStart.AddMinutes(PasswordAttemptWindow)

                If failureCount = 0 OrElse DateTime.Now > windowEnd Then
                    ' First password failure or outside of PasswordAttemptWindow. 
                    ' Start a New password failure count from 1 and a New window starting now.

                    If failureType = "password" Then _
                      cmd.CommandText = "UPDATE Users  " & _
                                        "  SET FailedPasswordAttemptCount = ?, " & _
                                        "      FailedPasswordAttemptWindowStart = ? " & _
                                        "  WHERE Username = ? AND ApplicationName = ?"

                    If failureType = "passwordAnswer" Then _
                      cmd.CommandText = "UPDATE Users  " & _
                                        "  SET FailedPasswordAnswerAttemptCount = ?, " & _
                                        "      FailedPasswordAnswerAttemptWindowStart = ? " & _
                                        "  WHERE Username = ? AND ApplicationName = ?"

                    cmd.Parameters.Clear()

                    cmd.Parameters.Add("@Count", OdbcType.Int).Value = 1
                    cmd.Parameters.Add("@WindowStart", OdbcType.DateTime).Value = DateTime.Now
                    cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                    cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

                    If cmd.ExecuteNonQuery() < 0 Then _
                      Throw New ProviderException("Unable to update failure count and window start.")
                Else
                    failureCount += 1

                    If failureCount >= MaxInvalidPasswordAttempts Then
                        ' Password attempts have exceeded the failure threshold. Lock out
                        ' the user.

                        cmd.CommandText = "UPDATE Users  " & _
                                          "  SET IsLockedOut = ?, LastLockedOutDate = ? " & _
                                          "  WHERE Username = ? AND ApplicationName = ?"

                        cmd.Parameters.Clear()

                        cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = True
                        cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now
                        cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                        cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

                        If cmd.ExecuteNonQuery() < 0 Then _
                          Throw New ProviderException("Unable to lock out user.")
                    Else
                        ' Password attempts have not exceeded the failure threshold. Update
                        ' the failure counts. Leave the window the same.

                        If failureType = "password" Then _
                          cmd.CommandText = "UPDATE Users  " & _
                                            "  SET FailedPasswordAttemptCount = ?" & _
                                            "  WHERE Username = ? AND ApplicationName = ?"

                        If failureType = "passwordAnswer" Then _
                          cmd.CommandText = "UPDATE Users  " & _
                                            "  SET FailedPasswordAnswerAttemptCount = ?" & _
                                            "  WHERE Username = ? AND ApplicationName = ?"

                        cmd.Parameters.Clear()

                        cmd.Parameters.Add("@Count", OdbcType.Int).Value = failureCount
                        cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username
                        cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

                        If cmd.ExecuteNonQuery() < 0 Then _
                          Throw New ProviderException("Unable to update failure count.")
                    End If
                End If
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "UpdateFailureCount")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()
                conn.Close()
            End Try
    End Sub


    '
    ' CheckPassword
    '   Compares password values based on the MembershipPasswordFormat.
    '

    Private Function CheckPassword(password As String, dbpassword As String) As Boolean    
      Dim pass1 As String = password
      Dim pass2 As String = dbpassword

      Select Case PasswordFormat      
        Case MembershipPasswordFormat.Encrypted
          pass2 = UnEncodePassword(dbpassword)
        Case MembershipPasswordFormat.Hashed
          pass1 = EncodePassword(password)
        Case Else
      End Select

      If pass1 = pass2 Then     
        Return True
      End If

      Return False
    End Function


    '
    ' EncodePassword
    '   Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
    '

    Private Function EncodePassword(password As String) As String
      Dim encodedPassword As String = password

      Select Case PasswordFormat
        Case MembershipPasswordFormat.Clear

        Case MembershipPasswordFormat.Encrypted
          encodedPassword = _
            Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)))
        Case MembershipPasswordFormat.Hashed
          Dim hash As HMACSHA1 = New HMACSHA1()
          hash.Key = HexToByte(machineKey.ValidationKey)
          encodedPassword = _
            Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)))
        Case Else
          Throw New ProviderException("Unsupported password format.")
      End Select

      Return encodedPassword
    End Function


    '
    ' UnEncodePassword
    '   Decrypts or leaves the password clear based on the PasswordFormat.
    '

    Private Function UnEncodePassword(encodedPassword As String) As String
      Dim password As String = encodedPassword

      Select Case PasswordFormat
        Case MembershipPasswordFormat.Clear

        Case MembershipPasswordFormat.Encrypted
          password = _
            Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)))
        Case MembershipPasswordFormat.Hashed
          Throw New ProviderException("Cannot unencode a hashed password.")
        Case Else
          throw new ProviderException("Unsupported password format.")
      End Select

      Return password
    End Function

    '
    ' HexToByte
    '   Converts a hexadecimal string to a byte array. Used to convert encryption
    ' key values from the configuration.
    '

    Private Function HexToByte(hexString As String) As Byte()
      Dim ReturnBytes((hexString.Length \ 2) - 1) As Byte
      For i As Integer = 0 To ReturnBytes.Length - 1
        ReturnBytes(i) = Convert.ToByte(hexString.Substring(i*2, 2), 16)
      Next
      Return ReturnBytes
    End Function


    '
    ' MembershipProvider.FindUsersByName
    '

    Public Overrides Function FindUsersByName(usernameToMatch As String, _
                                              pageIndex As Integer, _
                                              pageSize As Integer, _
                                              ByRef totalRecords As Integer) _
                              As MembershipUserCollection

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users  " & _
                      "WHERE Username LIKE ? AND ApplicationName = ?", conn)
            cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName

            Dim users As MembershipUserCollection = New MembershipUserCollection()

            Dim reader As OdbcDataReader = Nothing

            Try
                conn.Open()
                totalRecords = CInt(cmd.ExecuteScalar())

                If totalRecords <= 0 Then Return users

                cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
                  " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
                  " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
                  " FROM Users  " & _
                  " WHERE Username LIKE ? AND ApplicationName = ? " & _
                  " ORDER BY Username Asc"

                reader = cmd.ExecuteReader()

                Dim counter As Integer = 0
                Dim startIndex As Integer = pageSize * pageIndex
                Dim endIndex As Integer = startIndex + pageSize - 1

                Do While reader.Read()
                    If counter >= startIndex Then
                        Dim u As MembershipUser = GetUserFromReader(reader)
                        users.Add(u)
                    End If

                    If counter >= endIndex Then cmd.Cancel()

                    counter += 1
                Loop
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "FindUsersByName")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()

                conn.Close()
            End Try

      Return users
    End Function

    '
    ' MembershipProvider.FindUsersByEmail
    '

    Public Overrides Function FindUsersByEmail(emailToMatch As String, _
                                               pageIndex As Integer, _
                                               pageSize As Integer, _
                                               ByRef totalRecords As Integer) _
                              As MembershipUserCollection

      Dim conn As OdbcConnection = New OdbcConnection(connectionString)
            Dim cmd As OdbcCommand = New OdbcCommand("SELECT Count(*) FROM Users  " & _
                                              "WHERE Email LIKE ? AND ApplicationName = ?", conn)
            cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName

            Dim users As MembershipUserCollection = New MembershipUserCollection()

            Dim reader As OdbcDataReader = Nothing
            totalRecords = 0

            Try
                conn.Open()
                totalRecords = CInt(cmd.ExecuteScalar())

                If totalRecords <= 0 Then Return users

                cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," & _
                         " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," & _
                         " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " & _
                         " FROM Users  " & _
                         " WHERE Email LIKE ? AND ApplicationName = ? " & _
                         " ORDER BY Username Asc"

                reader = cmd.ExecuteReader()

                Dim counter As Integer = 0
                Dim startIndex As Integer = pageSize * pageIndex
                Dim endIndex As Integer = startIndex + pageSize - 1

                Do While reader.Read()
                    If counter >= startIndex Then
                        Dim u As MembershipUser = GetUserFromReader(reader)
                        users.Add(u)
                    End If

                    If counter >= endIndex Then cmd.Cancel()

                    counter += 1
                Loop
            Catch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "FindUsersByEmail")

                    Throw New ProviderException(exceptionMessage)
                Else
                    Throw e
                End If
            Finally
                If Not reader Is Nothing Then reader.Close()

                conn.Close()
            End Try

      Return users
    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 Exception, action As String)    
      Dim log As EventLog = New EventLog()
      log.Source = eventSource
      log.Log = eventLog

      Dim message As String = "An exception occurred communicating with the data source." & 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;
using System.Security.Cryptography;
using System.Text;
using System.Web.Configuration;

/*

This provider works with the following schema for the table of user data.

CREATE TABLE Users
(
  PKID Guid NOT NULL PRIMARY KEY,
  Username Text (255) NOT NULL,
  ApplicationName Text (255) NOT NULL,
  Email Text (128) NOT NULL,
  Comment Text (255),
  Password Text (128) NOT NULL,
  PasswordQuestion Text (255),
  PasswordAnswer Text (255),
  IsApproved YesNo, 
  LastActivityDate DateTime,
  LastLoginDate DateTime,
  LastPasswordChangedDate DateTime,
  CreationDate DateTime, 
  IsOnLine YesNo,
  IsLockedOut YesNo,
  LastLockedOutDate DateTime,
  FailedPasswordAttemptCount Integer,
  FailedPasswordAttemptWindowStart DateTime,
  FailedPasswordAnswerAttemptCount Integer,
  FailedPasswordAnswerAttemptWindowStart DateTime
)

*/


namespace Samples.AspNet.Membership
{

  public sealed class OdbcMembershipProvider: MembershipProvider
  {

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

    private int    newPasswordLength = 8;
    private string eventSource = "OdbcMembershipProvider";
    private string eventLog = "Application";
    private string exceptionMessage = "An exception occurred. Please check the Event Log.";
    private string connectionString;

    //
    // Used when determining encryption key values.
    //

    private MachineKeySection machineKey;

    //
    // If false, exceptions are thrown to the caller. If true,
    // exceptions are written to the event log.
    //

    private bool pWriteExceptionsToEventLog;

    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 = "OdbcMembershipProvider";

      if (String.IsNullOrEmpty(config["description"]))
      {
        config.Remove("description");
        config.Add("description", "Sample ODBC Membership provider");
      }

      // Initialize the abstract base class.
      base.Initialize(name, config);

      pApplicationName            = GetConfigValue(config["applicationName"], 
                                      System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
      pMaxInvalidPasswordAttempts = Convert.ToInt32(GetConfigValue(config["maxInvalidPasswordAttempts"], "5"));
      pPasswordAttemptWindow = Convert.ToInt32(GetConfigValue(config["passwordAttemptWindow"], "10"));
      pMinRequiredNonAlphanumericCharacters = Convert.ToInt32(GetConfigValue(config["minRequiredNonAlphanumericCharacters"], "1"));
      pMinRequiredPasswordLength = Convert.ToInt32(GetConfigValue(config["minRequiredPasswordLength"], "7"));
      pPasswordStrengthRegularExpression = Convert.ToString(GetConfigValue(config["passwordStrengthRegularExpression"], ""));
      pEnablePasswordReset = Convert.ToBoolean(GetConfigValue(config["enablePasswordReset"], "true"));
      pEnablePasswordRetrieval = Convert.ToBoolean(GetConfigValue(config["enablePasswordRetrieval"], "true"));
      pRequiresQuestionAndAnswer = Convert.ToBoolean(GetConfigValue(config["requiresQuestionAndAnswer"], "false"));
      pRequiresUniqueEmail = Convert.ToBoolean(GetConfigValue(config["requiresUniqueEmail"], "true"));
      pWriteExceptionsToEventLog = Convert.ToBoolean(GetConfigValue(config["writeExceptionsToEventLog"], "true"));

      string temp_format = config["passwordFormat"];
      if (temp_format == null)
      {
        temp_format = "Hashed";
      }

      switch (temp_format)
      {
        case "Hashed":
          pPasswordFormat = MembershipPasswordFormat.Hashed;
          break;
        case "Encrypted":
          pPasswordFormat = MembershipPasswordFormat.Encrypted;
          break;
        case "Clear":
          pPasswordFormat = MembershipPasswordFormat.Clear;
          break;
        default:
          throw new ProviderException("Password format not supported.");
      }

      //
      // Initialize OdbcConnection.
      //

      ConnectionStringSettings ConnectionStringSettings =
        ConfigurationManager.ConnectionStrings[config["connectionStringName"]];

      if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
      {
        throw new ProviderException("Connection string cannot be blank.");
      }

      connectionString = ConnectionStringSettings.ConnectionString;


      // Get encryption and decryption key information from the configuration.
      Configuration cfg =
        WebConfigurationManager.OpenWebConfiguration(System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath);
      machineKey = (MachineKeySection)cfg.GetSection("system.web/machineKey");

      if (machineKey.ValidationKey.Contains("AutoGenerate"))
        if (PasswordFormat != MembershipPasswordFormat.Clear)
          throw new ProviderException("Hashed or Encrypted passwords " +
                                      "are not supported with auto-generated keys.");
    }


    //
    // A helper function to retrieve config values from the configuration file.
    //

    private string GetConfigValue(string configValue, string defaultValue)
    {
      if (String.IsNullOrEmpty(configValue))
        return defaultValue;

      return configValue;
    }


    //
    // System.Web.Security.MembershipProvider properties.
    //


    private string pApplicationName;
    private bool   pEnablePasswordReset;
    private bool   pEnablePasswordRetrieval;
    private bool   pRequiresQuestionAndAnswer;
    private bool   pRequiresUniqueEmail;
    private int    pMaxInvalidPasswordAttempts;
    private int    pPasswordAttemptWindow;
    private MembershipPasswordFormat pPasswordFormat;

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

    public override bool EnablePasswordReset
    {
      get { return pEnablePasswordReset; }
    }


    public override bool EnablePasswordRetrieval
    {
      get { return pEnablePasswordRetrieval; }
    }


    public override bool RequiresQuestionAndAnswer
    {
      get { return pRequiresQuestionAndAnswer; }
    }


    public override bool RequiresUniqueEmail
    {
      get { return pRequiresUniqueEmail; }
    }


    public override int MaxInvalidPasswordAttempts
    {
      get { return pMaxInvalidPasswordAttempts; }
    }


    public override int PasswordAttemptWindow
    {
      get { return pPasswordAttemptWindow; }
    }


    public override MembershipPasswordFormat PasswordFormat
    {
      get { return pPasswordFormat; }
    }

    private int pMinRequiredNonAlphanumericCharacters;

    public override int MinRequiredNonAlphanumericCharacters
    {
      get { return pMinRequiredNonAlphanumericCharacters; }
    }

    private int pMinRequiredPasswordLength;

    public override int MinRequiredPasswordLength
    {
      get { return pMinRequiredPasswordLength; }
    }

    private string pPasswordStrengthRegularExpression;

    public override string PasswordStrengthRegularExpression
    {
      get { return pPasswordStrengthRegularExpression; }
    }

    //
    // System.Web.Security.MembershipProvider methods.
    //

    //
    // MembershipProvider.ChangePassword
    //

    public override bool ChangePassword(string username, string oldPwd, string newPwd)
    {
      if (!ValidateUser(username, oldPwd))
        return false;


      ValidatePasswordEventArgs args = 
        new ValidatePasswordEventArgs(username, newPwd, true);

      OnValidatingPassword(args);

      if (args.Cancel)
        if (args.FailureInformation != null)
          throw args.FailureInformation;
        else
          throw new MembershipPasswordException("Change password canceled due to new password validation failure.");


      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("UPDATE Users "  +
              " SET Password = ?, LastPasswordChangedDate = ? " +
              " WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPwd);
      cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now;
      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;


      int rowsAffected = 0;

      try
      {
        conn.Open();

        rowsAffected = cmd.ExecuteNonQuery();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "ChangePassword");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }

      if (rowsAffected > 0)
      {
        return true;
      }

      return false;
    }



    //
    // MembershipProvider.ChangePasswordQuestionAndAnswer
    //

    public override bool ChangePasswordQuestionAndAnswer(string username,
                  string password,
                  string newPwdQuestion,
                  string newPwdAnswer)
    {
      if (!ValidateUser(username, password))
        return false;

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
              " SET PasswordQuestion = ?, PasswordAnswer = ?" +
              " WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Question", OdbcType.VarChar, 255).Value = newPwdQuestion;
      cmd.Parameters.Add("@Answer", OdbcType.VarChar, 255).Value = EncodePassword(newPwdAnswer);
      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;


      int rowsAffected = 0;

      try
      {
        conn.Open();

        rowsAffected = cmd.ExecuteNonQuery();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "ChangePasswordQuestionAndAnswer");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }

      if (rowsAffected > 0)
      {
        return true;
      }

      return false;
    }



    //
    // MembershipProvider.CreateUser
    //

    public override MembershipUser CreateUser(string username,
             string password,
             string email,
             string passwordQuestion,
             string passwordAnswer,
             bool isApproved,
             object providerUserKey,
             out MembershipCreateStatus status)
    {
      ValidatePasswordEventArgs args = 
        new ValidatePasswordEventArgs(username, password, true);

      OnValidatingPassword(args);

      if (args.Cancel)
      {
        status = MembershipCreateStatus.InvalidPassword;
        return null;
      }



      if (RequiresUniqueEmail && GetUserNameByEmail(email) != "")
      {
        status = MembershipCreateStatus.DuplicateEmail;
        return null;
      }

      MembershipUser u = GetUser(username, false);

      if (u == null)
      {
        DateTime createDate = DateTime.Now;

        if (providerUserKey == null)
        {
          providerUserKey = Guid.NewGuid();
        }
        else
        {
          if ( !(providerUserKey is Guid) )
          {
            status = MembershipCreateStatus.InvalidProviderUserKey;
            return null;
          }
        }

        OdbcConnection conn = new OdbcConnection(connectionString);
        OdbcCommand cmd = new OdbcCommand("INSERT INTO Users " +
              " (PKID, Username, Password, Email, PasswordQuestion, " +
              " PasswordAnswer, IsApproved," +
              " Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," +
              " ApplicationName, IsLockedOut, LastLockedOutDate," +
              " FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " +
              " FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)" +
              " Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);

        cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey;
        cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
        cmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(password);
        cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email;
        cmd.Parameters.Add("@PasswordQuestion", OdbcType.VarChar, 255).Value = passwordQuestion;
        cmd.Parameters.Add("@PasswordAnswer", OdbcType.VarChar, 255).Value = EncodePassword(passwordAnswer);
        cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = isApproved;
        cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = "";
        cmd.Parameters.Add("@CreationDate", OdbcType.DateTime).Value = createDate;
        cmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = createDate;
        cmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = createDate;
        cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;
        cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = false;
        cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = createDate;
        cmd.Parameters.Add("@FailedPasswordAttemptCount", OdbcType.Int).Value = 0;
        cmd.Parameters.Add("@FailedPasswordAttemptWindowStart", OdbcType.DateTime).Value = createDate;
        cmd.Parameters.Add("@FailedPasswordAnswerAttemptCount", OdbcType.Int).Value = 0;
        cmd.Parameters.Add("@FailedPasswordAnswerAttemptWindowStart", OdbcType.DateTime).Value = createDate;

        try
        {
          conn.Open();

          int recAdded = cmd.ExecuteNonQuery();

          if (recAdded > 0)
          {
            status = MembershipCreateStatus.Success;
          }
          else
          {
            status = MembershipCreateStatus.UserRejected;
          }
        }
        catch (OdbcException e)
        {
          if (WriteExceptionsToEventLog)
          {
            WriteToEventLog(e, "CreateUser");
          }

          status = MembershipCreateStatus.ProviderError;
        }
        finally
        {
          conn.Close();
        }


        return GetUser(username, false);      
      }        
      else
      {
        status = MembershipCreateStatus.DuplicateUserName;
      }


      return null;
    }



    //
    // MembershipProvider.DeleteUser
    //

    public override bool DeleteUser(string username, bool deleteAllRelatedData)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("DELETE FROM Users " +
              " WHERE Username = ? AND Applicationname = ?", conn);

      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      int rowsAffected = 0;

      try
      {
        conn.Open();

        rowsAffected = cmd.ExecuteNonQuery();

        if (deleteAllRelatedData)
        {
          // Process commands to delete all data for the user in the database.
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "DeleteUser");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }

      if (rowsAffected > 0)
        return true;

      return false;
    }



    //
    // MembershipProvider.GetAllUsers
    //

    public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
                                        "WHERE ApplicationName = ?", conn);
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;

      MembershipUserCollection users = new MembershipUserCollection();

      OdbcDataReader reader = null;
      totalRecords = 0;

      try
      {
        conn.Open();
        totalRecords = (int)cmd.ExecuteScalar();

        if (totalRecords <= 0) { return users; }

        cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
                 " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
                 " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
                 " FROM Users " + 
                 " WHERE ApplicationName = ? " +
                 " ORDER BY Username Asc";

        reader = cmd.ExecuteReader();

        int counter = 0;
        int startIndex = pageSize * pageIndex;
        int endIndex = startIndex + pageSize - 1;

        while (reader.Read())
        {
          if (counter >= startIndex)
          {
            MembershipUser u = GetUserFromReader(reader);
            users.Add(u);
          }

          if (counter >= endIndex) { cmd.Cancel(); }

          counter++;
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "GetAllUsers ");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }
         conn.Close();
      }

      return users;
    }


    //
    // MembershipProvider.GetNumberOfUsersOnline
    //

    public override int GetNumberOfUsersOnline()
    {

      TimeSpan onlineSpan = new TimeSpan(0, System.Web.Security.Membership.UserIsOnlineTimeWindow, 0);
      DateTime compareTime = DateTime.Now.Subtract(onlineSpan);

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
              " WHERE LastActivityDate > ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@CompareDate", OdbcType.DateTime).Value = compareTime;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      int numOnline = 0;

      try
      {
        conn.Open();

        numOnline = (int)cmd.ExecuteScalar();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "GetNumberOfUsersOnline");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }

      return numOnline;
    }



    //
    // MembershipProvider.GetPassword
    //

    public override string GetPassword(string username, string answer)
    {
      if (!EnablePasswordRetrieval)
      {
        throw new ProviderException("Password Retrieval Not Enabled.");
      }

      if (PasswordFormat == MembershipPasswordFormat.Hashed)
      {
        throw new ProviderException("Cannot retrieve Hashed passwords.");
      }

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Password, PasswordAnswer, IsLockedOut FROM Users " +
            " WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      string password = "";
      string passwordAnswer = "";
      OdbcDataReader reader = null;

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        if (reader.HasRows)
        {
          reader.Read();

          if (reader.GetBoolean(2))
            throw new MembershipPasswordException("The supplied user is locked out.");

          password = reader.GetString(0);
          passwordAnswer = reader.GetString(1);
        }
        else
        {
          throw new MembershipPasswordException("The supplied user name is not found.");
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "GetPassword");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }
        conn.Close();
      }


      if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
      {
        UpdateFailureCount(username, "passwordAnswer");

        throw new MembershipPasswordException("Incorrect password answer.");
      }


      if (PasswordFormat == MembershipPasswordFormat.Encrypted)
      {
        password = UnEncodePassword(password);
      }

      return password;
    }



    //
    // MembershipProvider.GetUser(string, bool)
    //

    public override MembershipUser GetUser(string username, bool userIsOnline)
    {
       OdbcConnection conn = new OdbcConnection(connectionString);
       OdbcCommand cmd = new OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," +
            " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
            " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" +
            " FROM Users WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      MembershipUser u = null;
      OdbcDataReader reader = null;

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
          reader.Read();
          u = GetUserFromReader(reader);

          if (userIsOnline)
          {
            OdbcCommand updateCmd = new OdbcCommand("UPDATE Users " +
                      "SET LastActivityDate = ? " +
                      "WHERE Username = ? AND Applicationname = ?", conn);

            updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now;
            updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
            updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            updateCmd.ExecuteNonQuery();
          }
        }

      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "GetUser(String, Boolean)");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }

        conn.Close();
      }

      return u;      
    }


    //
    // MembershipProvider.GetUser(object, bool)
    //

    public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT PKID, Username, Email, PasswordQuestion," +
            " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
            " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate" +
            " FROM Users WHERE PKID = ?", conn);

      cmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey;

      MembershipUser u = null;
      OdbcDataReader reader = null;

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader();

        if (reader.HasRows)
        {
          reader.Read();
          u = GetUserFromReader(reader);

          if (userIsOnline)
          {
            OdbcCommand updateCmd = new OdbcCommand("UPDATE Users " +
                      "SET LastActivityDate = ? " +
                      "WHERE PKID = ?", conn);

            updateCmd.Parameters.Add("@LastActivityDate", OdbcType.DateTime).Value = DateTime.Now;
            updateCmd.Parameters.Add("@PKID", OdbcType.UniqueIdentifier).Value = providerUserKey;

            updateCmd.ExecuteNonQuery();
          }
        }

      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "GetUser(Object, Boolean)");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }

        conn.Close();
      }

      return u;      
    }


    //
    // GetUserFromReader
    //    A helper function that takes the current row from the OdbcDataReader
    // and hydrates a MembershiUser from the values. Called by the 
    // MembershipUser.GetUser implementation.
    //

    private MembershipUser GetUserFromReader(OdbcDataReader reader)
    {
      object providerUserKey = reader.GetValue(0);
      string username = reader.GetString(1);
      string email = reader.GetString(2);

      string passwordQuestion = "";
      if (reader.GetValue(3) != DBNull.Value)
        passwordQuestion = reader.GetString(3);

      string comment = "";
      if (reader.GetValue(4) != DBNull.Value)
        comment = reader.GetString(4);

      bool isApproved = reader.GetBoolean(5);
      bool isLockedOut = reader.GetBoolean(6);
      DateTime creationDate = reader.GetDateTime(7);

      DateTime lastLoginDate = new DateTime();
      if (reader.GetValue(8) != DBNull.Value)
        lastLoginDate = reader.GetDateTime(8);

      DateTime lastActivityDate = reader.GetDateTime(9);
      DateTime lastPasswordChangedDate = reader.GetDateTime(10);

      DateTime lastLockedOutDate = new DateTime();
      if (reader.GetValue(11) != DBNull.Value)
        lastLockedOutDate = reader.GetDateTime(11);

      MembershipUser u = new MembershipUser(this.Name,
                                            username,
                                            providerUserKey,
                                            email,
                                            passwordQuestion,
                                            comment,
                                            isApproved,
                                            isLockedOut,
                                            creationDate,
                                            lastLoginDate,
                                            lastActivityDate,
                                            lastPasswordChangedDate,
                                            lastLockedOutDate);

      return u;
    }


    //
    // MembershipProvider.UnlockUser
    //

    public override bool UnlockUser(string username)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
                                        " SET IsLockedOut = False, LastLockedOutDate = ? " +
                                        " WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now;
      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      int rowsAffected = 0;

      try
      {
        conn.Open();

        rowsAffected = cmd.ExecuteNonQuery();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "UnlockUser");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }

      if (rowsAffected > 0)
        return true;

      return false;      
    }


    //
    // MembershipProvider.GetUserNameByEmail
    //

    public override string GetUserNameByEmail(string email)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Username" +
            " FROM Users WHERE Email = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = email;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      string username = "";

      try
      {
        conn.Open();

        username = (string)cmd.ExecuteScalar();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "GetUserNameByEmail");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }

      if (username == null)
        username = "";

      return username;
    }




    //
    // MembershipProvider.ResetPassword
    //

    public override string ResetPassword(string username, string answer)
    {
      if (!EnablePasswordReset)
      {
        throw new NotSupportedException("Password reset is not enabled.");
      }

      if (answer == null && RequiresQuestionAndAnswer)
      {
        UpdateFailureCount(username, "passwordAnswer");

        throw new ProviderException("Password answer required for password reset.");
      }

      string newPassword = 
        System.Web.Security.Membership.GeneratePassword(newPasswordLength,MinRequiredNonAlphanumericCharacters);


      ValidatePasswordEventArgs args = 
        new ValidatePasswordEventArgs(username, newPassword, true);

      OnValidatingPassword(args);

      if (args.Cancel)
        if (args.FailureInformation != null)
          throw args.FailureInformation;
        else
          throw new MembershipPasswordException("Reset password canceled due to password validation failure.");


      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT PasswordAnswer, IsLockedOut FROM Users " +
            " WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      int rowsAffected = 0;
      string passwordAnswer = "";
      OdbcDataReader reader = null;

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        if (reader.HasRows)
        {
          reader.Read();

          if (reader.GetBoolean(1))
            throw new MembershipPasswordException("The supplied user is locked out.");

          passwordAnswer = reader.GetString(0);
        }
        else
        {
          throw new MembershipPasswordException("The supplied user name is not found.");
        }

        if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
        {
          UpdateFailureCount(username, "passwordAnswer");

          throw new MembershipPasswordException("Incorrect password answer.");
        }

        OdbcCommand updateCmd = new OdbcCommand("UPDATE Users " +
            " SET Password = ?, LastPasswordChangedDate = ?" +
            " WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn);

        updateCmd.Parameters.Add("@Password", OdbcType.VarChar, 255).Value = EncodePassword(newPassword);
        updateCmd.Parameters.Add("@LastPasswordChangedDate", OdbcType.DateTime).Value = DateTime.Now;
        updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
        updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

        rowsAffected = updateCmd.ExecuteNonQuery();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "ResetPassword");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }
        conn.Close();
      }

      if (rowsAffected > 0)
      {
        return newPassword;
      }
      else
      {
        throw new MembershipPasswordException("User not found, or user is locked out. Password not Reset.");
      }
    }


    //
    // MembershipProvider.UpdateUser
    //

    public override void UpdateUser(MembershipUser user)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("UPDATE Users " +
              " SET Email = ?, Comment = ?," +
              " IsApproved = ?" +
              " WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Email", OdbcType.VarChar, 128).Value = user.Email;
      cmd.Parameters.Add("@Comment", OdbcType.VarChar, 255).Value = user.Comment;
      cmd.Parameters.Add("@IsApproved", OdbcType.Bit).Value = user.IsApproved;
      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = user.UserName;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;


      try
      {
        conn.Open();

        cmd.ExecuteNonQuery();
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "UpdateUser");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        conn.Close();
      }
    }


    //
    // MembershipProvider.ValidateUser
    //

    public override bool ValidateUser(string username, string password)
    {
      bool isValid = false;

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Password, IsApproved FROM Users " +
              " WHERE Username = ? AND ApplicationName = ? AND IsLockedOut = False", conn);

      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      OdbcDataReader reader = null;
      bool isApproved = false;
      string pwd = "";

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        if (reader.HasRows)
        {
          reader.Read();
          pwd = reader.GetString(0);
          isApproved = reader.GetBoolean(1);
        }
        else
        {
          return false;
        }

        reader.Close();

        if (CheckPassword(password, pwd))
        {
          if (isApproved)
          {
            isValid = true;

            OdbcCommand updateCmd = new OdbcCommand("UPDATE Users SET LastLoginDate = ?" +
                                                    " WHERE Username = ? AND ApplicationName = ?", conn);

            updateCmd.Parameters.Add("@LastLoginDate", OdbcType.DateTime).Value = DateTime.Now;
            updateCmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
            updateCmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            updateCmd.ExecuteNonQuery();
          }
        }
        else
        {
          conn.Close();

          UpdateFailureCount(username, "password");
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "ValidateUser");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }
        conn.Close();
      }

      return isValid;
    }


    //
    // UpdateFailureCount
    //   A helper method that performs the checks and updates associated with
    // password failure tracking.
    //

    private void UpdateFailureCount(string username, string failureType)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT FailedPasswordAttemptCount, " +
                                        "  FailedPasswordAttemptWindowStart, " +
                                        "  FailedPasswordAnswerAttemptCount, " +
                                        "  FailedPasswordAnswerAttemptWindowStart " + 
                                        "  FROM Users " +
                                        "  WHERE Username = ? AND ApplicationName = ?", conn);

      cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      OdbcDataReader reader = null;
      DateTime windowStart = new DateTime();
      int failureCount = 0;

      try
      {
        conn.Open();

        reader = cmd.ExecuteReader(CommandBehavior.SingleRow);

        if (reader.HasRows)
        {
          reader.Read();

          if (failureType == "password")
          {
            failureCount = reader.GetInt32(0);
            windowStart = reader.GetDateTime(1);
          }

          if (failureType == "passwordAnswer")
          {
            failureCount = reader.GetInt32(2);
            windowStart = reader.GetDateTime(3);
          }
        }

        reader.Close();

        DateTime windowEnd = windowStart.AddMinutes(PasswordAttemptWindow);

        if (failureCount == 0 || DateTime.Now > windowEnd)
        {
          // First password failure or outside of PasswordAttemptWindow. 
          // Start a new password failure count from 1 and a new window starting now.

          if (failureType == "password")
            cmd.CommandText = "UPDATE Users " +
                              "  SET FailedPasswordAttemptCount = ?, " +
                              "      FailedPasswordAttemptWindowStart = ? " +
                              "  WHERE Username = ? AND ApplicationName = ?";

          if (failureType == "passwordAnswer")
            cmd.CommandText = "UPDATE Users " +
                              "  SET FailedPasswordAnswerAttemptCount = ?, " +
                              "      FailedPasswordAnswerAttemptWindowStart = ? " +
                              "  WHERE Username = ? AND ApplicationName = ?";

          cmd.Parameters.Clear();

          cmd.Parameters.Add("@Count", OdbcType.Int).Value = 1;
          cmd.Parameters.Add("@WindowStart", OdbcType.DateTime).Value = DateTime.Now;
          cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
          cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

          if (cmd.ExecuteNonQuery() < 0)
            throw new ProviderException("Unable to update failure count and window start.");
        }
        else
        {
          if (failureCount++ >= MaxInvalidPasswordAttempts)
          {
            // Password attempts have exceeded the failure threshold. Lock out
            // the user.

            cmd.CommandText = "UPDATE Users " +
                              "  SET IsLockedOut = ?, LastLockedOutDate = ? " +
                              "  WHERE Username = ? AND ApplicationName = ?";

            cmd.Parameters.Clear();

            cmd.Parameters.Add("@IsLockedOut", OdbcType.Bit).Value = true;
            cmd.Parameters.Add("@LastLockedOutDate", OdbcType.DateTime).Value = DateTime.Now;
            cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
            cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

            if (cmd.ExecuteNonQuery() < 0)
              throw new ProviderException("Unable to lock out user.");
          }
          else
          {
            // Password attempts have not exceeded the failure threshold. Update
            // the failure counts. Leave the window the same.

            if (failureType == "password")
              cmd.CommandText = "UPDATE Users " +
                                "  SET FailedPasswordAttemptCount = ?" +
                                "  WHERE Username = ? AND ApplicationName = ?";

            if (failureType == "passwordAnswer")
              cmd.CommandText = "UPDATE Users " +
                                "  SET FailedPasswordAnswerAttemptCount = ?" +
                                "  WHERE Username = ? AND ApplicationName = ?";

             cmd.Parameters.Clear();

             cmd.Parameters.Add("@Count", OdbcType.Int).Value = failureCount;
             cmd.Parameters.Add("@Username", OdbcType.VarChar, 255).Value = username;
             cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

             if (cmd.ExecuteNonQuery() < 0)
               throw new ProviderException("Unable to update failure count.");
          }
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "UpdateFailureCount");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }
        conn.Close();
      }       
    }


    //
    // CheckPassword
    //   Compares password values based on the MembershipPasswordFormat.
    //

    private bool CheckPassword(string password, string dbpassword)
    {
      string pass1 = password;
      string pass2 = dbpassword;

      switch (PasswordFormat)
      {
        case MembershipPasswordFormat.Encrypted:
          pass2 = UnEncodePassword(dbpassword);
          break;
        case MembershipPasswordFormat.Hashed:
          pass1 = EncodePassword(password);
          break;
        default:
          break;
      }

      if (pass1 == pass2)
      {
        return true;
      }

      return false;
    }


    //
    // EncodePassword
    //   Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
    //

    private string EncodePassword(string password)
    {
      string encodedPassword = password;

      switch (PasswordFormat)
      {
        case MembershipPasswordFormat.Clear:
          break;
        case MembershipPasswordFormat.Encrypted:
          encodedPassword = 
            Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)));
          break;
        case MembershipPasswordFormat.Hashed:
          HMACSHA1 hash = new HMACSHA1();
          hash.Key = HexToByte(machineKey.ValidationKey);
          encodedPassword = 
            Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)));
          break;
        default:
          throw new ProviderException("Unsupported password format.");
      }

      return encodedPassword;
    }


    //
    // UnEncodePassword
    //   Decrypts or leaves the password clear based on the PasswordFormat.
    //

    private string UnEncodePassword(string encodedPassword)
    {
      string password = encodedPassword;

      switch (PasswordFormat)
      {
        case MembershipPasswordFormat.Clear:
          break;
        case MembershipPasswordFormat.Encrypted:
          password = 
            Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)));
          break;
        case MembershipPasswordFormat.Hashed:
          throw new ProviderException("Cannot unencode a hashed password.");
        default:
          throw new ProviderException("Unsupported password format.");
      }

      return password;
    }

    //
    // HexToByte
    //   Converts a hexadecimal string to a byte array. Used to convert encryption
    // key values from the configuration.
    //

    private byte[] HexToByte(string hexString)
    {
      byte[] returnBytes = new byte[hexString.Length / 2];
      for (int i = 0; i < returnBytes.Length; i++)
        returnBytes[i] = Convert.ToByte(hexString.Substring(i*2, 2), 16);
      return returnBytes;
    }


    //
    // MembershipProvider.FindUsersByName
    //

    public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
    {

      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
                "WHERE Username LIKE ? AND ApplicationName = ?", conn);
      cmd.Parameters.Add("@UsernameSearch", OdbcType.VarChar, 255).Value = usernameToMatch;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = pApplicationName;

      MembershipUserCollection users = new MembershipUserCollection();

      OdbcDataReader reader = null;

      try
      {
        conn.Open();
        totalRecords = (int)cmd.ExecuteScalar();

        if (totalRecords <= 0) { return users; }

        cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
          " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
          " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
          " FROM Users " + 
          " WHERE Username LIKE ? AND ApplicationName = ? " +
          " ORDER BY Username Asc";

        reader = cmd.ExecuteReader();

        int counter = 0;
        int startIndex = pageSize * pageIndex;
        int endIndex = startIndex + pageSize - 1;

        while (reader.Read())
        {
          if (counter >= startIndex)
          {
            MembershipUser u = GetUserFromReader(reader);
            users.Add(u);
          }

          if (counter >= endIndex) { cmd.Cancel(); }

          counter++;
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "FindUsersByName");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }

        conn.Close();
      }

      return users;
    }

    //
    // MembershipProvider.FindUsersByEmail
    //

    public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
    {
      OdbcConnection conn = new OdbcConnection(connectionString);
      OdbcCommand cmd = new OdbcCommand("SELECT Count(*) FROM Users " +
                                        "WHERE Email LIKE ? AND ApplicationName = ?", conn);
      cmd.Parameters.Add("@EmailSearch", OdbcType.VarChar, 255).Value = emailToMatch;
      cmd.Parameters.Add("@ApplicationName", OdbcType.VarChar, 255).Value = ApplicationName;

      MembershipUserCollection users = new MembershipUserCollection();

      OdbcDataReader reader = null;
      totalRecords = 0;

      try
      {
        conn.Open();
        totalRecords = (int)cmd.ExecuteScalar();

        if (totalRecords <= 0) { return users; }

        cmd.CommandText = "SELECT PKID, Username, Email, PasswordQuestion," +
                 " Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," +
                 " LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " +
                 " FROM Users " + 
                 " WHERE Email LIKE ? AND ApplicationName = ? " +
                 " ORDER BY Username Asc";

        reader = cmd.ExecuteReader();

        int counter = 0;
        int startIndex = pageSize * pageIndex;
        int endIndex = startIndex + pageSize - 1;

        while (reader.Read())
        {
          if (counter >= startIndex)
          {
            MembershipUser u = GetUserFromReader(reader);
            users.Add(u);
          }

          if (counter >= endIndex) { cmd.Cancel(); }

          counter++;
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "FindUsersByEmail");

          throw new ProviderException(exceptionMessage);
        }
        else
        {
          throw e;
        }
      }
      finally
      {
        if (reader != null) { reader.Close(); }

        conn.Close();
      }

      return users;
    }


    //
    // 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(Exception e, string action)
    {
      EventLog log = new EventLog();
      log.Source = eventSource;
      log.Log = eventLog;

      string message = "An exception occurred communicating with the data source.\n\n";
      message += "Action: " + action + "\n\n";
      message += "Exception: " + e.ToString();

      log.WriteEntry(message);
    }

  }
}

See Also

Concepts

Sample Membership Provider Implementation

Other Resources

Managing Users by Using Membership