Solved

ADODB connection in VB.net application - WebPart

Posted on 2004-10-27
1,018 Views
Last Modified: 2012-06-27
I have created a class as below, it simple connects to the database, however, at first page load the SQL server tells me 10 users has accessed the database, at second load around 15 and so on. Until it finally shuts down the SQL server...

Imports System.Web
Public Class DBConnection
    Private Shared con As DBConnection
    Private Shared DBCon As ADODB.Connection
    Private Sub New()
        DBCon = New ADODB.Connection
        DBCon.Open("driver={SQL Server};server=agportal;database=agportal")
    End Sub
    Public Shared Function getInstance() As ADODB.Connection
        If con Is Nothing Then con = New DBConnection
        Return DBCon
    End Function
End Class

What have i done wrong because something must be clearly wrong...
The Page will also at some times take an enourmeous time to load

So my question is how should i connect to my database and should maintain the connection

Code is  written in VB.net and compiled to an DLL run by sharepoint
0
Question by:mSchmidt
    6 Comments
     
    LVL 10

    Expert Comment

    by:123654789987
    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.
    0
     
    LVL 1

    Expert Comment

    by:jameskearney
    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
    0
     

    Author Comment

    by:mSchmidt
    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
    0
     
    LVL 1

    Expert Comment

    by:jameskearney

    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
    0
     

    Author Comment

    by:mSchmidt
    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
    0
     
    LVL 1

    Accepted Solution

    by:

    create a connection string along the lines of:

    sqlConnectionString = "workstation id=""" & Environ("ComputerName") & """;packet size=4096;integrated security=SSPI;data source=" & "SERVERNAME" & ";persist security info=False;initial catalog=" & "INITIAL_CATALOG"

    that would be creating a connection object that connections to SERVERNAME initial catalog of INITIAL_CATALOG using windows authentication,

    and then you would do something like

    dim command as string = "select * from helpdesk_type where type_id=" & r.ID, DBConnection.getInstance
    Dim sqlh As SQLHandler = New SQLHandler(sqlConnectionString, command, CommandType.Text, dataset)

    then do

                    Dim th As Threading.Thread = New Threading.Thread(AddressOf sqlh.ExecuteSQL)
                    AddHandler sqlh.doneFillDataSet, AddressOf doneFillDataSet
                    th.Start()

    then create a method called doneFillDataSet, that the sql wrapping class will give the newly filled dataset back to.

    (note that if you probably want to use a delegate in doneFillDataSet to marshall control back to the main thread)

    hth

    james
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
    Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now