CODEWITHSUNDEEP
sql-servervb.net.net-4.0
Max Rojas
Max Rojas

Reputation: 21

VB.net application too fast for SQL query loop

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.

Upvotes: 1

Views: 1321

Answers (1)

Dan Pichelman
Dan Pichelman

Reputation: 2332

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()

Upvotes: 3

Related Questions