// of the IAsyncResult parameter.
Martley, Near Worcester, United Kingdom.
news:%23RnWPOhpGHA.4188@TK2MSFTNGP03.phx.gbl...
Quote:
>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:Oph$ygepGHA.4988@TK2MSFTNGP04.phx.gbl...
Quote:
>>Hi Siv,
>>>
>>1. You have to call EndExecuteNonQuery at some point.
>>>
>>2. Why loop until IsCompleted is true, sleeping for 100 ms every
>>iteration, when you can just specify an AsyncCallback and immediately
>>return to the caller? One of the overloads for BeginExecuteNonQuery
>>accepts an AsyncCallback argument and an Object argument that you can
>>use to send state information to the callback. The callback
>>implementation is the perfect opportunity to call EndExecuteNonQuery on
>>the SqlCommand, which can be supplied as the state argument, and to
>>update the UI to notify the user that the process has completed.
>>>
>>3. Use a Timer to update the UI thread if you want to display status
>>information to the user while processing asynchronously, unless the app
>>is targeted for users that have no need for performance or diagnostics
>>data, in which case I would omit the Timer altogether and display an
>>animating progress bar or something of the like.
>>>
>>4. See inline
>>>
>>>SqlCommand cm = new SqlCommand();
>>[snip]
>>>cm.Connection=Conn;
>>[snip]
>>>IAsyncResult Res = cm.BeginExecuteNonQuery();
>>>tran = Conn.BeginTransaction();
>>>
>>If you are trying to execute this command within a transaction:
>>>
>>tran = Conn.BeginTransaction();
>>SqlCommand cm = new SqlCommand("proc_Calc_Monthly", Conn, tran);
>>cm.CommandType = CommandType.StoredProcedure;
>>IAsyncResult Res = cm.BeginExecuteNonQuery();
>>>
>>5. Your code does not show any necessity for a transaction since you are
>>only executing a single command against the database. If your stored
>>procedure requires a transaction then code the transaction in T-SQL.
>>>
>>6. Use the SqlCommandBuilder.DeriveParameters method so you don't have
>>to hard-code every parameter in your SqlCommand, or better yet use the
>>Data Access Application Block from Microsoft, which provides a
>>ParameterCache and standardized data access utilities.
>>>
>>- Dave Sexton
>>>
>>"Siv" <siv@removeme.sivill.comwrote in message
>>news:%23xwOcCcpGHA.524@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.
>>>>
>>>
>>>
>>
>>
