CODEWITHSUNDEEP
vb.net
gfuller40
gfuller40

Reputation: 1195

how to keep a odbc connection open in vb.net

I'm trying to connect to a database and keep the connection open for any amount of user activity/queries within the database. Currently I have a connection that opens and closes for any query (save, update, etc...) which requires the logon process to the back-end every time the user saves etc... Is there a way to simply connect and leave the connection open so there won't be a lag time when running the query due to the logon process? This is what I'm using:

    Private sConStrFormat As String = "Provider=TDOLEDB;Data Source=TDDEV;Persist Security Info=True;User ID={0};Password={1};Default Database=bcpm_ddbo;Session Mode=ANSI;"


   Private Sub cmdsave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdsave.Click


        Dim sSQL As String
        Dim sConStr As String

        Dim user As String
        Dim pass As String
        user = txtuser.Text
        pass = txtpass.Text

        Dim UserName As String = user
        Dim Password As String = pass

        sConStr = String.Format(sConStrFormat, UserName, Password)
        Using Con As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(sConStr)
            Con.Open()
            sSQL = "INSERT INTO LTC_FBS (CLM_NUM) VALUES ('" & Me.txtClaim.Text & "')"
            Dim cmdins As New System.Data.OleDb.OleDbCommand(sSQL, Con)
            cmdins.ExecuteNonQuery()

            Con.Close()

        End Using


    End Sub

Upvotes: 2

Views: 4108

Answers (3)

Jonathan Nicol
Jonathan Nicol

Reputation: 61

In vb.net 4.5 do the following:

At the top straight after the class definer put in this line:

    Public Shared conn As OdbcConnection

Then, in the subs where you want to use the connection use this line:

    If conn Is Nothing Then conn = New OdbcConnection(<your_connection_string>): conn.Open()

Then the New OdbcCommand will use the existing connection without opening a new one. Don't close the connection in your script until you are quite certain you're finished with it (_Shutdown is a good spot and you're good to go.

This also solves problems with MySQL when constantly opening new connections causes the max connections error.

Upvotes: 1

Alain
Alain

Reputation: 27220

Instead of defining 'con' in the using statement, define it up above as a static variable. When the function is called, you can see if 'Con' has been assined or not. If not, you build your connection string and set Con = New OleDBConnection, and open it. Since it's a static variable, it will retain its value at the next call. Check that it's not nothing, and then use it right away. Make sure you don't call close in the routine or the connection will not remain open.

Upvotes: 0

Chris Haas
Chris Haas

Reputation: 55417

.Net automatically maintains a connection pool for you. According to MSDN, when you call Close() on the Connection the framework

releases the connection to the connection pool, or closes the connection if connection pooling is disabled

Upvotes: 2

Related Questions