Re: Asynchronous Stored Procedure Never Returns - Help?

Dave,
I modified my code so that it does the following:

==========================================
Conn.ConnectionString = Common.strCnn;
Conn.Open();

tran = Conn.BeginTransaction();
cm = new SqlCommand("proc_Calc_Monthly", Conn, tran);
cm.CommandType = CommandType.StoredProcedure;
cm.CommandTimeout = 0;

par = cm.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = "@SM";
par.SqlDbType = SqlDbType.Int;
par.Value = EndM;
cm.Parameters.Add(par);

par = cm.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = "@SY";
par.SqlDbType = SqlDbType.Int;
par.Value = EndY;
cm.Parameters.Add(par);

AsyncCallback callback = new AsyncCallback(HandleCallback);
cm.BeginExecuteNonQuery(callback, cm);

//Loop round waiting for the transaction to complete
//First Store time now
PrevTime = DateTime.Now;

while (isExecuting)
{
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 ... \nTime Elapsed=" + string.Format("{0:hh:mm:ss}",
ElapsedTime) + "\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;
}
}
}

//Decide what to return on state of CalcsAborted
if (CalcsAborted)
{
Readout.Text = "Monthly Calculations section of
Calculation Processing was aborted by the user.";
CalcsAborted = false; //Set flag back as it is a global
tran.Rollback();
cm.Cancel();
cm.Dispose();
par = null;
//exit back to caller with error flagged
return false;
}
else
{
Readout.Text = "Monthly Calculations section of
Calculation Processing was completed.";
tran.Commit(); //***************** Error here
*****************************
//Tidy up - Conn wil be closed by Asynch handler above
this routine.
cm.Dispose();
cm = null;
par = null;
//exit back to caller with success
return true;

}
==========================================

The callback is handled as follows:

==========================================
private void HandleCallback(IAsyncResult result)
{
try
{
// Retrieve the original command object, passed
// to this procedure in the AsyncState property
// of the IAsyncResult parameter.
SqlCommand command = (SqlCommand)result.AsyncState;
int rowCount = command.EndExecuteNonQuery(result);
string rowText = " rows affected.";
if (rowCount == 1)
{
rowText = " row affected.";
}
rowText = rowCount + rowText;

// You may not interact with the form and its contents
// from a different thread, and this callback procedure
// is all but guaranteed to be running from a different
thread
// than the form. Therefore you cannot simply call code that
// displays the results, like this:
// DisplayResults(rowText)

// Instead, you must call the procedure from the form's
thread.
// One simple way to accomplish this is to call the Invoke
// method of the form, which calls the delegate you supply
// from the form's thread.
ReadoutDelegate del = new ReadoutDelegate(DisplayStatus);
this.Invoke(del, rowText);

}
catch (Exception ex)
{
// Because you are now running code in a separate thread,
// if you do not handle the exception here, none of your
other
// code catches the exception. Because none of
// your code is on the call stack in this thread, there is
nothing
// higher up the stack to catch the exception if you do not
// handle it here. You can either log the exception or
// invoke a delegate (as in the non-error case in this
// example) to display the error on the form. In no case
// can you simply display the error without executing a
delegate
// as in the try block here.

// You can create the delegate instance as you
// invoke it, like this:
this.Invoke(new ReadoutDelegate(DisplayStatus),
String.Format("Error occurred processing report - (last
error:\n\n {0}", ex.Message));
}
finally
{
isExecuting = false;
if (Conn != null)
{
Conn.Close();
}
}
}
==========================================

In the main part of the class I have the following:

==========================================
private delegate void ReadoutDelegate(string Text);
private bool isExecuting = false;
private SqlConnection Conn;

private void DisplayStatus(string Text)
{
this.Readout.Text = Text;
}
==========================================

As you can see this is a mixture of your code and the example code I was
already working on.
It seems to work OK except that when the Async handler triggers and my
while(IsExecuting) traps it, the code then jumps to the line marked with
asterisks and I get an error:

"This SQL Transaction has completed; it is no longer usable."

How do I commit the transaction if the

int rowCount = command.EndExecuteNonQuery(result);

seems to be ending it all before I can commit the transaction??

--
Siv
Martley, Near Worcester, United Kingdom.
"Dave Sexton" <dave@jwa[remove.this]online.com> wrote in message
news:Oph$ygepGHA.4988@xxxxxxxxxxxxxxxxxxxxxxx
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.





.

Loading