Stored Procedures and knowing when they complete.

Question

I have been tring to figure out how to know when a stored procedure (such as .ExecuteNonQuery()) has completed.

I have a loop that is for a text reader that is looking up ID's of records in the SqlServer table that need to be updated. However the loop is faster than the server and I am trying to figure out how to make the loop wait

Example:
using (tr = File.OpenText(myFilePath)
{

SqlCommand UpdateCmd = new SqlCommand("MyStoredProcedure", conn);

UpdateCmd.CommandType = CommandType.StoredProcedure;

while((templine = tr.ReadLine()) != null)

{

conn.Open();

UpdateCmd.Parameters.AddWithValue("@IDNumber", templine);

UpdateCmd.ExecuteNonQuery();

//this is the point at which I need to Wait for the Nonquery to finish.. How?

conn.Close();

}

}

I can get errors on similar issues like connection still open or you already have an open reader. etc.
Is there a quick and dirty way to make it wait for procedure to wait?

Any help will be appreciated.
Thanks
Frank

Sam Hobbs · Answer

Since we still don't know what the error is that needs to be solved it seems impossible to know what the best solution is but asynchronous execution sounds like an over-complicated solution.

Frank Galavan · Answer

Just read the article... IAsynchResult to wait for.... Nice! Thanks Murali!

Frank Galavan · Answer

It is running right now..... Let me ask you Murali If I do an asynch call on the first record of in the text file what will happen on the second record if the first hasn't finished? Do they queue up? It's all the same table. I will take a look at the msdn artical you suggested.

Muralidharan Deenathayalan · Answer

Frank Is it possible for you to implement the asynchronous sql command execution like SQLCommand.BeginExecuteNonQuery() though your server is not responded immediately still you can process the other records. http://msdn.microsoft.com/en-us/library/ca56w9se(v=vs.80).aspx

Frank Galavan · Answer

Thanks you both. I have been coding it all day and I have implemented the new code based on what both of you have said. Open and closed are now out side the read loop and all of the statements are surrounded by try...catch statements. As soon as I run it I will let you know the outcome. Thanks again.

Sam Hobbs · Answer

I don't know if it is better to do the open and close inside the loop or outside. So you can try doing as Felipe advises. My suggestion is to use try/catch so you get the benefit of the information available that way. Then if you still need help tell us what the error is.

Felipe Ramos · Answer

The next iteration of the loop will not execute until ExecuteNonQuery is done processing. The issue I see is that you are opening and closing a connection for ever line. Move the Open and Close outside of your loop for better performance.