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 = new SqlCommand("StoredProc");
cmd.CommandType = CommandType.StoredProcedure;
One difference is how message pumping happens.
Where I used to work we had a number of batch processes that ran over night. Many of them simply involved 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, even the ones that had nothing to do with Sql Server.
It also allowed us to build better logging functionality into the .Net program that calls the procedures, so that the logging from all of the overnight processes was consistent. The stored procedures would use the sql
raiserror functions, and the .Net program will receive and log those. What we learned was that
CommandType.StoredProcedure would 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 did in your sql.
CommandType.Text fixed this for us.
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.