S
Siv
Hi,
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to stop
the user thinking the application has frozen when in fact it is just waiting
for a long SP to complete. Another reason for doing it like this is that I
also have had a problem in the past where the SP takes longer than the
timeout and the app then stops before the query has completed.
My code is as follows:
==========================================================
SqlCommand cm = new SqlCommand();
SqlParameter par = new SqlParameter();
SqlConnection Conn = new SqlConnection();
SqlTransaction tran=null;
DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;
Conn.ConnectionString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNAME;DATABASE=DATABASENAME;Integrated
Security=true;Asynchronous Processing=true;
Conn.Open();
cm.Connection=Conn;
cm.CommandText = "proc_Calc_Monthly";
cm.CommandType = CommandType.StoredProcedure;
cm.CommandTimeout = 0;
par = cm.CreateParameter();
par.Direction=ParameterDirection.Input;
par.ParameterName="@SM";
par.SqlDbType=SqlDbType.Int;
par.Value=EndM; //This is a variable holding an end month as an integer such
as 7 meaning July
cm.Parameters.Add(par);
par = cm.CreateParameter();
par.Direction=ParameterDirection.Input;
par.ParameterName="@SY";
par.SqlDbType=SqlDbType.Int;
par.Value=EndY; //This is a varible holding an end year as an integer such
as 2006
cm.Parameters.Add(par);
//Start transaction here
IAsyncResult Res = cm.BeginExecuteNonQuery();
tran = Conn.BeginTransaction();
while (!Res.IsCompleted)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtract(PrevTime);
System.Threading.Thread.Sleep(100);
//Let system in for a click
Readout.Text = "Processing Monthly Totals calculation - Please Wait
.... ";
Readout.Text += "\nTime Elapsed=" +
string.Format("{0:hh:mm:ss}",ElapsedTime);
Readout.text += "\nPress F2 function key to abort ...";
System.Windows.Forms.Application.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Monthly Stored Procedure will be rolled back?";
if(MessageBox.Show(msg,Common.H,MessageBoxButtons.OKCancel,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1)
== DialogResult.OK)
{
CalcsAborted = true;
break;
}
else
{
CalcsAborted = false;
break;
}
}
}
//Processing contines here once Res.IsCompleted becomes true, however it
never does??
// etc ...
==========================================================
I expect the while statement to keep looping round until the IsCompleted
flag is set true, but at the moment it seems to never return true. I took
this from an example in the BeginExecuteNonQuery help text.
Can anyone explain why this doesn't work? The above code is within a try
catch block that doesn't trigger as any error, so I can't see why SQL Server
never sends a message back. I am running this against a SQL Server 2000
database.
I have a stored procedure that I want to execute and then wait in a loop
showing a timer whilst it completes and then carry on once I get
notification that it has completed. The main reason for this being to stop
the user thinking the application has frozen when in fact it is just waiting
for a long SP to complete. Another reason for doing it like this is that I
also have had a problem in the past where the SP takes longer than the
timeout and the app then stops before the query has completed.
My code is as follows:
==========================================================
SqlCommand cm = new SqlCommand();
SqlParameter par = new SqlParameter();
SqlConnection Conn = new SqlConnection();
SqlTransaction tran=null;
DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;
Conn.ConnectionString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNAME;DATABASE=DATABASENAME;Integrated
Security=true;Asynchronous Processing=true;
Conn.Open();
cm.Connection=Conn;
cm.CommandText = "proc_Calc_Monthly";
cm.CommandType = CommandType.StoredProcedure;
cm.CommandTimeout = 0;
par = cm.CreateParameter();
par.Direction=ParameterDirection.Input;
par.ParameterName="@SM";
par.SqlDbType=SqlDbType.Int;
par.Value=EndM; //This is a variable holding an end month as an integer such
as 7 meaning July
cm.Parameters.Add(par);
par = cm.CreateParameter();
par.Direction=ParameterDirection.Input;
par.ParameterName="@SY";
par.SqlDbType=SqlDbType.Int;
par.Value=EndY; //This is a varible holding an end year as an integer such
as 2006
cm.Parameters.Add(par);
//Start transaction here
IAsyncResult Res = cm.BeginExecuteNonQuery();
tran = Conn.BeginTransaction();
while (!Res.IsCompleted)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtract(PrevTime);
System.Threading.Thread.Sleep(100);
//Let system in for a click
Readout.Text = "Processing Monthly Totals calculation - Please Wait
.... ";
Readout.Text += "\nTime Elapsed=" +
string.Format("{0:hh:mm:ss}",ElapsedTime);
Readout.text += "\nPress F2 function key to abort ...";
System.Windows.Forms.Application.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Monthly Stored Procedure will be rolled back?";
if(MessageBox.Show(msg,Common.H,MessageBoxButtons.OKCancel,MessageBoxIcon.Question,MessageBoxDefaultButton.Button1)
== DialogResult.OK)
{
CalcsAborted = true;
break;
}
else
{
CalcsAborted = false;
break;
}
}
}
//Processing contines here once Res.IsCompleted becomes true, however it
never does??
// etc ...
==========================================================
I expect the while statement to keep looping round until the IsCompleted
flag is set true, but at the moment it seems to never return true. I took
this from an example in the BeginExecuteNonQuery help text.
Can anyone explain why this doesn't work? The above code is within a try
catch block that doesn't trigger as any error, so I can't see why SQL Server
never sends a message back. I am running this against a SQL Server 2000
database.