Re: Closing a connection returned from separate class

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!
*************************************************
"Ross" <Ross@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1E4D5CE6-8F09-422B-9DF0-EFE42CD040FC@xxxxxxxxxxxxxxxx
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


.