The connection object that you declare and instantiate is not the same as
the physical connection to the db, it's an abstraction of it that you can
manipulate. Once you use a connection to connect to the db, you can call
close on it, but it will stay open if you have pooling on from the DB's
perspective. That way if you call it again, it already has it avialable.
So the real issue is closing your client connections as soon as you are done
with them so they go back in the pool. If you open one connection and close
it , there's still one open from the DB's perspective. Say you open another
one, it will use it right. Now let's say you leave it open but then create
and open another one, it'll have to manage another coneection. But if you
closed the first one, it could reuse it.
So where you declare and instantiate them may have other consequences, but
Opening and closing them are probably the biggest and as long as you close
stuff as soon as you are done with it,it's doubtful you are going to run
into a problem. Also, there are a lot of other issues here and having a
connection local to your routine may make a lot more sense in terms of your
design and maintenance
Of the two , I prefer the first implementation for many reasons. Depending
on how you called this, passing in the connection may complicate things if
you made this multithreaded for one thing. You can easily work around this
but it still complicates things.
So in your two methods, the effect on the db is pretty much the same since
you are closing both of them as soon as you are done.
I think you're on the right track either way although my personal preference
is the first.
Good Luck,
Bill
--
W.G. Ryan, eMVP
http://forums.devbuzz.com/ http://www.knowdotnet.com/williamryan.html http://www.msmvps.com/WilliamRyan/ http://www.devbuzz.com/content/zinc_...center_pg1.asp
"Jon Maz" <jo****@surfeu.de.no.spam> wrote in message
news:e$****************@TK2MSFTNGP11.phx.gbl...
Hi Bill,
Thanks for the reply! Just to check how this affects the code changes I'm
planning, do you think the code below:
(a) will work without explicitly opening and closing the passed
SqlConnection in DoFirstThing()
and
(b) will be more efficient than what I've been doing up to now, ie
creating a new SqlConnection in *every* DataLayer class?
Thanks,
JON
-----------------------------------------------------------
public class ABC
{
public static DataSet DoFirstThing(long articleID)
{
SqlConnection con = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
DataSet ds = new DataSet();
string sql = "SELECT ............";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DataTable extraInfo = XYZ.DoSecondThing(articleID, ref con);
ds.Tables.Add(extraInfo);
try
{
return ds;
}
catch
{
return null;
}
}
}
-----------------------------------------------------------
public class XYZ
{
public static DataTable DoSecondThing(long articleID, ref SqlConnection
con) {
//NO LONGER NEED TO CREATE NEW SQLCONNECTION HERE
DataSet ds = new DataSet();
DataTable dt = new DataTable();
string sql = "SELECT ............";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
try
{
dt = ds.Tables[0];
return dt;
}
catch
{
return null;
}
}
}