ASP.Net - BeginExecuteNonQuery Question

Hello all, I am going to test later running a SQL proc call so it runs asynch as the operation on the SQL side takes up to 30 minutes to complete and return.  I want to just call it and continue the code run on my web page.  I have pasted my code.  The PrepareCommand is just putting together the parameters I pass in a param array etc. so there is not need to post that with the code below I think.  My questions are:

- How will the EndAsyncOperation get called when it is done or do I need some more code like a while loop and thread sleep to handle the end of the call?  I don't want a loop again because I don't want to wait for it I think.  I put together examples I found but nothing was totally clear.

- I did not know how to close the connection so is it okay to have the connection.close where I have it?

- Do I need a command timeout at all due to it may take 30 minutes and I don't want to error at all.  I assume no if it is asynch it will be fine.

Thanks for any help.
public void AsynchBeginExecuteNonQuery(SqlConnection conn, CommandType commandType, string commandText, SqlParameter[] commandParameters)
        {
            try
            {
                if (conn == null) throw new ArgumentNullException("connection");
 
                // open the connection here because of asynch
                SqlConnection connection = new SqlConnection(conn + "async=true;");
 
                connection.Open();
 
                // Create a command and prepare it for execution
                SqlCommand cmd = new SqlCommand();
 
                PrepareCommand(cmd, conn, (SqlTransaction)null, commandType, commandText, commandParameters);
                AsyncCallback callback = new AsyncCallback(EndAsyncOperation);
                cmd.BeginExecuteNonQuery(callback, cmd);
                connection.Close();
            }
            catch (Exception e)
            {
                conn.Close();
                csLog.LogMsg("\r\n\r\nDAController:AsynchBeginExecuteNonQuery( " + e.Source + "\r\n\r\n" + e.Message + "\r\n\r\n" + e.StackTrace);
                throw;
            }
        }
 
        void EndAsyncOperation(IAsyncResult ar)
        {
            SqlCommand cmdAsyn = (SqlCommand)ar.AsyncState;
            //End the asynchronous call
            cmdAsyn.EndExecuteNonQuery(ar);
        }

Open in new window

sbornstein2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GuitarRichCommented:
The AsyncCallback object should handle calling the EndAsyncOperation method so you don't want to do any kind of loop until its done - that would defeat the object of doing the query asynchronusly.
I think you will definitely need to change the CommandTimeout property of the command object - the default is 30 seconds I belive, so unless you change this, it will timeout.
0
sbornstein2Author Commented:
but does the connection string append of asynch=true mean I don't have to do that?  I thought I read somewhere I don't have to worry about it but I am double checking here.  I guess I could add it as a fallback.
0
sbornstein2Author Commented:
i see actually the connection and command are completly different, so I will add that in as well.  Does the connection.close right after the Begin call going to be fine ya think?
0
GuitarRichCommented:
Probably not - I would stick this line in the EndAsyncOperation method

cmdAsyn.Connection.Close()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sbornstein2Author Commented:
thanks for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.