Asynchronous database calls

  • Thread starter Thread starter Amir Tohidi
  • Start date Start date
A

Amir Tohidi

Hi

Using .NET 2.0, is there anyway of invoking Sybase and Oracle stored
procedures asynchronously?

The scenario is this: we have some (Sybase and Oracle) stored procedures
that are taking a few minutes to run. This means that our middle tier objects
are “blocked†waiting for the stored procedures to return their results. I
know I can use a worker thread from the user interface to effectively kick
off the time consuming call to the middle tier and database. However, my real
goal is to release middle tier resources as early as possible. In other
words, I would like to invoke a stored procedure and immediately free up the
resources consumed by the middle tier (e.g. database connection). Then, when
the stored procedure completes, it somehow signals to the middle tier that
the data is ready.

I did something similar to this with SQL Server and extended stored
procedures, where my extended stored procedure called a COM component and
gave it the results.

Can this be done in a .NET 2.0, Oracle 10g and Sybase ASE 12 setup?

Thanks!
 
Sure. Use a BackgroundWorkerThread to start the async operation. While the
Oracle and Sybase namespaces might not support the SqlClient asyn ops
extensions, the BWT should do nicely.


Amir Tohidi said:
Hi

Using .NET 2.0, is there anyway of invoking Sybase and Oracle stored
procedures asynchronously?

The scenario is this: we have some (Sybase and Oracle) stored procedures
that are taking a few minutes to run. This means that our middle tier
objects
are “blocked†waiting for the stored procedures to return their results. I
know I can use a worker thread from the user interface to effectively kick
off the time consuming call to the middle tier and database. However, my
real
goal is to release middle tier resources as early as possible. In other
words, I would like to invoke a stored procedure and immediately free up
the
resources consumed by the middle tier (e.g. database connection). Then,
when
the stored procedure completes, it somehow signals to the middle tier that
the data is ready.

I did something similar to this with SQL Server and extended stored
procedures, where my extended stored procedure called a COM component and
gave it the results.

Can this be done in a .NET 2.0, Oracle 10g and Sybase ASE 12 setup?

Thanks!
Click to expand...

--
__________________________________________________________________________
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)
____________________________________________________________________________________________
 
Thanks Bill.

In the end I used MSQM: I put the requests in a queue and immediately return
to the caller. A Windows Service then processes the requests, one at a time,
from the queue. The nice thing about this solution is that it throttles the
database hits too, as the requests are handled one at a time. We are using
this route only for queries that take a long time to process so the
throttling provided by MSQM is a nice side effect.
 
Interesting approach. If it works, don't fix it... ;)

--
__________________________________________________________________________
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)
____________________________________________________________________________________________
 
Back
Top