CODEWITHSUNDEEP
sql-serverado.netasynchronous
Snowy
Snowy

Reputation: 6122

SQL Server Asynchronous Execute?

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!

Upvotes: 0

Views: 2250

Answers (4)

Remus Rusanu
Remus Rusanu

Reputation: 294247

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.

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85056

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.

Upvotes: 0

rhett
rhett

Reputation: 131

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.

Upvotes: 0

Jake Kalstad
Jake Kalstad

Reputation: 2065

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

Upvotes: 0

Related Questions