Optimising Connections & DataAdapters

[Jon Maz]

Hi,

I just read the following in an old NG thread:

"when you use the adapter with a closed connection it will open it, do your
requested database access, and close it immediately. If you use the adapter
with an open connection the connection will continue to be open after the
adapter is done."

My DataLayer makes extensive use of DataAdapters to fill DataSets, and
currently each DataLayer function creates its own, new, SqlConnection, and I
do not explicitly open/close the SqlConnection, as the DataAdapter takes
care of that. As a result of reading the above quote, I think I can improve
performance by better use of SqlConnections.

Specifically, I'm now thinking about manually opening an SqlConnection at
the beginning of a DataLayer function before filling the DataSet, so that I
have the option of passing the still-open connection to another function
where it could be used to fill another DataSet, before passing this
still-open connection back to the original function to be manually closed.

Would this be (much?) more efficient than each separate function creating a
new SqlConnection?
Would such an approach create any new problems?

Thanks,

JON

PS Also: can anyone compare the performance of re-opening an
existing-but-closed SqlConnection with the cost of creating and opening a
brand new one?

 

[William Ryan eMVP]

Jon:

That quote is referring to the actual mechanics of Connection pooling behind
the scenes. The DB will actually hold a connection open even though it's
'closed' in your code so that when you call Open again, it can reuse the
connection if possible.

Anyway, calling Open and Close in your code isn't going to do anything
different then letting the Adapter do it for you, at least in this regard so
there's no need to worry about that.

The only case where you might want to leave it 'open' is if you had a loop
for instance that was firing a ton of queries against the db back to back.
Instead of closing the connection only to immediately open it again, it may
make sense to open the connection at the beginning of the loop and close it
at the end (or in a finally block). Instances where this is a good idea
aren't that common though so as a rule of thumb,letting the adapter open and
close it for you is a good idea, and closing the connection as soon as you
are done with it, is always a good idea.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[Jon Maz]

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;
}
}

}

 

[William Ryan eMVP]

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_personal_media_center_pg1.asp

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;
}
}

}

 

[Jon Maz]

That's helped a lot, much appreciated!

JON