Passing connection string to vb.net Class on Each Call

Question

User1480781148 posted

Dear Friends,

I am using following class in my vb.net and I want to pass new connection string on each call. please help me how I can achive this. 

Public Class Dataconnect    

    Private objcon As New SqlConnection("Data Source=MY-PC;Initial Catalog=db_performance;Integrated Security=True")       

  Public Shared Function ExecutenonQuery(sqlcmd As SqlCommand) As Integer

        Try

            Dim objdc As New Dataconnect()         

    Dim affectedrecord As Integer = 0       

      If objdc.Opencon() = True Then

                sqlcmd.Connection = objdc.objcon           

      affectedrecord = sqlcmd.ExecuteNonQuery()           

      objdc.Closecon()              

   objdc = Nothing               

  Return affectedrecord          

   Else             

    Return affectedrecord      

       End If      

   Catch ex As Exception   

          ' new Exception("Error: In ExecuteNonquery");        

       Throw ex       

  End Try

    End Function

    Public Function Opencon() As Boolean    

     Try          

   If objcon.State = ConnectionState.Closed Then    

             objcon.Open()           

  End If            

objcmd.Connection = objcon       

      Return True      

   Catch ex As Exception

            Throw New Exception("Error: In Open connesction" + ex.Message)

            Return False    

     End Try   

  End Function

    Public Function Closecon() As Boolean

        Try             If objcon.State = ConnectionState.Open Then

                objcon.Close()       

      End If       

      objcmd.Dispose()  

           Return True      

   Catch ex As Exception       

      Throw New Exception("Error: In Close connesction" + ex.Message)       

      Return False     

    End Try   

  End Function

End Class

thank you.

regards,

asad

Answer 1

User-2031393618 posted

Hello asad_dba,       

ADO.NET uses connection pooling, so it automatically reuses already opened connection, even when you think that you are opening a new connection. Therefore, you don’t need to pass a connection string to each call. The following is an example that you can refer to.

Public Class Dataconnect
    ReadOnly cstr001 As String = "Data Source=MY-PC;Initial Catalog=db_performance;Integrated Security=True"
    Dim conn As SqlConnection = New SqlConnection(cstr001)
    Public Function ExecutenonQuery(sqlcmd As SqlCommand) As Integer
        Try
            conn.Open()
            sqlcmd.Connection = conn
            Return cmd.ExecuteNonQuery()
        Catch ex As Exception
            Return 0
        Finally
            Call CloseConn(conn)
            Call CloseCmd(sqlcmd)
        End Try
    End Function
    Public Sub CloseConn(ByVal conn As SqlConnection)
        If (conn.State <> ConnectionState.Closed) Then
            conn.Close()
            conn = Nothing
        End If

    End Sub

    Public Sub CloseCmd(ByVal cmd As SqlCommand)

        If Not IsNothing(cmd) Then
            cmd.Dispose()
            cmd = Nothing
        End If
    End Sub
End Class

Besides, you can read the following two articles.  I hope they are helpful to you.

http://stackoverflow.com/questions/9807268/is-it-best-to-pass-an-open-sqlconnection-as-a-parameter-or-call-a-new-one-in-ea

https://msdn.microsoft.com/en-us/library/bb399543.aspx

Best Regards,

Albert Zhang

Answer 2

User1480781148 posted

thank you for reply, but I want to use same class for more then one sqlserver go gather data , then I must pass connection string of each sqlserver to same class.

regards,

asad

Answer 3

User-2031393618 posted

Hi asad_dba,

I have made some modifications to the Dataconnect. You could use the following codes to connect a sqlserver.

Dim dc As New Dataconnect()
Dim str As String = "" //connection string
dc.GetConnection(str)

the Dataconnect.cs

Public Class Dataconnect
	Private _conn As SqlConnection
	Public Sub GetConnection(connstr As String)
		_conn = New SqlConnection(connstr)
	End Sub

	Public Function ExecutenonQuery(sqlcmd As SqlCommand) As Integer
		Try
			_conn.Open()
			sqlcmd.Connection = _conn
			Return sqlcmd.ExecuteNonQuery()
		Catch generatedExceptionName As Exception
			Return 0
		Finally
			CloseConn()
			CloseCmd(sqlcmd)
		End Try
	End Function
	Public Sub CloseConn()
		If (_conn.State <> ConnectionState.Closed) Then
			_conn.Close()
			_conn = Nothing
		End If

	End Sub
	Public Sub CloseCmd(cmd As SqlCommand)
		If (cmd IsNot Nothing) Then
			cmd.Dispose()
			cmd = Nothing
		End If
	End Sub
End Class

Best Regards,

Albert Zhang