Asynchronous Stored Procedure Never Returns - Help?

[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,MessageBoxDefault Button.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.

[Dave Sexton]

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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

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,MessageBoxDefault Button.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.

[Siv]

Dave,
Funnily enough I was just implementing the overload that uses the Async
Callback as when I last checked no-one had responded. The SP uses a cursor
to iterate through a recordset created from one query applying an update to
another set of records using some fields from the first query. So it's a
little bit more complex than it may appear from my code.

What I want to do is ensure that if the SP fails I can roll back all the
transactions done as it is an all or nothing job as reporting is done from
this and if only half of the records have been processed there will be some
fairly big problems.

I take your point about coding a rollback into the SP, I will have to look
at that, do you have any examples of doing that as I must admit to not
having done that before, I always do it from the calling application?

Thanks for your advice I will attempt to implement that and feedback here
how it works.

--
Siv
Martley, Near Worcester, United Kingdom.
"Dave Sexton" <dave@jwa[remove.this]online.comwrote in message
news:Op**************@TK2MSFTNGP04.phx.gbl...

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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

>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,MessageBoxDefaul tButton.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.

[Siv]

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.comwrote in message
news:Op**************@TK2MSFTNGP04.phx.gbl...

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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

>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,MessageBoxDefaul tButton.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.

[Siv]

Dave,
Then I modified the call-back again:

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;
command.Transaction.Commit(); //<=*******************
int rowCount = command.EndExecuteNonQuery(result);
string rowText = " rows affected.";
if (rowCount == 1)
{
rowText = " row affected.";
}
rowText = rowCount + rowText;

It now passes through this code OK and the process completes, but I am
getting a strange effect in that the process appears to start over again
with no apparent reason why? The routine seems to be in a loop, as soon as
the transaction completes I get back to the point just before I clicked "OK"
in a dialog that started the process off?

Is this some sort of threading problem??
--
Siv
Martley, Near Worcester, United Kingdom.
"Siv" <si*@removeme.sivill.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

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.comwrote in message
news:Op**************@TK2MSFTNGP04.phx.gbl...

>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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

>>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,MessageBoxButton s.OKCancel,MessageBoxIcon.Question,MessageBoxDefau ltButton.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.

[Dave Sexton]

Hi Siv,

If you have the Sql Server 2000 or 2005 docs they are thorough and can be
used as a reference while writing procedures. Open the help file and filter
for Transaction SQL Reference from the drop-down or select a keyword from
your query in Query Analyzer or Sql Server Management Studio, such as
"TRAN", and press Shift+F1. You could also just type "TRAN' in the search
and you'll get some useful docs.

-- this variable will hold the first error code encountered
DECLARE @Error int;

-- begin an unnamed transaction
BEGIN TRAN;

-- perform some operation
INSERT TestTable SELECT N'Test String Value';

-- you must copy the global error value into a local variable immediately
after
-- you perform certain operations in T-SQL because you should expect that
-- operations such as SET, SELECT, INSERT, UPDATE, DELETE, EXEC
-- all set the @@Error to 0 if they succeed.
SET @Error = @@ERROR;

IF @Error <0 GOTO Complete;

-- perform another operation
INSERT TestTable SELECT N'Another String Value';

-- check the error state once again
SET @Error = @@ERROR;

-- this check isn't necessary here, but I wanted to illustrate that you
would need
-- it again if the previous INSERT statement wasn't the last operation that
this
-- procedure were to perform
IF @Error <0 GOTO Complete;

-- Todo: more db work

Complete: -- label for when an error occurs

-- complete the procedure
IF @Error <0
ROLLBACK TRAN;
ELSE
COMMIT TRAN;

-- Dave Sexton

"Siv" <si*@removeme.sivill.comwrote in message
news:%2****************@TK2MSFTNGP05.phx.gbl...

Dave,
Funnily enough I was just implementing the overload that uses the Async
Callback as when I last checked no-one had responded. The SP uses a
cursor to iterate through a recordset created from one query applying an
update to another set of records using some fields from the first query.
So it's a little bit more complex than it may appear from my code.

What I want to do is ensure that if the SP fails I can roll back all the
transactions done as it is an all or nothing job as reporting is done from
this and if only half of the records have been processed there will be
some fairly big problems.

I take your point about coding a rollback into the SP, I will have to look
at that, do you have any examples of doing that as I must admit to not
having done that before, I always do it from the calling application?

Thanks for your advice I will attempt to implement that and feedback here
how it works.

--
Siv
Martley, Near Worcester, United Kingdom.
"Dave Sexton" <dave@jwa[remove.this]online.comwrote in message
news:Op**************@TK2MSFTNGP04.phx.gbl...

>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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

>>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,MessageBoxButton s.OKCancel,MessageBoxIcon.Question,MessageBoxDefau ltButton.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.

[Dave Sexton]

Hi Siv,

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

Your AsyncCallback is closing the connection, which means that all
transactions must be either rolled-back or committed so Sql Server takes it
upon itself to commit your transactions if there are no known errors at the
time you call Conn.Close(), afterwards your loop attempts to explicitly
close the 'completed' transaction and you get an error.

For this reason you don't have to call commit and I don't think you need to
rollback the transaction if you close the connection after an SqlException
is thrown, if there is an error in your stored procedure, but I would test
that to make sure.

-- Dave Sexton

"Siv" <si*@removeme.sivill.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

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.comwrote in message
news:Op**************@TK2MSFTNGP04.phx.gbl...

>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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...

>>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,MessageBoxButton s.OKCancel,MessageBoxIcon.Question,MessageBoxDefau ltButton.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.

[Dave Sexton]

Hi Siv,

Interesting, how I posted a response to your previous post and didn't see
this one until after mine showed up and mine was posted 15 minutes after
yours. I'm confused by what happened and by what I just wrote :p

Anyway, review my last post and omit the call to Commit and Rollback. Try
that and see if it solves your problem.

Think about my original suggestion of returning to the caller immediately
after invoking the asynchronous operation and using a Timer to update the
GUI with periodic progress notifications, if completely necessary. For one
thing it will be cleaner having the GUI code in one method and the
data-access code in another, but that's the least you could do to make your
program more OO.

If your using the 2.0 framework then you might also think about using the
BackgroundWorker component instead of "Asynchronous Process" and
Begin/EndExecuteNonQuery. BackgroundWorker provides an asynchronous
ThreadPool thread for doing "Work" and raises an event on the UI Thread when
the process has completed. BackgroundWorker supports progress notifications
through an event and, if you'd like, cancellation.

-- Dave Sexton

"Siv" <si*@removeme.sivill.comwrote in message
news:eL**************@TK2MSFTNGP03.phx.gbl...

Dave,
Then I modified the call-back again:

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;
command.Transaction.Commit(); //<=*******************
int rowCount = command.EndExecuteNonQuery(result);
string rowText = " rows affected.";
if (rowCount == 1)
{
rowText = " row affected.";
}
rowText = rowCount + rowText;

It now passes through this code OK and the process completes, but I am
getting a strange effect in that the process appears to start over again
with no apparent reason why? The routine seems to be in a loop, as soon as
the transaction completes I get back to the point just before I clicked
"OK" in a dialog that started the process off?

Is this some sort of threading problem??
--
Siv
Martley, Near Worcester, United Kingdom.
"Siv" <si*@removeme.sivill.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...

>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.comwrote in message
news:Op**************@TK2MSFTNGP04.phx.gbl...

>>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" <si*@removeme.sivill.comwrote in message
news:%2***************@TK2MSFTNGP05.phx.gbl...
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,MessageBoxButto ns.OKCancel,MessageBoxIcon.Question,MessageBoxDefa ultButton.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.