Share via


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


  PublicNotInheritableClass OdbcMembershipProvider
    Inherits MembershipProvider

    '    ' Global generated password length, generic exception message, event log info.    'Private newPasswordLength AsInteger = 8
    Private eventSource AsString = "OdbcMembershipProvider"Private eventLog AsString = "Application"Private exceptionMessage AsString = "An exception occurred. Please check the Event Log."Private connectionString AsString
    '    ' 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 AsBooleanPublicProperty WriteExceptionsToEventLog AsBooleanGetReturn pWriteExceptionsToEventLog
      EndGetSet
        pWriteExceptionsToEventLog = value
      EndSetEndProperty


    '    ' System.Configuration.Provider.ProviderBase.Initialize Method    'PublicOverridesSub Initialize(name AsString, config As NameValueCollection)


      '      ' Initialize values from web.config.      'If config IsNothingThen _
        ThrowNew ArgumentNullException("config")

      If name IsNothingOrElse name.Length = 0 Then _
        name = "OdbcMembershipProvider"IfString.IsNullOrEmpty(config("description")) Then
        config.Remove("description")
        config.Add("description", "Sample ODBC Membership provider")
      EndIf
      ' 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 AsString = config("passwordFormat")
      If temp_format IsNothingThen      
        temp_format = "Hashed"EndIfSelectCase temp_format      
        Case"Hashed"
          pPasswordFormat = MembershipPasswordFormat.Hashed
        Case"Encrypted"
          pPasswordFormat = MembershipPasswordFormat.Encrypted
        Case"Clear"
          pPasswordFormat = MembershipPasswordFormat.Clear
        CaseElseThrowNew ProviderException("Password format not supported.")
      EndSelect
      '      ' Initialize OdbcConnection.      'Dim ConnectionStringSettings As ConnectionStringSettings = _
        ConfigurationManager.ConnectionStrings(config("connectionStringName"))

      If ConnectionStringSettings IsNothingOrElse ConnectionStringSettings.ConnectionString.Trim() = ""ThenThrowNew ProviderException("Connection string cannot be blank.")
      EndIf

      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 _
          ThrowNew ProviderException("Hashed or Encrypted passwords " & _
                                      "are not supported with auto-generated keys.")
    EndSub

    '    ' A helper function to retrieve config values from the configuration file.    'PrivateFunction GetConfigValue(configValue AsString, defaultValue AsString) AsStringIfString.IsNullOrEmpty(configValue) Then _
        Return defaultValue

      Return configValue
    EndFunction

    '    ' System.Web.Security.MembershipProvider properties.    'Private pApplicationName           AsStringPrivate pEnablePasswordReset       AsBooleanPrivate pEnablePasswordRetrieval   AsBooleanPrivate pRequiresQuestionAndAnswer AsBooleanPrivate pRequiresUniqueEmail       AsBooleanPrivate pMaxInvalidPasswordAttempts  AsIntegerPrivate pPasswordAttemptWindow     AsIntegerPrivate pPasswordFormat            As MembershipPasswordFormat

    PublicOverridesProperty ApplicationName AsStringGetReturn pApplicationName
      EndGetSet
        pApplicationName = value
      EndSetEndPropertyPublicOverridesReadOnlyProperty EnablePasswordReset AsBooleanGetReturn pEnablePasswordReset
      EndGetEndPropertyPublicOverridesReadOnlyProperty EnablePasswordRetrieval AsBooleanGetReturn pEnablePasswordRetrieval
      EndGetEndPropertyPublicOverridesReadOnlyProperty RequiresQuestionAndAnswer AsBooleanGetReturn pRequiresQuestionAndAnswer
      EndGetEndPropertyPublicOverridesReadOnlyProperty RequiresUniqueEmail AsBooleanGetReturn pRequiresUniqueEmail
      EndGetEndPropertyPublicOverridesReadOnlyProperty MaxInvalidPasswordAttempts AsIntegerGetReturn pMaxInvalidPasswordAttempts
      EndGetEndPropertyPublicOverridesReadOnlyProperty PasswordAttemptWindow AsIntegerGetReturn pPasswordAttemptWindow
      EndGetEndPropertyPublicOverridesReadOnlyProperty PasswordFormat As MembershipPasswordFormat
      GetReturn pPasswordFormat
      EndGetEndPropertyPrivate pMinRequiredNonAlphanumericCharacters AsIntegerPublicOverridesReadOnlyProperty MinRequiredNonAlphanumericCharacters() AsIntegerGetReturn pMinRequiredNonAlphanumericCharacters
      EndGetEndPropertyPrivate pMinRequiredPasswordLength AsIntegerPublicOverridesReadOnlyProperty MinRequiredPasswordLength() AsIntegerGetReturn pMinRequiredPasswordLength
      EndGetEndPropertyPrivate pPasswordStrengthRegularExpression AsStringPublicOverridesReadOnlyProperty PasswordStrengthRegularExpression() AsStringGetReturn pPasswordStrengthRegularExpression
      EndGetEndProperty
    '    ' System.Web.Security.MembershipProvider methods.    '
    '    ' MembershipProvider.ChangePassword    'PublicOverridesFunction ChangePassword(username AsString, _
                                             oldPwd AsString, _
                                             newPwd AsString) AsBooleanIfNot ValidateUser(username, oldPwd) Then _
        ReturnFalseDim args As ValidatePasswordEventArgs = _
        New ValidatePasswordEventArgs(username, newPwd, True)

      OnValidatingPassword(args)

      If args.Cancel ThenIfNot args.FailureInformation IsNothingThenThrow args.FailureInformation
        ElseThrowNew ProviderException("Change password canceled due to New password validation failure.")
        EndIfEndIfDim 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 AsInteger = 0

      Try
        conn.Open()

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

          WriteToEventLog(e, "ChangePassword")

          ThrowNew ProviderException(exceptionMessage)
        ElseThrow e
        EndIfFinally      
        conn.Close()
      EndTryIf rowsAffected > 0 ThenReturnTrueEndIfReturnFalseEndFunction


    '    ' MembershipProvider.ChangePasswordQuestionAndAnswer    'PublicOverridesFunction ChangePasswordQuestionAndAnswer(username AsString, _
                  password AsString, _
                  newPwdQuestion AsString, _
                  newPwdAnswer AsString) AsBooleanIfNot ValidateUser(username, password) Then _
        ReturnFalseDim 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 AsInteger = 0

            Try
                conn.Open()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinally
                conn.Close()
            EndTryIf rowsAffected > 0 ThenReturnTrueEndIfReturnFalseEndFunction


        '        ' MembershipProvider.CreateUser        'PublicOverridesFunction CreateUser(ByVal username AsString, _
        ByVal password AsString, _
        ByVal email AsString, _
        ByVal passwordQuestion AsString, _
        ByVal passwordAnswer AsString, _
        ByVal isApproved AsBoolean, _
        ByVal providerUserKey AsObject, _
                 ByRef status As MembershipCreateStatus) As MembershipUser

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

            OnValidatingPassword(args)

            If args.Cancel Then
                status = MembershipCreateStatus.InvalidPassword
                ReturnNothingEndIfIf RequiresUniqueEmail AndAlso GetUserNameByEmail(email) <> ""Then
                status = MembershipCreateStatus.DuplicateEmail
                ReturnNothingEndIfDim u As MembershipUser = GetUser(username, False)

            If u IsNothingThenDim createDate As DateTime = DateTime.Now

                If providerUserKey IsNothingThen
                    providerUserKey = Guid.NewGuid()
                ElseIfNotTypeOf providerUserKey Is Guid Then
                        status = MembershipCreateStatus.InvalidProviderUserKey
                        ReturnNothingEndIfEndIfDim 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 AsInteger = cmd.ExecuteNonQuery()

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

                    status = MembershipCreateStatus.ProviderError
                Finally
                    conn.Close()
                EndTryReturn GetUser(username, False)
            Else
                status = MembershipCreateStatus.DuplicateUserName
            EndIfReturnNothingEndFunction


    '    ' MembershipProvider.DeleteUser    'PublicOverridesFunction DeleteUser(username AsString, _
                                         deleteAllRelatedData AsBoolean) AsBooleanDim 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 AsInteger = 0

            Try
                conn.Open()

                rowsAffected = cmd.ExecuteNonQuery()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinally
                conn.Close()
            EndTryIf rowsAffected > 0 Then _
              ReturnTrueReturnFalseEndFunction


        '        ' MembershipProvider.GetAllUsers        'PublicOverridesFunction GetAllUsers(ByVal pageIndex AsInteger, _
        ByVal pageSize AsInteger, _
                                              ByRef totalRecords AsInteger) _
                                  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 ThenReturn 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 AsInteger = 0
                Dim startIndex AsInteger = pageSize * pageIndex
                Dim endIndex AsInteger = startIndex + pageSize - 1

                DoWhile reader.Read()
                    If counter >= startIndex ThenDim u As MembershipUser = GetUserFromReader(reader)
                        users.Add(u)
                    EndIfIf counter >= endIndex Then cmd.Cancel()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()
                conn.Close()
            EndTryReturn users
        EndFunction

        '        ' MembershipProvider.GetNumberOfUsersOnline        'PublicOverridesFunction GetNumberOfUsersOnline() AsIntegerDim 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 AsInteger = 0

            Try
                conn.Open()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinally
                conn.Close()
            EndTryReturn numOnline
        EndFunction


    '    ' MembershipProvider.GetPassword    'PublicOverridesFunction GetPassword(username AsString, answer AsString) AsStringIfNot EnablePasswordRetrieval ThenThrowNew ProviderException("Password Retrieval Not Enabled.")
      EndIfIf PasswordFormat = MembershipPasswordFormat.Hashed ThenThrowNew ProviderException("Cannot retrieve Hashed passwords.")
      EndIfDim 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 AsString = ""Dim passwordAnswer AsString = ""Dim reader As OdbcDataReader = NothingTry
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()

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

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()
                conn.Close()
            EndTryIf RequiresQuestionAndAnswer AndAlsoNot CheckPassword(answer, passwordAnswer) Then
                UpdateFailureCount(username, "passwordAnswer")

                ThrowNew MembershipPasswordException("Incorrect password answer.")
            EndIfIf PasswordFormat = MembershipPasswordFormat.Encrypted Then
                password = UnEncodePassword(password)
            EndIfReturn password
        EndFunction


        '        ' MembershipProvider.GetUser(String, Boolean)        'PublicOverridesFunction GetUser(ByVal username AsString, _
        ByVal userIsOnline AsBoolean) 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 = NothingDim reader As OdbcDataReader = NothingTry
                conn.Open()

                reader = cmd.ExecuteReader()

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

                    If userIsOnline ThenDim 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()
                    EndIfEndIfCatch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetUser(String, Boolean)")

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()

                conn.Close()
            EndTryReturn u
        EndFunction

        '        ' MembershipProvider.GetUser(Object, Boolean)        'PublicOverridesFunction GetUser(ByVal providerUserKey AsObject, _
        ByVal userIsOnline AsBoolean) 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 = NothingDim reader As OdbcDataReader = NothingTry
                conn.Open()

                reader = cmd.ExecuteReader()

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

                    If userIsOnline ThenDim 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()
                    EndIfEndIfCatch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "GetUser(Object, Boolean)")

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()

                conn.Close()
            EndTryReturn u
        EndFunction

        '        ' 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.        'PrivateFunction GetUserFromReader(ByVal reader As OdbcDataReader) As MembershipUser
            Dim providerUserKey AsObject = reader.GetValue(0)
            Dim username AsString = reader.GetString(1)
            Dim email AsString = reader.GetString(2)

            Dim passwordQuestion AsString = ""IfNot reader.GetValue(3) Is DBNull.Value Then _
              passwordQuestion = reader.GetString(3)

            Dim comment AsString = ""IfNot reader.GetValue(4) Is DBNull.Value Then _
              comment = reader.GetString(4)

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

            Dim lastLoginDate As DateTime = New DateTime()
            IfNot 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()
            IfNot 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
        EndFunction

        '        ' MembershipProvider.UnlockUser        'PublicOverridesFunction UnlockUser(ByVal username AsString) AsBooleanDim 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 AsInteger = 0

            Try
                conn.Open()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinally
                conn.Close()
            EndTryIf rowsAffected > 0 Then _
              ReturnTrueReturnFalseEndFunction

    '    ' MembershipProvider.GetUserNameByEmail    'PublicOverridesFunction GetUserNameByEmail(email AsString) AsStringDim 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 AsString = ""Try
                conn.Open()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinally
                conn.Close()
            EndTryIf username IsNothingThen _
              username = ""Return username
        EndFunction



        '        ' MembershipProvider.ResetPassword        'PublicOverridesFunction ResetPassword(ByVal username AsString, ByVal answer AsString) AsStringIfNot EnablePasswordReset ThenThrowNew NotSupportedException("Password Reset is not enabled.")
            EndIfIf answer IsNothingAndAlso RequiresQuestionAndAnswer Then
                UpdateFailureCount(username, "passwordAnswer")

                ThrowNew ProviderException("Password answer required for password Reset.")
            EndIfDim newPassword AsString = _
              System.Web.Security.Membership.GeneratePassword(newPasswordLength, MinRequiredNonAlphanumericCharacters)


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

            OnValidatingPassword(args)

            If args.Cancel ThenIfNot args.FailureInformation IsNothingThenThrow args.FailureInformation
                ElseThrowNew MembershipPasswordException("Reset password canceled due to password validation failure.")
                EndIfEndIfDim 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 AsInteger = 0
            Dim passwordAnswer AsString = ""Dim reader As OdbcDataReader = NothingTry
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()

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

                    passwordAnswer = reader.GetString(0)
                ElseThrowNew MembershipPasswordException("The supplied user name is not found.")
                EndIfIf RequiresQuestionAndAnswer AndAlsoNot CheckPassword(answer, passwordAnswer) Then
                    UpdateFailureCount(username, "passwordAnswer")

                    ThrowNew MembershipPasswordException("Incorrect password answer.")
                EndIfDim 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")

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()
                conn.Close()
            EndTryIf rowsAffected > 0 ThenReturn newPassword
            ElseThrowNew MembershipPasswordException("User not found, or user is locked out. Password not Reset.")
            EndIfEndFunction

        '        ' MembershipProvider.UpdateUser        'PublicOverridesSub 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")

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinally
                conn.Close()
            EndTryEndSub

    '    ' MembershipProvider.ValidateUser    'PublicOverridesFunction ValidateUser(username AsString, password AsString) AsBooleanDim isValid AsBoolean = FalseDim 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 = NothingDim isApproved AsBoolean = FalseDim pwd AsString = ""Try
                conn.Open()

                reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

                If reader.HasRows Then
                    reader.Read()
                    pwd = reader.GetString(0)
                    isApproved = reader.GetBoolean(1)
                ElseReturnFalseEndIf

                reader.Close()

                If CheckPassword(password, pwd) ThenIf isApproved Then
                        isValid = TrueDim 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()
                    EndIfElse
                    conn.Close()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()
                conn.Close()
            EndTryReturn isValid
    EndFunction

    '    ' UpdateFailureCount    '   A helper method that performs the checks and updates associated with    ' password failure tracking.    'PrivateSub UpdateFailureCount(username AsString, failureType AsString)

      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 = NothingDim windowStart As DateTime = New DateTime()
            Dim failureCount AsInteger = 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)
                    EndIfIf failureType = "passwordAnswer"Then
                        failureCount = reader.GetInt32(2)
                        windowStart = reader.GetDateTime(3)
                    EndIfEndIf

                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 _
                      ThrowNew 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 _
                          ThrowNew 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 _
                          ThrowNew ProviderException("Unable to update failure count.")
                    EndIfEndIfCatch e As OdbcException
                If WriteExceptionsToEventLog Then
                    WriteToEventLog(e, "UpdateFailureCount")

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()
                conn.Close()
            EndTryEndSub

    '    ' CheckPassword    '   Compares password values based on the MembershipPasswordFormat.    'PrivateFunction CheckPassword(password AsString, dbpassword AsString) AsBooleanDim pass1 AsString = password
      Dim pass2 AsString = dbpassword

      SelectCase PasswordFormat      
        Case MembershipPasswordFormat.Encrypted
          pass2 = UnEncodePassword(dbpassword)
        Case MembershipPasswordFormat.Hashed
          pass1 = EncodePassword(password)
        CaseElseEndSelectIf pass1 = pass2 ThenReturnTrueEndIfReturnFalseEndFunction

    '    ' EncodePassword    '   Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.    'PrivateFunction EncodePassword(password AsString) AsStringDim encodedPassword AsString = password

      SelectCase 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)))
        CaseElseThrowNew ProviderException("Unsupported password format.")
      EndSelectReturn encodedPassword
    EndFunction

    '    ' UnEncodePassword    '   Decrypts or leaves the password clear based on the PasswordFormat.    'PrivateFunction UnEncodePassword(encodedPassword AsString) AsStringDim password AsString = encodedPassword

      SelectCase PasswordFormat
        Case MembershipPasswordFormat.Clear

        Case MembershipPasswordFormat.Encrypted
          password = _
            Encoding.Unicode.GetString(DecryptPassword(Convert.FromBase64String(password)))
        Case MembershipPasswordFormat.Hashed
          ThrowNew ProviderException("Cannot unencode a hashed password.")
        CaseElse
          throw new ProviderException("Unsupported password format.")
      EndSelectReturn password
    EndFunction
    '    ' HexToByte    '   Converts a hexadecimal string to a byte array. Used to convert encryption    ' key values from the configuration.    'PrivateFunction HexToByte(hexString AsString) AsByte()
      Dim ReturnBytes((hexString.Length \ 2) - 1) AsByteFor i AsInteger = 0 To ReturnBytes.Length - 1
        ReturnBytes(i) = Convert.ToByte(hexString.Substring(i*2, 2), 16)
      NextReturn ReturnBytes
    EndFunction

    '    ' MembershipProvider.FindUsersByName    'PublicOverridesFunction FindUsersByName(usernameToMatch AsString, _
                                              pageIndex AsInteger, _
                                              pageSize AsInteger, _
                                              ByRef totalRecords AsInteger) _
                              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 = NothingTry
                conn.Open()
                totalRecords = CInt(cmd.ExecuteScalar())

                If totalRecords <= 0 ThenReturn 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 AsInteger = 0
                Dim startIndex AsInteger = pageSize * pageIndex
                Dim endIndex AsInteger = startIndex + pageSize - 1

                DoWhile reader.Read()
                    If counter >= startIndex ThenDim u As MembershipUser = GetUserFromReader(reader)
                        users.Add(u)
                    EndIfIf counter >= endIndex Then cmd.Cancel()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()

                conn.Close()
            EndTryReturn users
    EndFunction
    '    ' MembershipProvider.FindUsersByEmail    'PublicOverridesFunction FindUsersByEmail(emailToMatch AsString, _
                                               pageIndex AsInteger, _
                                               pageSize AsInteger, _
                                               ByRef totalRecords AsInteger) _
                              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 ThenReturn 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 AsInteger = 0
                Dim startIndex AsInteger = pageSize * pageIndex
                Dim endIndex AsInteger = startIndex + pageSize - 1

                DoWhile reader.Read()
                    If counter >= startIndex ThenDim u As MembershipUser = GetUserFromReader(reader)
                        users.Add(u)
                    EndIfIf counter >= endIndex Then cmd.Cancel()

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

                    ThrowNew ProviderException(exceptionMessage)
                ElseThrow e
                EndIfFinallyIfNot reader IsNothingThen reader.Close()

                conn.Close()
            EndTryReturn users
    EndFunction

    '    ' WriteToEventLog    '   A helper function that writes exception detail to the event log. Exceptions    ' are written to the event log as a security measure to aSub Private database    ' details from being Returned to the browser. If a method does not Return a status    ' or boolean indicating the action succeeded or failed, a generic exception is also     ' Thrown by the caller.    'PrivateSub WriteToEventLog(e As Exception, action AsString)    
      Dim log As EventLog = New EventLog()
      log.Source = eventSource
      log.Log = eventLog

      Dim message AsString = "An exception occurred communicating with the data source." & vbCrLf & vbCrLf
      message &= "Action: " & action & vbCrLf & vbCrLf
      message &= "Exception: " & e.ToString()

      log.WriteEnTry(message)
    EndSubEndClassEndNamespace
using System.Web.Security;
using System.Configuration.Provider;
using System.Collections.Specialized;
using System;
using System.Data;
using System.Data.Odbc;
using System.Configuration;
using System.Diagnostics;
using System.Web;
using System.Globalization;
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
{

  publicsealedclass OdbcMembershipProvider: MembershipProvider
  {

    //// Global connection string, generated password length, generic exception message, event log info.//privateint    newPasswordLength = 8;
    privatestring eventSource = "OdbcMembershipProvider";
    privatestring eventLog = "Application";
    privatestring exceptionMessage = "An exception occurred. Please check the Event Log.";
    privatestring 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.//privatebool pWriteExceptionsToEventLog;

    publicbool WriteExceptionsToEventLog
    {
      get { return pWriteExceptionsToEventLog; }
      set { pWriteExceptionsToEventLog = value; }
    }


    //// System.Configuration.Provider.ProviderBase.Initialize Method//publicoverridevoid Initialize(string name, NameValueCollection config)
    {
      //// Initialize values from web.config.//if (config == null)
        thrownew ArgumentNullException("config");

      if (name == null || name.Length == 0)
        name = "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:
          thrownew ProviderException("Password format not supported.");
      }

      //// Initialize OdbcConnection.//

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

      if (ConnectionStringSettings == null || ConnectionStringSettings.ConnectionString.Trim() == "")
      {
        thrownew 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)
          thrownew ProviderException("Hashed or Encrypted passwords " +
                                      "are not supported with auto-generated keys.");
    }


    //// A helper function to retrieve config values from the configuration file.//privatestring GetConfigValue(string configValue, string defaultValue)
    {
      if (String.IsNullOrEmpty(configValue))
        return defaultValue;

      return configValue;
    }


    //// System.Web.Security.MembershipProvider properties.//privatestring pApplicationName;
    privatebool   pEnablePasswordReset;
    privatebool   pEnablePasswordRetrieval;
    privatebool   pRequiresQuestionAndAnswer;
    privatebool   pRequiresUniqueEmail;
    privateint    pMaxInvalidPasswordAttempts;
    privateint    pPasswordAttemptWindow;
    private MembershipPasswordFormat pPasswordFormat;

    publicoverridestring ApplicationName
    {
      get { return pApplicationName; }
      set { pApplicationName = value; }
    } 

    publicoverridebool EnablePasswordReset
    {
      get { return pEnablePasswordReset; }
    }


    publicoverridebool EnablePasswordRetrieval
    {
      get { return pEnablePasswordRetrieval; }
    }


    publicoverridebool RequiresQuestionAndAnswer
    {
      get { return pRequiresQuestionAndAnswer; }
    }


    publicoverridebool RequiresUniqueEmail
    {
      get { return pRequiresUniqueEmail; }
    }


    publicoverrideint MaxInvalidPasswordAttempts
    {
      get { return pMaxInvalidPasswordAttempts; }
    }


    publicoverrideint PasswordAttemptWindow
    {
      get { return pPasswordAttemptWindow; }
    }


    publicoverride MembershipPasswordFormat PasswordFormat
    {
      get { return pPasswordFormat; }
    }

    privateint pMinRequiredNonAlphanumericCharacters;

    publicoverrideint MinRequiredNonAlphanumericCharacters
    {
      get { return pMinRequiredNonAlphanumericCharacters; }
    }

    privateint pMinRequiredPasswordLength;

    publicoverrideint MinRequiredPasswordLength
    {
      get { return pMinRequiredPasswordLength; }
    }

    privatestring pPasswordStrengthRegularExpression;

    publicoverridestring PasswordStrengthRegularExpression
    {
      get { return pPasswordStrengthRegularExpression; }
    }

    //// System.Web.Security.MembershipProvider methods.////// MembershipProvider.ChangePassword//publicoverridebool ChangePassword(string username, string oldPwd, string newPwd)
    {
      if (!ValidateUser(username, oldPwd))
        returnfalse;


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

      OnValidatingPassword(args);

      if (args.Cancel)
        if (args.FailureInformation != null)
          throw args.FailureInformation;
        elsethrownew 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");

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

      if (rowsAffected > 0)
      {
        returntrue;
      }

      returnfalse;
    }



    //// MembershipProvider.ChangePasswordQuestionAndAnswer//publicoverridebool ChangePasswordQuestionAndAnswer(string username,
                  string password,
                  string newPwdQuestion,
                  string newPwdAnswer)
    {
      if (!ValidateUser(username, password))
        returnfalse;

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

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

      if (rowsAffected > 0)
      {
        returntrue;
      }

      returnfalse;
    }



    //// MembershipProvider.CreateUser//publicoverride 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;
        returnnull;
      }



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

      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;
            returnnull;
          }
        }

        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;
      }


      returnnull;
    }



    //// MembershipProvider.DeleteUser//publicoverridebool 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");

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

      if (rowsAffected > 0)
        returntrue;

      returnfalse;
    }



    //// MembershipProvider.GetAllUsers//publicoverride MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, outint 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 ");

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

      return users;
    }


    //// MembershipProvider.GetNumberOfUsersOnline//publicoverrideint 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");

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

      return numOnline;
    }



    //// MembershipProvider.GetPassword//publicoverridestring GetPassword(string username, string answer)
    {
      if (!EnablePasswordRetrieval)
      {
        thrownew ProviderException("Password Retrieval Not Enabled.");
      }

      if (PasswordFormat == MembershipPasswordFormat.Hashed)
      {
        thrownew 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))
            thrownew MembershipPasswordException("The supplied user is locked out.");

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

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


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

        thrownew MembershipPasswordException("Incorrect password answer.");
      }


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

      return password;
    }



    //// MembershipProvider.GetUser(string, bool)//publicoverride 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)");

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

        conn.Close();
      }

      return u;      
    }


    //// MembershipProvider.GetUser(object, bool)//publicoverride 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)");

          thrownew 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//publicoverridebool 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");

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

      if (rowsAffected > 0)
        returntrue;

      returnfalse;      
    }


    //// MembershipProvider.GetUserNameByEmail//publicoverridestring 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");

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

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

      return username;
    }




    //// MembershipProvider.ResetPassword//publicoverridestring ResetPassword(string username, string answer)
    {
      if (!EnablePasswordReset)
      {
        thrownew NotSupportedException("Password reset is not enabled.");
      }

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

        thrownew 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;
        elsethrownew 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))
            thrownew MembershipPasswordException("The supplied user is locked out.");

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

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

          thrownew 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");

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

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


    //// MembershipProvider.UpdateUser//publicoverridevoid 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");

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


    //// MembershipProvider.ValidateUser//publicoverridebool 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
        {
          returnfalse;
        }

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

          thrownew 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.//privatevoid 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)
            thrownew 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)
              thrownew 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)
               thrownew ProviderException("Unable to update failure count.");
          }
        }
      }
      catch (OdbcException e)
      {
        if (WriteExceptionsToEventLog)
        {
          WriteToEventLog(e, "UpdateFailureCount");

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


    //// CheckPassword//   Compares password values based on the MembershipPasswordFormat.//privatebool 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)
      {
        returntrue;
      }

      returnfalse;
    }


    //// EncodePassword//   Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.//privatestring 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:
          thrownew ProviderException("Unsupported password format.");
      }

      return encodedPassword;
    }


    //// UnEncodePassword//   Decrypts or leaves the password clear based on the PasswordFormat.//privatestring 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:
          thrownew ProviderException("Cannot unencode a hashed password.");
        default:
          thrownew ProviderException("Unsupported password format.");
      }

      return password;
    }

    //// HexToByte//   Converts a hexadecimal string to a byte array. Used to convert encryption// key values from the configuration.//privatebyte[] HexToByte(string hexString)
    {
      byte[] returnBytes = newbyte[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//publicoverride MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, outint 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");

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

        conn.Close();
      }

      return users;
    }

    //// MembershipProvider.FindUsersByEmail//publicoverride MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, outint 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");

          thrownew 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.//privatevoid 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