CODEWITHSUNDEEP
mysqlvb.net
user1410081
user1410081

Reputation:

VB.Net reuse sql connection

I'm a newbie to vb.net, I'd like to ask is there a way to reuse the sql connection command?

Here is the code for my main.vb:

 Dim ServerString As String = "Server=localhost;User Id=root;Password=;Database=pos"
 Dim SQLConnection As MySqlConnection = New MySqlConnection

 Private Sub Main_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    SQLConnection.ConnectionString = ServerString

    Try
        If SQLConnection.State = ConnectionState.Closed Then
            SQLConnection.Open()
        Else
            SQLConnection.Close()
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

Since I'd like to use this in my other classes, i don't want to re-write this code in every form. Any help is truly appreciated. Thanks.

Upvotes: 0

Views: 5938

Answers (2)

Rick Agustin
Rick Agustin

Reputation: 11

This is what I normally do: I create a class, e.g. ConnectDB, and a method within this class, e.g. GetConnection. Here is the code:

Imports System.Data
Imports System.Data.SqlClient

Public Class ConnectDB

    Public Shared Function GetConnection() As SqlConnection
        Dim dbConnString As String = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"
        Return New SqlConnection(dbConnString)
    End Function
End Class


Then from the method that needs a connection to the database, I call this function.  Here is a sample code:

Imports System.Data.SqlClient

Public Class EmployeeDB

    Public Shared Function GetEmployees() As List(Of Employee)
        Dim con As SqlConnection = ConnectDB.GetConnection()
        Dim selectStmt As String = "SELECT * FROM Employees"
        Dim selectCmd As New SqlCommand(selectStmt, con)
        Dim employees As New List(Of Employee)

        Try
            con.Open()
            Dim reader As SqlDataReader = selectCmd.ExecuteReader()
            Do While reader.Read
                Dim employee as New Employee

                employee.LastName = reader("LastName").ToString
                employee.FirstName = reader("FirstName").ToString
                ...
                employees.Add(employee)
            Loop
            reader.Close()
         Catch ex As Exception
             Throw ex
         Finally
             con.Close()
         End Try
         Return employees
    End Function
End Class

You can also modify the selectStmt string to include filter conditions, parameters, and sort order just like Tim's example above and include selectCmd.Parameters.AddWithValue("@<parameterName>", value) for each of your parameters.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460268

Reusing a connection (or any other unmanaged resource) is normally not a good idea. You should dispose them as soon as possible.

But there's no problem in always creating a new connection anyway, since you're using the ADO.NET connection-pool by default. So you are not creating (and opening) a new physical connection. Actually you're just telling the pool that a conenction is reusable somewhere else now when you close/dispose it. And when you open it, it cannot be used somewhere else, that's why it's important to always close it.

Therefore always use the Using-statement.

Public Shared Function GetColumn1(column2 As Int32) As String
    Dim sql = "SELECT Column1 From dbo.tableName WHERE Column2=@Column2 ORDER BY Column1 ASC"
    Using con = New SqlConnection(connectionString)
        Using cmd = New SqlCommand(sql, con)
            cmd.Parameters.AddWithValue("@Column2", column2)
            Try
                con.Open()
                Using rd = cmd.ExecuteReader()
                    If rd.Read() Then
                        Dim Column1 As String = rd.GetString(0)
                        Return Column1
                    Else
                        Return Nothing
                    End If
                End Using
            Catch ex As Exception
                ' log the exception here or do not catch it '
                ' note that you don't need a Finally to close the connection '
                ' since a Using-Statement disposes the object even in case of exception(which also closes a connection implicitely)
            End Try
        End Using
    End Using
End Function

Above is a sample method to demontrate that you should not reuse anything.

Upvotes: 3

Related Questions