Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADODB connection in VB.net application - WebPart

Posted on 2004-10-27
6
Medium Priority
?
1,025 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
Comment
Question by:mSchmidt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Expert Comment

by:123654789987
ID: 12419877
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
ID: 12419929
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
ID: 12420011
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:jameskearney
ID: 12420102

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
ID: 12420118
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:
jameskearney earned 2000 total points
ID: 12420223

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

610 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