Watch How You Declare Those Parameters

by William Ryan

If you are using ADO.NET much, you have probably come across Parameters by now.  They are a clean and elegant way of using SQL, and in many instances, they are the only way to pass data to your server.  However, there's a gotcha out there that you need to be aware of.  Take a look at the code sample below (I'm using each of the available overloads, going from most generic to most precise, to declare @SomeParam and set its value to 0 - Note also that you could declare the Parameter and then add it to the collection, I just chose to use the simplified syntax):

SqlCommand cmd = new SqlCommand();    //Add a Parameter and then give it a value    cmd.Parameters.Add();    cmd.Parameters[0].Value = 0;    //Add a Parameter and give it a value (String, Object)    cmd.Parameters.Add("@SomeParam", 0);    //Add a Paramater and Specify a type, then add a value (String, DbType)    cmd.Parameters.Add("SomeParam", SqlDbType.Int).Value = 0;             //Specify a length (String, DbType, Int32)    cmd.Parameters.Add("@SomeParam", SqlDbType.Int, 1).Value = 0;    //Map it back to a Column    cmd.Parameters.Add("@SomeParam", SqlDbType.Int, 1, "TableColumnName").Value = 0;

Now, let's say that instead of 0 I was using a String value and setting it to the literal "Something".  In that case, everything would work as planned.  However, since I'm using an Int value, all of these are valid declarations, but one of them isn't setting the value to 0.  Huh?  If you check the value of the parameter with the second declaration, you'll see that it's not 0, actually it's not anything.  So why would it work with a String value (or just about anything else, but not an Int)?  It's kind of tricky but here's the deal.  The second constructor's second Parameter  (no pun intended) is of type Object.  So, if you use 0 for instance, the compiler will assume that you are using the THIRD overload which is declaring a SqlDbType.  0 is an oveload that corresponds to the Int64 type.  I've tried to figure out the others, setting it to 2, 3, 4 and the other likely candidates, but no can do so far.  If you use other numbers, it simply adds them at face value and the type reflected is going to be in Int32 b/c that's what 2, 3, 4 etc are.  This is a subtle little gotcha, but it can cause you a lot of problems, and in most cases (like calling a Stored Procedure without  a default Parameter) your routine will bomb.

So what's the fix?  Use one of the longer constructors, after all, the more precision the better your app will behave.  However, the more precision your define the more coupled your code is with the Proc and that may not be what you're after.  So, if you just make one small change like so:

cmd.Parameters.Add("@SomeParam", Convert.ToInt32(0));

You'll be fine.

*I used SqlParameter, but the same holds for the other types like OleDbParameter