Tech Off Thread

6 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

.Net SqlCommand parameters

Back to Forum: Tech Off
  • User profile image
    Troj

    Hi All,

    Geeky question here Smiley

    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.

  • User profile image
    spod

    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...




  • User profile image
    Troj

    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.

  • User profile image
    RomSteady

    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()

  • User profile image
    Troj

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

    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.

  • User profile image
    RomSteady

    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]

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.