Open/Close Database Connection for each page

Discussion in 'ASP .Net' started by Michael, Dec 30, 2003.

  1. Michael

    Michael Guest

    Hello,

    In my past ASP pages, at the top I used an include file to open an ado
    connection for the entire page, then at the bottom, I would have another
    include file to close the connection.

    Now that I'm moving to .NET, and using ado.net as well, does anyone have any
    ideas to accomplish this for asp.net. I'd like for it to be as much behind
    the scenes as possible, hopefully so I'll never have to look at statements
    like myConnection.open and myConnection.close in my main code.

    Thanks,
    --Michael
    Michael, Dec 30, 2003
    #1
    1. Advertising

  2. Michael

    Hermit Dave Guest

    Michael,

    ASP was nice but it was never object oriented... plus since it was
    interpreted... it could be tuned but only to a certain extent.
    With ASP.NET... its fully object oriented and you go about fetching the data
    as you do in a proper n-tier application. (Though nothing stops you from
    doing otherwise)
    This is my recommended approach:
    Say you have customer related pages where you do whole lot of different
    things but still pertaining to the User.
    well you create a UserDB class... And create methods which will either
    return or take say an instance of a UserDetail Class.

    so all you have to do in your aspx code behind file is create instance of
    UserDB class and call corresponding methods. It is much more object oriented
    and much easier to debug...

    Unfortunately in asp.net you dont have the #include which used to be so
    frequently used... so you have to open the connection and close it in the
    same file...

    class UserDetail
    {
    public int UserID;
    public string Name;
    public string Email;
    }

    class UserDB
    {
    public UserDetail GetUserDetail(UserID)
    {
    SqlConnection myCon = SqlConnection("connection string here")
    try
    {
    SqlCommand myCommand = new SqlCommand("sp_Users_Select", myCon);
    myCommand.CommandType = CommandType.StoredProc;

    SqlParameter parameterUserID = SqlParameter("@UserID",
    SqlDbType.Int, 4);
    parameterUserID.value = UserID;
    myCommand.Parameters.Add(parameterUserID);

    SqlParameter parameterUserName = SqlParameter("@UserName",
    SqlDbType.NVarChar, 50);
    parameterUserName.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterUserName);

    SqlParameter parameterUserID = SqlParameter("@UserEmail",
    SqlDbType.NVarChar, 50);
    parameterUserEmail.Direction = ParameterDirection.Output;
    myCommand.Parameters.Add(parameterUserEmail);

    myCon.open();
    myCommand.ExecuteNonQuery();
    UserDetail myUser = new UserDetail();
    myUser.UserID = UserID;
    myUser.UserName = parameterUserName.value;
    myUser.UserEmail = parameterUserEmail.value;
    myCommand.Dispose();
    return myUser;
    }
    finally
    {
    myCon.Close();
    myCon.Dispose();
    }
    }
    }

    hope this example helps...

    --
    Regards,

    HD

    "Michael" <raterus@localhost> wrote in message
    news:%...
    > Hello,
    >
    > In my past ASP pages, at the top I used an include file to open an ado
    > connection for the entire page, then at the bottom, I would have another
    > include file to close the connection.
    >
    > Now that I'm moving to .NET, and using ado.net as well, does anyone have

    any
    > ideas to accomplish this for asp.net. I'd like for it to be as much

    behind
    > the scenes as possible, hopefully so I'll never have to look at statements
    > like myConnection.open and myConnection.close in my main code.
    >
    > Thanks,
    > --Michael
    >
    >
    Hermit Dave, Dec 30, 2003
    #2
    1. Advertising

  3. Trying to think like ASP when working with ASP.Net is going to drive you
    crazy. ADO.Net and ADO have very little in common, about as much as ASP and
    ASP.Net have in common.

    ASP.Net is object-oriented, which means that you have to think in objects
    and classes. For example, typically, an ASP.Net application that talks to a
    database includes some kind of class or classes that do the database work,
    and return results. This is the "N-Tier" approach, which puts all database
    functionality into a tier of its own, and separates it from code that works
    with the Interface tier. This makes code maintenance much simpler, as well
    as efficient, and would be the way I would recommend you design your
    application.

    Also, keep in mind that ASP.Net uses Connection Pooling, so you don't have
    to worry about using the same Connection for multiple database operations
    (which can be tricky and difficult at best with ADO.Net, for several
    reasons). Instead, the recommended practice is to open and close your
    Connections explicitly whenever you need to connect to the database. As long
    as the Connections all use the same Connection String, they will be pooled,
    and you wan't actually be creating a new one each time you "create" one;
    instead, you'll be using one you already created previously, which was
    pooled.

    --
    HTH,
    Kevin Spencer
    ..Net Developer
    Microsoft MVP
    Big things are made up
    of lots of little things.

    "Michael" <raterus@localhost> wrote in message
    news:#...
    > Hello,
    >
    > In my past ASP pages, at the top I used an include file to open an ado
    > connection for the entire page, then at the bottom, I would have another
    > include file to close the connection.
    >
    > Now that I'm moving to .NET, and using ado.net as well, does anyone have

    any
    > ideas to accomplish this for asp.net. I'd like for it to be as much

    behind
    > the scenes as possible, hopefully so I'll never have to look at statements
    > like myConnection.open and myConnection.close in my main code.
    >
    > Thanks,
    > --Michael
    >
    >
    Kevin Spencer, Dec 30, 2003
    #3
    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. Maziar Aflatoun

    Database update with auto connection open/close

    Maziar Aflatoun, Aug 11, 2005, in forum: ASP .Net
    Replies:
    2
    Views:
    523
    tom pester
    Aug 11, 2005
  2. Paul van Rossem
    Replies:
    0
    Views:
    592
    Paul van Rossem
    Apr 7, 2005
  3. Bob
    Replies:
    3
    Views:
    462
    =?Utf-8?B?TWlsb3N6IFNrYWxlY2tpIFtNQ0FEXQ==?=
    Feb 22, 2007
  4. Iñaki Baz Castillo
    Replies:
    7
    Views:
    815
    Iñaki Baz Castillo
    Jan 12, 2010
  5. Iulian Ilea
    Replies:
    1
    Views:
    290
    pcx99
    Dec 21, 2006
Loading...

Share This Page