Getting output params from stored proc to BL function in vb

Question

I am not very well versed in vb.net coming from a c# background. I have a form with a btn and input that takes a string and runs and update in the DB via a stored proc. The stored proc should be returning an @Message and @Success parameter. However, I am sort of confused how to get these params back. I have 3 functions. The btn click sub(which calls the BL function), the BL function (which calls the DA function), and the DA function (which calls the stored proc within sql server). I am also sort of confused as to how the whole byref thing works. I read that I don't have to return anything from my DA function, but if this is true, then how would the BA function receive the success/message params?

I know I am missing a lot here. Could someone please help me re-structure this, and give a brief explanation of how/why it works that way. Below is my code.

BTN click sub:

    'Button click that calls CertificateOrder BL function InvalidateCertificate() 
    Private Sub btnInvalidateCertificate_Click(sender As Object, e As System.EventArgs) Handles btnInvalidateCertificate.Click
        'Cert ID to invalidate
        Dim certificateId As String = txtCertificateId.Value
        Dim msg As Response.BaseResponse

        'Call stored proc from BL

        msg = CertificateOrder.InvalidateCertificate(certificateId)
        Me.txtCertificateId.ErrorText = msg.Message

        If msg.Success Then
            Forms.FadeForm.ShowDialog(msg.Message, "Success", Forms.FadeForm.MessageIcons.Information, Forms.FadeForm.MessageButtons.Ok)
        End If
    End Sub

BL function:

   ''' <summary>
    ''' Calls DataAccess function InvalidateCertificate()
    ''' to invalidate certificate
    ''' </summary>
    Public Shared Function InvalidateCertificate(ByVal certificateId As System.String) As Response.BaseResponse

        Using context As New CORADBContext

            Dim message As System.String = ""
            Dim success As System.Boolean = False

            context.InvalidateCertificate(certificateId, success, message)

            Return message
        End Using
    End Function

DA function:

   ''' <summary>
    ''' Calls the [spInvalidateCertificate] stored proc
    ''' to update a cert to be invalid
    ''' </summary>
    ''' <returns></returns>
    <Extension>
    Public Function InvalidateCertificate(ByVal context As DbContextBase,
                                          ByVal certificateId As System.String,
                                          ByRef success As System.Boolean,
                                          ByRef message As System.String) As Int32

        Dim successParameter As New SqlParameter("@Success", success) With {.Direction = ParameterDirection.InputOutput, .Value = False}
        Dim messageParameter As New SqlParameter("@Message", message) With {.Direction = ParameterDirection.InputOutput, .Value = ""}

        Dim parameters() As SqlParameter = {New SqlParameter("@CertificationValue", certificateId), successParameter, messageParameter}

        Dim results As Int32 = context.ExecuteProcedure("Orders.spInvalidateCertificate", parameters)

        success = DirectCast(successParameter.Value, System.Boolean)
        message = DirectCast(messageParameter.Value, System.String)
        'success and message need to be returned

        Return 0
    End Function

Answer 1

You can make the flow a bit cleaner. As stated in the comments, I'm not sure what that Response.BaseResponse object is, so let's assume you don't have a data structure (i.e. a class) to return the response data. You can create one like this:

Public Class MyResponse

    Public ReadOnly Property Success As Boolean = False
    Public ReadOnly Property Message As String = String.Empty

    Public Sub New(success As Boolean, message As String)
        Me.Success = success
        Me.Message = message
    End Sub

End Class

Now, refactor the code so the DA Function returns an object of type MyResponse:

Public Function InvalidateCertificate(context As DbContextBase,
                                      certificateId As String) As MyResponse

    Dim successParameter As New SqlParameter("@Success", success) With {.Direction = ParameterDirection.InputOutput, .Value = False}
    Dim messageParameter As New SqlParameter("@Message", message) With {.Direction = ParameterDirection.InputOutput, .Value = String.Empty}

    Dim parameters() As SqlParameter = {New SqlParameter("@CertificationValue", certificateId), successParameter, messageParameter}

    Dim results As Integer = context.ExecuteProcedure("Orders.spInvalidateCertificate", parameters)

    Return New MyResponse(successParameter.Value, messageParameter.Value)

End Function

Now, refactor the BL function to also return a MyResponse object:

Public Shared Function InvalidateCertificate(ByVal certificateId As System.String) As MyResponse

    Using context As New CORADBContext

        ' Where does context get passed to the DA???
        Return context.InvalidateCertificate(certificateId)

    End Using

End Function

One thing I don't understand is the original code for the call to contact.InvalidateCertificate() doesn't have a variable for context being passed in???

Finally, refactor your UI to handle the MyResponse result from the call to CertificateOrder.InvalidateCertificate(certificateId)