.Net SqlCommand parameters

Hi All,

Geeky question here

Me and a friend are arguing about the overloads of sqlCommand.Parameters.Add , namely the

add(string, object) and the add(string, SqlDbType) overloads.

My preference is to use the former ... certainly using Ildasm, it generates less code as you dont have to create a Parameter object to assign the value too.

But which is better, and if some one knows, what is the difference in the system.data.dll, as looking through Ildasm is just plain confusing me!

Thanks in advance.

Hi Troj

In general i would go with the second method, as it should generate more efficient code for base types ( avoid boxing etc ). This will make quite a difference potentially on high-load servers etc.

You only end up creating an extra reference to a parameter object, rather than a new object which isn't too bad from an overhead pov, and i think you can avoid this in some cases if you don't mind weird looking code like...

cmd.Parameters.Add( "n", SQLDBType.int32 ).Value = 10;

i'll have a look at sys.data when i get into the office and report back on what the real diffs are...

Thanks for that spod, it makes sense  ... It seems to depend on the type though.
If you pass a Guid to an SqlParameter, the CLR boxes it regardless of which overload you use, at least thats what my ILDASM is telling me.

Also, if you are using prepared statements (think one-time use stored procedures to help eliminate SQL injection attacks), you need to use the (string, SqlDbType) and (string, SqlDbType, size) overloads.

Normally, I'd use a stored procedure for this, but here is an example:

Dim cmd As New SqlCommand("INSERT INTO ecom_pplog(txn_id,info) VALUES (@1,@2)", conn)
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(New SqlParameter("@1", SqlDbType.Char, 17))
cmd.Parameters.Item("@1").Value = Request.Form("txn_id")
cmd.Parameters.Add(New SqlParameter("@2", SqlDbType.Text, Request.Form.ToString.Length))
cmd.Parameters.Item("@2").Value = Request.Form.ToString

conn.Open()
cmd.Prepare()
cmd.ExecuteNonQuery()
conn.Close()

Yeah, in our production systems, we always use Sized sprocs  

This particular argument was regarding the performance differences, It's very hard to measure the throughput of either overload because Network speeds and TCP overheads become an issue, so I was interested in what work has to happen in the background.

For what you were discussing up top, there is no difference as to what would be sent over the wire.  You may have a slight hit using the (string, object) overload due to the conversion required, but the time you're waiting on the wire would dwarf the extra delay.

So in this case, I'd just pick whichever one would give you the most readable code.  Besides, if you're adding enough parameters that you're going to noticably slow down the client app, there are problems far beyond the .NET Framework with your design.  [grin]