Query in vb.net - good practice

[kdejan87]

I would like to know which way querying is a better, safer, more efficient. And whether there are some major differences. Sending two examples of code. Would I need something to add, modify or delete. Feel free to express your opinion.

 Try
    con.Close()
    con.Open()

    Dim sqlQuery As String = "INSERT INTO tblPerson(ID, Name,Email,GenderID) VALUES(@ID, @Name,@Email,@GenderID)"
    com = New SqlCommand(sqlQuery, con)

    com.Parameters.AddWithValue("@ID", CInt(txtID.Text))
    com.Parameters.AddWithValue("@Name", CStr(txtName.Text))
    com.Parameters.AddWithValue("@Email", CStr(txtEmail.Text))
    com.Parameters.AddWithValue("@GenderID", CInt(txtGender.Text))

    com.ExecuteNonQuery()
    com.Dispose()

Catch ex As Exception

    MessageBox.Show(ex.Message)
    con.Close()
Finally
    con.Close()

End Try

'and second:

con.Close()
con.Open()

exe = "basicInsert"
Dim startTransaction = con.BeginTransaction

Try
    com = New SqlCommand("EXECUTE " & exe & " '" & txtID.EditValue & "','" & txtName.EditValue & "','" & txtEmail.EditValue & "','" & txtGender.EditValue & "'", con)

    com.Transaction = startTransaction

    com.ExecuteNonQuery()
    com.dispose()

    startTransaction.Commit()

Catch ex As Exception
    startTransaction.Rollback()
    MessageBox.Show(ex.Message, ex.GetType.ToString)

End Try

[deceptikon]

1. Adding arguments inline is faster, but more dangerous due to the ease of SQL injection attacks. Using parameters is safer, but a smidge less efficient. In this case your first example is better.

2. Transactions have overhead, so you shouldn't use them if they're not needed. In this case, I'd question the need for the transaction. However, it's not clear just from the code if you want to remove it or not. It would depend on the contents of the basicInsert stored procedure.