Working with Connection object

[Frédéric Mayot]

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

 

[Paul Mason]

Hi 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

 

[Naoom Ryzhy]

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/default.asp?url=/library/en-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.