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