Can there be any help to clearly while using StoredProcedure CommandType instead of simply using a Text Command? Quite simply, 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 entire reason for now you ask , for any data access class. I'd prefer to have the ability to pass the saved proc title and parameters in a single line instead of extra lines for every parameter.
You are calling a saved procedure, why don't you only use the CommandType.StoredProcedure?
One difference is when message moving happens. Where Sometimes we have many batch processes running over evening. Most of them simply involve managing a saved procedure. We accustomed to schedule these using sql server jobs, but moved from it to rather call the methods from the .Internet program.
This permitted us to help keep all our scheduled tasks in one location. Additionally, it permitted us to construct better logging functionality in to the .Internet program that calls the methods. The saved methods uses the sql
raiserror functions, and also the .Internet program will get and log individuals. What we should learned was that
CommandType.StoredProcedure will always buffer these messages into batches around 50. The .Internet code wouldn't use whatever log occasions before the procedure finished or flushed the buffer regardless of what options you place around the connection or that which you do inside your sql.
CommandType.Text fixed this.
Like a side problem, I'd use explicit types together with your query parameters. Letting .Internet attempt to infer your parameter types may cause issues in certain situations.
Since it would annoy the hell from me....