Updating the database using parameters in .net

Question

Which one of these two ways would be more recommended to update a database with a given query string:

Option 1:

Dim query As String = "INSERT INTO employee VALUES (@Name, @Age)"
Dim command As New SqlClient.SqlCommand(query, sqlConnection)

Dim params As SqlParameter() = {
    New SqlParameter("@Name", txtName.Value),
    New SqlParameter("@Age", txtAge.Value))
}

Call UpdateDatabase(command, params, NumError, DescError)

Public Sub UpdateDatabase(ByVal command As SqlCommand, ByVal parameters() As SqlParameter, ByRef NumError As Double, ByRef DescError As String)
Try
    For Each parameter In parameters
        command.Parameters.Add(parameter)
    Next
    command.ExecuteNonQuery()
    command.Dispose()
    NumError = 0
    DescError = ""
    Catch ex As Exception
        NumError = Err.Number
        DescError = Err.Description
    End Try
End Sub

Option 2:

Dim query As String = "INSERT INTO employee VALUES (@Name, @Age)"
Dim command As New SqlClient.SqlCommand(query, sqlConnection)

command.Parameters.AddWithValue("@Name", txtName.Value)
command.Parameters.AddWithValue("@Age", txtAge.Value)

Call UpdateDatabase(command, NumError, DescError)

Public Sub UpdateDatabase(ByVal command As SqlCommand, ByRef NumError As Double, ByRef DescError As String)
    Try
        command.ExecuteNonQuery()
        command.Dispose()
        NumError = 0
        DescError = ""
    Catch ex As Exception
        NumError = Err.Number
        DescError = Err.Description
    End Try
End Sub

Or is there any other better way to do this?

Answer 1

It looks like you're trying to create a reusable UpdateCommand, which is all well and good. In addtion to not opening the connection, I'm not sure that you're closing the connection (unless the command.Dispose also closes the connection. You'd be better off to move as much of the db connectivity into the Sub as possible.

Also, I'm not sure where you're getting Err. from in your exception block, but I'd recommend designing a better way to handle any exceptions - perhaps log it somewhere.

Finally, make the Sub a Function and return a bool indicating success/failure:

Public Function UpdateDatabase(ByVal sql As String, ByVal parameters() As SqlParameter) As Boolean

    Dim Successful As Boolean = False

    Try
        Using conn As SqlConnection = new SqlConnection(sqlConnection)
            Using command As New SqlCommand(sql, conn)

                command.CommandType = CommandType.Text        

                For Each parameter As SqlParameter In parameters
                    command.Parameters.Add(parameter)
                Next

                conn.Open()

                command.ExecuteNonQuery()
                Successful = True
            End Using       
        End Using        
    Catch ex As Exception
        Successful = False
        ' Do something with the exception
    End Try

End Function

You could then do this:

Dim query As String = "INSERT INTO employee VALUES (@Name, @Age)"

Dim params As SqlParameter() = {
    New SqlParameter("@Name", txtName.Value),
    New SqlParameter("@Age", txtAge.Value))
}

Dim Updated As Boolean = UpdateDatabase(query, params)

This example assumes sqlConnection is a class level variable holding your connection string. You could also read it directly from the config file if desired.

If you don't have any parameters for the command, you'll need to pass in an empty array (or modify the code in the function to check for params = Nothing):

Dim params As SqlParameter()
Dim Updated As Boolean = UpdateDatabase(query, params)