Re: Threading with SQL Server methods

Hi Mr. Vaughn,

I added a BackgroundWorker component to my form, then populated all of it's
events similar to the example on MSDN. Launching it was no problem, but the
BackgroundWorker.IsBusy property was always true, so I could not call it
again for another SQL operation.

When the routine (i.e. Routine7) that my BackgroundWorker object calls is
finished, shouldn't the BackgroundWorker object terminate itself, or should I
call the object's CancelAsync method from Routine7?

"William Vaughn [MVP]" wrote:

I have a long section on threading in my book. There I discuss any number
of issues to consider when trying to execute queries or updates
asynchronously. First, connecting is always done synchronously in ADO.NET.
While it's possible to create a BackgroundWorker thread to open a
connection, after the connection pool is filled there is no reason to add
the complexity of thread handling to the connection strategy.

In addition, if you're working with ASP.NET, it's still possible to run
SQL operations asynchronously, it's rarely done as there is nowhere to
call back to and the worker process is typically not going to wait around
for operations to complete.

Let's assume you're working with Windows Forms applications. In this case,
you need to consider executing more than one operation on a single
connection--SQL Server won't permit this. While MARS gives you some
flexibility here, I don't endorse its use as there are so many complex
issues involved. In addition, if you try to use ADO.NET asynchronous
operations, you'll discover that less than half of the operation is async.
That is, the BeginExecuteReader only executes the query and signals that
it's done when the server is ready to return the first row. The fetch
operations (which can take far more than 1/2 of the execution time) are
synchronous. Again BackgroundWorker threads are really needed here to
execute queries asynchronously--especially if you want to fetch async too.

So, once the data is fetched, it's placed in a DataTable. There is no
reason you shouldn't be able to access this data as long as you do so from
the thread that owns it's address space. The async ops give you an
opportunity to signal out to other threads to start working on a populated
DataTable.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________________________________
__________________

"Linchi Shea" <LinchiShea@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:214E5720-E54F-4031-8058-B18EC0171360@xxxxxxxxxxxxxxxx
By threading, do you want to make your client application program
multi-threaded? You can easily make calls to SQL Server in a separate
thread,
no different from coding a multi-threaded app without making any SQL
calls.
Can you clarify exactly what problem you are trying to address?

Linchi

"jp2msft" wrote:

I am developing a new application.

Since it is being developed from scratch, I want to implement a lot of
threading - especially for my SQL calls.

The problem I noticed is that I can't seem to interact with data that
is in
a thread, and all data that goes to a thread must be static - hence my
application can not access it.

Is there a place that spells out how to thread my routines and make it
so
that I can pass data to it and read the resulting data that results?

My preferred language is C# or VB, using Visual Studio 2005.

Thanks for the help!

(FYI: I posted this also in the "General" area, but I received an error
when
posting. My apologies if this is a double post)

.

Relevant Pages

  • Re: try...catch execution issue
    ... If I execute a procedure, an error occurs, call the ... But I would suggest that when you use TRY-CATCH, you don't care about the ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Problem with bulk load security.
    ... go back read my previous post about using EXECUTE AS ... you need to impersonate a *server login*. ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)
  • Re: Starting up database customers every minute or so in SQL LOG
    ... Did you check the initial few lines of the sql log as I mentioned?If you are ... unable to find the location of the error log, then execute the below command ... priority class 'normal'(n CPU detected) " where n indicates the number. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: proper permissions for aspnetdb membership access
    ... Windows built-in account. ... application's application pool to execute the T-SQL statements. ... Open your SQL Server Management Studio with a local ... If it does not exist in the logins of your SQL Server ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Server calling a Universe Subroutine
    ... I'm just trying to run from Sql Analyzer now but eventually it will be ... see if an error is being thrown that Sql Server is hiding and I check ... I'm trying to call an .NET console exe program from a trigger in Sql ... Services calling other services or trying to execute something ...
    (comp.databases.pick)
Loading