Working with Connection object

Hi,

In my web application, I need to connect to an SQL Server database. My pages
(UI) will use one or more business objects as well as datareaders (I cannot
make everything a business object...)

With ASP 3, I created a VB class named DataAccess which maintained an ADO
Connection object and facilitates the creation of recordsets. One instance
was created at the beginning of the page and a reference was given to the
business objects.

This is not an easy approach because I need to pass the dataacess object to
any object which needs to access the DB. The advantage is that only one
connection is opened by a page. Another advantage was the ability to
retrieve scalars as well as arrays, but this is already done with ADO.NET.

What is your opinion ? With ASP.NET, should I create a new Connection each
time I need to access the DB or should I still encapsulate this ?

Thanks.

FredHi Frederic,

When I've done this I've used partial encapsulation. By this I mean that
each object has a connection object, which may (or may not) get instantiated
when the object is instantiated.

This is the base class for all data bound classes in my programming...It's
still in development I warn you:

Public MustInherit Class clsObject

Dim objConnection As SqlClient.SqlConnection

Public Sub New(Optional ByVal objCN As SqlClient.SqlConnection =
Nothing)

'mybase.new

If IsNothing(objCN) Then

Connection = New SqlClient.SqlConnection

Connection.ConnectionString =
HttpContext.Current.Cache.Item("SQL_CONNECTION_STRING").ToString

Else

Connection = objCN

End If

End Sub

Protected Overrides Sub Finalize()

'MyBase.Finalize()

Me.Connection = Nothing

End Sub

Public Property Connection() As SqlClient.SqlConnection

Get

Return Me.objConnection

End Get

Set(ByVal Value As SqlClient.SqlConnection)

Me.objConnection = Value

End Set

End Property

End Class

"Frdric Mayot" <toto@.toto.com> wrote in message
news:%23NH2slYaEHA.3692@.TK2MSFTNGP09.phx.gbl...
> Hi,
> In my web application, I need to connect to an SQL Server database. My
pages
> (UI) will use one or more business objects as well as datareaders (I
cannot
> make everything a business object...)
> With ASP 3, I created a VB class named DataAccess which maintained an ADO
> Connection object and facilitates the creation of recordsets. One instance
> was created at the beginning of the page and a reference was given to the
> business objects.
> This is not an easy approach because I need to pass the dataacess object
to
> any object which needs to access the DB. The advantage is that only one
> connection is opened by a page. Another advantage was the ability to
> retrieve scalars as well as arrays, but this is already done with ADO.NET.
> What is your opinion ? With ASP.NET, should I create a new Connection each
> time I need to access the DB or should I still encapsulate this ?
> Thanks.
> Fred
On 7/14/04 4:00 AM, in article #NH2slYaEHA.3692@.TK2MSFTNGP09.phx.gbl,
"Frdric Mayot" <toto@.toto.com> wrote:

> Hi,
> In my web application, I need to connect to an SQL Server database. My pages
> (UI) will use one or more business objects as well as datareaders (I cannot
> make everything a business object...)
> With ASP 3, I created a VB class named DataAccess which maintained an ADO
> Connection object and facilitates the creation of recordsets. One instance
> was created at the beginning of the page and a reference was given to the
> business objects.
> This is not an easy approach because I need to pass the dataacess object to
> any object which needs to access the DB. The advantage is that only one
> connection is opened by a page. Another advantage was the ability to
> retrieve scalars as well as arrays, but this is already done with ADO.NET.
> What is your opinion ? With ASP.NET, should I create a new Connection each
> time I need to access the DB or should I still encapsulate this ?
> Thanks.
> Fred

Use the Microsoft Data Block -
http://msdn.microsoft.com/library/d...-us/dnbda/html/
daab-rm.asp

IIS uses connection pooling - so yes you should close and open new
connections every time. Let the driver manage it - its good at it.