VB.net application too fast for SQL query loop

Question

I am designing software using Visual Basic with .Net 4.0 with MS SQL server backend for storing data. I have designed a class for the "Employee Wages" section of the database. The purpose of this Class's sub routine is to reset the payroll tables that have been prepared. Each time the user "runs payroll" it creates a backup of the current associated tables using the [SELECT * INTO] method. This part usually works very well and without issue or problem. The problem I run into is when I attempt to [RESET] the payroll, which [DROPS] the "modified" tables and once more uses the [SELECT * INTO] method to re-create the tables from the previously created backups. Here is a sample of the [RESET] code I have written.

Public Sub Reset_Payroll()
    Dim conn As New SqlConnection()
    conn.ConnectionString = _sqlConnector

    Dim varSQL(4) As String
    varSQL(0) = "DROP TABLE [EMPLOYEEWAGES]"
    varSQL(1) = "DROP TABLE [EMPLOYEECHECKS]"
    varSQL(2) = "DROP TABLE [EMPLOYEEREGISTAR]"
    varSQL(3) = "DROP TABLE [EMPLOYEEPAY]"

    For x As Integer = 0 To 3
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            Dim cmd As New SqlCommand(varSQL(x), conn)
            cmd.ExecuteNonQuery()
            Application.DoEvents()
        Catch ex As Exception
            MsgBox("PLEASE REPORT THIS MESSAGE TO CIS DEPT: " & ex.ToString, vbOKOnly, "STAFF WAGES [CREATE WAGES_TEMP] GENERAL EXCEPTION ERROR.")
            'Utilities.CreateMessageAlert(_aspxPage, "PLEASE REPORT THIS MESSAGE TO CIS DEPT: " & ex.ToString, "strKey")
            conn.Close()
        End Try
    Next

    varSQL(0) = "SELECT * INTO [EMPLOYEEWAGES] FROM [EMPLOYEEWAGES_RUNCHK]"
    varSQL(1) = "SELECT * INTO [EMPLOYEECHECKS] FROM [EMPLOYEECHECKS_RUNCHK]"
    varSQL(2) = "SELECT * INTO [EMPLOYEEREGISTAR] FROM [EMPLOYEEREGISTAR_RUNCHK]"
    varSQL(3) = "SELECT * INTO [EMPLOYEEPAY] FROM [EMPLOYEEPAY_RUNCHK]"

    For x As Integer = 0 To 3
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            Dim cmd As New SqlCommand(varSQL(x), conn)
            cmd.ExecuteNonQuery()
            Application.DoEvents()
        Catch ex As Exception
            MsgBox("PLEASE REPORT THIS MESSAGE TO CIS DEPT: " & ex.ToString, vbOKOnly, "STAFF WAGES [CREATE WAGES_TEMP] GENERAL EXCEPTION ERROR.")
            'Utilities.CreateMessageAlert(_aspxPage, "PLEASE REPORT THIS MESSAGE TO CIS DEPT: " & ex.ToString, "strKey")
        Finally
            conn.Close()
        End Try
    Next

    varSQL(0) = "DROP TABLE [EMPLOYEEWAGES_RUNCHK]"
    varSQL(1) = "DROP TABLE [EMPLOYEECHECKS_RUNCHK]"
    varSQL(2) = "DROP TABLE [EMPLOYEEREGISTAR_RUNCHK]"
    varSQL(3) = "DROP TABLE [EMPLOYEEPAY_RUNCHK]"

    For x As Integer = 0 To 3
        Try
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            Dim cmd As New SqlCommand(varSQL(x), conn)
            Application.DoEvents()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("PLEASE REPORT THIS MESSAGE TO CIS DEPT: " & ex.ToString, vbOKOnly, "STAFF WAGES [CREATE WAGES_TEMP] GENERAL EXCEPTION ERROR.")
            'Utilities.CreateMessageAlert(_aspxPage, "PLEASE REPORT THIS MESSAGE TO CIS DEPT: " & ex.ToString, "strKey")
        Finally
            conn.Close()
        End Try
    Next

End Sub

The area I have the problems in are in the second portion of the code, where the system is recreating the Wages, Checks, Registar, and Pay tables from the backup tables [WAGES_RUNCHK]. It appears that the for loop processing between the sql commands is executing too quickly before the previous SQL query has been completed. As such, some of the tables are not being re-created from the backups and data is being lost. I added the application.DoEvents() but have not put it into production. Previously I had to implement the thread.sleep() event to attempt to give it time to process but I am not comfortable with either of these solutions.

Is there any method or way I can implement to pause until the previous query has completed. Allow the system to communicate with the SQL server within the For Loop: "Is previous query complete, If so then [SELECT INTO] the next query. I have been attempting to read on multi-thread processing but still having found a comfortable solution. Any assistance would be greatly appreciated.

Why not put all of that into a stored procedure? It would simplify your code. — Dan Pichelman
– Dan Pichelman, Commented Feb 23, 2015 at 15:54
Thank you for the quick response. Then I would just need to make a call for the SQL server to run the stored procedure. Would this solve the issue of the program moving "too fast" for the queries and not waiting for them to complete? In other words, would calling the stored procedure while the other stored procedure is running still cause a problem? — Max Rojas
– Max Rojas, Commented Feb 23, 2015 at 16:11
I'm sorry JeffO, I don't quite understand the question. Originally the program was being written in ASP.net 4.0 but we migrated over to a Windows Application and now using Windows forms. This code though is in a vb.net 4.0 class that calls the subroutine. I hope that answers the question. — Max Rojas
– Max Rojas, Commented Feb 23, 2015 at 22:03
Hehe: vb4.0 was originally released in August of 1995, but it's okay. We understood what you meant. — Joel Coehoorn
– Joel Coehoorn, Commented Feb 24, 2015 at 0:45

Dan Pichelman · Accepted Answer · 2015-02-23 16:38:49Z

3

I would make a single stored procedure with all 12 statements in it:

CREATE PROCEDURE dbo.resetPayroll 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DROP TABLE [EMPLOYEEWAGES]
    DROP TABLE [EMPLOYEECHECKS]
    DROP TABLE [EMPLOYEEREGISTAR]
    DROP TABLE [EMPLOYEEPAY]
    SELECT * INTO [EMPLOYEEWAGES] FROM [EMPLOYEEWAGES_RUNCHK]
    SELECT * INTO [EMPLOYEECHECKS] FROM [EMPLOYEECHECKS_RUNCHK]
    SELECT * INTO [EMPLOYEEREGISTAR] FROM [EMPLOYEEREGISTAR_RUNCHK]
    SELECT * INTO [EMPLOYEEPAY] FROM [EMPLOYEEPAY_RUNCHK]
    DROP TABLE [EMPLOYEEWAGES_RUNCHK]
    DROP TABLE [EMPLOYEECHECKS_RUNCHK]
    DROP TABLE [EMPLOYEEREGISTAR_RUNCHK]
    DROP TABLE [EMPLOYEEPAY_RUNCHK]
END

Please note that my sample stored procedure does not include any error trapping or reporting. Wrapping that in a transaction might be a really good idea.

Your code then becomes a single call to the database:

Dim cmd as New SqlCommand("resetPayroll", conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.ExecuteNonQuery()

answered Feb 23, 2015 at 16:38

Dan Pichelman

2,3422 gold badges32 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mgw854 Over a year ago

+1 for stored procedures. All too often I see developers getting down on them for all the wrong reasons. I'd agree with Dan's assessment that this should probably be wrapped in a transaction.

Max Rojas Over a year ago

Thank you all very much for recommending stored procedures. I can see the software developing into something much stronger now due to it. Thank you all very much.

Max Rojas Over a year ago

@wewesthemenace I accidently posted the code I changed as an "answer" and not as a comment to ask if I had implemented as suggested. I believe I have the answer. I do not appear to have the same problems that were occurring while testing. Thank you all for your assistance. Now I may need some more assistance by knowing where to mark this as answered. Literally was my first time on this site as I was recommended to come here by a colleague.

Collectives™ on Stack Overflow

VB.net application too fast for SQL query loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related