Hi

I am a new user to LLBL and a trying to teach myself vb.net object methods - I am finding it a steep learning curve!

I am having problems with using a repeated fill of a typed view. After multiple refills of the view collection my connection pool is exhausted.

I don't really need to use a transaction, but I don't want to have a new connection opened each time I fill, which is possibly what your code wants to do. I therefore attempted the code below to see if it helped. But it didn't!

Private colExisting As New SupplierMatching.TypedViewClasses.MatchingExistingTypedView

 Dim myconn As SqlConnection = DbUtils.CreateConnection
                    Dim mytrans As New Transaction(IsolationLevel.ReadUncommitted, "Test")

                    colExisting.Clear()
                    colExisting.Fill(10000, Nothing, True, Nothing, mytrans)

                    mytrans.Commit()
                    mytrans.Dispose()
                    myconn.Close()
                    myconn.Dispose()

I have the latest build 29Sep04 templates for vb.net . I am using the service model code.

Ideally I would open a connection at the form load stage and leave it open for all fills etc and close/dispose of it on closing the form. I am probably missing something simple but how do I make the "fill" use the connection I have open?

Any help would be much appreciated.

The fill is done by opening a new connection object, but this is done by ADO.NET or the SqlClient, as the connection isn't opened before the SqlDataAdapter.Fill() method is called. So the SqlDataAdapter opens the connection if it'snot open, executes the query and closes the connection again.

Closing it will give it back to the pool.

Now, you don't need to grab a connection from DbUtils, as it will create a new one for you, so that's not necessary. The transaction object creates a new connection AND transaction and should use the same connection with all the fills.

Could you paste the loop in where you fill the typedview repeatedly which causes an error? thanks. Also, have you added settings to your connection string?

Thanks for the quick reply!

My connection string is

        <add key="Main.ConnectionString" value="data source=DELL\DELLSQL; Min Pool Size=3;Max Pool Size=100; initial catalog=SupplierMatching;integrated security=SSPI;"/>

I added the min & max pool settings along the way as I was trying to fix the problem.

The loop is like this:

1) user clicks a grid element - datasource = collection from typedview

2) I call a sub routine to execute a sql stored procedure to update two under lying tables

        
   Dim SqlConnection1 As New SqlConnection(Globals.ConnectionString())
            Dim sqlCommand1 As New SqlCommand
            sqlCommand1.Connection() = SqlConnection1

            sqlCommand1.CommandText = "MatchingProcess"
            sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure



            sqlCommand1.Parameters.Add("@Operation", "UNMATCH")
            sqlCommand1.Parameters.Add("@MatchingRunSeqNo", MatchingRunSeqNo())
            sqlCommand1.Parameters.Add("@MasterCompCode", UcNewLinksMatches1.grd.GetRow.Cells("MasterCompCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@MasterSuppCode", UcNewLinksMatches1.grd.GetRow.Cells("MasterSuppCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@MasterSuppSeqNo", UcNewLinksMatches1.grd.GetRow.Cells("MasterSuppSeqNo").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@PossibleMatchCompCode", UcNewLinksMatches1.grd.GetRow.Cells("CompCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@PossibleMatchSuppCode", UcNewLinksMatches1.grd.GetRow.Cells("SuppCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@PossibleMatchSuppSeqNo", UcNewLinksMatches1.grd.GetRow.Cells("SuppSeqNo").Value.ToString.Trim)


            Dim intRows As Integer

            SqlConnection1.Open()
            sqlCommand1.CommandTimeout = 600

            intRows = sqlCommand1.ExecuteNonQuery()

            SqlConnection1.Close()
            SqlConnection1.Dispose()

            colPossibleFill(True)
            colNewLinkFill(True)

            Me.Cursor.Current = Cursors.Arrow

1. This sub routine calls another routine to empty the collection and refill the entire view. This is a bit of overkill but I have no primary key on the view to allow fetching of a few changed rows - correct me if my you have any better ideas please! This is then filtered by the grid methods to give the updated view in the grid.

        Try

            If refill = True Then
                Me.Cursor.Current = Cursors.WaitCursor
                Try

                    Dim myconn As SqlConnection = DbUtils.CreateConnection
                    Dim mytrans As New Transaction(IsolationLevel.ReadUncommitted, "Test")

                    colExisting.Clear()
                    colExisting.Fill(10000, Nothing, True, Nothing, mytrans)

                    mytrans.Commit()
                    mytrans.Dispose()
                    myconn.Close()
                    myconn.Dispose()

    

                Catch ex As Exception
                    ExceptionManager.Publish(ex)
                End Try
                Me.Cursor.Current = Cursors.Arrow
            End If

            Dim condition1 As GridEXFilterCondition
            Dim condition2 As GridEXFilterCondition
            Dim condition3 As GridEXFilterCondition
            Dim FullCondition As GridEXFilterCondition

            condition1 = New GridEXFilterCondition(UcExistingMatches1.grd.RootTable.Columns("MasterCompCode"), ConditionOperator.Equal, grdmatchingDriver.GetRow.Cells("MasterCompCode").Value)
            condition2 = New GridEXFilterCondition(UcExistingMatches1.grd.RootTable.Columns("MasterSuppCode"), ConditionOperator.Equal, grdmatchingDriver.GetRow.Cells("MasterSuppCode").Value)
            condition3 = New GridEXFilterCondition(UcExistingMatches1.grd.RootTable.Columns("MasterSuppSeqNo"), ConditionOperator.Equal, grdmatchingDriver.GetRow.Cells("MasterSuppSeqNo").Value)

            FullCondition = New GridEXFilterCondition
            FullCondition.AddCondition(condition1)
            FullCondition.AddCondition(LogicalOperator.And, condition2)
            FullCondition.AddCondition(LogicalOperator.And, condition3)

            UcExistingMatches1.grd.RootTable.FilterCondition = FullCondition

The whole thing is quite inefficient but my views are quite complex with 3-4 tables so I have taken the easy option of clearing and refilling on every underlying change rather than maintaining objects etc. Any better ideas wuld be welcome.

Many thanks for your help.

zephyrs wrote:

The loop is like this: 1) user clicks a grid element - datasource = collection from typedview 2) I call a sub routine to execute a sql stored procedure to update two under lying tables

        
   Dim SqlConnection1 As New SqlConnection(Globals.ConnectionString())
            Dim sqlCommand1 As New SqlCommand
            sqlCommand1.Connection() = SqlConnection1
            sqlCommand1.CommandText = "MatchingProcess"
            sqlCommand1.CommandType = System.Data.CommandType.StoredProcedure
            sqlCommand1.Parameters.Add("@Operation", "UNMATCH")
            sqlCommand1.Parameters.Add("@MatchingRunSeqNo", MatchingRunSeqNo())
            sqlCommand1.Parameters.Add("@MasterCompCode", UcNewLinksMatches1.grd.GetRow.Cells("MasterCompCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@MasterSuppCode", UcNewLinksMatches1.grd.GetRow.Cells("MasterSuppCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@MasterSuppSeqNo", UcNewLinksMatches1.grd.GetRow.Cells("MasterSuppSeqNo").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@PossibleMatchCompCode", UcNewLinksMatches1.grd.GetRow.Cells("CompCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@PossibleMatchSuppCode", UcNewLinksMatches1.grd.GetRow.Cells("SuppCode").Value.ToString.Trim)
            sqlCommand1.Parameters.Add("@PossibleMatchSuppSeqNo", UcNewLinksMatches1.grd.GetRow.Cells("SuppSeqNo").Value.ToString.Trim)
            Dim intRows As Integer

            SqlConnection1.Open()
            sqlCommand1.CommandTimeout = 600

            intRows = sqlCommand1.ExecuteNonQuery()

            SqlConnection1.Close()
            SqlConnection1.Dispose()

            colPossibleFill(True)
            colNewLinkFill(True)

            Me.Cursor.Current = Cursors.Arrow

This is raw ADO.NET, does this routine has something to do with your question? WHy don't yuo call the procedure through an SP Call generated into the code?

1. This sub routine calls another routine to empty the collection and refill the entire view. This is a bit of overkill but I have no primary key on the view to allow fetching of a few changed rows - correct me if my you have any better ideas please! This is then filtered by the grid methods to give the updated view in the grid.

Views don't have pk's You can of course formulate a predicateexpression to filter out the rows you want.

        Try
            If refill = True Then
                Me.Cursor.Current = Cursors.WaitCursor
                Try
                    Dim myconn As SqlConnection = DbUtils.CreateConnection

This is unnecessary ^^^^

                    Dim mytrans As New Transaction(IsolationLevel.ReadUncommitted, "Test")

                    colExisting.Clear()
                    colExisting.Fill(10000, Nothing, True, Nothing, mytrans)

This fetches 10000 rows if your view contains 10000 rows. Also, because you use a transaction, this locks 10000 rows or more in the database, until the transaction is commited. This is pretty slow compared to a normal select by just calling Fill().

                    mytrans.Commit()
                    mytrans.Dispose()
                    myconn.Close()
                    myconn.Dispose()
                Catch ex As Exception
                    ExceptionManager.Publish(ex)
                End Try
                Me.Cursor.Current = Cursors.Arrow
            End If

            Dim condition1 As GridEXFilterCondition
            Dim condition2 As GridEXFilterCondition
            Dim condition3 As GridEXFilterCondition
            Dim FullCondition As GridEXFilterCondition

            condition1 = New GridEXFilterCondition(UcExistingMatches1.grd.RootTable.Columns("MasterCompCode"), ConditionOperator.Equal, grdmatchingDriver.GetRow.Cells("MasterCompCode").Value)
            condition2 = New GridEXFilterCondition(UcExistingMatches1.grd.RootTable.Columns("MasterSuppCode"), ConditionOperator.Equal, grdmatchingDriver.GetRow.Cells("MasterSuppCode").Value)
            condition3 = New GridEXFilterCondition(UcExistingMatches1.grd.RootTable.Columns("MasterSuppSeqNo"), ConditionOperator.Equal, grdmatchingDriver.GetRow.Cells("MasterSuppSeqNo").Value)

I'm not sure what this does, but if it filters on the rows, please create a predicate expression and pass that to Fill() You'll then not fetch 10000 rows but just the few you want.

The whole thing is quite inefficient but my views are quite complex with 3-4 tables so I have taken the easy option of clearing and refilling on every underlying change rather than maintaining objects etc. Any better ideas wuld be welcome.

A view's execution plan is cached by Sqlserver. So a second fetch is very fast. Just try it with a predicate expression, you'll see that's much faster than fetching 10000 rows and filtering these on the client

I have fixed my connection pool problem. It seems there is a bug in Visual studio when the sql debugger is set on. See:

http://www.error-bank.com/microsoft.public.dotnet.framework.adonet/78771_Thread.aspx

Thanks for the tips. I was using the predicates for filter prior to finding the bug, at which point I rewrote it with grid filters thinking I was doing the wrong thing with the the LLBL methods. I have now reinstated the predicate code and everything is working well and response times are near instant.

Thanks again for your help

Mark

Thanks for the feedback! I didn't know about that issue, great to have such a link in the forum base so others can find it as well