More Efficient Way of Adding Parameters to a SqlCommand .NET

Question

I was just reading a question about how to add parameters to a SqlCommand in .NET, and it raised a question for me. In all of my programs, this is how I add parameters to my commands:

SqlCommand cmd = new SqlCommand(cmdText,conn);
cmd.Parameters.Add(new SqlParameter("@name",value));

I know that you can also add parameters in the following way:

cmd.Parameters.Add(name, dbType, size).Value = value;

Which of these methods of adding parameters is better? Does it matter? I know that using the Sql namespaces is more efficient with SQL Server queries, so my first response would be that using the SqlParameter would be more efficient. However, since it's already a SqlCommand, I'm not quite sure about that. Also, since using the SqlParameter instantiates a new object, could that make it less efficient than the other case?

Answer 1

This is how I am doing it in code:

cmd.Parameters.AddWithValue("@name", value);

Answer 2

Both ways are going to create objects - whether you call the constructor yourself or whether another method does, the object is still going to be created.

More importantly, however, you're about to make a database call. The cost of creating a dozen objects is going to be absolutely peanuts compared with the database call, even if it's a very fast call.

Don't worry about it - just use the most readable code.

Answer 3

here is my way

cmd.InjectFrom<SetParamsValues>(foo);

all the properties foo (I sometimes use anonymous, and you can also specify ignores ) are going to be AddWithValue to the cmd, and for null DBNull.Value is going to be set

I recommend you too look at samples project from here http://valueinjecter.codeplex.com/ (it contains DAL sample)