SqlConnection Question

  • Thread starter Thread starter Cor Ligthert
  • Start date Start date
C

Cor Ligthert

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 said:
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
 
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.
 
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 :-)
 
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
 
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.
 
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
 
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
 
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
 
Gr8 ,

Thanks And Have A Good Day :-)

Bye

William Ryan eMVP said:
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
will remember
 
Back
Top