Hej! Vet inte hur rätt det är, översatt av http://www.carlosag.net/Tools/CodeTranslator/Default.aspx Tack tack!mysql membership and role provider for asp.net 2.0
Är det någon som har eller kan fixa en VB-variant av providern på följande sida: http://sprinj.com/ideas/view/5-a-mysql-membership-and-role-provider-for-aspnet-20
Det är bara filerna MySQLMembershipProvider.cs samt MySQLRoleProvider.cs det handlar om.
Tyvärr är jag inte tillräckligt händig för att fixa översättningen på egen hand.
Andra lösningar är givetvis oxå välkomna.
Mvh MickeSv: mysql membership and role provider for asp.net 2.0
MySQLMembershipProvider.vb:
Namespace Andri.Web
Imports System.Web.Security
Imports System.Configuration.Provider
Imports System.Collections.Specialized
Imports System
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Configuration
Imports System.Diagnostics
Imports System.Web
Imports System.Globalization
Imports System.Security.Cryptography
Imports System.Text
Imports System.Web.Configuration
Public NotInheritable Class MySqlMembershipProvider
Inherits MembershipProvider
'
' Global connection string, generated password length, generic exception message, event log info.
'
Private Const newPasswordLength As Integer = 5
Private Const tableName As String = "Users"
Private connectionString As String
Private encryptionKey() As Byte
'
' 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
Private pMinRequiredNonAlphanumericCharacters As Integer
Private pMinRequiredPasswordLength As Integer
Private pPasswordStrengthRegularExpression As String
Public Overrides Property ApplicationName As String
Get
Return pApplicationName
End Get
Set
pApplicationName = value
End Set
End Property
Public Overrides ReadOnly Property EnablePasswordReset As Boolean
Get
Return pEnablePasswordReset
End Get
End Property
Public Overrides ReadOnly Property EnablePasswordRetrieval As Boolean
Get
Return pEnablePasswordRetrieval
End Get
End Property
Public Overrides ReadOnly Property RequiresQuestionAndAnswer As Boolean
Get
Return pRequiresQuestionAndAnswer
End Get
End Property
Public Overrides ReadOnly Property RequiresUniqueEmail As Boolean
Get
Return pRequiresUniqueEmail
End Get
End Property
Public Overrides ReadOnly Property MaxInvalidPasswordAttempts As Integer
Get
Return pMaxInvalidPasswordAttempts
End Get
End Property
Public Overrides ReadOnly Property PasswordAttemptWindow As Integer
Get
Return pPasswordAttemptWindow
End Get
End Property
Public Overrides ReadOnly Property PasswordFormat As MembershipPasswordFormat
Get
Return pPasswordFormat
End Get
End Property
Public Overrides ReadOnly Property MinRequiredNonAlphanumericCharacters As Integer
Get
Return pMinRequiredNonAlphanumericCharacters
End Get
End Property
Public Overrides ReadOnly Property MinRequiredPasswordLength As Integer
Get
Return pMinRequiredPasswordLength
End Get
End Property
Public Overrides ReadOnly Property PasswordStrengthRegularExpression As String
Get
Return pPasswordStrengthRegularExpression
End Get
End Property
' = new byte[] { 255, 30, 40, 20, 13, 59 };
'
' System.Configuration.Provider.ProviderBase.Initialize Method
'
Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.Initialize")
'
' Initialize values from web.config.
'
If (config = Nothing) Then
Throw New ArgumentNullException("config")
End If
If ((name = Nothing) _
OrElse (name.Length = 0)) Then
name = "MySqlMembershipProvider"
End If
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("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"))
Try
encryptionKey = HexToByte(GetConfigValue(config("encryptionKey"), "AB56FE8EA700B42A"))
Catch e As Exception
End Try
Dim temp_format As String = config("passwordFormat")
If (temp_format = 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 = Nothing) _
OrElse (ConnectionStringSettings.ConnectionString.Trim.Length = 0)) Then
Throw New ProviderException("Connection string cannot be blank.")
End If
connectionString = ConnectionStringSettings.ConnectionString
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
Return String.IsNullOrEmpty(configValue)
'TODO: Warning!!!, inline IF is not supported ?
End Function
'
' System.Web.Security.MembershipProvider methods.
'
Public Overrides Function ChangePassword(ByVal username As String, ByVal oldPwd As String, ByVal newPwd As String) As Boolean
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.ChangePassword")
If Not ValidateUser(username, oldPwd) Then
Return false
End If
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 MembershipPasswordException("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
conn.Open
Dim rowsAffected As Integer = cmd.ExecuteNonQuery
conn.Close
Return (rowsAffected > 0)
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
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.ChangePasswordQuestionAndAnswer")
If Not ValidateUser(username, password) Then
Return false
End If
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("UPDATE `" _
+ (tableName + ("`" + (" SET PasswordQuestion = ?Question, PasswordAnswer = ?Answer" + " 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
conn.Open
Dim rowsAffected As Integer = cmd.ExecuteNonQuery
conn.Close
Return (rowsAffected > 0)
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
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.CreateUser")
Dim args As ValidatePasswordEventArgs = New ValidatePasswordEventArgs(username, password, true)
OnValidatingPassword(args)
If args.Cancel Then
status = MembershipCreateStatus.InvalidPassword
Return Nothing
End If
Dim name As String = GetUserNameByEmail(email)
If (RequiresUniqueEmail AndAlso (Not (name) Is Nothing)) Then
status = MembershipCreateStatus.DuplicateEmail
Return Nothing
End If
If (Nothing = GetUser(username, false)) Then
Dim createDate As DateTime = DateTime.Now
If (providerUserKey = Nothing) Then
providerUserKey = Guid.NewGuid
ElseIf Not (providerUserKey = Guid) Then
status = MembershipCreateStatus.InvalidProviderUserKey
Return Nothing
End If
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("INSERT INTO `" _
+ (tableName + ("`" + (" (PKID, Username, Password, Email, PasswordQuestion, " + (" PasswordAnswer, IsApproved," + (" Comment, CreationDate, LastPasswordChangedDate, LastActivityDate," + (" ApplicationName, IsLockedOut, LastLockedOutDate," + (" FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, " + (" FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart)" + (" Values(?PKID, ?Username, ?Password, ?Email, ?PasswordQuestion, " + (" ?PasswordAnswer, ?IsApproved, ?Comment, ?CreationDate, ?LastPasswordChangedDate, " + (" ?LastActivityDate, ?ApplicationName, ?IsLockedOut, ?LastLockedOutDate, " + (" ?FailedPasswordAttemptCount, ?FailedPasswordAttemptWindowStart, " + " ?FailedPasswordAnswerAttemptCount, ?FailedPasswordAnswerAttemptWindowStart)"))))))))))))), conn)
cmd.Parameters.Add("?PKID", MySqlDbType.VarChar).Value = providerUserKey.ToString
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 = (passwordAnswer = Nothing)
'TODO: Warning!!!, inline IF is not supported ?
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 = 0
'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
conn.Open
Dim recAdded As Integer = cmd.ExecuteNonQuery
status = (recAdded > 0)
'TODO: Warning!!!, inline IF is not supported ?
conn.Close
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
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.DeleteUser")
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
conn.Open
Dim rowsAffected As Integer = cmd.ExecuteNonQuery
If deleteAllRelatedData Then
' nothing to do ? Process commands to delete all data for the user in the database.
End If
conn.Close
Return (rowsAffected > 0)
End Function
'
' MembershipProvider.GetAllUsers
'
Public Overrides Function GetAllUsers(ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As MembershipUserCollection
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.GetAllUsers")
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim users As MembershipUserCollection = New MembershipUserCollection
conn.Open
Dim startIndex As Integer = (pageSize * pageIndex)
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT SQL_CALC_FOUND_ROWS PKID, Username, Email, PasswordQuestion," + (" Comment, IsApproved, IsLockedOut, CreationDate, LastLoginDate," + (" LastActivityDate, LastPasswordChangedDate, LastLockedOutDate " + (" FROM `" _
+ (tableName + ("` " + (" WHERE ApplicationName = ?ApplicationName " + (" ORDER BY Username Asc" + (" LIMIT " _
+ (startIndex + ("," + pageSize))))))))))), conn)
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
users.Add(GetUserFromReader(reader))
End While
reader.Close
cmd.CommandText = "SELECT FOUND_ROWS()"
totalRecords = Convert.ToInt32(cmd.ExecuteScalar)
conn.Close
Return users
End Function
'
' MembershipProvider.GetNumberOfUsersOnline
'
Public Overrides Function GetNumberOfUsersOnline() As Integer
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.GetNumberOfUsersOnline")
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
conn.Open
Dim numOnline As Integer = Convert.ToInt32(cmd.ExecuteScalar)
conn.Close
Return numOnline
End Function
'
' MembershipProvider.GetPassword
'
Public Overrides Function GetPassword(ByVal username As String, ByVal answer As String) As String
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.GetPassword")
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 password As String = ""
Dim passwordAnswer As String = ""
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
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read
If reader.GetBoolean(2) Then
Throw New MembershipPasswordException("The supplied user is locked out.")
End If
password = reader.GetString(0)
passwordAnswer = reader.GetString(1)
Else
Throw New MembershipPasswordException("The supplied user name is not found.")
End If
reader.Close
conn.Close
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, bool)
'
Public Overloads Overrides Function GetUser(ByVal username As String, ByVal userIsOnline As Boolean) As MembershipUser
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.GetUser")
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim u As MembershipUser = Nothing
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
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
If reader.HasRows Then
reader.Read
u = GetUserFromReader(reader)
reader.Close
End If
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.VarChar).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
updateCmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
updateCmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName
updateCmd.ExecuteNonQuery
End If
conn.Close
Return u
End Function
'
' MembershipProvider.GetUser(object, bool)
'
Public Overloads Overrides Function GetUser(ByVal providerUserKey As Object, ByVal userIsOnline As Boolean) As MembershipUser
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.GetUser")
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.VarChar).Value = providerUserKey
Dim u As MembershipUser = Nothing
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
If reader.HasRows Then
reader.Read
u = GetUserFromReader(reader)
reader.Close
End If
reader.Close
If userIsOnline Then
Dim updateCmd As MySqlCommand = New MySqlCommand(("UPDATE `" _
+ (tableName + ("` " + (" SET LastActivityDate = ?LastActivityDate " + " WHERE PKID = ?PKID")))), conn)
updateCmd.Parameters.Add("?LastActivityDate", MySqlDbType.VarChar).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
updateCmd.Parameters.Add("?PKID", MySqlDbType.VarChar).Value = providerUserKey
updateCmd.ExecuteNonQuery
End If
conn.Close
Return u
End Function
'
' GetUserFromReader
' A helper function that takes the current row from the MySqlDataReader
' and hydrates a MembershiUser from the values. Called by the
' MembershipUser.GetUser implementation.
'
Private Function GetUserFromReader(ByVal reader As MySqlDataReader) As MembershipUser
Dim providerUserKey As Object = New Guid(reader.GetValue(0).ToString)
Dim username As String = reader.IsDBNull(1)
'TODO: Warning!!!, inline IF is not supported ?
Dim email As String = reader.IsDBNull(2)
'TODO: Warning!!!, inline IF is not supported ?
Dim passwordQuestion As String = reader.IsDBNull(3)
'TODO: Warning!!!, inline IF is not supported ?
Dim comment As String = reader.IsDBNull(4)
'TODO: Warning!!!, inline IF is not supported ?
Dim isApproved As Boolean = reader.IsDBNull(5)
'TODO: Warning!!!, inline IF is not supported ?
Dim isLockedOut As Boolean = reader.IsDBNull(6)
'TODO: Warning!!!, inline IF is not supported ?
Dim creationDate As DateTime = reader.IsDBNull(7)
'TODO: Warning!!!, inline IF is not supported ?
Dim lastLoginDate As DateTime = reader.IsDBNull(8)
'TODO: Warning!!!, inline IF is not supported ?
Dim lastActivityDate As DateTime = reader.IsDBNull(9)
'TODO: Warning!!!, inline IF is not supported ?
Dim lastPasswordChangedDate As DateTime = reader.IsDBNull(10)
'TODO: Warning!!!, inline IF is not supported ?
Dim lastLockedOutDate As DateTime = reader.IsDBNull(11)
'TODO: Warning!!!, inline IF is not supported ?
Return New MembershipUser(Me.Name, username, providerUserKey, email, passwordQuestion, comment, isApproved, isLockedOut, creationDate, lastLoginDate, lastActivityDate, lastPasswordChangedDate, lastLockedOutDate)
End Function
'
' MembershipProvider.UnlockUser
'
Public Overrides Function UnlockUser(ByVal username As String) As Boolean
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.UnlockUser")
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("UPDATE `" _
+ (tableName + ("` " + (" SET IsLockedOut = 0, 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
conn.Open
Dim rowsAffected As Integer = cmd.ExecuteNonQuery
conn.Close
Return (rowsAffected > 0)
End Function
'
' MembershipProvider.GetUserNameByEmail
'
Public Overrides Function GetUserNameByEmail(ByVal email As String) As String
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.GetUserNameByEmail")
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
conn.Open
Dim username As String = CType(cmd.ExecuteScalar,String)
conn.Close
Return username
End Function
'
' MembershipProvider.ResetPassword
'
Public Overrides Function ResetPassword(ByVal username As String, ByVal answer As String) As String
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.ResetPassword")
If Not EnablePasswordReset Then
Throw New NotSupportedException("Password reset is not enabled.")
End If
If ((answer = 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 passwordAnswer As String = ""
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read
If reader.GetBoolean(1) Then
Throw New MembershipPasswordException("The supplied user is locked out.")
End If
passwordAnswer = reader.GetString(0)
Else
Throw New MembershipPasswordException("The supplied user name is not found.")
End If
reader.Close
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 = 0")))), 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
Dim rowsAffected As Integer = updateCmd.ExecuteNonQuery
conn.Close
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)
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.UpdateUser")
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
conn.Open
cmd.ExecuteNonQuery
conn.Close
End Sub
'
' MembershipProvider.ValidateUser
'
Public Overrides Function ValidateUser(ByVal username As String, ByVal password As String) As Boolean
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.ValidateUser")
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim isValid As Boolean = false
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT Password, IsApproved FROM `" _
+ (tableName + ("`" + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName AND IsLockedOut = 0"))), conn)
cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName
Dim isApproved As Boolean = false
Dim pwd As String = ""
conn.Open
Dim reader As MySqlDataReader = 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, LastActivityDate = ?LastActivityDate" + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName"))), conn)
updateCmd.Parameters.Add("?LastLoginDate", MySqlDbType.Datetime).Value = DateTime.Now
updateCmd.Parameters.Add("?LastActivityDate", MySqlDbType.Datetime).Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
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
conn.Close
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)
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.UpdateFailureCount")
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 windowStart As DateTime = New DateTime
Dim failureCount As Integer = 0
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader(CommandBehavior.SingleRow)
If reader.HasRows Then
reader.Read
Select Case (failureType)
Case "password"
failureCount = reader.GetInt32(0)
windowStart = reader.GetDateTime(1)
Case "passwordAnswer"
failureCount = reader.GetInt32(2)
windowStart = reader.GetDateTime(3)
End Select
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.
Select Case (failureType)
Case "password"
cmd.CommandText = ("UPDATE `" _
+ (tableName + ("` " + (" SET FailedPasswordAttemptCount = ?Count, " + (" FailedPasswordAttemptWindowStart = ?WindowStart " + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName")))))
Case "passwordAnswer"
cmd.CommandText = ("UPDATE `" _
+ (tableName + ("` " + (" SET FailedPasswordAnswerAttemptCount = ?Count, " + (" FailedPasswordAnswerAttemptWindowStart = ?WindowStart " + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName")))))
End Select
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.")
End If
End If
MaxInvalidPasswordAttempts
' 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.")
End If
' Password attempts have not exceeded the failure threshold. Update
' the failure counts. Leave the window the same.
Select Case (failureType)
Case "password"
cmd.CommandText = ("UPDATE `" _
+ (tableName + ("` " + (" SET FailedPasswordAttemptCount = ?Count" + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName"))))
Case "passwordAnswer"
cmd.CommandText = ("UPDATE `" _
+ (tableName + ("` " + (" SET FailedPasswordAnswerAttemptCount = ?Count" + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName"))))
End Select
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
conn.Close
End Sub
'
' CheckPassword
' Compares password values based on the MembershipPasswordFormat.
'
Private Function CheckPassword(ByVal password As String, ByVal dbpassword As String) As Boolean
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.CheckPassword")
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)
End Select
Return (pass1 = pass2)
End Function
'
' EncodePassword
' Encrypts, Hashes, or leaves the password clear based on the PasswordFormat.
'
Private Function EncodePassword(ByVal password As String) As String
Select Case (PasswordFormat)
Case MembershipPasswordFormat.Clear
Return password
Case MembershipPasswordFormat.Encrypted
Return Convert.ToBase64String(EncryptPassword(Encoding.Unicode.GetBytes(password)))
Case MembershipPasswordFormat.Hashed
Dim hash As HMACSHA1 = New HMACSHA1
hash.Key = encryptionKey
Return Convert.ToBase64String(hash.ComputeHash(Encoding.Unicode.GetBytes(password)))
Case Else
Throw New ProviderException("Unsupported password format.")
End Select
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
'
' MembershipProvider.FindUsersByName
'
Public Overrides Function FindUsersByName(ByVal usernameToMatch As String, ByVal pageIndex As Integer, ByVal pageSize As Integer, ByRef totalRecords As Integer) As MembershipUserCollection
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.FindUsersByName")
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim users As MembershipUserCollection = New MembershipUserCollection
Dim startIndex As Integer = (pageSize * pageIndex)
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT SQL_CALC_FOUND_ROWS 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" + (" LIMIT " _
+ (startIndex + ("," + pageSize))))))))))), conn)
cmd.Parameters.Add("?UsernameSearch", MySqlDbType.VarChar, 255).Value = usernameToMatch
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
users.Add(GetUserFromReader(reader))
End While
reader.Close
cmd.CommandText = "SELECT FOUND_ROWS()"
totalRecords = Convert.ToInt32(cmd.ExecuteScalar)
conn.Close
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
System.Web.HttpContext.Current.Trace.Warn("Andri.Web.MySqlMembershipProvider.FindUsersByEmail")
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim users As MembershipUserCollection = New MembershipUserCollection
conn.Open
Dim startIndex As Integer = (pageSize * pageIndex)
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT SQL_CALC_FOUND_ROWS 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" + (" LIMIT " _
+ (startIndex + ("," + pageSize))))))))))), conn)
cmd.Parameters.Add("?EmailSearch", MySqlDbType.VarChar, 255).Value = emailToMatch
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
users.Add(GetUserFromReader(reader))
End While
reader.Close
cmd.CommandText = "SELECT FOUND_ROWS()"
totalRecords = Convert.ToInt32(cmd.ExecuteScalar)
conn.Close
Return users
End Function
Private Function HexToByte(ByVal hexString As String) As Byte()
Dim returnBytes() As Byte = New Byte(((hexString.Length / 2)) - 1) {}
Dim i As Integer = 0
Do While (i < returnBytes.Length)
returnBytes(i) = Convert.ToByte(hexString.Substring((i * 2), 2), 16)
i = (i + 1)
Loop
Return returnBytes
End Function
End Class
End Namespace
MySQLRoleProvider.vb:
Namespace Andri.Web
Imports System.Web.Security
Imports System.Configuration.Provider
Imports System.Collections.Specialized
Imports System
Imports MySql.Data.MySqlClient
Imports System.Configuration
Imports System.Diagnostics
Imports System.Web
Imports System.Globalization
Imports System.Collections.Generic
Public NotInheritable Class MySqlRoleProvider
Inherits RoleProvider
'
' Global connection string, generic exception message, event log info.
'
Private Const rolesTable As String = "Roles"
Private Const usersInRolesTable As String = "UsersInRoles"
Private pConnectionStringSettings As ConnectionStringSettings
Private connectionString As String
'
' System.Web.Security.RoleProvider properties.
'
Private pApplicationName As String
Public Overrides Property ApplicationName As String
Get
Return pApplicationName
End Get
Set
pApplicationName = value
End Set
End Property
'
' System.Configuration.Provider.ProviderBase.Initialize Method
'
Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
'
' Initialize values from web.config.
'
If (config = Nothing) Then
Throw New ArgumentNullException("config")
End If
If ((name = Nothing) _
OrElse (name.Length = 0)) Then
name = "MySqlRoleProvider"
End If
If String.IsNullOrEmpty(config("description")) Then
config.Remove("description")
config.Add("description", "Sample MySql Role provider")
End If
' Initialize the abstract base class.
MyBase.Initialize(name, config)
If ((config("applicationName") = Nothing) _
OrElse (config("applicationName").Trim = "")) Then
pApplicationName = System.Web.Hosting.HostingEnvironment.ApplicationVirtualPath
Else
pApplicationName = config("applicationName")
End If
'
' Initialize MySqlConnection.
'
pConnectionStringSettings = ConfigurationManager.ConnectionStrings(config("connectionStringName"))
If ((pConnectionStringSettings = Nothing) _
OrElse (pConnectionStringSettings.ConnectionString.Trim = "")) Then
Throw New ProviderException("Connection string cannot be blank.")
End If
connectionString = pConnectionStringSettings.ConnectionString
End Sub
'
' RoleProvider.AddUsersToRoles
'
Public Overrides Sub AddUsersToRoles(ByVal usernames() As String, ByVal rolenames() As String)
For Each rolename As String In rolenames
If Not RoleExists(rolename) Then
Throw New ProviderException("Role name not found.")
End If
Next
For Each username As String In usernames
If (username.IndexOf(Microsoft.VisualBasic.ChrW(44)) > 0) Then
Throw New ArgumentException("User names cannot contain commas.")
End If
For Each rolename As String In rolenames
If IsUserInRole(username, rolename) Then
Throw New ProviderException("User is already in role.")
End If
Next
Next
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("INSERT INTO `" _
+ (usersInRolesTable + ("`" + (" (Username, Rolename, ApplicationName) " + " Values(?Username, ?Rolename, ?ApplicationName)")))), conn)
Dim userParm As MySqlParameter = cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255)
Dim roleParm As MySqlParameter = cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255)
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
Dim tran As MySqlTransaction = Nothing
conn.Open
Try
tran = conn.BeginTransaction
cmd.Transaction = tran
For Each username As String In usernames
For Each rolename As String In rolenames
userParm.Value = username
roleParm.Value = rolename
cmd.ExecuteNonQuery
Next
Next
tran.Commit
Catch e As MySqlException
Try
tran.Rollback
Catch As System.Exception
End Try
Throw e
End Try
conn.Close
End Sub
'
' RoleProvider.CreateRole
'
Public Overrides Sub CreateRole(ByVal rolename As String)
If (rolename.IndexOf(Microsoft.VisualBasic.ChrW(44)) > 0) Then
Throw New ArgumentException("Role names cannot contain commas.")
End If
If RoleExists(rolename) Then
Throw New ProviderException("Role name already exists.")
End If
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("INSERT INTO `" _
+ (rolesTable + ("`" + (" (Rolename, ApplicationName) " + " Values(?Rolename, ?ApplicationName)")))), conn)
cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = rolename
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
conn.Open
cmd.ExecuteNonQuery
conn.Close
End Sub
'
' RoleProvider.DeleteRole
'
Public Overrides Function DeleteRole(ByVal rolename As String, ByVal throwOnPopulatedRole As Boolean) As Boolean
If Not RoleExists(rolename) Then
Throw New ProviderException("Role does not exist.")
End If
If (throwOnPopulatedRole _
AndAlso (GetUsersInRole(rolename).Length > 0)) Then
Throw New ProviderException("Cannot delete a populated role.")
End If
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("DELETE FROM `" _
+ (rolesTable + ("`" + " WHERE Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = rolename
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
Dim cmd2 As MySqlCommand = New MySqlCommand(("DELETE FROM `" _
+ (usersInRolesTable + ("`" + " WHERE Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
cmd2.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = rolename
cmd2.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
Dim tran As MySqlTransaction = Nothing
conn.Open
Try
tran = conn.BeginTransaction
cmd.Transaction = tran
cmd2.Transaction = tran
cmd2.ExecuteNonQuery
cmd.ExecuteNonQuery
tran.Commit
Catch e As MySqlException
Try
tran.Rollback
Catch As System.Exception
End Try
Throw e
Finally
conn.Close
End Try
Return true
End Function
'
' RoleProvider.GetAllRoles
'
Public Overrides Function GetAllRoles() As String()
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim roles As List = New List
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT Rolename FROM `" _
+ (rolesTable + ("`" + " WHERE ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
roles.Add(reader.GetString(0))
End While
reader.Close
conn.Close
Return roles.ToArray
End Function
'
' RoleProvider.GetRolesForUser
'
Public Overrides Function GetRolesForUser(ByVal username As String) As String()
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim roles As List = New List
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT Rolename FROM `" _
+ (usersInRolesTable + ("`" + " WHERE Username = ?Username AND ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
roles.Add(reader.GetString(0))
End While
reader.Close
conn.Close
Return roles.ToArray
End Function
'
' RoleProvider.GetUsersInRole
'
Public Overrides Function GetUsersInRole(ByVal rolename As String) As String()
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim roles As List = New List
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT Username FROM `" _
+ (usersInRolesTable + ("`" + " WHERE Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = rolename
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
roles.Add(reader.GetString(0))
End While
reader.Close
conn.Close
Return roles.ToArray
End Function
'
' RoleProvider.IsUserInRole
'
Public Overrides Function IsUserInRole(ByVal username As String, ByVal rolename As String) As Boolean
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT COUNT(*) FROM `" _
+ (usersInRolesTable + ("`" + " WHERE Username = ?Username AND Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255).Value = username
cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = rolename
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
conn.Open
Dim numRecs As Long = Convert.ToInt64(cmd.ExecuteScalar)
conn.Close
Return (numRecs > 0)
End Function
'
' RoleProvider.RemoveUsersFromRoles
'
Public Overrides Sub RemoveUsersFromRoles(ByVal usernames() As String, ByVal rolenames() As String)
For Each rolename As String In rolenames
If Not RoleExists(rolename) Then
Throw New ProviderException("Role name not found.")
End If
Next
For Each username As String In usernames
For Each rolename As String In rolenames
If Not IsUserInRole(username, rolename) Then
Throw New ProviderException("User is not in role.")
End If
Next
Next
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("DELETE FROM `" _
+ (usersInRolesTable + ("`" + " WHERE Username = ?Username AND Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
Dim userParm As MySqlParameter = cmd.Parameters.Add("?Username", MySqlDbType.VarChar, 255)
Dim roleParm As MySqlParameter = cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255)
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
Dim tran As MySqlTransaction = Nothing
conn.Open
Try
tran = conn.BeginTransaction
cmd.Transaction = tran
For Each username As String In usernames
For Each rolename As String In rolenames
userParm.Value = username
roleParm.Value = rolename
cmd.ExecuteNonQuery
Next
Next
tran.Commit
conn.Close
Catch e As MySqlException
Try
tran.Rollback
Catch As System.Exception
End Try
Throw e
End Try
End Sub
'
' RoleProvider.RoleExists
'
Public Overrides Function RoleExists(ByVal rolename As String) As Boolean
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT COUNT(*) FROM `" _
+ (rolesTable + ("`" + " WHERE Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?Rolename", MySqlDbType.VarChar, 255).Value = rolename
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = ApplicationName
conn.Open
Dim numRecs As Long = Convert.ToInt64(cmd.ExecuteScalar)
conn.Close
Return (numRecs > 0)
End Function
'
' RoleProvider.FindUsersInRole
'
Public Overrides Function FindUsersInRole(ByVal rolename As String, ByVal usernameToMatch As String) As String()
Dim conn As MySqlConnection = New MySqlConnection(connectionString)
Dim cmd As MySqlCommand = New MySqlCommand(("SELECT Username FROM `" _
+ (usersInRolesTable + ("` " + "WHERE Username LIKE ?UsernameSearch AND Rolename = ?Rolename AND ApplicationName = ?ApplicationName"))), conn)
cmd.Parameters.Add("?UsernameSearch", MySqlDbType.VarChar, 255).Value = usernameToMatch
cmd.Parameters.Add("?RoleName", MySqlDbType.VarChar, 255).Value = rolename
cmd.Parameters.Add("?ApplicationName", MySqlDbType.VarChar, 255).Value = pApplicationName
Dim users As List = New List
conn.Open
Dim reader As MySqlDataReader = cmd.ExecuteReader
While reader.Read
users.Add(reader.GetString(0))
End While
reader.Close
conn.Close
Return users.ToArray
End Function
End Class
End Namespace
Sv:mysql membership and role provider for asp.net 2.0
Jag provade en del converters själv innan, men ingen blev direkt bra.
Denna funkade skapligt, har nu gått igenom hela koden och jag tror att jag fått ordning på allt (läs nästan allt)
När jag testat lite mer kan det hända att jag lägger ut det hela här på forumet.
/Micke