Static OledbConnection and DataReaders in remote objects

Static OledbConnection and DataReaders in remote objects

Post by Fabrício de Novaes Kucinski » Thu, 29 May 2003 11:34:20



Hi all,

My app has an object published on the server, wich is accessed by the
clients in Singleton mode. This object uses an unique and static
OleDbConnection.

In some methods, I use DataReaders (one by time), attacheds to this
connection. When I'm running the app, I get errors concerning the
DataReaders; the compiler shows errors telling me that the connection can't
have more than one Datareader attached.

I think this error occurs because The OleDb object is a static object, and
more than one client tries to create datareaders.

Is my supposition correct? If no, what can be the problem?
If yes, whats the best way to correct? Changing to SingleCall? Using a
dynamic OleDbConnection?

Thanks in advance,

Fabrcio de Novaes Kucinskis.

 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Terminato » Thu, 29 May 2003 16:49:18


You should close the datareader once done fetching the data before using the
next data reader. An open data reader locks the connection.



Quote:> Hi all,

> My app has an object published on the server, wich is accessed by the
> clients in Singleton mode. This object uses an unique and static
> OleDbConnection.

> In some methods, I use DataReaders (one by time), attacheds to this
> connection. When I'm running the app, I get errors concerning the
> DataReaders; the compiler shows errors telling me that the connection
can't
> have more than one Datareader attached.

> I think this error occurs because The OleDb object is a static object, and
> more than one client tries to create datareaders.

> Is my supposition correct? If no, what can be the problem?
> If yes, whats the best way to correct? Changing to SingleCall? Using a
> dynamic OleDbConnection?

> Thanks in advance,

> Fabrcio de Novaes Kucinskis.


 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Scott Alle » Fri, 30 May 2003 00:09:48


That won't be enough if there is concurrent access, and it sounds like
there is.

The easiest solution is to create a new connection each time a client
makes a call to your object, and close the connection as soon as
possible. Don't worry about the overhead of setting up and tearing
down real database connections as ADO.NET will pool the connections
for you, assuming you are using the same connection string and
security context each time.

If you really want to restrict yourself to a single connection, you'll
need to serialize calls (look up the Monitor class, or the lock
keyword in C#) while the data readers are open. This limits the
throughput of your object however, so I'd suggest the earlier
solution.

On Wed, 28 May 2003 00:49:18 -0700, "Terminator"


>You should close the datareader once done fetching the data before using the
>next data reader. An open data reader locks the connection.



>> Hi all,

>> My app has an object published on the server, wich is accessed by the
>> clients in Singleton mode. This object uses an unique and static
>> OleDbConnection.

>> In some methods, I use DataReaders (one by time), attacheds to this
>> connection. When I'm running the app, I get errors concerning the
>> DataReaders; the compiler shows errors telling me that the connection
>can't
>> have more than one Datareader attached.

>> I think this error occurs because The OleDb object is a static object, and
>> more than one client tries to create datareaders.

>> Is my supposition correct? If no, what can be the problem?
>> If yes, whats the best way to correct? Changing to SingleCall? Using a
>> dynamic OleDbConnection?

>> Thanks in advance,

>> Fabrcio de Novaes Kucinskis.

--
Scott
 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Fabrício de Novaes Kucinski » Fri, 30 May 2003 01:39:22


Terminator and Scott,

Thank you for the information. As lock the connection will decrease the
performance, I'll open and close the connections only when needed.
Best regards,

Fabrcio.


> That won't be enough if there is concurrent access, and it sounds like
> there is.

> The easiest solution is to create a new connection each time a client
> makes a call to your object, and close the connection as soon as
> possible. Don't worry about the overhead of setting up and tearing
> down real database connections as ADO.NET will pool the connections
> for you, assuming you are using the same connection string and
> security context each time.

> If you really want to restrict yourself to a single connection, you'll
> need to serialize calls (look up the Monitor class, or the lock
> keyword in C#) while the data readers are open. This limits the
> throughput of your object however, so I'd suggest the earlier
> solution.

> On Wed, 28 May 2003 00:49:18 -0700, "Terminator"

> >You should close the datareader once done fetching the data before using
the
> >next data reader. An open data reader locks the connection.



> >> Hi all,

> >> My app has an object published on the server, wich is accessed by the
> >> clients in Singleton mode. This object uses an unique and static
> >> OleDbConnection.

> >> In some methods, I use DataReaders (one by time), attacheds to this
> >> connection. When I'm running the app, I get errors concerning the
> >> DataReaders; the compiler shows errors telling me that the connection
> >can't
> >> have more than one Datareader attached.

> >> I think this error occurs because The OleDb object is a static object,
and
> >> more than one client tries to create datareaders.

> >> Is my supposition correct? If no, what can be the problem?
> >> If yes, whats the best way to correct? Changing to SingleCall? Using a
> >> dynamic OleDbConnection?

> >> Thanks in advance,

> >> Fabrcio de Novaes Kucinskis.

> --
> Scott

 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Fabrício de Novaes Kucinski » Sat, 31 May 2003 05:08:00


Hi Scott,

I have another doubt concerning the data access: if I'll open and close the
connections each time I need, is it a good idea to call the Dispose method
of the connections, or it will compromise this ADO.NET pooling?

Thanks again,

Fabrcio.


> That won't be enough if there is concurrent access, and it sounds like
> there is.

> The easiest solution is to create a new connection each time a client
> makes a call to your object, and close the connection as soon as
> possible. Don't worry about the overhead of setting up and tearing
> down real database connections as ADO.NET will pool the connections
> for you, assuming you are using the same connection string and
> security context each time.

> If you really want to restrict yourself to a single connection, you'll
> need to serialize calls (look up the Monitor class, or the lock
> keyword in C#) while the data readers are open. This limits the
> throughput of your object however, so I'd suggest the earlier
> solution.

> On Wed, 28 May 2003 00:49:18 -0700, "Terminator"

> >You should close the datareader once done fetching the data before using
the
> >next data reader. An open data reader locks the connection.



> >> Hi all,

> >> My app has an object published on the server, wich is accessed by the
> >> clients in Singleton mode. This object uses an unique and static
> >> OleDbConnection.

> >> In some methods, I use DataReaders (one by time), attacheds to this
> >> connection. When I'm running the app, I get errors concerning the
> >> DataReaders; the compiler shows errors telling me that the connection
> >can't
> >> have more than one Datareader attached.

> >> I think this error occurs because The OleDb object is a static object,
and
> >> more than one client tries to create datareaders.

> >> Is my supposition correct? If no, what can be the problem?
> >> If yes, whats the best way to correct? Changing to SingleCall? Using a
> >> dynamic OleDbConnection?

> >> Thanks in advance,

> >> Fabrcio de Novaes Kucinskis.

> --
> Scott

 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Scott Alle » Sat, 31 May 2003 06:10:59


Hi Fabrcio,

Actually Close and Dispose both produce the same result: the
underlying database connection gets returned to the pool. Just make
sure one of these two methods will get invoked after a call to Open
and everything should run smoothly (you will surely want to use a
finally block, or the using keyword in c#).

On Thu, 29 May 2003 17:08:00 -0300, "Fabrcio de Novaes Kucinskis"


>Hi Scott,

>I have another doubt concerning the data access: if I'll open and close the
>connections each time I need, is it a good idea to call the Dispose method
>of the connections, or it will compromise this ADO.NET pooling?

>Thanks again,

>Fabrcio.

--
Scott
 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Bill » Sat, 31 May 2003 08:16:27


The documentation is incorrect. Dispose does not remove a connection from
the pool. Calling dispose (as Scott said) closes the connection and marks
the object for disposal when the garbage collector runs.

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________



> Hi Scott,

> I have another doubt concerning the data access: if I'll open and close
the
> connections each time I need, is it a good idea to call the Dispose method
> of the connections, or it will compromise this ADO.NET pooling?

> Thanks again,

> Fabrcio.



> > That won't be enough if there is concurrent access, and it sounds like
> > there is.

> > The easiest solution is to create a new connection each time a client
> > makes a call to your object, and close the connection as soon as
> > possible. Don't worry about the overhead of setting up and tearing
> > down real database connections as ADO.NET will pool the connections
> > for you, assuming you are using the same connection string and
> > security context each time.

> > If you really want to restrict yourself to a single connection, you'll
> > need to serialize calls (look up the Monitor class, or the lock
> > keyword in C#) while the data readers are open. This limits the
> > throughput of your object however, so I'd suggest the earlier
> > solution.

> > On Wed, 28 May 2003 00:49:18 -0700, "Terminator"

> > >You should close the datareader once done fetching the data before
using
> the
> > >next data reader. An open data reader locks the connection.


message

> > >> Hi all,

> > >> My app has an object published on the server, wich is accessed by the
> > >> clients in Singleton mode. This object uses an unique and static
> > >> OleDbConnection.

> > >> In some methods, I use DataReaders (one by time), attacheds to this
> > >> connection. When I'm running the app, I get errors concerning the
> > >> DataReaders; the compiler shows errors telling me that the connection
> > >can't
> > >> have more than one Datareader attached.

> > >> I think this error occurs because The OleDb object is a static
object,
> and
> > >> more than one client tries to create datareaders.

> > >> Is my supposition correct? If no, what can be the problem?
> > >> If yes, whats the best way to correct? Changing to SingleCall? Using
a
> > >> dynamic OleDbConnection?

> > >> Thanks in advance,

> > >> Fabrcio de Novaes Kucinskis.

> > --
> > Scott

 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Fabrício de Novaes Kucinski » Sat, 31 May 2003 08:51:47


This is the point. The fact of a connection object be marked as "disposed"
to the GC will afect the app performance, if after this dispose I make
another connection, with another object, to the same database? Dispose a
connection object will afect the way as ADO.net uses data in "cache" to open
other connections?

My doubt is: whats the better choice to close connections in an application
wich open and closes connections to a database many times? Just call Close,
or Dispose, too?



> The documentation is incorrect. Dispose does not remove a connection from
> the pool. Calling dispose (as Scott said) closes the connection and marks
> the object for disposal when the garbage collector runs.

> --
> ____________________________________
> Bill Vaughn
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________



> > Hi Scott,

> > I have another doubt concerning the data access: if I'll open and close
> the
> > connections each time I need, is it a good idea to call the Dispose
method
> > of the connections, or it will compromise this ADO.NET pooling?

> > Thanks again,

> > Fabrcio.



> > > That won't be enough if there is concurrent access, and it sounds like
> > > there is.

> > > The easiest solution is to create a new connection each time a client
> > > makes a call to your object, and close the connection as soon as
> > > possible. Don't worry about the overhead of setting up and tearing
> > > down real database connections as ADO.NET will pool the connections
> > > for you, assuming you are using the same connection string and
> > > security context each time.

> > > If you really want to restrict yourself to a single connection, you'll
> > > need to serialize calls (look up the Monitor class, or the lock
> > > keyword in C#) while the data readers are open. This limits the
> > > throughput of your object however, so I'd suggest the earlier
> > > solution.

> > > On Wed, 28 May 2003 00:49:18 -0700, "Terminator"

> > > >You should close the datareader once done fetching the data before
> using
> > the
> > > >next data reader. An open data reader locks the connection.


> message

> > > >> Hi all,

> > > >> My app has an object published on the server, wich is accessed by
the
> > > >> clients in Singleton mode. This object uses an unique and static
> > > >> OleDbConnection.

> > > >> In some methods, I use DataReaders (one by time), attacheds to this
> > > >> connection. When I'm running the app, I get errors concerning the
> > > >> DataReaders; the compiler shows errors telling me that the
connection
> > > >can't
> > > >> have more than one Datareader attached.

> > > >> I think this error occurs because The OleDb object is a static
> object,
> > and
> > > >> more than one client tries to create datareaders.

> > > >> Is my supposition correct? If no, what can be the problem?
> > > >> If yes, whats the best way to correct? Changing to SingleCall?
Using
> a
> > > >> dynamic OleDbConnection?

> > > >> Thanks in advance,

> > > >> Fabrcio de Novaes Kucinskis.

> > > --
> > > Scott

 
 
 

Static OledbConnection and DataReaders in remote objects

Post by Bill » Sat, 31 May 2003 09:49:34


I expect that if you plan to reuse the Connection object again, calling
dispose is not a good idea. Close is enough. This releases the Connection
back to the pool. When you reopen the connection the (existing) object can
re-address a valid pooled connection.

hth

--
____________________________________
Bill Vaughn
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________



> This is the point. The fact of a connection object be marked as "disposed"
> to the GC will afect the app performance, if after this dispose I make
> another connection, with another object, to the same database? Dispose a
> connection object will afect the way as ADO.net uses data in "cache" to
open
> other connections?

> My doubt is: whats the better choice to close connections in an
application
> wich open and closes connections to a database many times? Just call
Close,
> or Dispose, too?



> > The documentation is incorrect. Dispose does not remove a connection
from
> > the pool. Calling dispose (as Scott said) closes the connection and
marks
> > the object for disposal when the garbage collector runs.

> > --
> > ____________________________________
> > Bill Vaughn
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > __________________________________



> > > Hi Scott,

> > > I have another doubt concerning the data access: if I'll open and
close
> > the
> > > connections each time I need, is it a good idea to call the Dispose
> method
> > > of the connections, or it will compromise this ADO.NET pooling?

> > > Thanks again,

> > > Fabrcio.



> > > > That won't be enough if there is concurrent access, and it sounds
like
> > > > there is.

> > > > The easiest solution is to create a new connection each time a
client
> > > > makes a call to your object, and close the connection as soon as
> > > > possible. Don't worry about the overhead of setting up and tearing
> > > > down real database connections as ADO.NET will pool the connections
> > > > for you, assuming you are using the same connection string and
> > > > security context each time.

> > > > If you really want to restrict yourself to a single connection,
you'll
> > > > need to serialize calls (look up the Monitor class, or the lock
> > > > keyword in C#) while the data readers are open. This limits the
> > > > throughput of your object however, so I'd suggest the earlier
> > > > solution.

> > > > On Wed, 28 May 2003 00:49:18 -0700, "Terminator"

> > > > >You should close the datareader once done fetching the data before
> > using
> > > the
> > > > >next data reader. An open data reader locks the connection.


> > message

> > > > >> Hi all,

> > > > >> My app has an object published on the server, wich is accessed by
> the
> > > > >> clients in Singleton mode. This object uses an unique and static
> > > > >> OleDbConnection.

> > > > >> In some methods, I use DataReaders (one by time), attacheds to
this
> > > > >> connection. When I'm running the app, I get errors concerning the
> > > > >> DataReaders; the compiler shows errors telling me that the
> connection
> > > > >can't
> > > > >> have more than one Datareader attached.

> > > > >> I think this error occurs because The OleDb object is a static
> > object,
> > > and
> > > > >> more than one client tries to create datareaders.

> > > > >> Is my supposition correct? If no, what can be the problem?
> > > > >> If yes, whats the best way to correct? Changing to SingleCall?
> Using
> > a
> > > > >> dynamic OleDbConnection?

> > > > >> Thanks in advance,

> > > > >> Fabrcio de Novaes Kucinskis.

> > > > --
> > > > Scott