locked
Passing connection string to vb.net Class on Each Call RRS feed

  • 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

    Monday, May 4, 2015 10:23 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 11, 2015 7:29 AM

All replies

  • 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

    Tuesday, May 5, 2015 6:29 AM
  • 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

    Wednesday, May 6, 2015 2:09 AM
  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, May 11, 2015 7:29 AM