Re: Asynchronous Stored Procedure Never Returns - Help?

Hi Siv,

1. You have to call EndExecuteNonQuery at some point.

2. Why loop until IsCompleted is true, sleeping for 100 ms every iteration,
when you can just specify an AsyncCallback and immediately return to the
caller? One of the overloads for BeginExecuteNonQuery accepts an
AsyncCallback argument and an Object argument that you can use to send state
information to the callback. The callback implementation is the perfect
opportunity to call EndExecuteNonQuery on the SqlCommand, which can be
supplied as the state argument, and to update the UI to notify the user that
the process has completed.

3. Use a Timer to update the UI thread if you want to display status
information to the user while processing asynchronously, unless the app is
targeted for users that have no need for performance or diagnostics data, in
which case I would omit the Timer altogether and display an animating
progress bar or something of the like.

4. See inline

SqlCommand cm = new SqlCommand();
[snip]
cm.Connection=Conn;
[snip]
IAsyncResult Res = cm.BeginExecuteNonQuery();
tran = Conn.BeginTransaction();

If you are trying to execute this command within a transaction:

tran = Conn.BeginTransaction();
SqlCommand cm = new SqlCommand("proc_Calc_Monthly", Conn, tran);
cm.CommandType = CommandType.StoredProcedure;
IAsyncResult Res = cm.BeginExecuteNonQuery();

5. Your code does not show any necessity for a transaction since you are
only executing a single command against the database. If your stored
procedure requires a transaction then code the transaction in T-SQL.

6. Use the SqlCommandBuilder.DeriveParameters method so you don't have to
hard-code every parameter in your SqlCommand, or better yet use the Data
Access Application Block from Microsoft, which provides a ParameterCache and
standardized data access utilities.

- Dave Sexton

"Siv" <siv@xxxxxxxxxxxxxxxxxxx> wrote in message
news:%23xwOcCcpGHA.524@xxxxxxxxxxxxxxxxxxxxxxx
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.
--
Siv
Martley, Near Worcester, United Kingdom.



.