When should "SqlDbType" and "size" be used when adding SqlCommand Parameters?

Question

There is a related question to this:

What's the best method to pass parameters to SQLCommand?

But I am wanting to know what the differences are and if there are any problems with the different ways.

I usually use a structure something like this:

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand(SQL, conn))
{
     cmd.CommandType = CommandType.Text;
     cmd.CommandTimeout = Settings.Default.reportTimeout;
     cmd.Parameters.Add("type", SqlDbType.VarChar, 4).Value = type;

     cmd.Connection.Open();

     using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
     {
         adapter.Fill(ds);
     }
      //use data                    
}

Now there are several ways to add the cmd parameters and I am wondering which is best:

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";
cmd.Parameters.Add("@Name").Value = "Bob";
cmd.Parameters.AddWithValue("@Name", "Bob");

Having the length of the field in the passing of the varchars I assume is not preferable as it's a magic value which may be changed later at the database. Is this correct? Does it cause any issue passing a varchar in this way (performance or other), I assume it defaults to varchar(max) or the database equivalent. I am reasonably happy this will work.

The part that concerns me more is the loss of the SqlDbType enum if I am using the third or fourth options I listed above I am not supplying a type at all. Are there cases where this won't work I can imagine problems with varchar being incorrectly cast to char or vice versa or perhaps issues with decimal to money....

In terms of the database the field type I would say is much less likely to change than the length so is it worth retaining?

Integrated

Whilst not answering your question, You do know that you don't need to open the connection prior to calling SqlDataAdapter.Fill(), and that there is benefit in not doing so? – Johnnajohnnie

@Rowland good point. I did know that although it has been an oversight in my code. I think I took the original code block from somewhere that I was using an SqlDataReader not an adapter (You do need to open the connection for that right?). That's what you get for naughty copy/paste programming :-). Fixed in my code now. – Integrated

just thought I'd mention it, as it's one of those "obscure tips that people might not know about" – Johnnajohnnie

Answer 1

In my experience, I would make sure I do these things:

• make sure it's you that defines the data type for the parameter. ADO.NET does a decent job at guessing, but in some cases, it can be terribly off - so I would avoid this method:

  cmd.Parameters.Add("@Name").Value = "Bob";
  cmd.Parameters.AddWithValue("@Name", "Bob");
  

  Letting ADO.NET guess the type of the parameter by the value passed is tricky, and if it's off for any reason, those are really tricky bugs to track and find! Imagine what happens when you pass in a DBNull.Value - what datatype should ADO.NET pick for that?

  Just be explicit - say what type it is you want!

• if you're using string parameters, make sure to explicitly define the length - so I would avoid this method, too:

  cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Bob";
  

  If you don't provide a length, ADO.NET might default to some arbitrary value, or the length of the string passed in as a value, or something else - you're never quite sure. And if your length doesn't match what the stored proc really expects, you might see conversion and other unpleasant surprises. So if you define a string, define its length, too!

So in your case, the only approach that really works for me is this one here:

cmd.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = "Bob";

because it a) defines the data type to use explicitly, and b) defines the length of the string explicitly.

Amadeus

If you're using a stored procedure, can't you just get the parameter type from the meta-data provided by SqlCommandBuilder.DeriveParameters(cmd)? – Repel

Should the Length passed in be that of the underlying parameter definition? And if so, doesn't that kind of violate DRY? – Undo

Answer 2

By adding the type, your requests are more likely to improve performance by using cached query plan.

Here's a quote from MSDN:

Parameterized commands can also improve query execution performance, because they help the database server accurately match the incoming command with a proper cached query plan.

More to read in Execution Plan Caching and Reuse.

Schwing

All of Pete's options are paramterised, so I'd be surprised if adding the parameters in different ways affects performance -- it's certainly not explicitly mentioned in the article you've linked to. – Johnnajohnnie

I do disagree. What the article describing is that by defining the parameter as exact as possible its more likely that a cached query plan which will be used and then with higher performance. The sub section Simple Parameterization says this: In SQL Server, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new SQL statements with existing, previously-compiled execution plans. – Schwing