Closing a connection returned from separate class

[Guest]

Hi

I have a solution - just a test one - where there are two projects. One is
a simple windows form with a text box called txtClient & a command button.

The command button eventually calls a SP that has a parameter @ClientName.

The other project is a class library that has a class called clsDataAccess.
This class has a public function as follows:

------------------------
Public Function ConnectToDB(ByVal strAppID As String) As SqlConnection

Dim cn As SqlConnection
Dim strConnString As String

Try
cn = New SqlConnection
cn.ConnectionString =
"Server=(local);uid=sa;database=Test_DBCommonCode_ForOtherProjects;Integrated
Security = sspi"
cn.Open()
Return cn
Catch

Finally



End Try



End Function
--------------------------

the click event for the command button is as follows:

===============

Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCommit.Click
Dim cnn As SqlConnection
Dim cls As MyClasses.clsDataAccess
Dim cmd As SqlCommand

cnn = New SqlConnection
cls = New MyClasses.clsDataAccess

Try

'connect to DB
cnn = cls.ConnectToDB("Test_DBCommonCode_ForOtherProjects")

Try

'create command object & add parameters
cmd = New SqlCommand
cmd.Connection = cnn

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "procClientAdd"

Dim prmTemp As SqlParameter

prmTemp = cmd.Parameters.Add(New SqlParameter("@ClientName",
SqlDbType.VarChar, 50))
prmTemp.Direction = ParameterDirection.Input
prmTemp.Value = Me.txtClient.Text

cmd.ExecuteNonQuery()

Catch ex As Exception

End Try

Catch ex As Exception

Finally

If cnn.State <> ConnectionState.Closed Then cnn.Close()
cnn = Nothing

cmd = Nothing

Me.txtClient.Text = ""

End Try




End Sub

==============

What I was wondering was - is it necessary/desireable to close the
connection variable cn in the class library from within the code in the click
event for the command button? In other words - would that connection still
be open and therefore using up system resources.

Kind regards

Ross Petersen

 

[Cowboy \(Gregory A. Beamer\)]

I am not fond of the "return a connection" type of architecture, but the
correct method would be to close the connection in the event that uses it,
i.e. the click event. Your libraries tightly couple your code, reducing the
ability to separate into tiers.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Guest]

Thanks Greg

Appreciate your help.

The reason I was approaching it in this way is that where there is a
solution with a number of projects that are interacting with the DB in some
way or another (maybe through a XML web service) is that inevitably for the
different procedures there is some common code for establishing a connection
to the DB.

Kind regards

Ross Petersen

 

[William \(Bill\) Vaughn]

Ah, you can't share a connection across processes. You can share a
connection string but as we've discussed many times before you don't really
want to build too much "connection" management into your
applications--that's what the Connection Pool is for. Just have the
applications open and close their own connections using a shared (or unique)
ConnectionString--you'll get a new pool for each unique ConnectionString.



--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________