[sql server 2k/vb.net] Locks na select

[Verwijderd]

Ik heb een applicatie geschreven in vb.net die een aantal selects doet en met die gegevens een aantal email's verstuurd.

Visual Basic:

Private Sub SendTheEmails()
        Try
            Dim SendVC As New SendOutVCards()
            Dim SendOrders As New SendOutOrders()
            Dim SendAds As New SendOutAds()
            SendVC.SendNow()
            SendOrders.SendNow()
            SendAds.SendNow()
            SendVC = Nothing
        Catch ex As Exception
            Dim LogIt As New LogError()
            LogIt.WriteToLog(ex.Message)
        End Try
End Sub

In de objectcode staan de selects queries.

Elke keer als deze sub gestart wordt dan wordt er voor elke select statement een lock in de database gezet. Ik geloof wel dat het een shared lock o.i.d. maar dit weer ik niet zeker.
Het probleem is dat deze locks ervoor zorgen dat de database langzamer wordt en uit eindelijk de select queries mislukken.

Hieronder de code hoe ik de selects statements uitvoer en een stored procedure.

Visual Basic:

Private Function GetConfirmedOrders() As SqlDataReader
        Dim SqlConn As New DBConn()
        Dim result As SqlDataReader
        Dim myConnection As SqlConnection = New SqlConnection(SqlConn.ConnectionString)
        Dim myCommand As SqlCommand = New SqlCommand("tsp_get_ConfirmedOrders", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        myConnection.Open()
        Try
            result = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Catch e As Exception
            Dim WriteLog As New LogError()
            WriteLog.WriteToLog(e.Message)
        End Try
        Return result
    End Function

Visual Basic:

REATE PROCEDURE tsp_get_ConfirmedOrders AS

SELECT     dbo.Products.ProductName, dbo.Products.ProductDescription, dbo.Products.CategoryID, dbo.Suppliers.SupplierID, 
                      dbo.OrderDetails.EmailSendSupplier, dbo.OrderDetails.Confirmed, dbo.OrderDetails.OrderID, dbo.Products.ModelNumberSupplier, 
                      dbo.Products.NumberPerUnit, dbo.Products.MinimumOrder, dbo.Products.Stock, dbo.Products.DeliveryTime, dbo.OrderDetails.OrderDetailID, 
                      dbo.OrderDetails.ProductID, dbo.OrderDetails.Quantity, dbo.OrderDetails.UnitPrice, dbo.OrderDetails.DateConfirmed, dbo.OrderDetails.UserID, 
                      dbo.OrderDetails.DateEmailSend, dbo.OrderDetails.ConfirmedSupplier, dbo.OrderDetails.DateConfirmedSupplier, dbo.Suppliers.Name, 
                      dbo.Suppliers.Description, dbo.Suppliers.Address, dbo.Suppliers.Zipcode, dbo.Suppliers.City, dbo.Suppliers.PostalAddress, 
                      dbo.Suppliers.PostalZipcode, dbo.Suppliers.PostalCity, dbo.Suppliers.EmailAddress, dbo.Suppliers.Telephone, dbo.Suppliers.Fax, 
                      dbo.Suppliers.OrderEmailAddress, dbo.Suppliers.ContactPerson, dbo.Suppliers.ContactTelephone, dbo.Suppliers.ContactMobilephone, 
                      dbo.Suppliers.ContactEmailAddress, dbo.[User].FirstName, dbo.[User].LastName, dbo.Departments.DepartmentName, dbo.Departments.CustumField1, 
                      dbo.Location.LocationName, dbo.Location.Description AS LocationDescription, dbo.Location.Street, dbo.Location.StreetNumber, 
                      dbo.Location.ZipCode AS LocationZipCode, dbo.Location.City AS LocationCity, dbo.Location.Telephone AS LocationTelephone, 
                      dbo.Location.Fax AS LocationFax, dbo.Suppliers.SupportsHTMLMail
FROM         dbo.Orders INNER JOIN
                      dbo.OrderDetails ON dbo.Orders.OrderID = dbo.OrderDetails.OrderID INNER JOIN
                      dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductID INNER JOIN
                      dbo.Suppliers ON dbo.Products.SupplierID = dbo.Suppliers.SupplierID INNER JOIN
                      dbo.[User] ON dbo.Orders.UserID = dbo.[User].UserID INNER JOIN
                      dbo.Departments ON dbo.[User].DepartmentID = dbo.Departments.DepartmentID INNER JOIN
                      dbo.Location ON dbo.[User].LocationID = dbo.Location.LocationID
WHERE     (dbo.OrderDetails.EmailSendSupplier = 0) AND (dbo.OrderDetails.Confirmed = 1) AND (dbo.OrderDetails.DateEmailSend IS NULL)
ORDER BY dbo.Suppliers.SupplierID, dbo.OrderDetails.OrderID
GO

Het rare is dat als ik de applicatie afsluit dat de locks dan verdwijnen....

Iemand enig idee?

[gorgi_19]

Je closed je connections niet?

[Verwijderd]

gorgi_19 schreef op 07 May 2003 @ 10:50:
Je closed je connections niet?

result = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
toch?

[whoami]

Je closed je DataReader niet?

[EfBe]

Je connection blijft open. De datareader closet de connection pas als de datareader zelf wordt geclosed. Zie de help van CommandBehavior:
Member name Description
CloseConnection When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Jij closet de datareader niet, waardoor de connection echt open blijft. Die wordt NIET opgeruimd door de garbage collector.

[Verwijderd]

whoami schreef op 07 May 2003 @ 11:11:
Je closed je DataReader niet?

Ik heb nu overal result.close neergezet en ook de datareader geclosed die de kopie ervan gebruikt. Geen locks meer! thanks