Hi,
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();
}