Как мне заставить фонового работника работать в этом направлении?
Ниже приведен код, над которым я работаю
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.
Все работает нормально, если фонового работника нет. Я хочу сообщить о прогрессе здесь, в панели прогресса, потому что, когда приложение будет работать с большими данными, это займет некоторое время
Что я уже пробовал:
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
Richard Deeming
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") & "')"
Не делай этого так! Ваш код уязвим для SQL-инъекция[^]. НИКОГДА используйте конкатенацию строк для построения SQL-запроса. ВСЕГДА используйте параметризованный запрос.
Все, что вы хотели знать о SQL-инъекции (но боялись спросить) | Трой Хант[^]
Как я могу объяснить SQL-инъекцию без технического жаргона? | Обмен Стеками Информационной Безопасности[^]
Шпаргалка по параметризации запросов | OWASP[^]