CODEWITHSUNDEEP
.netdatabasevb.netsqlparameter
rfc1484
rfc1484

Reputation: 9837

Updating the database using parameters in .net

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?

Upvotes: 0

Views: 1297

Answers (2)

Tim
Tim

Reputation: 28530

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)

Upvotes: 1

Adam Houldsworth
Adam Houldsworth

Reputation: 64487

Neither of those are any good for one main reason that has nothing to do with database access...

You are swallowing any and all managed exceptions. Only handle exceptions if you can handle them appropriately, there is hardly ever a need to take an exception and translate it into return values. In my opinion if you do this, it's not longer an exceptional circumstance.

I can't quite remember the VB.NET Using syntax so I can't as yet provide another example. I'd also not use Call, I don't think it's required.

Sort of pseudo-code:

Using Dim command As New SqlCommand("INSERT...", conn)
    command.Parameters.AddWithValue()
    ....

    command.ExecuteNonQuery() // No need to call dispose, Using does that.
End Using

Upvotes: 1

Related Questions