Optimising Connections & DataAdapters

Discussion in 'ASP .Net' started by Jon Maz, May 27, 2004.

  1. Jon Maz

    Jon Maz Guest

    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?
    Jon Maz, May 27, 2004
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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, May 27, 2004
    #2
    1. Advertising

  3. Jon Maz

    Jon Maz Guest

    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, May 27, 2004
    #3
  4. 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" <> wrote in message
    news:e$7$l7%...
    > 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, May 27, 2004
    #4
  5. Jon Maz

    Jon Maz Guest

    That's helped a lot, much appreciated!

    JON
    Jon Maz, May 27, 2004
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Vik

    Loop through all dataadapters

    Vik, Jul 16, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    336
  2. =?Utf-8?B?Sko=?=

    Sql DataAdapters

    =?Utf-8?B?Sko=?=, Jul 5, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    494
    =?Utf-8?B?Sko=?=
    Jul 5, 2005
  3. Tina
    Replies:
    0
    Views:
    345
  4. Replies:
    0
    Views:
    415
  5. Stewart
    Replies:
    2
    Views:
    302
    Mark Rae [MVP]
    Jul 20, 2007
Loading...

Share This Page