4 Replies - 926 Views - Last Post: 12 January 2015 - 07:12 AM Rate Topic: -----

#1 Lynchie435   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 08-December 14

SQLConnection, Multithreaded Application, Current State

Posted 12 January 2015 - 02:18 AM

Good Morning,

I have a multi-threaded application that we use for purposes of injecting information into a call at certain points.

I have used a SQL database as 2 other 3rd party components need to come together for my 'client' application to perform its task.

There are several timers that access the SQL database for information. I have a 'SendSQL' Sub that I pass 2 arguments through to access the SQL database.

Using cn As New SqlConnection
        'SqlConnection.ClearAllPools()
        Using cmd As New SqlCommand(SQL, objConn)
            Try
                With cmd
                    .Connection.Open()
                    If QryType = "ExecuteNonQuery" Then
                        ReturnVar = cmd.ExecuteNonQuery()
                        .Connection.Close()
                    ElseIf QryType = "ExecuteReader" Then
                        sqlreader = cmd.ExecuteReader()
                    ElseIf QryType = "ExecuteScalar" Then
                        ScalarVar = cmd.ExecuteScalar()
                        .Connection.Close()
                    End If

                End With

            Catch ex As Exception                    
                CreateErrorFile(ex.Message, ex.StackTrace, "Send SQL Sub")
            End Try
        End Using
    End Using

For all intents and purposes this works perfectly fine, until I see more and more users logging on. Then i start to receive

The connection was not closed. The connection's current state is connecting.
at System.Data.ProviderBase.DbConnectionClosedConnecting.TryOpenConnection(DbConnection    outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry,   DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at CRP_Client.MainForm.SendSQL(Object SQL, Object QryType)
I am concerned that the one I am using the above code is causing these problems, I was considering that all the client applications just create a SQL Connection Object and connect at the start-up of the Application and then stay connected rather than opening and closing the connection.


Is there anything wrong with this code?

The timers that fire off the above code are first at the application startup to register the application with the database, then every 5 seconds to check for the existence of a call in the database and also a timer that does every 5 minutes to make sure the application is logged in.

Any and all help is greatly appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: SQLConnection, Multithreaded Application, Current State

#2 Lynchie435   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 08-December 14

Re: SQLConnection, Multithreaded Application, Current State

Posted 12 January 2015 - 04:11 AM

I think I MAY have found my problem, but it needs testing.

In the top of my code i'm doing:

Using cn As New SqlConnection
'SqlConnection.ClearAllPools()
        Using cmd As New SqlCommand(SQL, objConn)
            Try



However I am doing objConn which is a SQLConnection Declaration I make at the top of my class, because I'm saying 'using CN' I'm not actually using CN at all SQLCommand is using objConn which it is not clearing properly as it has no relation to anything in the Using Statement, the Using Statement would close the 'cn' SQLConnection not objConn.

Right?!

:/
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: SQLConnection, Multithreaded Application, Current State

Posted 12 January 2015 - 07:08 AM

That sounds reasonable.

BTW I have removed the word HELP and .NET from your title, they aren't helpful. Every question here is asking for help of some kind, and you are already in a .NET forum.
Was This Post Helpful? 0
  • +
  • -

#4 Lynchie435   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 18
  • Joined: 08-December 14

Re: SQLConnection, Multithreaded Application, Current State

Posted 12 January 2015 - 07:10 AM

Apologies Andrews,

Just saw someone in a previous post use it.

Many Thanks :)

Jim
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6700
  • View blog
  • Posts: 27,542
  • Joined: 12-December 12

Re: SQLConnection, Multithreaded Application, Current State

Posted 12 January 2015 - 07:12 AM

I just removed it from a previous post as well ;) .. nipping it in the bud!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1