Looking for the best way to use ExecuteScalar()

问题:

This code works. It's based on some code I found on the internet.

Can you tell me if the coding is the best way to get a scalar value and if there is a better way can you show coding samples?

Dim objParentNameFound As Object

TextBoxParentsName.Text = ""

If TextBoxParentID.Text <> "" Then

    ' Display the parent's name using the parent ID. '
    Dim strSqlStatement As String = "Select FatherName " & _
                                      "From Parents " & _
                                     "Where ID = @SearchValue"

    ' Set up the sql command and lookup the parent. '
    Using objSqlCommand As SqlCommand = New SqlCommand(strSqlStatement, ObjConnection)

        With objSqlCommand

            ' Add SqlParameters to the SqlCommand. '
            .Parameters.Clear()
            .Parameters.AddWithValue("@SearchValue", TextBoxParentID.Text)

            ' Open the SqlConnection before executing the query. '
            Try
                ObjConnection.Open()

                Try
                    objParentNameFound = .ExecuteScalar()
                    If objParentNameFound <> Nothing Then

                        ' Display the parent name here. '
                        TextBoxParentsName.Text = objParentNameFound
                    End If

                Catch exSqlErrors As SqlException
                    MessageBox.Show("Sorry, I couldn't execute your query because of this error: " & _
                                    vbCrLf & vbCrLf & exSqlErrors.Message, _
                                    "Error")
                End Try
            Catch exErrors As Exception

                MessageBox.Show("Sorry, there was an error. Details follow: " & _
                                vbCrLf & vbCrLf & exErrors.Message, _
                                "Error")
            Finally
                ObjConnection.Close()
            End Try
        End With 
    End Using 
End If 

回答1:

The Microsoft Access Application block has some nice examples of how to use ADO.Net. In particular what you might find helpful is how they've organized tasks such as ExecuteScalar() into a series of overloaded methods making it easy to invoke the process you need. The sample you posted would greatly benefit from separating out the concerns. In other words, take the code you use to build up the connection, command and parameters and make that a separate method or methods. This allows the code to be reused without copy& pasting it throughout your codebase. This allows your calling code to simply pass in the parameter(s) and bind the result to your text box or other controls.

Edit: Example Assuming you use the SqlHelper.vb class you can do something like the following:

Dim searchValue As Integer = 1
Dim myConnectionString As String = "MyConnectionString"
Dim sqlStatement As String = "SELECT FatherName FROM Parents WHERE ID = @SearchValue"
Dim paramList(0) As SqlParameter
paramList(0) = New SqlParameter() With {.Value = searchValue, .ParameterName = "@SearchValue"}

TextBoxParentsName.Text = SqlHelper.ExecuteScalar(myConnectionString, CommandType.Text, sqlStatement, paramList).ToString()