CODEWITHSUNDEEP
vb.net
Ahmed Nazmy
Ahmed Nazmy

Reputation: 419

My sqlconnection is always nothing when calling it from class or dll file

I have a class to open a sqlconnection and return back with reference to that connection via the main application, the problem is this connection is always nothing how to modify this behaviour:

Class code:

Public Class Class1
Public ClassConnection As SqlClient.SqlConnection

Public Function OpenConnection() As Object
    Try
        Dim sConnString As String = ""
        sConnString = "Server=ServerName;Database=DBName;User ID=Username;Password=MyPassword"
        Using ClassConnection As New SqlClient.SqlConnection
            ClassConnection.ConnectionString = sConnString
            ClassConnection.Open()
            Return ClassConnection
        End Using
    Catch ex As Exception
        Throw ex
        Return Nothing
    End Try
End Function

End Class

My form Code:

Public Class Form1
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
    Dim uu As New Class1
    Dim iii As SqlClient.SqlConnection = uu.OpenConnection()
    MsgBox(iii.State)
End Sub

End Class

Upvotes: 1

Views: 524

Answers (1)

RB.
RB.

Reputation: 37192

You're problem is that by wrapping it in a Using block you are disposing the ClassConnection as soon as you return it.

Using ClassConnection As New SqlClient.SqlConnection
    ClassConnection.ConnectionString = sConnString
    ClassConnection.Open()
    Return ClassConnection
End Using ' This is equivalent to calling ClassConnection.Dispose()

should just be:

Dim ClassConnection As New SqlClient.SqlConnection
ClassConnection.ConnectionString = sConnString
ClassConnection.Open()
Return ClassConnection

And it is then the client's responsibility to dispose of the connection.

Note: Leaving the client responsible for cleaning up is generally considered poor API design. I would suggest investigating the Unit-of-Work pattern.

Upvotes: 1

Related Questions