Open/Close Database Connection for each page

[Michael]

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]

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

 

[Kevin Spencer]

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.