ADODB connection in VB.net application - WebPart

It is not a good practise to open the connection in the form load. use can declare a connection in form load but open the connection only when required and then immediately close your connection. This will help your performance.

you shouldnt leave your connection open, vb.net is connectionless, but you could wrap the database connecting class - so that you just have to call something like:

Dim sqlh As SQLHandler = New SQLHandler(sqlConnectionString, "[Stored Proc Name]", CommandType.StoredProcedure, sqlParameters, dataset to fill)


with the class below:

--8<--
Namespace ContraAct

Public Class SQLHandler

    Dim sqlConnectionString As String
    Dim sqlTransaction As SqlClient.SqlTransaction
    Dim sqlDataAdapter As SqlClient.SqlDataAdapter

    Dim commandText As String
    Dim commandType As commandType
    Dim dataSet As Data.DataSet
    Dim parameters As Collection

    Public Event doneFillDataSet(ByVal dataSet As Data.DataSet, ByVal parameters As Collection)

        Public Sub New(ByVal conn As String, ByVal text As String, ByVal type As commandType, ByVal parms As Collection, ByVal datas As Data.DataSet)
            Try
                dataSet = datas
                If dataSet Is Nothing Then
                    debugPrintLine("re-initialising the dataset because it was set to nothing")
                    dataSet = New DataSet
                End If
                sqlConnectionString = conn
                commandText = text
                commandType = type
                parameters = parms
            Catch ex As Exception
                MessageBox.Show("Caught ya! exception caught in the construction handler for sqlHandler class: " & vbCr & ex.StackTrace.ToString)
            End Try
        End Sub


        Public Sub ExecuteSQL()
            Try

                System.Threading.Monitor.Enter(Me)
                Dim sqlConnection As SqlClient.SqlConnection
                Try
                    debugPrintLine("- SQLHandler.executeSQL - " & "commandText: " & commandText)

                    sqlConnection = New SqlClient.SqlConnection(sqlConnectionString)
                    sqlConnection.Open()
                    sqlTransaction = sqlConnection.BeginTransaction()
                    Dim theCommand As New SqlClient.SqlCommand(commandText, sqlConnection, sqlTransaction)
                    theCommand.CommandType = commandType
                    theCommand.CommandTimeout = 30

                    theCommand.Connection = sqlConnection
                    sqlDataAdapter = New SqlClient.SqlDataAdapter
                    sqlDataAdapter.SelectCommand = theCommand

                    If Not parameters Is Nothing Then
                        Dim index As Integer = 0
                        For index = 1 To parameters.Count
                            sqlDataAdapter.SelectCommand.Parameters.Add((parameters.Item(index)))
                            debugPrintLine("- - added parameter: " & (parameters.Item(index)).ParameterName & " with value: " & (parameters.Item(index)).Value)
                        Next
                    End If

                    sqlTransaction.Commit()

                    'System.Threading.Monitor.Enter(sqlDataAdapter)
                    'Try
                    sqlDataAdapter.Fill(dataSet)
                    'Finally
                    '    System.Threading.Monitor.Exit(sqlDataAdapter)
                    'End Try

                    debugPrintLine("- raising the doneFillDataSet event")
                    RaiseEvent doneFillDataSet(dataSet, parameters)

                    sqlConnection.Close()
                Catch ex As Exception
                    debugPrintException("- SQLHandler.executeSQL - Exception occurred." & vbCr)
                    debugPrintException(ex)
                    Try
                        sqlTransaction.Rollback()
                        sqlConnection.Close()
                    Catch exclose As Exception
                        debugPrintException("- SQLHandler.executeSQL - Exception occurred." & vbCr)
                        debugPrintException(exclose)
                    End Try
                Catch ex As Exception
                    debugPrintException("- SQLHandler.executeSQL - Exception occurred." & vbCr)
                    debugPrintException(ex)
                End Try
            Finally
                System.Threading.Monitor.Exit(Me)
            End Try
        End Sub

        Private Sub debugPrintLine(ByVal info As String)
            Debug.WriteLine(info)
        End Sub

        Private Sub debugPrintException(ByVal info As String)
            Debug.WriteLine(info)
        End Sub

        Private Sub debugPrintException(ByVal ex As Exception)
            Debug.WriteLine(ex.StackTrace.ToString)
        End Sub

End Class
--8<--


and then you can do something like the below to call the class to do something:

--8<--

        Private Sub sql_retrieveGroupsTree()
            Try
                SqlDataSet_GroupTree.Clear()
                Dim sqlh As SQLHandler = New SQLHandler(sqlConnectionString, "[Act_GroupList]", CommandType.StoredProcedure, Nothing, SqlDataSet_GroupTree)
                Dim th As Threading.Thread = New Threading.Thread(AddressOf sqlh.ExecuteSQL)
                AddHandler sqlh.doneFillDataSet, AddressOf doneFillDataSet
                th.Start()
            Catch ex As Exception
                debugPrintException("- Form_Main.getFirstContactList- Exception occurred." & vbCr)
                debugPrintException(ex)
            End Try
        End Sub

--8<--

where the donefilldataSet is the method that catches the event after your sql stuff is finished...

hth

james

Well what iam currently doing is using that one as a singleton, but you say that i shouldnt keep the connection open ?
The thing is that this project will be compiled as a DLL file and run as an WebPart

this is a method using the class
    Public Function save(ByVal r As Category) As Boolean
        With rs
            .Open("select * from helpdesk_type where type_id=" & r.ID, DBConnection.getInstance, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            If .EOF Then
                .AddNew()
            Else
                .Update()
            End If
            .Fields("type_name").Value = r.name
            .Fields("parent_id").Value = r.parent.ID
            .Update()
            .Close()
        End With
        rs = New ADODB.Recordset
    End Function

The thing is i do alot of requests from different webParts and is it then very clever to reconnect everytime ?
How would you do the above with an SQLHandler ???

If you need more please ask

My intention was that you could create a class, and that each instance of the class has a different Connection object.

Then you could also have a method that updates/queries the database in that class - by first opening the connection, and then doing its operation, and then closing the connection.

If you then called this method that updates/queries the database as a thread - you could fire an event after the update has finished, to handle it in whatever way you wanted...  and of course you could then do your save operations at the same time

(not sure, but iirc ms sql server allows seven or so concurrent connections unless you have paid for more, but I could be completely and utterly wrong about that bit, as I cannot remember where or why I heard that...)

hope that helps

james

Sorry could you give an example of how you would use your suggestion to do

 Public Function save(ByVal r As Category) As Boolean
        With rs
            .Open("select * from helpdesk_type where type_id=" & r.ID, DBConnection.getInstance, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            If .EOF Then
                .AddNew()
            Else
                .Update()
            End If
            .Fields("type_name").Value = r.name
            .Fields("parent_id").Value = r.parent.ID
            .Update()
            .Close()
        End With
        rs = New ADODB.Recordset
    End Function