Parameters cause sql query to time out.

Question

I have a long SQL query, it does some inserts, updates, then deletes. Each query uses the same 2 parameters. If I pass them in as SQL parameters from C#, it times out, after 20 mins. I just put the parameters into the command text, and it works. When I use it with the parameters it doesn't even show up in the profiler till it times out. Am I missing something?

SqlCommand comm = new SqlCommand(cmdText, conn); 
comm.CommandTimeout = 5 * 60; 
SqlParameter p = new SqlParameter("@key1", SqlDbType.Int);
p.Value = key1;
comm.Parameters.Add(p);
p = new SqlParameter("@key2", SqlDbType.Int);
p.Value = 1000000;
comm.Parameters.Add(p);
comm.ExecuteNonQuery();

If you take the parameter code out, and just to a replace on cmdText before executing the query it works. The query itself is a 300 lines or so. Each parameter gets used 51 times.

Answer 1

You may be missing the comm.Prepare() call before ExecuteNonQuery().

The key is in the SQL command, instead of saying "cmdText" post your SQL command.

EDIT: You are also not specifying a parameter direction in the code, it might be important.

Answer 2

Did you set the command type?

var command = new SqlCommand() { CommandType = CommandType.StoredProcedure };