如何从数据库中提取 GUID 以更新用户名

Question

我有一个与 ASP.net 中的 SQL 数据库不同的 SQL 数据库，因此我必须创建一个自定义成员资格提供程序。获取用户的提供者如下所示： 我的 SQL 类名称是 CustomSqlProvider：

Public Overrides Function GetUser(ByVal username As String, _
    ByVal userIsOnline As Boolean) As MembershipUser
    'Dim connectionString As String = "Server=***;Database=***;User Id=***password=****"'

    Dim conn As SqlConnection = New SqlConnection(connectionString)
    Dim cmd As SqlCommand = New SqlCommand("Get_User", conn)
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
    cmd.Parameters.Add("@Password", SqlDbType.NVarChar)

    Dim u As MembershipUser = Nothing
    Dim reader As SqlDataReader = Nothing

    Try
        conn.Open()

        If userIsOnline Then
            Dim updateCmd As SqlCommand = New SqlCommand("Update_User", conn)

            updateCmd.Parameters.Add("@UserName", SqlDbType.NVarChar)
            updateCmd.Parameters.Add("@F_Name", SqlDbType.NVarChar)
            updateCmd.Parameters.Add("@L_Name", SqlDbType.NVarChar)
            updateCmd.Parameters.Add("@PWD", SqlDbType.VarChar)
            updateCmd.Parameters.Add("@Email", SqlDbType.VarChar)

            updateCmd.ExecuteNonQuery()
        End If

    Catch e As SqlException
        'If WriteExceptionsToEventLog Then
        '    WriteToEventLog(e, "Get_User, as String")
        '    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

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

    Dim conn As SqlConnection = New SqlConnection(connectionString)
    Dim cmd As SqlCommand = New SqlCommand("Get_User", conn)
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = providerUserKey

    Dim u As MembershipUser = Nothing
    Dim reader As SqlDataReader = Nothing

    Try
        conn.Open()

        reader = cmd.ExecuteReader()

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

            If userIsOnline Then
                Dim updateCmd As SqlCommand = New SqlCommand("Update_User", conn)

                updateCmd.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier)


                updateCmd.ExecuteNonQuery()
            End If
        End If
    Catch e As SqlException
        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

现在我想做的是首先登录我的网站，它进行身份验证，然后我就进入了。然后我被重定向到另一个页面，我可以在其中更改我的用户名。但我似乎无法获取登录用户的 GUID 来更改它。我正在使用更新用户表的存储过程。

我在页面上有以下代码，我可以在其中更改凭据：

Dim currentUser as MembershipUser = Membership.GetUser() Dim CurrentUSerId as Guid = CType(currentUser.ProviderUserKey, Guid)

我得到的引用未设置为对象实例。任何帮助将不胜感激。

感谢您的编辑。我还添加了获取和设置属性，以便我能够向这些参数添加值。 Update_User 存储过程中的参数是输入。我的 Get_User 存储过程看起来像这样..这就是为什么我认为它没有获取 GUID，因为当它运行时，它会要求用户名和密码，然后它返回包括 Guid 在内的所有内容：

GO
/****** Object:  StoredProcedure [dbo].[Get_User]    Script Date: 07/14/2010 09:16:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Get_User]
    @UserName nvarchar(50),
    @Password varchar(50)
AS
    SELECT USER_ID, USER_NAME, F_NAME, L_NAME, BUILDING_ID, SIP_ROLE, INTERNAL_ID, PWD, EMAIL FROM dbo.USERS WHERE USER_NAME = @UserName AND PWD = @Password
    RETURN

/////////// /////////////////////////////////////////////////////////// ///////////////////////////////

添加这些获取和设置并将参数变量分配给我的 Sproc 参数后，出现以下错误:

未设置对对象实例的引用。

在这行代码中：

Dim currentUser As MembershipUser = Membership.GetUser()
Dim UserId As Guid = CType(currentUser.ProviderUserKey, Guid)

这就是我在 UserInfo.aspx.vb 页面上单击按钮下的内容：

Dim sql As New SqlClient.SqlCommand("Update_User", con)
        sql.Parameters.Add("@UserId", SqlDbType.UniqueIdentifier).Value = UserId
        sql.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = txtUserName.Text

Answer 1

在第一个 GetUser OverRide 中，它在哪里执行以下行：

Dim cmd As SqlCommand = New SqlCommand("Get_User", conn)

我看不到该命令已执行，但众所周知，我非常愚蠢:)

也许您的代码需要遵循 Get_User by ProviderUserKey 模式中的模式，我添加了一些缺失的内容和问题，希望这会有所帮助。

    Public Overrides Function GetUser(ByVal username As String, _
    ByVal userIsOnline As Boolean) As MembershipUser
    'Dim connectionString As String = "Server=***;Database=***;User Id=***password=****"'

    Dim conn As SqlConnection = New SqlConnection(connectionString)
    Dim cmd As SqlCommand = New SqlCommand("Get_User", conn)
    cmd.CommandType = CommandType.StoredProcedure

    cmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = "??????"
    cmd.Parameters.Add(("@Password", SqlDbType.NVarChar).Value = "??????"

    Dim u As MembershipUser = Nothing
    Dim reader As SqlDataReader = Nothing

    Try
        conn.Open()
        reader = cmd.ExecuteReader()
        If reader.HasRows Then
            reader.Read()
            u = GetUserFromReader(reader)


            If userIsOnline Then
                Dim updateCmd As SqlCommand = New SqlCommand("Update_User", conn)
                'Are these input or output parameters?
                updateCmd.Parameters.Add("@UserName", SqlDbType.NVarChar).Value = u.UserName
                updateCmd.Parameters.Add("@F_Name", SqlDbType.NVarChar).Value = "??????"
                updateCmd.Parameters.Add("@L_Name", SqlDbType.NVarChar).Value = "??????"
                updateCmd.Parameters.Add("@PWD", SqlDbType.VarChar).Value = "??????"
                updateCmd.Parameters.Add("@Email", SqlDbType.VarChar).Value = "??????"

                updateCmd.ExecuteNonQuery()
            End If
        End If
    Catch e As SqlException
        'If WriteExceptionsToEventLog Then
        '    WriteToEventLog(e, "Get_User, as String")
        '    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