Asynchronous database calls

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
Tue Jul 08 12:11:12 CDT 2008

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" <AmirTohidi@discussions.microsoft.com> wrote in message
news:1053EACB-087B-46BB-98B5-68DD3639372D@microsoft.com...
> 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)
____________________________________________________________________________________________

AmirTohidi
Tue Jul 22 10:06:03 CDT 2008

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)" wrote:

> 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" <AmirTohidi@discussions.microsoft.com> wrote in message
> news:1053EACB-087B-46BB-98B5-68DD3639372D@microsoft.com...
> > 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)
> ____________________________________________________________________________________________
>
>
>

William
Tue Jul 22 10:39:26 CDT 2008

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



"Amir Tohidi" <AmirTohidi@discussions.microsoft.com> wrote in message
news:DB2D79B7-0747-4C51-A2B3-59FB000B1AF6@microsoft.com...
> 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)" wrote:
>
>> 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" <AmirTohidi@discussions.microsoft.com> wrote in message
>> news:1053EACB-087B-46BB-98B5-68DD3639372D@microsoft.com...
>> > 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)
>> ____________________________________________________________________________________________
>>
>>
>>

Cor
Tue Jul 22 11:25:30 CDT 2008

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


"Amir Tohidi" <AmirTohidi@discussions.microsoft.com> schreef in bericht
news:DB2D79B7-0747-4C51-A2B3-59FB000B1AF6@microsoft.com...
> 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)" wrote:
>
>> 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" <AmirTohidi@discussions.microsoft.com> wrote in message
>> news:1053EACB-087B-46BB-98B5-68DD3639372D@microsoft.com...
>> > 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)
>> ____________________________________________________________________________________________
>>
>>
>>