executenonquery calling a parametrised stored procedure is not responding

I'm executing a SQL stored procedure from a c# code but it is not responding at all. There is no exception or error generated. The processing as if gets hung. The stored procedure consists of multiple update statements and a select statement. The stored procedure is running fine independently and takes about 3-5 minutes to execute whereas when called from the C# code it is not responding even after 20 mins or more. When I comment most of the updates statements one run only one or two the executenonquery works. I have even increased the commandTimeout time.

Please suggest as this is something urgent. Please find below the C# code:

C# function:

private void PanDatabase(DateTime StartDate, DateTime EndDate)
    {
        SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["connectionString"]);

        SqlCommand cmd = new SqlCommand("PanData", conn);

        cmd开发者_开发技巧.CommandType = System.Data.CommandType.StoredProcedure;

        cmd.Parameters.Add(new SqlParameter("@start_date", StartDate.ToShortDateString()));
        cmd.Parameters.Add(new SqlParameter("@end_date", EndDate.ToShortDateString()));
        cmd.Parameters.Add(new SqlParameter("@period_status", _periodPan));

        conn.Open();

        cmd.CommandTimeout = 9000;

        cmd.ExecuteNonQuery();

        cmd.Dispose();
        conn.Close();
        conn.Dispose();
    }

By default your .Net code is running in a Serializable transaction. Could that be causing some locking? Within a single Stored Proc, I don't see this being an issue, but if you are calling multiple DB queries from .Net, then they may be interfering with each other.

You can verify the locking by looking at the job status in DB Manager "Activity Monitor"*. More detailed diagnosis should be possible with SQL Profiler, as Martin suggests. (*Only if you have sufficient DB permissions.)

Check for multiple calls to the same Stored Proc by adding some System.Diagnostics.Trace statements to the .Net code.

The simple answer is to add...

cmd.Parameters.Clear();

...before adding new parameters and calling ExecuteNonQuery() again.