Re: Optimising Connections & DataAdapters

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: William Ryan eMVP (dotnetguru_at_comcast.nospam.net)
Date: 05/27/04 

Date: Thu, 27 May 2004 11:32:13 -0400

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
"Jon Maz" <jonmaz@surfeu.de.no.spam> wrote in message
news:e$7$l7%23QEHA.2520@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;
>  }
> }
>
> }
>
>

Relevant Pages

  • Re: Memory Leak in swing application (ImageIcon problem)
    ... private class Person extends Object { ... public void run{ ... java.sql.Connection con = ... I.E you fire your timer, do the random, which opens your connection. ...
    (comp.lang.java.gui)
  • Re: static data access
    ... Never use the static connection. ... public class test ... public static string ConnectionString ... SqlConnection con = new SqlConnection ...
    (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
  • Re: where should I dispose the connection ?
    ... Con variable points to the same connection object you created in ... connection was opened before closing it otherwise you'll get the exception ... Dim da As SqlDataAdapter ... >> Public Class Connection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Row cannot be located for updating
    ... > result a recordset containing one row, ... > Strangly enough, the value that I changed, is changed in the database ... > Dim Con As New ADODB.Connection ... > 'Execute the query and remove the active connection ...
    (microsoft.public.data.ado)
  • Re: Optimising Connections & DataAdapters
    ... The connection object that you declare and instantiate is not the same as ... > a new SqlConnection in *every* DataLayer class? ... > public class ABC ... > SqlCommand cmd = new SqlCommand(sql, con); ...
    (microsoft.public.dotnet.framework.aspnet)