ASP.NET 2.0 Membership Provider for MySQL

[Tom Sweeney]

Just to explore the new Membership API model in ASP.NET 2.0, I decided to implement a MembershipProvider for MySQL. Here is my first kick at the can. I'm not sure if this is the proper place for this sort of thing; if not, let me know.

Some notes:
- I used VS 2005 with Connector 1.0.7 for .NET 2.0 and MySQL 5.0.18
- For the membership passwordFormat key, if you are using the "Hashed" (default) or "Encrypted" values, you MUST create your own keys in the <machineKey/> element in web.config. See the "How To: Configure MachineKey in ASP.NET 2.0" article in MSDN for more details

Code begins:

Imports System
Imports System.Collections.Specialized
Imports System.Configuration
Imports System.Configuration.Provider
Imports System.Data
Imports System.Diagnostics
Imports System.Globalization
Imports System.Security.Cryptography
Imports System.Text
Imports System.Web
Imports System.Web.Configuration
Imports System.Web.Security
Imports MySql.Data.MySqlClient 'for MySQL access
Imports MySql.Data.Types 'for MySQL data type conversions

'
' This provider works with the following schema for the table of user data.
' From MySQL 5.0; this CREATE statement may not work with older versions of MySQL
'
'CREATE TABLE `users` (
' `PKID` int(11) NOT NULL auto_increment,
' `Username` varchar(255) NOT NULL,
' `ApplicationName` varchar(255) NOT NULL,
' `Email` varchar(128) NOT NULL,
' `Comment` varchar(255) default NULL,
' `Password` varchar(128) NOT NULL,
' `PasswordQuestion` varchar(255) default NULL,
' `PasswordAnswer` varchar(255) default NULL,
' `IsApproved` bit(1) default NULL,
' `LastActivityDate` datetime default NULL,
' `LastLoginDate` datetime default NULL,
' `LastPasswordChangedDate` datetime default NULL,
' `CreationDate` datetime default NULL,
' `IsOnLine` bit(1) default NULL,
' `IsLockedOut` bit(1) default NULL,
' `LastLockedOutDate` datetime default NULL,
' `FailedPasswordAttemptCount` int(11) default NULL,
' `FailedPasswordAttemptWindowStart` datetime default NULL,
' `FailedPasswordAnswerAttemptCount` int(11) default NULL,
' `FailedPasswordAnswerAttemptWindowStart` datetime default NULL,
' PRIMARY KEY (`PKID`)
') ENGINE=InnoDB DEFAULT CHARSET=utf8;
'

Namespace MySql.Membership


Public NotInheritable Class MySqlMembershipProvider
Inherits MembershipProvider

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

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

'
' Used when determining encryption key values.
'

Private machineKey As MachineKeySection


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

Private pWriteExceptionsToEventLog As Boolean

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



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

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


'
' Initialize values from web.config.
'

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

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

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

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


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

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

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

'
' Initialize MySqlConnection.
'

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

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

connectionString = ConnectionStringSettings.ConnectionString


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


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

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

Return configValue
End Function


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


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

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

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


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


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


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


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


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


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

Private pMinRequiredNonAlphanumericCharacters As Integer

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

Private pMinRequiredPasswordLength As Integer

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

Private pPasswordStrengthRegularExpression As String

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

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

'
' MembershipProvider.ChangePassword
'

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


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

OnValidatingPassword(args)

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


Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("UPDATE `" & tableName & "`" & _
" SET Password = ?Password, LastPasswordChangedDate = ?LastPasswordChangedDate " & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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


Dim rowsAffected As Integer = 0

Try
conn.Open()

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

WriteToEventLog(e, "ChangePassword")

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

If rowsAffected > 0 Then
Return True
End If

Return False
End Function



'
' MembershipProvider.ChangePasswordQuestionAndAnswer
'

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

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("UPDATE `" & tableName & "`" & _
" SET PasswordQuestion = ?PasswordQuestion, PasswordAnswer = ?PasswordAnswer" & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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


Dim rowsAffected As Integer = 0

Try
conn.Open()

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

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

If rowsAffected > 0 Then
Return True
End If

Return False
End Function



'
' MembershipProvider.CreateUser
'

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

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

OnValidatingPassword(Args)

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


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

Dim u As MembershipUser = GetUser(username, False)

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

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

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

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

Try
conn.Open()

Dim recAdded As Integer = cmd.ExecuteNonQuery()

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

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


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

Return Nothing
End Function



'
' MembershipProvider.DeleteUser
'

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("DELETE FROM `" & tableName & "`" & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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

Dim rowsAffected As Integer = 0

Try
conn.Open()

rowsAffected = cmd.ExecuteNonQuery()

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

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

If rowsAffected > 0 Then _
Return True

Return False
End Function



'
' MembershipProvider.GetAllUsers
'

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Count(*) FROM `" & tableName & "` " & _
"WHERE ApplicationName = ?ApplicationName", conn)
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName

Dim users As MembershipUserCollection = New MembershipUserCollection()

Dim reader As MySqlDataReader = Nothing
totalRecords = 0

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

If totalRecords <= 0 Then Return users

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

reader = cmd.ExecuteReader()

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

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

If counter >= endIndex Then cmd.Cancel()

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

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

Return users
End Function


'
' MembershipProvider.GetNumberOfUsersOnline
'

Public Overrides Function GetNumberOfUsersOnline() As Integer

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Count(*) FROM `" & tableName & "`" & _
" WHERE LastActivityDate > ?CompareDate AND ApplicationName = ?ApplicationName", conn)

cmd.Parameters.Add("?CompareDate", MySqlDbType.Datetime).Value = compareTime
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

Dim numOnline As Integer = 0

Try
conn.Open()

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

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

Return numOnline
End Function



'
' MembershipProvider.GetPassword
'

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

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

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Password, PasswordAnswer, IsLockedOut FROM `" & tableName & "`" & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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

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

Try
conn.Open()

reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

If reader.HasRows Then
reader.Read()

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

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

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


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

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


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

Return password
End Function



'
' MembershipProvider.GetUser(String, Boolean)
'

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

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

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

Dim u As MembershipUser = Nothing
Dim reader As MySqlDataReader = Nothing

Try
conn.Open()

reader = cmd.ExecuteReader()

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

If userIsOnline Then
Dim updateCmd As MySqlCommand = New MySqlCommand("UPDATE `" & tableName & "` " & _
"SET LastActivityDate = ?LastActivityDate " & _
"WHERE Username = ?Username AND Applicationname = ?ApplicationName", conn)

updateCmd.Parameters.Add("?LastActivityDate", MySqlDbType.Datetime).Value = DateTime.Now
updateCmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
updateCmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

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

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

conn.Close()
End Try

Return u
End Function


'
' MembershipProvider.GetUser(Object, Boolean)
'

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

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

cmd.Parameters.Add("?PKID", MySqlDbType.Int32).Value = providerUserKey

Dim u As MembershipUser = Nothing
Dim reader As MySqlDataReader = Nothing

Try
conn.Open()

reader = cmd.ExecuteReader()

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

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

updateCmd.Parameters.Add("?LastActivityDate", MySqlDbType.Datetime).Value = DateTime.Now
updateCmd.Parameters.Add("?PKID", MySqlDbType.Int32).Value = providerUserKey

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

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

conn.Close()
End Try

Return u
End Function


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

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

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

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

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

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

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

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

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

Return u
End Function


'
' MembershipProvider.UnlockUser
'

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

cmd.Parameters.Add("?LastLockedOutDate", MySqlDbType.Datetime).Value = DateTime.Now
cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

Dim rowsAffected As Integer = 0

Try
conn.Open()

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

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

If rowsAffected > 0 Then _
Return True

Return False
End Function


'
' MembershipProvider.GetUserNameByEmail
'

Public Overrides Function GetUserNameByEmail(ByVal email As String) As String
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Username" & _
" FROM `" & tableName & "` WHERE Email = ?Email AND ApplicationName = ?ApplicationName", conn)

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

'Dim username As String = ""
Dim username As Object

Try
conn.Open()

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

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

If username Is Nothing Then
Return ""
Else
Return username.ToString()
End If

End Function




'
' MembershipProvider.ResetPassword
'

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

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

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

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

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


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

OnValidatingPassword(Args)

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


Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT PasswordAnswer, IsLockedOut FROM `" & tableName & "`" & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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

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

Try
conn.Open()

reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

If reader.HasRows Then
reader.Read()

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

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

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

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

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

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

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

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

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


'
' MembershipProvider.UpdateUser
'

Public Overrides Sub UpdateUser(ByVal user As MembershipUser)

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("UPDATE `" & tableName & "`" & _
" SET Email = ?Email, Comment = ?Comment," & _
" IsApproved = ?IsApproved" & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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


Try
conn.Open()

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

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


'
' MembershipProvider.ValidateUser
'

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Password, IsApproved FROM `" & tableName & "`" & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName AND IsLockedOut = False", conn)

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

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

Try
conn.Open()

reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

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

reader.Close()

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

Dim updateCmd As MySqlCommand = New MySqlCommand("UPDATE `" & tableName & "` SET LastLoginDate = ?LastLoginDate" & _
" WHERE Username = ?UserName AND ApplicationName = ?ApplicationName", conn)

updateCmd.Parameters.Add("?LastLoginDate", MySqlDbType.Datetime).Value = DateTime.Now
updateCmd.Parameters.Add("?UserName", MySqlDbType.VarChar, 255).Value = username
updateCmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

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

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

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

Return isValid
End Function


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

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT FailedPasswordAttemptCount, " & _
" FailedPasswordAttemptWindowStart, " & _
" FailedPasswordAnswerAttemptCount, " & _
" FailedPasswordAnswerAttemptWindowStart " & _
" FROM `" & tableName & "` " & _
" WHERE Username = ?Username AND ApplicationName = ?ApplicationName", conn)

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

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

Try
conn.Open()

reader = cmd.ExecuteReader(CommandBehavior.SingleRow)

If reader.HasRows Then
reader.Read()

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

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

reader.Close()

Dim windowEnd As DateTime = windowStart.AddMinutes(PasswordAttemptWindow)

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

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

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

cmd.Parameters.Clear()

cmd.Parameters.Add("?Count", MySqlDbType.Int32).Value = 1
cmd.Parameters.Add("?WindowStart", MySqlDbType.Datetime).Value = DateTime.Now
cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

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

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

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

cmd.Parameters.Clear()

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

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

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

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

cmd.Parameters.Clear()

cmd.Parameters.Add("?Count", MySqlDbType.Int32).Value = failureCount
cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

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

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


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

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

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

If pass1 = pass2 Then
Return True
End If

Return False
End Function


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

Private Function EncodePassword(ByVal password As String) As String
If password = "" Then
Return ""
End If

Dim encodedPassword As String = password

Select Case PasswordFormat
Case MembershipPasswordFormat.Clear

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

Return encodedPassword
End Function


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

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

Select Case PasswordFormat
Case MembershipPasswordFormat.Clear

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

Return password
End Function

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

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


'
' MembershipProvider.FindUsersByName
'

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Count(*) FROM `" & tableName & "` " & _
"WHERE Username LIKE ?UsernameSearch AND ApplicationName = ?ApplicationName", conn)
cmd.Parameters.Add("?UsernameSearch", MySqlDbType.VarChar, 255).Value = usernameToMatch
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName

Dim users As MembershipUserCollection = New MembershipUserCollection()

Dim reader As MySqlDataReader = Nothing

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

If totalRecords <= 0 Then Return users

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

reader = cmd.ExecuteReader()

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

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

If counter >= endIndex Then cmd.Cancel()

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

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

conn.Close()
End Try

Return users
End Function

'
' MembershipProvider.FindUsersByEmail
'

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

Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand("SELECT Count(*) FROM `" & tableName & "` " & _
"WHERE Email LIKE ?EmailSearch AND ApplicationName = ?ApplicationName", conn)
cmd.Parameters.Add("?EmailSearch", MySqlDbType.VarChar, 255).Value = emailToMatch
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName

Dim users As MembershipUserCollection = New MembershipUserCollection()

Dim reader As MySqlDataReader = Nothing
totalRecords = 0

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

If totalRecords <= 0 Then Return users

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

reader = cmd.ExecuteReader()

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

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

If counter >= endIndex Then cmd.Cancel()

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

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

conn.Close()
End Try

Return users
End Function


'
' WriteToEventLog
' A helper function that writes exception detail to the event log. Exceptions
' are written to the event log as a security measure to aSub Private database
' details from being Returned to the browser. If a method does not Return a status
' or boolean indicating the action succeeded or failed, a generic exception is also
' Thrown by the caller.
'

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

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

log.WriteEntry(message)
End Sub

End Class
End Namespace



Edited 3 time(s). Last edit at 01/11/2006 11:58AM by Tom Sweeney.