MultiThreaded App and Connection to SQL server issues 2

[NoCoolHandle]

I am hoping someone can help me here.

I am attempting to write an applicaion that can connect to 150 + sql box's in a multi threaded manor.

i.e. I don't want to wait for the first command to complete before opening a new connection and starting the process there.

When I start a new thread (second and beyond) that should connect to a different server, I get an error indicating that the connection pool is full and another connection can't be made.

The strage thing here seems to be that each connection is to a different server. My understanding is that connection pooling only kicks in for connection strings that are indentical. Each string here is different.

Is it possible that ado is single threaded and once a connection is open and executing a new connecton can't be created?

The connection in question lives in a seperate class that is instanciated for each server.. I.e. dim c as new controller

then a thread is created and points to a method of the class which is then exectued via the start method of the thread.

This one is kicking my B(^%*%

TIA

Rob

 

[ThatRickGuy]

Can you post some code. What you are saying makes sence, and I just can't see the flag ship that is ADO.Net not being thread safe.

Does the error occur as soon as you open the second connection? or say at 10 connections? Are you positive that each connection string is different?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NoCoolHandle]

As soon as the second connection is being opened.

It is bizare. I am thinking of creating the connection outside the thread and passing it into the class..

The worker thread code.. been hacking it a bit, hopefully it is still mostly there

To say that debugging is hard is an understatement. MS tends to recomed placing your code in RELEASE mode.. Didn't help. I have tried turning off Pooling.. didn't help.

ANy ideas are very welcome Rick

If you want full code I will post it to my server when I get home.

Thanks

Rob

Public Class Worker

    Dim strServer As String, strMessage As String, strIPAddress As String
    Dim mController As Controller
    'The following valriables are used to figure out if we need to do anything and then to confirm "Who" confirmed reciept of the message
    Dim strCollector As String
    Dim bolAutomaticLane As Boolean
    Dim bolLaneLoggedOn As Boolean

    Public Sub New(ByVal ServerName As String, ByVal IPAddress As String, ByVal Message As String)
        strServer = ServerName
        strMessage = Message
        strIPAddress = IPAddress
    End Sub
    Public Sub Initialize(ByVal controller As Controller)
        mController = controller
    End Sub

    Public Sub DoWork()
        Dim c As New SqlClient.SqlConnection("Server=" & strIPAddress & ",1433;Network Library=DBMSSOCN;Initial Catalog=Master;User ID=gandaff;")
        Dim cm As New SqlClient.SqlCommand("Select @@servername + 'was sent the folloing message: " & strMessage & "'", c)
        Dim cm1 As New SqlClient.SqlCommand("Select SodField,SodFieldValue from lanemessages.dbo.sodinfo where sodfield in ('CollectorNumber','LaneType','LogOnOffBit')", c)
        Dim sResult As New System.Text.StringBuilder
        Dim dr As SqlClient.SqlDataReader
        Try
            c.Open()
            Dim dr1 As SqlClient.SqlDataReader
            dr1 = cm1.ExecuteReader()
            While dr1.Read
                If dr1.Item(0).ToString = "CollectorNumber" Then
                    strCollector = dr1.Item(1).ToString
                End If
                If dr1.Item(0).ToString = "LaneType" Then
                    If dr1.Item(1).ToString = "3" Then
                        'this is an ATIM nothing to do
                        bolAutomaticLane = True
                    Else
                        bolAutomaticLane = False
                    End If
                End If
                If dr1.Item(0).ToString = "LogOnOffBit" Then
                    If dr1.Item(1).ToString = 0 Then
                        'this is logged off nothing to do
                        bolLaneLoggedOn = False
                    Else
                        bolLaneLoggedOn = True
                    End If
                End If
            End While
            dr1.Close()
            sResult.Append(Now().ToShortDateString & " " & Now.ToShortTimeString & "|" & strServer & "|")
            If bolLaneLoggedOn And Not bolAutomaticLane Then
                dr = cm.ExecuteReader()
                While dr.Read
                    For intX As Integer = 0 To dr.FieldCount - 1
                        sResult.Append(dr.Item(intX).ToString & vbCrLf)
                    Next
                End While
                '         .Columns.Add("Date", GetType(Date))
                '         .Columns.Add("ServerName", GetType(String))
                '         .Columns.Add("Status", GetType(String))
                'Send A message to the controller that allows the client to display good status data.
                mController.Display(sResult.ToString.Substring(0, sResult.Length - 1))
                'Tells the controller it wasn't cancelled and the task is complete
                mController.Completed(False)
            End If
        Catch ex As Exception
            Dim sError() As String = {Now().ToShortDateString & " " & Now.ToShortTimeString, strServer, ex.Message}
            mController.Failed(sError)
        Finally
            c.Close()
            c.Dispose()
        End Try
    End Sub

End Class

 

[ThatRickGuy]

That looks like it should be fine, you said you are instantiating a new Worker object for each call right?

What about the threading code?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NoCoolHandle]

Yep and here...

Controller object(1 sectioon) then Client Code(2 sections)

Sorry for the size. thanks for sticking with this.. I havn't been able to get a grip on why this doesn't work.

Rob

Imports System.Threading
Public Class Controller

    Private lWorker As Worker
    Private lClient As Form1
    Private lRunning As Boolean
    Private lPercent As Integer
    Public Index As Integer

    Private Delegate Sub DisplayDelegate(ByVal text As String)
    Private Delegate Sub CompletedDelegate(ByVal cancelled As Boolean, ByVal Sender As Controller)
    Private Delegate Sub FailedDelegate(ByVal e() As String, ByVal Sender As Controller)

#Region "Code Called by client"
    Sub New(ByVal client As Form1)
        lClient = client
        'lIndex = Index
    End Sub
    Sub Start(Optional ByVal worker As Worker = Nothing)
        If lRunning Then
            Throw New Exception("Already Running!")
        End If
        lRunning = True
        lWorker = worker
        lWorker.Initialize(Me)
        Dim t As New Thread(AddressOf lWorker.DoWork)
        t.Start()
        lClient.Start(Me)
    End Sub
    Public Sub Cancel()
        ' This method is called by the client to tell the worker to stop
        ' The worker gets this value via the "Running" Property below.
        ' It retrieves the value of lRunning 
        '(this is checked every loop of activity in the worker)
        lRunning = False
    End Sub
    Public ReadOnly Property Percent()
        Get
            Return lPercent
        End Get
    End Property
#End Region

#Region "Called By Worker Thread"
    Sub Display(ByVal text As String)
        Dim disp As New DisplayDelegate(AddressOf lClient.Display)
        Dim ar() As Object = {text}
        lClient.BeginInvoke(disp, ar) 'async call
    End Sub
    Sub Failed(ByVal e() As String)
        Dim disp As New FailedDelegate(AddressOf lClient.Failed)
        Dim ar() As Object = {e, Me}
        lClient.Invoke(disp, ar) 'Sync call back to client
    End Sub
    Sub SetPercent(ByVal percent As Integer)
        lPercent = percent 'Return From Worker - can be retrieved via client.
    End Sub
    Sub Completed(ByVal cancelled As Boolean)
        lRunning = False
        Dim comp As New CompletedDelegate(AddressOf lClient.Completed)
        Dim ar() As Object = {cancelled, Me}
        lClient.Invoke(comp, ar) ' Sync Call
    End Sub
    Public ReadOnly Property Running()
        ' THis property is checked by the worker thread to see if it needs to stop
        Get
            Return lRunning
        End Get
    End Property
#End Region
End Class

and

Client
(COde that makes the calls)

            If oRet.Status = ICMPClass.ICMPStatusEnum.Success Then
                dv.RowFilter = "IP='" & ds.Tables(0).Rows(i)(1).ToString & "'"
                lstSuccess.Items.Add("Ping of Lane " & dv.Item(0)(0).ToString & " returned success")
                lIndex += 1
                If c Is Nothing Then
                    ReDim c(0)
                Else
                    ReDim Preserve c(c.Length)
                End If
                Dim cont As New Controller(Me)
                cont.Index = lIndex
                c(c.Length - 1) = cont
                Try
                    c(c.Length - 1).Start(New Worker(dv.Item(0)(0).ToString, cIP, TextBox1.Text))
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            Else
                dv.RowFilter = "IP='" & ds.Tables(0).Rows(i)(1).ToString & "'"
                Dim s As String = oRet.Status.ToString
                Dim sRow() As String = {Now.ToString, dv.Item(0)(0).ToString, "Error: " & s}
                dtErrors.Rows.Add(sRow)
                lstFailure.Items.Add("Lane " & dv.Item(0)(0).ToString & " " & s)
            End If

and Declaratoins

#Region "MultiThread"

    Private lActive As Boolean
    Private lIndex As Integer
    Dim c() As Controller
    '          .Columns.Add("Date", GetType(Date))
    '          .Columns.Add("ServerName", GetType(String))
    '          .Columns.Add("Status", GetType(String))

    Public Sub Display(ByVal text As String)
        Dim srow() As String = text.Split("|")
        dsSuccess.Tables(0).Rows.Add(srow)
        lstSuccess.Items.Add(text)
        lstSuccess.SelectedIndex = lstSuccess.Items.Count - 1
    End Sub
    Public Sub Start(ByVal controller As Controller)
        lActive = True
        Me.Text = "Starting"
    End Sub
    Public Sub Failed(ByVal e As String(), ByVal Sender As Controller)
        Dim sRow() As String = e
        dsFailure.Tables(0).Rows.Add(sRow)
        lActive = False
        lstFailure.Items.Add(e(0).ToString & ": " & e(1).ToString & ": " & e(2).ToString)
        Sender = Nothing
    End Sub
    Public Sub Completed(ByVal cancelled As Boolean, ByVal Sender As Controller)
        Me.Text = "Task Finished!"
        lActive = False
        Debug.Write((c.GetUpperBound(0) - c.GetLowerBound(0)).ToString & " Conrrollers open")
        Sender = Nothing
    End Sub
#End Region

 

[AccordingToDale]

Although not much help, but you can definitely have more than 1 connection open at the same time. I've done that several times.

Dale

 

[chiph]

The connection pool has a limit as to how many open connections it can have at once. You can try adding the Max Pool Size argument to your connection string to see if that changes things. It defaults to 100, I believe.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[ThatRickGuy]

Chip, should that even be an issue since each of these connections is running to different servers?

I'm going to take a guess (too brain fried to work through the code tonight, been in ASP.Net training all week) that the issue is something to do with the multi-threading not creating an independant instance of the class that opens the connection, so when the 2nd process tries to open the connection from another thread, the connection object pukes because it's already open.

that's a guess from breezing over the code. I would say try stripping it down to just the basic multi-thread and connection code, get a proof of concept working first, then reassemble the working code.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[chiph]

Chip, should that even be an issue since each of these connections is running to different servers?

If you're using integrated security, the connect string may appear to the runtime to be identical, and end up in the same pool.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[NoCoolHandle]

Chip..

Each connectoin string is different. Connection pooling is only supposed to work when you have "Exactly the same attribtes" as per Rick

Each one goes to a differnt server.

150 + different connections
and only 1 gets to go.

 

[NoCoolHandle]

Well, intersting.

The code that works creates a connecton object at the client end and then passes it to the new instance of the worker which is in turn kicked off via the controllers start method.

So.. Creating the connection in the WorkerThread caused an error. When this happened it got confused somehow.

Building the Connection in the Client seems to slow things down a little (reduced applicaion responsiveness) but it works.

BIZZARE.

Thanks guys.

Rob

PS Rick - are you teaching or sitting?

 

[NoCoolHandle]

Ran a test with a nice little proc for simulating strange delays in processing, and it kicked butt! Beautiful when it happens. I managed to get up to 70 threads going at once. Ahhh they all gave the client good success and failure info.

Rick and Chip. Thanks it was nice to have someone to bounce this one against. I love this forum! (a star to you both for being there.)

Rob

Create Proc  WaitAWhile
as
set nocount on
Declare @x int
Set @x = cast(rand() * 20 as int)
waitfor delay @x

 

[ThatRickGuy]

Interesting, maybe there's something not quite thread safe happening in a constructor...

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NoCoolHandle]

It has me wondering Rick. I will probably make sence of it some time in the future.
Right now however, I am just happy it is working.\

Rick were you sitting an asp.net class?

If so an you ever need a quick answer email me.

I feel like I owe you a few!

[green]'Robs Email'[/green]
	Dim data ,i ,email
	code = "qpaAKzmdgudl-dnn"
	data = ""
	For i = 1 to LEN(code)
  	if i mod 2 = 0 then
   		data = data & chr(asc(mid(code,i,1)) -1 )
	else
   		data = data & chr(asc(mid(code,i,1)) +1 )
	end if
	next
	msgbox  lcase(data),,"Important Contact Information"

 

[ThatRickGuy]

Still am. day 3 of an auctioneer's pace. my brain is swiss cheese. I may take you up on that email offer some time

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NoCoolHandle]

Rick you are more than welcome to do that. You have been alot of help to me here.

I find Asp.net does need some understanding of basic Request Response objects, but does a great job of hiding alot of the complexity.

I find that about 1/2 of the new devlopment I do takes advantage of asp.net. It lets you build really good client and reporting interfaces that require NO installation, period!

Great at crossing domains. Webservices are sooooo coool. they even work great with desktop apps.

Asp.Net 2.0 rocks!! easier to do many things.

Thanks for going over the code. there was alot of it.

Enjoy the class (if you brain doen't melt. I know that you will be able to do sooo much with it that it just isn't funny.


Rob
MCSE (nt4), MSCD,MCDBA 7 and 2k

PS I used to be an MCT (wasn't getting enough hours to stay certified and didn't want to give it away so the ctec could see ridiculus margins.), so got to see it from the other side

Now I am pretty well a fulltime database/developer geek. Proabaly looking for work in a few months, but ....

 

[stsuing]

What's MSCD?
do you mean MCSD?

 

[NoCoolHandle]

hahah.. if my typing was better..

yes

 

[stsuing]

Just making sure I didn't miss another MS certification. You know they change daily

 

[ThatRickGuy]

I had a chance to get my MCSD back in 2k/2k1... back in the Military, but the day before the MCSD boot camp started, they pulled me from the roster and put one of the consultants in. 2 months later, he quit.

As for ASP.Net, I really want to get into AJAX, and I hear .Net v2.0 has tons of great goodies for it.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.