SqlConnection Question

Hi ,

I have a Dll with few functions and when i m opening a connection by using
one of the Dll functions
and i m relating the connection to some SqlDataReader then i can't use the
connection for other purpose
until i will close the DataReader .

why is that ?

why i can use the connection for other things but for SqlDataReader more
then once ?

how can i work with the same connection ?

should i work with one connection or should i open a new one ?

please notice that i need to use the connection while i m in the middle of
some loop in the datareader (While DataReader.Read() ...)

Best Regards ,

Tiraman :-)

Cor
Sat Apr 24 10:44:16 CDT 2004

Hi Tiraman,

The answer is very simple in Net1x you can only use one connection for one
thing.

And than the answer is simple, you should open a new one

I hope this helps?

Cor

William
Sat Apr 24 11:04:00 CDT 2004


"Tiraman" <tiraman@netvision.net.il> wrote in message
news:uvH2i9gKEHA.1144@TK2MSFTNGP12.phx.gbl...
> Hi ,
>
> I have a Dll with few functions and when i m opening a connection by using
> one of the Dll functions
> and i m relating the connection to some SqlDataReader then i can't use the
> connection for other purpose
> until i will close the DataReader .
>
> why is that ?
A datareader requires and open connection to be useful, it's a 'connected'
object and useless without a connection

>
> why i can use the connection for other things but for SqlDataReader more
> then once ?
You can reuse the connection but whether or not you want to is a different
story, it depends on the scenario. You'll probably want to set the
CommandBehavior property of the reader to Closeconnection so it
automatically closes the connection when it's done reading. If you do this,
other objects will be able to use the connection
http://www.knowdotnet.com/articles/schemas2.html
>
> how can i work with the same connection ?
Close the DataReader once you are done with it. Remember there's a
disctinction between closing the reader and closing the connection. Once
you close the reader, other objects can use the connection
>
> should i work with one connection or should i open a new one ?
Like I mentioned above, it depends on the scenario. Unless you have
connection pooling turned off, then the main thing is to use the same
connection string(often people think if you add a space in the connection
string, this will affect pooling. No so. Just make sure all of the arguement
of the connection string are exactly the same and you'll be safe). You can
leave a connection open if you have a few back to back sql queries and
nothign in between them - this might bolster performance. But remember to
ALWAYS close the connection ASAP when you are done with it, this way it gets
returned to the pool and can be reused. Done with it means literally , when
that action is done using it so it mayspan 5 queries or it may span one,
usually it's just one.
>
> please notice that i need to use the connection while i m in the middle of
> some loop in the datareader (While DataReader.Read() ...)
I'd grab another connection. I don't see the code, but you don't want to
share a live connection between multiple routines that are executing
simultaneously. If it's open for the dataReader.Read loop, don't use it for
anything else (even if it works you are asking for trouble). You can just
declare another one if you are sending it for some other query. Also, to be
sure, I'd let the method that I can calling create ,open and close it's own
connection. But as long as you use a second one for additional processing
while you are using the first one in the loop, you'll be good to go. Trust
me, deadlocks suck and you'll be begging for them if you try passing around
a connection that's open and in the middle of being used.


>
> Best Regards ,
>
> Tiraman :-)
>
If you posted the code it might help in formulating a specific
recommendation, but the guidelines above are pretty much the rules of usage.
Sharing an open connection between multiple processes that are using it
simultaneously is not a good idea. Leaving connections open after you no
longer need them is a bad idea. Close your connnections as soon as you can.
Close your readers as soon as you can (remember, if you have a reader open,
nothing will be able to use it until you close the Reader). Reuse
connection strings . Turn pooling on (it's on by default, so it'd be better
to say don't turn it off without a compelling reason).

This will keep you out of a good bit of trouble.

HTH,

Bill

Chris
Sat Apr 24 11:06:07 CDT 2004

Cor, not really, the connection can be used for selects, then updates, etc.
If Tiraman filled a DataTable, then in his loop get a DataReader of fill
another DataTable, using the same connection, it will work. In the case of
the DataReader it is different, it won't allow you to open a second one on
the same connection until the first is closed.

"Cor Ligthert" <notfirstname@planet.nl> wrote in message
news:e5WqDMhKEHA.3016@tk2msftngp13.phx.gbl...
> Hi Tiraman,
>
> The answer is very simple in Net1x you can only use one connection for one
> thing.
>
> And than the answer is simple, you should open a new one
>
> I hope this helps?
>
> Cor
>
>

Cor
Sat Apr 24 11:32:19 CDT 2004

Hi Crhis,

> Cor, not really, the connection can be used for selects, then updates,
etc.
> If Tiraman filled a DataTable, then in his loop get a DataReader of fill
> another DataTable, using the same connection, it will work. In the case of
> the DataReader it is different, it won't allow you to open a second one on
> the same connection until the first is closed.

That is what you call a thing, however as far as I know can you not do two
fills at the same time (in one thread) and therefore it is working
sequential.

But this
conn.open
da.fill(x, "a")
da.fill(x, "y")
conn.close
is of course the best solution with a dataset with multiple tables.

However for a datareader is a parallel proces possible and than you cannot
do that with one connection. And I thought that was what Tiraman was talking
about.

However when he wants to process his datareader sequential (one by one),
than it is of course something different.

Cor

William
Sat Apr 24 13:04:57 CDT 2004

Chris:

Thanks for posting, you bring up many excellent points. I think Cor didn't
mean that you could only use one connection for one thing in total, rather
one thing at a time.. but the way it read it might have been a little
confusing.

I think the whole are can get muddled b/c a connection must be opened (and
closed) just as a DataReader can be opened and closed. However, readers are
connected objects, whereas datatables aren't (I know, I'm stating the
painfully obvious). However, you can use one connection for x number of
commands with disconnected methodology without ever worrying about opening
or closing the connection, after all the adapter does it for you. You can
of course overide this, but most people don't. However, you must manually
open (and close) your connections with any of the command.Executexxx
methods. So, you can open a Connection, open a DataReader, walk through the
reader, close the reader leaving the connection open all along and fire
another command on it immediately afterward. But if you don't close the
reader it won't work. Since you close readers but don't close DataTables,
many get confused with what needs to happen when.

You are correct in that you can reuse the connection, however you can't use
it simultaneously by different commands, at least you can't do it safely.
So if you used a given connection to fill a datatable, then the dataadapter
would open the connection and close it, (unless you chose to do it manually
in which case you could leave it open either accidentally or intentionally),
at that point the connection is considered "Open and Available" So a
datareader can use it as you say. However, lets say that the same scenario
involved two datareaders. You open the first datareader and call
cmd.ExecuteReader then in the while dr.Reader() loop you could reset the
commandtext of the first command and try firing another query. You can 'try'
but you aren't going to get much success.

I think your point was that you can defintiely use a connection for multiple
'things' it just depends on what those things are and when they happen.
There are many times for instance that you need to fire x queries is
succession or one after another with some very tiny lapse between them. If
you did this in a loop 1000 times for instance, the overhead of opening and
closing the connection would eat you up. So leaving it open may make sense.
However, at some point you are logically 'done' with the connection and at
that point it should be closed.

Another issue is threads and async execution and sharing a connection there
without synclocking it. But we've probably caused enough confusion with the
basic stuff, throwing in the multithreaded aspects could really cause
problems.


"Chris Botha" <chris_s_botha@AT_h.o.t.m.a.i.l.com> wrote in message
news:#ojpVXhKEHA.2144@TK2MSFTNGP10.phx.gbl...
> Cor, not really, the connection can be used for selects, then updates,
etc.
> If Tiraman filled a DataTable, then in his loop get a DataReader of fill
> another DataTable, using the same connection, it will work. In the case of
> the DataReader it is different, it won't allow you to open a second one on
> the same connection until the first is closed.
>
> "Cor Ligthert" <notfirstname@planet.nl> wrote in message
> news:e5WqDMhKEHA.3016@tk2msftngp13.phx.gbl...
> > Hi Tiraman,
> >
> > The answer is very simple in Net1x you can only use one connection for
one
> > thing.
> >
> > And than the answer is simple, you should open a new one
> >
> > I hope this helps?
> >
> > Cor
> >
> >
>
>

Tiraman
Sat Apr 24 15:10:02 CDT 2004

Hi ,

I Think That Chris is right ,
when i m first using the DataReader then i can't do any thing with the
connection but if i will first use the connection for other things and then
i will use it for the DataReader it will work :-)

10x

"Chris Botha" <chris_s_botha@AT_h.o.t.m.a.i.l.com> wrote in message
news:#ojpVXhKEHA.2144@TK2MSFTNGP10.phx.gbl...
> Cor, not really, the connection can be used for selects, then updates,
etc.
> If Tiraman filled a DataTable, then in his loop get a DataReader of fill
> another DataTable, using the same connection, it will work. In the case of
> the DataReader it is different, it won't allow you to open a second one on
> the same connection until the first is closed.
>
> "Cor Ligthert" <notfirstname@planet.nl> wrote in message
> news:e5WqDMhKEHA.3016@tk2msftngp13.phx.gbl...
> > Hi Tiraman,
> >
> > The answer is very simple in Net1x you can only use one connection for
one
> > thing.
> >
> > And than the answer is simple, you should open a new one
> >
> > I hope this helps?
> >
> > Cor
> >
> >
>
>

Tiraman
Sat Apr 24 15:23:42 CDT 2004

Hi William ,

first 10x for you and the other good guys for the gr8 response :-)

i got the point of using the connection and in my case i opened a new
connection .

let me ask you one more thing ,

when i m calling a function (under some dll) from aspx file which open
connection
and i m closing the connection in the "Finalize" of the DLL when it will be
close ?

Should i close the connection in the aspx file ?

10x



"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:#MwflUhKEHA.808@tk2msftngp13.phx.gbl...
>
> "Tiraman" <tiraman@netvision.net.il> wrote in message
> news:uvH2i9gKEHA.1144@TK2MSFTNGP12.phx.gbl...
> > Hi ,
> >
> > I have a Dll with few functions and when i m opening a connection by
using
> > one of the Dll functions
> > and i m relating the connection to some SqlDataReader then i can't use
the
> > connection for other purpose
> > until i will close the DataReader .
> >
> > why is that ?
> A datareader requires and open connection to be useful, it's a 'connected'
> object and useless without a connection
>
> >
> > why i can use the connection for other things but for SqlDataReader more
> > then once ?
> You can reuse the connection but whether or not you want to is a
different
> story, it depends on the scenario. You'll probably want to set the
> CommandBehavior property of the reader to Closeconnection so it
> automatically closes the connection when it's done reading. If you do
this,
> other objects will be able to use the connection
> http://www.knowdotnet.com/articles/schemas2.html
> >
> > how can i work with the same connection ?
> Close the DataReader once you are done with it. Remember there's a
> disctinction between closing the reader and closing the connection. Once
> you close the reader, other objects can use the connection
> >
> > should i work with one connection or should i open a new one ?
> Like I mentioned above, it depends on the scenario. Unless you have
> connection pooling turned off, then the main thing is to use the same
> connection string(often people think if you add a space in the connection
> string, this will affect pooling. No so. Just make sure all of the
arguement
> of the connection string are exactly the same and you'll be safe). You
can
> leave a connection open if you have a few back to back sql queries and
> nothign in between them - this might bolster performance. But remember to
> ALWAYS close the connection ASAP when you are done with it, this way it
gets
> returned to the pool and can be reused. Done with it means literally ,
when
> that action is done using it so it mayspan 5 queries or it may span one,
> usually it's just one.
> >
> > please notice that i need to use the connection while i m in the middle
of
> > some loop in the datareader (While DataReader.Read() ...)
> I'd grab another connection. I don't see the code, but you don't want to
> share a live connection between multiple routines that are executing
> simultaneously. If it's open for the dataReader.Read loop, don't use it
for
> anything else (even if it works you are asking for trouble). You can just
> declare another one if you are sending it for some other query. Also, to
be
> sure, I'd let the method that I can calling create ,open and close it's
own
> connection. But as long as you use a second one for additional processing
> while you are using the first one in the loop, you'll be good to go.
Trust
> me, deadlocks suck and you'll be begging for them if you try passing
around
> a connection that's open and in the middle of being used.
>
>
> >
> > Best Regards ,
> >
> > Tiraman :-)
> >
> If you posted the code it might help in formulating a specific
> recommendation, but the guidelines above are pretty much the rules of
usage.
> Sharing an open connection between multiple processes that are using it
> simultaneously is not a good idea. Leaving connections open after you no
> longer need them is a bad idea. Close your connnections as soon as you
can.
> Close your readers as soon as you can (remember, if you have a reader
open,
> nothing will be able to use it until you close the Reader). Reuse
> connection strings . Turn pooling on (it's on by default, so it'd be
better
> to say don't turn it off without a compelling reason).
>
> This will keep you out of a good bit of trouble.
>
> HTH,
>
> Bill
>
>

William
Sat Apr 24 15:12:35 CDT 2004

Close it as soon as you are done with it. Finalize should get it, but
that's going to happen way after you are done with it in most instances.
YOu may also decide to move the object around in the calling code which may
expand its lifetime.. Or something may happen where you don't want to
dispose of it when you used to. So doing this either limits flexibility or
exposes you to the risk of the connection being closed later than it should
be. If you are using an adapter, just let it open and close it for you. If
you are using a datareader, then close it Immediately after the last
dr.Read() (many times you'll only have one, but if you have batch queries
then you might have a few .NextResult calls and more than one read...so
whenever you fire the last read and it finishes, close it. If you use any
other of the command.Executexxx then close it in the next line after the
call to .Executexxx. These are general guidelines that you should adhere to
but aren't written in stone. If you find that the benefit of closing a
connection immediately in some instance is small compared to the cost, don't
be afraid to adjust, just make sure you have a good reason for the change.

Good Luck,

Bill
"Tiraman" <tiraman@netvision.net.il> wrote in message
news:##RkrHjKEHA.1312@TK2MSFTNGP12.phx.gbl...
> Hi William ,
>
> first 10x for you and the other good guys for the gr8 response :-)
>
> i got the point of using the connection and in my case i opened a new
> connection .
>
> let me ask you one more thing ,
>
> when i m calling a function (under some dll) from aspx file which open
> connection
> and i m closing the connection in the "Finalize" of the DLL when it will
be
> close ?
>
> Should i close the connection in the aspx file ?
>
> 10x
>
>
>
> "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> news:#MwflUhKEHA.808@tk2msftngp13.phx.gbl...
> >
> > "Tiraman" <tiraman@netvision.net.il> wrote in message
> > news:uvH2i9gKEHA.1144@TK2MSFTNGP12.phx.gbl...
> > > Hi ,
> > >
> > > I have a Dll with few functions and when i m opening a connection by
> using
> > > one of the Dll functions
> > > and i m relating the connection to some SqlDataReader then i can't use
> the
> > > connection for other purpose
> > > until i will close the DataReader .
> > >
> > > why is that ?
> > A datareader requires and open connection to be useful, it's a
'connected'
> > object and useless without a connection
> >
> > >
> > > why i can use the connection for other things but for SqlDataReader
more
> > > then once ?
> > You can reuse the connection but whether or not you want to is a
> different
> > story, it depends on the scenario. You'll probably want to set the
> > CommandBehavior property of the reader to Closeconnection so it
> > automatically closes the connection when it's done reading. If you do
> this,
> > other objects will be able to use the connection
> > http://www.knowdotnet.com/articles/schemas2.html
> > >
> > > how can i work with the same connection ?
> > Close the DataReader once you are done with it. Remember there's a
> > disctinction between closing the reader and closing the connection.
Once
> > you close the reader, other objects can use the connection
> > >
> > > should i work with one connection or should i open a new one ?
> > Like I mentioned above, it depends on the scenario. Unless you have
> > connection pooling turned off, then the main thing is to use the same
> > connection string(often people think if you add a space in the
connection
> > string, this will affect pooling. No so. Just make sure all of the
> arguement
> > of the connection string are exactly the same and you'll be safe). You
> can
> > leave a connection open if you have a few back to back sql queries and
> > nothign in between them - this might bolster performance. But remember
to
> > ALWAYS close the connection ASAP when you are done with it, this way it
> gets
> > returned to the pool and can be reused. Done with it means literally ,
> when
> > that action is done using it so it mayspan 5 queries or it may span one,
> > usually it's just one.
> > >
> > > please notice that i need to use the connection while i m in the
middle
> of
> > > some loop in the datareader (While DataReader.Read() ...)
> > I'd grab another connection. I don't see the code, but you don't want
to
> > share a live connection between multiple routines that are executing
> > simultaneously. If it's open for the dataReader.Read loop, don't use it
> for
> > anything else (even if it works you are asking for trouble). You can
just
> > declare another one if you are sending it for some other query. Also,
to
> be
> > sure, I'd let the method that I can calling create ,open and close it's
> own
> > connection. But as long as you use a second one for additional
processing
> > while you are using the first one in the loop, you'll be good to go.
> Trust
> > me, deadlocks suck and you'll be begging for them if you try passing
> around
> > a connection that's open and in the middle of being used.
> >
> >
> > >
> > > Best Regards ,
> > >
> > > Tiraman :-)
> > >
> > If you posted the code it might help in formulating a specific
> > recommendation, but the guidelines above are pretty much the rules of
> usage.
> > Sharing an open connection between multiple processes that are using it
> > simultaneously is not a good idea. Leaving connections open after you
no
> > longer need them is a bad idea. Close your connnections as soon as you
> can.
> > Close your readers as soon as you can (remember, if you have a reader
> open,
> > nothing will be able to use it until you close the Reader). Reuse
> > connection strings . Turn pooling on (it's on by default, so it'd be
> better
> > to say don't turn it off without a compelling reason).
> >
> > This will keep you out of a good bit of trouble.
> >
> > HTH,
> >
> > Bill
> >
> >
>
>

Tiraman
Sun Apr 25 14:11:27 CDT 2004

Gr8 ,

Thanks And Have A Good Day :-)

Bye

"William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
news:enNbffjKEHA.2260@TK2MSFTNGP09.phx.gbl...
> Close it as soon as you are done with it. Finalize should get it, but
> that's going to happen way after you are done with it in most instances.
> YOu may also decide to move the object around in the calling code which
may
> expand its lifetime.. Or something may happen where you don't want to
> dispose of it when you used to. So doing this either limits flexibility
or
> exposes you to the risk of the connection being closed later than it
should
> be. If you are using an adapter, just let it open and close it for you.
If
> you are using a datareader, then close it Immediately after the last
> dr.Read() (many times you'll only have one, but if you have batch queries
> then you might have a few .NextResult calls and more than one read...so
> whenever you fire the last read and it finishes, close it. If you use any
> other of the command.Executexxx then close it in the next line after the
> call to .Executexxx. These are general guidelines that you should adhere
to
> but aren't written in stone. If you find that the benefit of closing a
> connection immediately in some instance is small compared to the cost,
don't
> be afraid to adjust, just make sure you have a good reason for the change.
>
> Good Luck,
>
> Bill
> "Tiraman" <tiraman@netvision.net.il> wrote in message
> news:##RkrHjKEHA.1312@TK2MSFTNGP12.phx.gbl...
> > Hi William ,
> >
> > first 10x for you and the other good guys for the gr8 response :-)
> >
> > i got the point of using the connection and in my case i opened a new
> > connection .
> >
> > let me ask you one more thing ,
> >
> > when i m calling a function (under some dll) from aspx file which open
> > connection
> > and i m closing the connection in the "Finalize" of the DLL when it
will
> be
> > close ?
> >
> > Should i close the connection in the aspx file ?
> >
> > 10x
> >
> >
> >
> > "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> > news:#MwflUhKEHA.808@tk2msftngp13.phx.gbl...
> > >
> > > "Tiraman" <tiraman@netvision.net.il> wrote in message
> > > news:uvH2i9gKEHA.1144@TK2MSFTNGP12.phx.gbl...
> > > > Hi ,
> > > >
> > > > I have a Dll with few functions and when i m opening a connection by
> > using
> > > > one of the Dll functions
> > > > and i m relating the connection to some SqlDataReader then i can't
use
> > the
> > > > connection for other purpose
> > > > until i will close the DataReader .
> > > >
> > > > why is that ?
> > > A datareader requires and open connection to be useful, it's a
> 'connected'
> > > object and useless without a connection
> > >
> > > >
> > > > why i can use the connection for other things but for SqlDataReader
> more
> > > > then once ?
> > > You can reuse the connection but whether or not you want to is a
> > different
> > > story, it depends on the scenario. You'll probably want to set the
> > > CommandBehavior property of the reader to Closeconnection so it
> > > automatically closes the connection when it's done reading. If you do
> > this,
> > > other objects will be able to use the connection
> > > http://www.knowdotnet.com/articles/schemas2.html
> > > >
> > > > how can i work with the same connection ?
> > > Close the DataReader once you are done with it. Remember there's a
> > > disctinction between closing the reader and closing the connection.
> Once
> > > you close the reader, other objects can use the connection
> > > >
> > > > should i work with one connection or should i open a new one ?
> > > Like I mentioned above, it depends on the scenario. Unless you have
> > > connection pooling turned off, then the main thing is to use the same
> > > connection string(often people think if you add a space in the
> connection
> > > string, this will affect pooling. No so. Just make sure all of the
> > arguement
> > > of the connection string are exactly the same and you'll be safe).
You
> > can
> > > leave a connection open if you have a few back to back sql queries and
> > > nothign in between them - this might bolster performance. But
remember
> to
> > > ALWAYS close the connection ASAP when you are done with it, this way
it
> > gets
> > > returned to the pool and can be reused. Done with it means literally
,
> > when
> > > that action is done using it so it mayspan 5 queries or it may span
one,
> > > usually it's just one.
> > > >
> > > > please notice that i need to use the connection while i m in the
> middle
> > of
> > > > some loop in the datareader (While DataReader.Read() ...)
> > > I'd grab another connection. I don't see the code, but you don't want
> to
> > > share a live connection between multiple routines that are executing
> > > simultaneously. If it's open for the dataReader.Read loop, don't use
it
> > for
> > > anything else (even if it works you are asking for trouble). You can
> just
> > > declare another one if you are sending it for some other query. Also,
> to
> > be
> > > sure, I'd let the method that I can calling create ,open and close
it's
> > own
> > > connection. But as long as you use a second one for additional
> processing
> > > while you are using the first one in the loop, you'll be good to go.
> > Trust
> > > me, deadlocks suck and you'll be begging for them if you try passing
> > around
> > > a connection that's open and in the middle of being used.
> > >
> > >
> > > >
> > > > Best Regards ,
> > > >
> > > > Tiraman :-)
> > > >
> > > If you posted the code it might help in formulating a specific
> > > recommendation, but the guidelines above are pretty much the rules of
> > usage.
> > > Sharing an open connection between multiple processes that are using
it
> > > simultaneously is not a good idea. Leaving connections open after you
> no
> > > longer need them is a bad idea. Close your connnections as soon as
you
> > can.
> > > Close your readers as soon as you can (remember, if you have a reader
> > open,
> > > nothing will be able to use it until you close the Reader). Reuse
> > > connection strings . Turn pooling on (it's on by default, so it'd be
> > better
> > > to say don't turn it off without a compelling reason).
> > >
> > > This will keep you out of a good bit of trouble.
> > >
> > > HTH,
> > >
> > > Bill
> > >
> > >
> >
> >
>
>