如何让后台工作人员在这工作？ [英] How do I make background worker work in this?

问题描述

以下是我正在使用的代码

The following is the code on which I am working

Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO
Imports System.Text.RegularExpressions
Imports MySql
Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class frmBulkUpload
    Dim conn As OleDbConnection
    Dim dta As OleDbDataAdapter
    Dim dts As DataSet
    Dim excel As String
    Dim openfiledialog As New OpenFileDialog

    Private Sub frmBulkUpload_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        Control.CheckForIllegalCrossThreadCalls = False
        btnSubmit.Enabled = False
        dgwUserMaster.DataSource = Nothing
        lblMaster.Text = "Upload Bulk Salary Payable Transactions"
        ProgressBar1.Visible = False
    End Sub
    Dim inc As Integer
    Dim validator As Boolean
    Dim line As String = ""
    Private Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
        line = ""

        btnSubmit.Enabled = False

        validator = True
        Try
            openfiledialog.Filter = "Excel files (*.xlsx)|*.xlsx|XLS Files (*.xls)|*xls"
            If (openfiledialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                Dim fi As New FileInfo(openfiledialog.FileName)
                Dim FileName As String = openfiledialog.FileName

                excel = fi.FullName


                conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
                dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")

                dgwUserMaster.DataSource = dts
                dgwUserMaster.DataMember = "[Sheet1$]"
                dgwUserMaster.Columns(0).DefaultCellStyle.Format = "dd-MM-yyyy"
                conn.Close()
                If dts.Tables("[Sheet1$]").Columns.Count = 5 Then

                    If dgwUserMaster.Columns(0).HeaderText = "Date of Entry" And dgwUserMaster.Columns(1).HeaderText = "Employee Code" And dgwUserMaster.Columns(2).HeaderText = "Employee Name" And dgwUserMaster.Columns(3).HeaderText = "Amount" And dgwUserMaster.Columns(4).HeaderText = "Remark" Then
                        For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
                            ProgressBar1.Visible = True
                            Dim pattern As String
                            'pattern = ""
                            'Dim regexAmt As New Regex(pattern)
                            'If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(0).Value.ToString) Then
                            'Else
                            '    validator = False
                            '    line &= "The format of amount on line " & x.ToString & " is incorrect." & vbCrLf
                            'End If

                            Try
                                pattern = DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            Catch ex As Exception
                                validator = False
                                line &= "The format of date on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End Try

                            'DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            pattern = "^\d*[0-9](|.\d*[0-9]|,\d*[0-9])?$"
                            Dim regexAmt As New Regex(pattern)
                            If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(3).Value.ToString) Then
                            Else
                                validator = False
                                line &= "The format of amount on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End If


                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value.ToString("dd/MM/yyyy")) Then

                            'Else
                            '    validator = False
                            'End If



                            'Dim pattern As String = "/^(0|[1-9]\d*)(\.\d+)?$/"
                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value) Then

                            'Else
                            '    validator = False
                            'End If

                        Next

                    Else
                        MsgBox("Data Format not supported!")
                    End If
                Else
                    MsgBox("Data Format not supported!")
                End If


            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


        Try

            Dim sqladapter As New MySqlDataAdapter("SELECT MAX(TransactionID)+1 from transactionmaster;", MDIParent1.MysqlConn)

            Dim dt As New DataTable
            sqladapter.Fill(dt)
            If (dt IsNot Nothing AndAlso dt.Rows.Count = 1) Then
                inc = dt.Rows(0)(0)
            Else
                inc = 1
            End If
            dts.Tables("[Sheet1$]").Columns.Add("EmployeeID", GetType(String))
            dts.Tables("[Sheet1$]").Columns.Add("Employee Name as per Database", GetType(String))
            dgwUserMaster.Columns("EmployeeID").Visible = False
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
                Dim sqladapter2 As New MySqlDataAdapter("Select * from ledgermaster where EmployeeID='" & dgwUserMaster.Rows(x).Cells(1).Value & "'", MDIParent1.MysqlConn)
                Dim dt2 As New DataTable
                sqladapter2.Fill(dt2)
                If (dt2 IsNot Nothing AndAlso dt2.Rows.Count = 1) Then
                    dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") = dt2.Rows(0)(0)
                    dts.Tables("[Sheet1$]").Rows(x)("Employee Name as per Database") = dt2.Rows(0)(1)
                Else
                    validator = False
                    line &= "The Employee ID on line " & (x + 1).ToString & " does not exist." & vbCrLf



                End If


            Next


        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        If dgwUserMaster.Rows.Count > 0 Then
            If validator = True Then
                btnSubmit.Enabled = True
            Else
                MsgBox(line)
            End If
        Else
            MsgBox("No data available!")
        End If
        ProgressBar1.Visible = False
    End Sub

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click


        Try
            Dim command As New MySqlCommand
            Dim query As String
            Dim query2 As String
            command.Connection = MDIParent1.MysqlConn
            command.CommandText = "begin;"
            command.CommandText &= "INSERT into transactionmaster (TransactionID, VoucherType,CreatedBy,CreatedOn,DateOfEntry) values"
            query2 = "INSERT into journalentrymaster (TransactionID, DrID, CrID, DrAmount, CrAmount, Remark) values "
            query = ""
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1

                query &= "('" & inc & "','Salary Payable', '" & MDIParent1.UserID & "', '" & Date.Now.ToString("yyyy-MM-dd HH:mm:ss") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Date of Entry").ToString & "')"
                query2 &= "('" & inc & "', '4','" & dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Remark") & "')"
                inc = inc + 1
                If x = dgwUserMaster.Rows.Count - 1 Then
                    query &= ";"
                    query2 &= ";"
                Else
                    query &= ","
                    query2 &= ","
                End If
            Next
            command.CommandText &= query
            command.CommandText &= query2
            command.CommandText &= "commit;"
            command.ExecuteNonQuery()
            MsgBox(dgwUserMaster.Rows.Count.ToString & " entries have been inserted!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

我无法通过后台工作人员使用它。

它涉及两个元素

一个是导入一个excel文件，另一个是在mysql中插入数据。

如果后台工作者不存在，一切正常。我想在进度条中报告进度，因为当应用程序使用大数据时，需要时间



我尝试过：

I am unable to use it through background worker.
It has two elements involved
One is importing an excel file and the other one is inserting the data in mysql.
Everything works fine if the background worker is not present. I want to report progress here in the progressbar because when the app will work with large data, it will take time

What I have tried:

Imports System.ComponentModel
Imports System.Data.OleDb
Imports System.IO
Imports System.Text.RegularExpressions
Imports MySql
Imports MySql.Data
Imports MySql.Data.MySqlClient
Public Class frmBulkUpload
    Dim conn As OleDbConnection
    Dim dta As OleDbDataAdapter
    Dim dts As DataSet
    Dim excel As String
    Dim openfiledialog As New OpenFileDialog
    Dim bckgrndconn As New MySqlConnection
    Private Sub frmBulkUpload_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        bckgrndconn = MDIParent1.MysqlConn
        Control.CheckForIllegalCrossThreadCalls = False
        btnSubmit.Enabled = False
        dgwUserMaster.DataSource = Nothing
        lblMaster.Text = "Upload Bulk Salary Payable Transactions"
        ProgressBar1.Visible = False
    End Sub
    Dim inc As Integer
    Dim validator As Boolean
    Dim line As String = ""
    Private Sub btnGetData_Click(sender As Object, e As EventArgs) Handles btnGetData.Click
        line = ""

        btnSubmit.Enabled = False

        validator = True
        BackgroundWorker1.RunWorkerAsync()
    End Sub

    Private Sub btnSubmit_Click(sender As Object, e As EventArgs) Handles btnSubmit.Click


        Try
            Dim command As New MySqlCommand
            Dim query As String
            Dim query2 As String
            command.Connection = MDIParent1.MysqlConn
            command.CommandText = "begin;"
            command.CommandText &= "INSERT into transactionmaster (TransactionID, VoucherType,CreatedBy,CreatedOn,DateOfEntry) values"
            query2 = "INSERT into journalentrymaster (TransactionID, DrID, CrID, DrAmount, CrAmount, Remark) values "
            query = ""
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1

                query &= "('" & inc & "','Salary Payable', '" & MDIParent1.UserID & "', '" & Date.Now.ToString("yyyy-MM-dd HH:mm:ss") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Date of Entry").ToString & "')"
                query2 &= "('" & inc & "', '4','" & dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Amount") & "','" & dts.Tables("[Sheet1$]").Rows(x)("Remark") & "')"
                inc = inc + 1
                If x = dgwUserMaster.Rows.Count - 1 Then
                    query &= ";"
                    query2 &= ";"
                Else
                    query &= ","
                    query2 &= ","
                End If
            Next
            command.CommandText &= query
            command.CommandText &= query2
            command.CommandText &= "commit;"
            command.ExecuteNonQuery()
            MsgBox(dgwUserMaster.Rows.Count.ToString & " entries have been inserted!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmBulkUpload_Closed(sender As Object, e As EventArgs) Handles Me.Closed

    End Sub



    Private Sub BackgroundWorker1_DoWork(sender As Object, e As System.ComponentModel.DoWorkEventArgs) Handles BackgroundWorker1.DoWork

        Try
            openfiledialog.Filter = "Excel files (*.xlsx)|*.xlsx|XLS Files (*.xls)|*xls"
            If (openfiledialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
                Dim fi As New FileInfo(openfiledialog.FileName)
                Dim FileName As String = openfiledialog.FileName

                excel = fi.FullName


                conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;")
                dta = New OleDbDataAdapter("Select * From [Sheet1$]", conn)
                dts = New DataSet
                dta.Fill(dts, "[Sheet1$]")

                dgwUserMaster.DataSource = dts
                dgwUserMaster.DataMember = "[Sheet1$]"
                dgwUserMaster.Columns(0).DefaultCellStyle.Format = "dd-MM-yyyy"
                conn.Close()
                If dts.Tables("[Sheet1$]").Columns.Count = 5 Then

                    If dgwUserMaster.Columns(0).HeaderText = "Date of Entry" And dgwUserMaster.Columns(1).HeaderText = "Employee Code" And dgwUserMaster.Columns(2).HeaderText = "Employee Name" And dgwUserMaster.Columns(3).HeaderText = "Amount" And dgwUserMaster.Columns(4).HeaderText = "Remark" Then
                        For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
                            ProgressBar1.Visible = True
                            Dim pattern As String
                            'pattern = ""
                            'Dim regexAmt As New Regex(pattern)
                            'If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(0).Value.ToString) Then
                            'Else
                            '    validator = False
                            '    line &= "The format of amount on line " & x.ToString & " is incorrect." & vbCrLf
                            'End If

                            Try
                                pattern = DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            Catch ex As Exception
                                validator = False
                                line &= "The format of date on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End Try
                            BackgroundWorker1.ReportProgress((x + 0.5) * 50 / dgwUserMaster.Rows.Count)
                            'DateTime.Parse(dgwUserMaster.Rows(x).Cells(0).Value).ToString("dd-MM-yyyy")
                            pattern = "^\d*[0-9](|.\d*[0-9]|,\d*[0-9])?$"
                            Dim regexAmt As New Regex(pattern)
                            If regexAmt.IsMatch(dgwUserMaster.Rows(x).Cells(3).Value.ToString) Then
                            Else
                                validator = False
                                line &= "The format of amount on line " & (x + 1).ToString & " is incorrect." & vbCrLf
                            End If
                            BackgroundWorker1.ReportProgress((x + 1) * 50 / dgwUserMaster.Rows.Count)

                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value.ToString("dd/MM/yyyy")) Then

                            'Else
                            '    validator = False
                            'End If



                            'Dim pattern As String = "/^(0|[1-9]\d*)(\.\d+)?$/"
                            'Dim regex As New Regex(pattern)
                            'If regex.IsMatch(dgwUserMaster.Rows(x).Cells(y).Value) Then

                            'Else
                            '    validator = False
                            'End If

                        Next

                    Else
                        MsgBox("Data Format not supported!")
                    End If
                Else
                    MsgBox("Data Format not supported!")
                End If


            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


        ' Try

        Dim sqladapter As New MySqlDataAdapter("SELECT MAX(TransactionID)+1 from transactionmaster;", bckgrndconn)

        Dim dt As New DataTable
            sqladapter.Fill(dt)
            If (dt IsNot Nothing AndAlso dt.Rows.Count = 1) Then
                inc = dt.Rows(0)(0)
            Else
                inc = 1
            End If
            dts.Tables("[Sheet1$]").Columns.Add("EmployeeID", GetType(String))
            dts.Tables("[Sheet1$]").Columns.Add("Employee Name as per Database", GetType(String))
            dgwUserMaster.Columns("EmployeeID").Visible = False
            For x As Integer = 0 To dgwUserMaster.Rows.Count - 1
            Dim sqladapter2 As New MySqlDataAdapter("Select * from ledgermaster where EmployeeID='" & dgwUserMaster.Rows(x).Cells(1).Value & "'", bckgrndconn)
            Dim dt2 As New DataTable
                sqladapter2.Fill(dt2)
                If (dt2 IsNot Nothing AndAlso dt2.Rows.Count = 1) Then
                    dts.Tables("[Sheet1$]").Rows(x)("EmployeeID") = dt2.Rows(0)(0)
                    dts.Tables("[Sheet1$]").Rows(x)("Employee Name as per Database") = dt2.Rows(0)(1)
                Else
                    validator = False
                    line &= "The Employee ID on line " & (x + 1).ToString & " does not exist." & vbCrLf



                End If
            BackgroundWorker1.ReportProgress(50 + ((x + 1) * 50 / dgwUserMaster.Rows.Count))
            Threading.Thread.Sleep(2000)
        Next


        ' Catch ex As Exception
        '   MsgBox(ex.ToString)
        ' End Try


    End Sub

    Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged
        ProgressBar1.Value = e.ProgressPercentage
    End Sub

    Private Sub BackgroundWorker1_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker1.RunWorkerCompleted

        If dgwUserMaster.Rows.Count > 0 Then
            If validator = True Then
                btnSubmit.Enabled = True
            Else
                MsgBox(line)
            End If
        Else
            MsgBox("No data available!")
        End If
        ProgressBar1.Visible = False
    End Sub

    Private Sub BackgroundWorker2_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker2.DoWork

    End Sub

    Private Sub BackgroundWorker2_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker2.ProgressChanged

    End Sub

    Private Sub BackgroundWorker2_RunWorkerCompleted(sender As Object, e As RunWorkerCompletedEventArgs) Handles BackgroundWorker2.RunWorkerCompleted

    End Sub
End Class

推荐答案

\", conn)
dts = New DataSet
dta.Fill(dts, \"[Sheet1

", conn) dts = New DataSet dta.Fill(dts, "[Sheet1

\")

dgwUserMaster.DataSource = dts
dgwUserMaster.DataMember = \"[Sheet1

") dgwUserMaster.DataSource = dts dgwUserMaster.DataMember = "[Sheet1

\"
dgwUserMaster.Columns(0).DefaultCellStyle.Format = \"dd-MM-yyyy\"
conn.Close()
If dts.Tables(\"[Sheet1

" dgwUserMaster.Columns(0).DefaultCellStyle.Format = "dd-MM-yyyy" conn.Close() If dts.Tables("[Sheet1

这篇关于如何让后台工作人员在这工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！