Opinion: SqlCommand style: bind int vs. put it in the string

Question

Suppose that I have, for example:

Dim _id As Integer = 7

And I'm going to use it to select a row from a table where foo_id is the primary key. Either I can bind the Integer value with AddWithValue such as:

Dim cmd As New SqlCommand("select * from foo where foo_id = @id", sqlconn)
cmd.Parameters.AddWithValue("@id", _id)

Or I can construct the statement as a pure string:

Dim cmd As New SqlCommand("select * from foo where foo_id = " & _id, sqlconn)

Obviously I would always, always bind a string, but with an Integer key I can talk myself into either method.

Anybody have an opinion either way, and why?

First why is because using paramters avoids the sql injection. — Mahesh, Feb 07 '15 at 08:13
Injection with Strings, yes. Integers... not really possible unless the code could be corrupted to turn the Integer object into a string, at which point you could argue that the entire SqlCommand could be corrupted. — Jon Boede, Feb 07 '15 at 08:22

score 2 · Accepted Answer · edited May 23 '17 at 11:57

There is no room to debate or opinions here. The second method, concatenating strings to create an sql command, is the worst choiche. In your actual case there are no bad consequences because you have an integer and you seem to have strict control on how it is defined and initialized, but in any case, this is a bad habit that could kick you back when you don't think about its nasty consequences.

It is not my intention here to repeat what has already been said thousands times.
The main problems of the second approach are:

The first method is not free of problems too. AddWithValue should be used with extreme care because it decides what parameter to pass to the underlying database looking at the datatype of the value and sometime (particularly with dates and numeric values with decimals its decisions are not always correct) and string parameters having different length destroy the work of the query cache maintained by the database engine. Again, read the articles in these links for a more detailed description of the problems.

So, the best approach is

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = _id

score 0 · Answer 2 · answered Feb 07 '15 at 08:33

When you bind the variables in correct data type the SQL server can cache the plan with the variable and you save time / cpu in the next call. If you use string with values inside them you'll most likely end up bloating your plan cache with a lot of similar SQL statements which can't be used for other statements because the SQL text isn't the same.

Opinion: SqlCommand style: bind int vs. put it in the string

2 Answers2