SQL Server Asynchronous Execute?

Question

I am looking for a way to let my C# (4.0) app send data to a SQL Server 2008 instance, asynchronously. I kind of like what I saw at http://nayyeri.net/asynchronous-command-execution-in-net-2-0 but that is not quite what I am looking for.

I have code like this:

// myDataTable is a .NET DataTable object
SqlCommand sc= new SqlCommand("dbo.ExtBegin", conn);
SqlParameter param1 = sc.Parameters.AddWithValue("@param1", "a");
SqlParameter param2 = sc.Parameters.AddWithValue("@param2", "b");
SqlParameter param3 = sc.Parameters.AddWithValue("@param3", myDataTable);
param3.SqlDbType = SqlDbType.Structured;
param3.TypeName = "dbo.MyTableType";
int execState = sc.ExecuteNonQuery();

And because the myDataTable is potentially large, I don't want the console app to hang while it sends the data to the server, if there are 6 big loads I want them all going at the same time without blocking at the console app. I don't want to send serially.

All ideas appreciated, thanks!

Remus Rusanu · Accepted Answer · 2011-03-18 19:33:29Z

2

set the AsynchronousProcessing property on the connection string to True.
Use BeginExecuteNonQuery

But what is dbo.ExtBegin doing? It all depends on it, as the calls may well serialize on locks in the database (at best) or, at worst, you may get incorrect results if the procedure is not properly designed for concurency.

answered Mar 18, 2011 at 19:33

Remus Rusanu

296k42 gold badges459 silver badges583 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Snowy Over a year ago

Thanks I think you just gave me the right combination of words to websearch for. I will also look at the DB side to see if it can actually handly concurrent calls in its receiving sproc. Service Broker is out because of the additional server config overhead. I didn't want to add custom threading code, I think BeginExecuteNonQuery might do it for me. Thanks.

Jake Kalstad · Accepted Answer · 2011-03-18 19:24:33Z

0

Create a thread and execute the query within that thread, make sure not to have subsequent database calls that would cause race conditions.

answered Mar 18, 2011 at 19:24

Jake Kalstad

2,0651 gold badge14 silver badges20 bronze badges

Comments

rhett · Accepted Answer · 2011-03-18 19:27:36Z

0

My first thought would be to spawn a new thread for the inserts, and have the main thread check the spawned thread's execution with AutoResetEvent, TimerCallback, and Timer objects.

I do it in Silverlight all the time.

answered Mar 18, 2011 at 19:27

rhett

1311 silver badge2 bronze badges

Comments

Abe Miessler · Accepted Answer · 2011-03-18 19:32:04Z

0

Take a look at using Service Broker Activation. This will allow you to call a stored proc and have it run on it's own thread why you continue on the current thread.

Here is an excellent article that goes over how to do this.

answered Mar 18, 2011 at 19:32

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Collectives™ on Stack Overflow

SQL Server Asynchronous Execute?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related