Static vs. non-static connection

Discussion in 'ASP .Net' started by Natan, May 24, 2004.

  1. Natan

    Natan Guest

    Hi,

    I`m creating a asp.net intranet system, and would like to put all the
    logic of the system in a single dll. That is, when i need to create an
    user for example, i would use "User.Check()" and "User.Add()" instead of
    putting "insert" sql statements in the page.

    Considering he following logic:

    User u = new User();

    u.Name = "John Doe";
    u.Email = "";
    u.Login = "john";
    u.Pass = "blah";

    if (!User.Exists(u) and User.IsValid(u))
    User.Add(u);


    User.Exists checks if there is a user with that login, User.IsValid
    checks to see if the name and other data is valid.. and User.Add adds
    the user to the database. All the three methods access the database and
    i'm creating a method that will return this database connection.

    My question is: What is the best practice? Should i create a method to
    return a new SqlConnection object, open and close that conection in each
    method i use it, or create a property that returns a static opened
    connection? Even with connection pooling, i think there is a better way
    to do things than opening and closing a connection 5 or 6 times in a
    single page.

    Other idea was open a single static connection once, let`s say in
    global.asax and using it for every everywhere. Is there any problem with
    this?

    Thanks.
    Natan, May 24, 2004
    #1
    1. Advertising

  2. Natan

    Patrice Guest

    A static connection is shared by all users.

    You could create the connection once for the lifetime of the request (and
    check first if opening/closing the connection on a per method basis is a
    real performance hit).

    Patrice


    "Natan" <> a écrit dans le message de
    news:%23%...
    > Hi,
    >
    > I`m creating a asp.net intranet system, and would like to put all the
    > logic of the system in a single dll. That is, when i need to create an
    > user for example, i would use "User.Check()" and "User.Add()" instead of
    > putting "insert" sql statements in the page.
    >
    > Considering he following logic:
    >
    > User u = new User();
    >
    > u.Name = "John Doe";
    > u.Email = "";
    > u.Login = "john";
    > u.Pass = "blah";
    >
    > if (!User.Exists(u) and User.IsValid(u))
    > User.Add(u);
    >
    >
    > User.Exists checks if there is a user with that login, User.IsValid
    > checks to see if the name and other data is valid.. and User.Add adds
    > the user to the database. All the three methods access the database and
    > i'm creating a method that will return this database connection.
    >
    > My question is: What is the best practice? Should i create a method to
    > return a new SqlConnection object, open and close that conection in each
    > method i use it, or create a property that returns a static opened
    > connection? Even with connection pooling, i think there is a better way
    > to do things than opening and closing a connection 5 or 6 times in a
    > single page.
    >
    > Other idea was open a single static connection once, let`s say in
    > global.asax and using it for every everywhere. Is there any problem with
    > this?
    >
    > Thanks.
    Patrice, May 24, 2004
    #2
    1. Advertising

  3. Natan

    Natan Guest

    Patrice wrote:
    > A static connection is shared by all users.
    >
    > You could create the connection once for the lifetime of the request (and
    > check first if opening/closing the connection on a per method basis is a
    > real performance hit).


    I thought about this, but how? I would need to pass the connection to
    every method i use...

    does anyone know a better way?
    Natan, May 24, 2004
    #3
  4. Natan

    Marina Guest

    Try adding the connection to the Context collection, this should be
    available in every method. Make sure you close it after everyone is done
    with it.

    "Natan" <> wrote in message
    news:%...
    > Patrice wrote:
    > > A static connection is shared by all users.
    > >
    > > You could create the connection once for the lifetime of the request

    (and
    > > check first if opening/closing the connection on a per method basis is a
    > > real performance hit).

    >
    > I thought about this, but how? I would need to pass the connection to
    > every method i use...
    >
    > does anyone know a better way?
    Marina, May 24, 2004
    #4
  5. Natan

    Natan Guest

    Thanks! I didn`t know about this collection! Thanks a lot!

    Marina wrote:
    > Try adding the connection to the Context collection, this should be
    > available in every method. Make sure you close it after everyone is done
    > with it.
    Natan, May 24, 2004
    #5
  6. Best practice is NOT to instantiate a single connection and keep it open for multiple users or multiple queries, etc. The way connection pooling works is that the allocation of actual connections is managed for you. If many sessions need DB access, multiple connections are allocated and shared, as needed. As long as the connection strings are IDENTICAL, you will get optimized allocation for multiple users. (So, use a single user account for all database access, unless different users - or different application functions - require different permissions within the DB itself. If that's the case, the multple connection pools will be allocated for the different connection strings.

    Keeping a connection open for longer than it absolutely has to be could, in effect, tie up an actual physical connection longer than necessary - making it unavaliable for the next command, or another command in another session. So, the only thing you have to persist (in an application variable - or wherever) is the connection string, not the connection

    Open as late as possible, close as soon as possible. It's that easy - don't try to defeat the purpose of connection pooling


    It's that easy - don't try to get
    =?Utf-8?B?UmljaA==?=, May 24, 2004
    #6
  7. Natan

    spamfurnace Guest

    How many users are you signing on at once for that to be a performance
    consideration?
    spamfurnace, May 25, 2004
    #7
  8. Natan

    Natan Guest

    spamfurnace wrote:

    > How many users are you signing on at once for that to be a performance
    > consideration?


    There is no heavy traffic, I just wondered what was the best way to do
    it. I think using Context collection to store the connection is the best
    way. This way I open one connection per request, use it everywhere and
    then close it, like in old asp.
    Natan, May 25, 2004
    #8
  9. "Natan" <> wrote in message
    news:e%...
    > spamfurnace wrote:
    >
    > > How many users are you signing on at once for that to be a performance
    > > consideration?

    >
    > There is no heavy traffic, I just wondered what was the best way to do
    > it. I think using Context collection to store the connection is the best
    > way. This way I open one connection per request, use it everywhere and
    > then close it, like in old asp.


    There's one caveat though - if you do anything during the request processing
    that may block for a while (e.g., call a web service or read a file) then
    the database connection will be in use unnecessarily. This will reduce the
    number of concurrent requests you can serve. In this case, opening and
    closing the database connection whenever you need it is better than storing
    it (and keeping it open) in the HttpContext.

    Sami
    Sami Vaaraniemi, May 26, 2004
    #9
    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. =?ISO-8859-1?Q?Thomas_Gagn=E9?=

    Why do static and non-static method names collide?

    =?ISO-8859-1?Q?Thomas_Gagn=E9?=, Jul 2, 2003, in forum: Java
    Replies:
    12
    Views:
    5,590
    cgbusch
    Jul 5, 2003
  2. Gary
    Replies:
    1
    Views:
    3,957
    Ryan Stewart
    Jan 16, 2004
  3. Alan Woodland
    Replies:
    12
    Views:
    530
    Marco Manfredini
    Nov 22, 2007
  4. Dobedani
    Replies:
    0
    Views:
    369
    Dobedani
    Jul 15, 2008
  5. rickman
    Replies:
    5
    Views:
    414
    rickman
    Mar 30, 2013
Loading...

Share This Page