Click here to Skip to main content
16,020,626 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Good day,

I wrote a web-service in Visual Web Developer Express with VB.NET.

It is a scanning function that takes in a shipping label bar code and marks a SQL database field when found. If it is found the web-service function returns TRUE otherwise FALSE.

It works perfectly and quickly, but after you have scanned about fifty shipping labels, everything slows down. It seems to be related to the web-service as I use the same functions to scan with a laptop, but not employing web-services and that system still works perfectly.

If I test the web-service in a browser it seems to return a new result page every time, showing the true or false result, after you enter a serial number and invoke the service. Could this eventually slow it down?

Any ideas?

thanks
Posted

What I'm saying is you should always use SQL parameters instead of creating SQL statement such as "WHERE ShippingLabel='" & ScanndedShippingLabel & "' "

Using SQL parameters prevents you from SQL injection attacks.
 
Share this answer
 
Did you close your SQL readers and connections after using it?
Did you use DataSets, EF and such?
What SQL statement did you use?

We probably can't say much until we know more on what your web service is doing. But you can check the number of connections to your SQL server, your memory usage on the web service, etc that might indicate the issue.
 
Share this answer
 
So it looked like you didn't close/dispose the connection properly before opening a new one.

PS: Move your ScannedShippingLabel and other variables into SQL parameters as well. You are exposing yourself to SQL injection. There is no excuse for you not to use SQL parameters or to implement a half n half solution.
 
Share this answer
 
Hi,

I went through the code last night and made sure that everything is disposed of. I have added a dispose function for the connection object which I am going to test now.

the webservice code creates a business object (not sure if it should be disposed)
the business object creates a data object (not sure if it should be disposed)

Web service:
Public Class GRV
    Inherits System.Web.Services.WebService
    Dim bus As New GRBussiness

    <webmethod()> _
    Public Function HelloWorld(ByVal strShippingLabel As String, ByVal strBoxStatus As String, ByVal strWeekNo As String) As Boolean
        Dim ShippingLabelisFound As Boolean = False
        Try
            ShippingLabelisFound = bus.Scan_MarkShippingLabelsFound(strShippingLabel, strWeekNo, strBoxStatus)
            Return ShippingLabelisFound
        Catch ex As Exception
            Return False
        End Try
    End Function
End Class


data layer - is a class that is instantiated in the business layer
Dim data As New DataLayer - instantiated in bus object

Sub MakeConnect_GRV()
        GRVcon.ConnectionString = "Data Source=192.168.10.24\BDMS;" & _
                                       "Initial Catalog=FLATPAN_STOCK;" & _
                                       "User ID=flatpan;" & _
                                       "Password="password"
        Try
            GRVcon.Open()
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
        End Try
    End Sub
Sub DestroyConnection()
        GRVcon.Dispose()
    End Sub



Below is the code I use to do the actual update. This is part of a class that gets called in the webserivice code asmx.vb.
Dim bus As New GRBussiness 'Not sure if I am disposing of this correctly?


Function Scan_MarkShippingLabelsFound(ByVal ScanndedShippingLabel As String, ByVal WeekLookup As String, ByVal strBoxStatus As String) As Boolean
 
Try
  Dim ShippingLabebelStatusIsUpdated As Boolean
  Dim strUpdate As String = ""
  If ScanndedShippingLabel <> "" And strBoxStatus <> "" Then
    If WeekLookup = "" Then
     
     strUpdate = "Update PackingLists " & _
       "Set BoxStatus=@BoxStatus, ScanDate=@ScanDate " & _
       "WHERE ShippingLabel='" & ScanndedShippingLabel & "' " & _
       "AND BoxStatus='" & strBoxStatus & "'"

    Else

     strUpdate = "Update PackingLists " & _
     "Set BoxStatus=@BoxStatus, ScanDate=@ScanDate " & _
     "WHERE ShippingLabel='" & ScanndedShippingLabel & "' " & _
     "AND WeekNo='" & WeekLookup & "' AND BoxStatus='"      strBoxStatus & "'"

    End If

  If data.grvConnection.State = ConnectionState.Closed Then _
   data.MakeConnect_GRV()

Dim cmdEdit As New SqlClient.SqlCommand(strUpdate, data.grvConnection)
cmdEdit.Parameters.Add("@BoxStatus", SqlDbType.VarChar,50).Value="FOUND"
cmdEdit.Parameters.Add("@ScanDate", SqlDbType.VarChar,50).Value=Date.Now

Dim IsRowadded As Integer = 0 'Variable to check if row is added
If data.grvConnection.State = ConnectionState.Closed  Then data.grvConnection.Open()
   
IsRowadded = cmdEdit.ExecuteNonQuery()
   If IsRowadded = 1 Or IsRowadded > 1 Then
    ShippingLabebelStatusIsUpdated = True
   Else
    ShippingLabebelStatusIsUpdated = False
   End If
    cmdEdit.Dispose()
data.DestroyConnection() ' just added this now and will test...
   End If
    Return ShippingLabebelStatusIsUpdated
   Catch ex As Exception
  'MessageBox.Show("GRBusiness:- MarkShippingLabelsFound: " & ex.Message)
            Return False
        End Try
    End Function
 
Share this answer
 
Good day,

This solved the problem...

The webservice was creating a new connection object with every scan.

DestroyConnection()        
GRVcon.Dispose()    
End Sub
Sub
 
Share this answer
 
Hi,

Thanks for the response. Can you please explain your comment about:
"Move your ScannedShippingLabel and other variables into SQL parameters as well"
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900