473,729 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.Connection String = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true;A synchronous Processing=true ;

Conn.Open();
cm.Connection=C onn;
cm.CommandText = "proc_Calc_Mont hly";
cm.CommandType = CommandType.Sto redProcedure;
cm.CommandTimeo ut = 0;

par = cm.CreateParame ter();
par.Direction=P arameterDirecti on.Input;
par.ParameterNa me="@SM";
par.SqlDbType=S qlDbType.Int;
par.Value=EndM; //This is a variable holding an end month as an integer such
as 7 meaning July
cm.Parameters.A dd(par);

par = cm.CreateParame ter();
par.Direction=P arameterDirecti on.Input;
par.ParameterNa me="@SY";
par.SqlDbType=S qlDbType.Int;
par.Value=EndY; //This is a varible holding an end year as an integer such
as 2006
cm.Parameters.A dd(par);

//Start transaction here
IAsyncResult Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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}",E lapsedTime);
Readout.text += "\nPress F2 function key to abort ...";
System.Windows. Forms.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Month ly Stored Procedure will be rolled back?";
if(MessageBox.S how(msg,Common. H,MessageBoxBut tons.OKCancel,M essageBoxIcon.Q uestion,Message BoxDefaultButto n.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 BeginExecuteNon Query 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.
Jul 12 '06 #1
7 9711
Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query 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 EndExecuteNonQu ery 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=C onn;
[snip]
IAsyncResult Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();
If you are trying to execute this command within a transaction:

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
IAsyncResult Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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.s ivill.comwrote in message
news:%2******** *******@TK2MSFT NGP05.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.Connection String = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true;A synchronous Processing=true ;

Conn.Open();
cm.Connection=C onn;
cm.CommandText = "proc_Calc_Mont hly";
cm.CommandType = CommandType.Sto redProcedure;
cm.CommandTimeo ut = 0;

par = cm.CreateParame ter();
par.Direction=P arameterDirecti on.Input;
par.ParameterNa me="@SM";
par.SqlDbType=S qlDbType.Int;
par.Value=EndM; //This is a variable holding an end month as an integer
such as 7 meaning July
cm.Parameters.A dd(par);

par = cm.CreateParame ter();
par.Direction=P arameterDirecti on.Input;
par.ParameterNa me="@SY";
par.SqlDbType=S qlDbType.Int;
par.Value=EndY; //This is a varible holding an end year as an integer such
as 2006
cm.Parameters.A dd(par);

//Start transaction here
IAsyncResult Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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}",E lapsedTime);
Readout.text += "\nPress F2 function key to abort ...";
System.Windows. Forms.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Month ly Stored Procedure will be rolled back?";

if(MessageBox.S how(msg,Common. H,MessageBoxBut tons.OKCancel,M essageBoxIcon.Q uestion,Message BoxDefaultButto n.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 BeginExecuteNon Query 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.

Jul 12 '06 #2
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******** ******@TK2MSFTN GP04.phx.gbl...
Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query
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 EndExecuteNonQu ery 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.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

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

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
IAsyncResult Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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.s ivill.comwrote in message
news:%2******** *******@TK2MSFT NGP05.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();
SqlConnectio n Conn = new SqlConnection() ;

SqlTransacti on tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.Connectio nString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true; Asynchronous Processing=true ;

Conn.Open();
cm.Connection= Conn;
cm.CommandTe xt = "proc_Calc_Mont hly";
cm.CommandTy pe = CommandType.Sto redProcedure;
cm.CommandTime out = 0;

par = cm.CreateParame ter();
par.Direction= ParameterDirect ion.Input;
par.ParameterN ame="@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.CreateParame ter();
par.Direction= ParameterDirect ion.Input;
par.ParameterN ame="@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.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Mont hly Stored Procedure will be rolled back?";

if(MessageBox. Show(msg,Common .H,MessageBoxBu ttons.OKCancel, MessageBoxIcon. Question,Messag eBoxDefaultButt on.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 BeginExecuteNon Query 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.


Jul 12 '06 #3
Siv
Dave,
I modified my code so that it does the following:

=============== =============== ============
Conn.Connection String = Common.strCnn;
Conn.Open();

tran = Conn.BeginTrans action();
cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
cm.CommandTimeo ut = 0;

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SM";
par.SqlDbType = SqlDbType.Int;
par.Value = EndM;
cm.Parameters.A dd(par);

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SY";
par.SqlDbType = SqlDbType.Int;
par.Value = EndY;
cm.Parameters.A dd(par);

AsyncCallback callback = new AsyncCallback(H andleCallback);
cm.BeginExecute NonQuery(callba ck, cm);

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

while (isExecuting)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created
by the Proc_Calc_Month ly Stored Procedure will be rolled back?";
if (MessageBox.Sho w(msg, Common.H,
MessageBoxButto ns.OKCancel, MessageBoxIcon. Question,
MessageBoxDefau ltButton.Button 1) == 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)res ult.AsyncState;
int rowCount = command.EndExec uteNonQuery(res ult);
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(s tring Text)
{
this.Readout.Te xt = 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(IsExecuti ng) 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.EndExec uteNonQuery(res ult);

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******** ******@TK2MSFTN GP04.phx.gbl...
Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query
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 EndExecuteNonQu ery 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.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

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

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
IAsyncResult Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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.s ivill.comwrote in message
news:%2******** *******@TK2MSFT NGP05.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();
SqlConnectio n Conn = new SqlConnection() ;

SqlTransacti on tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.Connectio nString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true; Asynchronous Processing=true ;

Conn.Open();
cm.Connection= Conn;
cm.CommandTe xt = "proc_Calc_Mont hly";
cm.CommandTy pe = CommandType.Sto redProcedure;
cm.CommandTime out = 0;

par = cm.CreateParame ter();
par.Direction= ParameterDirect ion.Input;
par.ParameterN ame="@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.CreateParame ter();
par.Direction= ParameterDirect ion.Input;
par.ParameterN ame="@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.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Mont hly Stored Procedure will be rolled back?";

if(MessageBox. Show(msg,Common .H,MessageBoxBu ttons.OKCancel, MessageBoxIcon. Question,Messag eBoxDefaultButt on.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 BeginExecuteNon Query 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.


Jul 13 '06 #4
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)res ult.AsyncState;
command.Transac tion.Commit(); //<=************* ******
int rowCount = command.EndExec uteNonQuery(res ult);
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.s ivill.comwrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..
Dave,
I modified my code so that it does the following:

=============== =============== ============
Conn.Connection String = Common.strCnn;
Conn.Open();

tran = Conn.BeginTrans action();
cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
cm.CommandTimeo ut = 0;

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SM";
par.SqlDbType = SqlDbType.Int;
par.Value = EndM;
cm.Parameters.A dd(par);

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SY";
par.SqlDbType = SqlDbType.Int;
par.Value = EndY;
cm.Parameters.A dd(par);

AsyncCallback callback = new AsyncCallback(H andleCallback);
cm.BeginExecute NonQuery(callba ck, cm);

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

while (isExecuting)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions
created by the Proc_Calc_Month ly Stored Procedure will be rolled back?";
if (MessageBox.Sho w(msg, Common.H,
MessageBoxButto ns.OKCancel, MessageBoxIcon. Question,
MessageBoxDefau ltButton.Button 1) == 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)res ult.AsyncState;
int rowCount = command.EndExec uteNonQuery(res ult);
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(s tring Text)
{
this.Readout.Te xt = 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(IsExecuti ng) 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.EndExec uteNonQuery(res ult);

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******** ******@TK2MSFTN GP04.phx.gbl...
>Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query
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 EndExecuteNonQu ery 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]
>>IAsyncResul t Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

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

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandTy pe = CommandType.Sto redProcedure;
IAsyncResult Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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.s ivill.comwrote in message
news:%2******* ********@TK2MSF TNGP05.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
notificatio n 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();
SqlParamete r par = new SqlParameter();
SqlConnecti on Conn = new SqlConnection() ;

SqlTransactio n tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.Connecti onString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true ;Asynchronous Processing=true ;

Conn.Open() ;
cm.Connection =Conn;
cm.CommandTex t = "proc_Calc_Mont hly";
cm.CommandTyp e = CommandType.Sto redProcedure;
cm.CommandTim eout = 0;

par = cm.CreateParame ter();
par.Direction =ParameterDirec tion.Input;
par.Parameter Name="@SM";
par.SqlDbType =SqlDbType.Int;
par.Value=End M; //This is a variable holding an end month as an integer
such as 7 meaning July
cm.Parameters .Add(par);

par = cm.CreateParame ter();
par.Direction =ParameterDirec tion.Input;
par.Parameter Name="@SY";
par.SqlDbType =SqlDbType.Int;
par.Value=End Y; //This is a varible holding an end year as an integer
such as 2006
cm.Parameters .Add(par);

//Start transaction here
IAsyncResul t Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Mon thly Stored Procedure will be rolled back?";

if(MessageBox .Show(msg,Commo n.H,MessageBoxB uttons.OKCancel ,MessageBoxIcon .Question,Messa geBoxDefaultBut ton.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 BeginExecuteNon Query 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.



Jul 13 '06 #5
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.s ivill.comwrote in message
news:%2******** ********@TK2MSF TNGP05.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******** ******@TK2MSFTN GP04.phx.gbl...
>Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query
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 EndExecuteNonQu ery 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]
>>IAsyncResul t Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

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

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandTy pe = CommandType.Sto redProcedure;
IAsyncResult Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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.s ivill.comwrote in message
news:%2******* ********@TK2MSF TNGP05.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
notificatio n 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();
SqlParamete r par = new SqlParameter();
SqlConnecti on Conn = new SqlConnection() ;

SqlTransactio n tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.Connecti onString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true ;Asynchronous Processing=true ;

Conn.Open() ;
cm.Connection =Conn;
cm.CommandTex t = "proc_Calc_Mont hly";
cm.CommandTyp e = CommandType.Sto redProcedure;
cm.CommandTim eout = 0;

par = cm.CreateParame ter();
par.Direction =ParameterDirec tion.Input;
par.Parameter Name="@SM";
par.SqlDbType =SqlDbType.Int;
par.Value=End M; //This is a variable holding an end month as an integer
such as 7 meaning July
cm.Parameters .Add(par);

par = cm.CreateParame ter();
par.Direction =ParameterDirec tion.Input;
par.Parameter Name="@SY";
par.SqlDbType =SqlDbType.Int;
par.Value=End Y; //This is a varible holding an end year as an integer
such as 2006
cm.Parameters .Add(par);

//Start transaction here
IAsyncResul t Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Mon thly Stored Procedure will be rolled back?";

if(MessageBox .Show(msg,Commo n.H,MessageBoxB uttons.OKCancel ,MessageBoxIcon .Question,Messa geBoxDefaultBut ton.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 BeginExecuteNon Query 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.



Jul 13 '06 #6
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.s ivill.comwrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..
Dave,
I modified my code so that it does the following:

=============== =============== ============
Conn.Connection String = Common.strCnn;
Conn.Open();

tran = Conn.BeginTrans action();
cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
cm.CommandTimeo ut = 0;

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SM";
par.SqlDbType = SqlDbType.Int;
par.Value = EndM;
cm.Parameters.A dd(par);

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SY";
par.SqlDbType = SqlDbType.Int;
par.Value = EndY;
cm.Parameters.A dd(par);

AsyncCallback callback = new AsyncCallback(H andleCallback);
cm.BeginExecute NonQuery(callba ck, cm);

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

while (isExecuting)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions
created by the Proc_Calc_Month ly Stored Procedure will be rolled back?";
if (MessageBox.Sho w(msg, Common.H,
MessageBoxButto ns.OKCancel, MessageBoxIcon. Question,
MessageBoxDefau ltButton.Button 1) == 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)res ult.AsyncState;
int rowCount = command.EndExec uteNonQuery(res ult);
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(s tring Text)
{
this.Readout.Te xt = 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(IsExecuti ng) 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.EndExec uteNonQuery(res ult);

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******** ******@TK2MSFTN GP04.phx.gbl...
>Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query
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 EndExecuteNonQu ery 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]
>>IAsyncResul t Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

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

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandTy pe = CommandType.Sto redProcedure;
IAsyncResult Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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.s ivill.comwrote in message
news:%2******* ********@TK2MSF TNGP05.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
notificatio n 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();
SqlParamete r par = new SqlParameter();
SqlConnecti on Conn = new SqlConnection() ;

SqlTransactio n tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.Connecti onString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=true ;Asynchronous Processing=true ;

Conn.Open() ;
cm.Connection =Conn;
cm.CommandTex t = "proc_Calc_Mont hly";
cm.CommandTyp e = CommandType.Sto redProcedure;
cm.CommandTim eout = 0;

par = cm.CreateParame ter();
par.Direction =ParameterDirec tion.Input;
par.Parameter Name="@SM";
par.SqlDbType =SqlDbType.Int;
par.Value=End M; //This is a variable holding an end month as an integer
such as 7 meaning July
cm.Parameters .Add(par);

par = cm.CreateParame ter();
par.Direction =ParameterDirec tion.Input;
par.Parameter Name="@SY";
par.SqlDbType =SqlDbType.Int;
par.Value=End Y; //This is a varible holding an end year as an integer
such as 2006
cm.Parameters .Add(par);

//Start transaction here
IAsyncResul t Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.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.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Mon thly Stored Procedure will be rolled back?";

if(MessageBox .Show(msg,Commo n.H,MessageBoxB uttons.OKCancel ,MessageBoxIcon .Question,Messa geBoxDefaultBut ton.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 BeginExecuteNon Query 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.



Jul 13 '06 #7
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
BackgroundWorke r component instead of "Asynchrono us Process" and
Begin/EndExecuteNonQu ery. BackgroundWorke r provides an asynchronous
ThreadPool thread for doing "Work" and raises an event on the UI Thread when
the process has completed. BackgroundWorke r supports progress notifications
through an event and, if you'd like, cancellation.

-- Dave Sexton

"Siv" <si*@removeme.s ivill.comwrote in message
news:eL******** ******@TK2MSFTN GP03.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)res ult.AsyncState;
command.Transac tion.Commit(); //<=************* ******
int rowCount = command.EndExec uteNonQuery(res ult);
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.s ivill.comwrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..
>Dave,
I modified my code so that it does the following:

============== =============== =============
Conn.Connection String = Common.strCnn;
Conn.Open();

tran = Conn.BeginTrans action();
cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandType = CommandType.Sto redProcedure;
cm.CommandTimeo ut = 0;

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SM";
par.SqlDbType = SqlDbType.Int;
par.Value = EndM;
cm.Parameters.A dd(par);

par = cm.CreateParame ter();
par.Direction = ParameterDirect ion.Input;
par.ParameterNa me = "@SY";
par.SqlDbType = SqlDbType.Int;
par.Value = EndY;
cm.Parameters.A dd(par);

AsyncCallback callback = new
AsyncCallback( HandleCallback) ;
cm.BeginExecute NonQuery(callba ck, cm);

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

while (isExecuting)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.Thread.Sleep( 100);
//Let system in for a click
Readout.Text = "Processing Monthly Totals
calculation - Please Wait ... \nTime Elapsed=" +
string.Format( "{0:hh:mm:s s}", ElapsedTime) + "\nPress F2 function key to
abort ...";
System.Windows. Forms.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions
created by the Proc_Calc_Month ly Stored Procedure will be rolled back?";
if (MessageBox.Sho w(msg, Common.H,
MessageBoxButt ons.OKCancel, MessageBoxIcon. Question,
MessageBoxDefa ultButton.Butto n1) == 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)res ult.AsyncState;
int rowCount = command.EndExec uteNonQuery(res ult);
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(s tring Text)
{
this.Readout.Te xt = 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(IsExecut ing) 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.EndExec uteNonQuery(res ult);

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******* *******@TK2MSFT NGP04.phx.gbl.. .
>>Hi Siv,

1. You have to call EndExecuteNonQu ery 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 BeginExecuteNon Query
accepts an AsyncCallback argument and an Object argument that you can
use to send state information to the callback. The callback
implementatio n is the perfect opportunity to call EndExecuteNonQu ery 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.Connectio n=Conn;
[snip]
IAsyncResu lt Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

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

tran = Conn.BeginTrans action();
SqlCommand cm = new SqlCommand("pro c_Calc_Monthly" , Conn, tran);
cm.CommandTyp e = CommandType.Sto redProcedure;
IAsyncResul t Res = cm.BeginExecute NonQuery();

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 SqlCommandBuild er.DeriveParame ters 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
ParameterCach e and standardized data access utilities.

- Dave Sexton

"Siv" <si*@removeme.s ivill.comwrote in message
news:%2****** *********@TK2MS FTNGP05.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
notificati on 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();
SqlParamet er par = new SqlParameter();
SqlConnectio n Conn = new SqlConnection() ;

SqlTransacti on tran=null;

DateTime CurrTime, PrevTime;
TimeSpan ElapsedTime;

Conn.Connect ionString = Common.strCnn;
//Common.strCnn is a user setting pointing to a Connection string
//that is formatted like this:
//Server=SERVERNA ME;DATABASE=DAT ABASENAME;Integ rated
Security=tru e;Asynchronous Processing=true ;

Conn.Open( );
cm.Connectio n=Conn;
cm.CommandTe xt = "proc_Calc_Mont hly";
cm.CommandTy pe = CommandType.Sto redProcedure;
cm.CommandTi meout = 0;

par = cm.CreateParame ter();
par.Directio n=ParameterDire ction.Input;
par.Paramete rName="@SM";
par.SqlDbTyp e=SqlDbType.Int ;
par.Value=En dM; //This is a variable holding an end month as an integer
such as 7 meaning July
cm.Parameter s.Add(par);

par = cm.CreateParame ter();
par.Directio n=ParameterDire ction.Input;
par.Paramete rName="@SY";
par.SqlDbTyp e=SqlDbType.Int ;
par.Value=En dY; //This is a varible holding an end year as an integer
such as 2006
cm.Parameter s.Add(par);

//Start transaction here
IAsyncResu lt Res = cm.BeginExecute NonQuery();
tran = Conn.BeginTrans action();

while (!Res.IsComplet ed)
{
CurrTime = DateTime.Now;
ElapsedTime = CurrTime.Subtra ct(PrevTime);
System.Threadin g.Thread.Sleep( 100);
//Let system in for a click
Readout.Text = "Processing Monthly Totals calculation - Please
Wait ... ";
Readout.Text += "\nTime Elapsed=" +
string.Forma t("{0:hh:mm:ss} ",ElapsedTi me);
Readout.text += "\nPress F2 function key to abort ...";
System.Windows. Forms.Applicati on.DoEvents();
if (CalcsAborted)
{
string msg = "Are you sure, all transactions created by the
Proc_Calc_Mo nthly Stored Procedure will be rolled back?";

if(MessageBo x.Show(msg,Comm on.H,MessageBox Buttons.OKCance l,MessageBoxIco n.Question,Mess ageBoxDefaultBu tton.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
IsComplete d flag is set true, but at the moment it seems to never
return true. I took this from an example in the BeginExecuteNon Query
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.



Jul 13 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3303
by: Jarrod Morrison | last post by:
Hi All Im using a stored procedure on my sql server and am unsure of the syntax that i should use in it. Im pretty sure that there is a way to do what i want, but as yet i havent been able to find much info on it. Basically the procedure takes the machinename and username supplied and searches a table or two for some matches and this part works great. The only problem i have is that with the app that ties in with the procedure returns...
0
6702
by: Nashat Wanly | last post by:
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET View products that this article applies to. This article was previously published under Q310070 For a Microsoft Visual Basic .NET version of this article, see 308049. For a Microsoft Visual C++ .NET version of this article, see 310071. For a Microsoft Visual J# .NET version of this article, see 320627. This article refers to the following Microsoft .NET...
3
1867
by: David | last post by:
Hi There! I'm using Timer control to record how long my application perform certain tasks. However, apparently Timer control is not doing its' job (i.e. Not firing Tick event) while my application is busy. So even if my application took 2 mins, the label that is used to show the number of seconds elapsed will still say "2 seconds".
4
13466
by: marc | last post by:
I've been developing a stored procedure that uses a user defined function in the query portion of the procedure. However, since the end product needs to allow for dynamic table names, the UDF will not work. I've been trying to get this to work with converting the UDF to a procedure, but I'm having no luck. Here is the background on what I'm trying to accomplish. I need to perform a sub-identity on a table, I have the normal identity set,...
2
5456
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
5
6837
by: Sandy | last post by:
Hello - I need a good example of how to take a return value from a stored procedure and use it in vb code. I have an app that searches a database by city and state. If a user makes a typo, the info sent to the stored procedure makes the stored procedure fail. I want to be able to display a label indicating they should try again, if this is the case. I can't use row count, because the stored procedure doesn't get that far. What...
6
2361
by: SandySears | last post by:
I am trying to use a stored procedure to insert a record using VS 2005, VB and SQL Server Express. The code runs without errors or exceptions, and returns the new identifer in the output parameters. It returns my error text message in another output parameter as "ok", which is the value that is set in the stored procedure prior to doing the insert. It returns my var for @@rowcount as 1. However, the record does not get into the table. ...
0
1274
by: r1 | last post by:
I am relatively inexperienced in using delegates and asynchronous methods. I read several articles, and then I developed my own code with a mission to improve the performance. Wow! I cannot believe the difference in speed! However, the asynch operation fails sometimes, despite of the fact that it works most of the time. I am really at a loss how to fix this sporadic and erratic behavior. This is a web application developed with...
7
3577
by: Susan | last post by:
In the application I am working on, the user makes a request of information from the database. As there is a great deal of information to go through and possibly return, to speed things up, the request for data only returns the first 25 rows of data. So thas the user knows how many total rows of data there are, a count is returned by another stored procedure. This query can take a while to complete so I have created a delete called...
0
8766
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9291
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9218
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9158
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4536
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2699
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.