CommandType.Text vs CommandType.StoredProcedure

Question

Is there any benefit to explicitly using the StoredProcedure CommandType as opposed to just using a Text Command? In other words, is

cmd = new SqlCommand("EXEC StoredProc(@p1, @p2)");
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

any worse than

cmd = new SqlCommand("StoredProc");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@p1", 1);
cmd.Parameters.Add("@p2", 2);

EDIT: Fixed bad copy paste job (again). Also, the whole point of the question is for a data access class. I'd much rather be able to pass the stored proc name and parameters in one line as opposed to extra lines for each parameter.

Answer 1

It's cleaner.

You're calling a stored procedure, why not just use the CommandType.StoredProcedure?

Answer 2

Because it would annoy the hell out of me....

:)

Answer 3

One difference is how message pumping happens. Where I work we have a number of batch processes that run over night. Many of them simply involve running a stored procedure. We used to schedule these using sql server jobs, but moved away from it to instead call the procedures from a .Net program.

This allowed us to keep all our scheduled tasks in one place. It also allowed us to build better logging functionality into the .Net program that calls the procedures. The stored procedures will use the sql print and raiserror functions, and the .Net program will receive and log those. What we learned was that CommandType.StoredProcedure will always buffer these messages into batches of about 50. The .Net code wouldn't see any log events until the procedure finished or flushed the buffer no matter what options you set on the connection or what you do in your sql. CommandType.Text fixed this.

As a side issue, I'd use explicit types with your query parameters. Letting .Net try to infer your parameter types can cause issues in some situations.