Asynchronous database calls

[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!

 

[William Vaughn \(MVP\)]

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.

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!

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

 

[Amir Tohidi]

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.

 

[William Vaughn \(MVP\)]

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)
____________________________________________________________________________________________

 

[Cor Ligthert[MVP]]

Amir,

I have the idea that I use in a way in one applicatation the same approach
as you.

However it is a simple application so I have created my own windows forms
service that handles a queue.

If it was a more difficult situation I would probably use this:
http://msdn.microsoft.com/en-us/library/aa365590.aspx

Just to show you.

Cor