threading in oledb connection, i dont know how to make a thread in this situation. its very slow

Question

getting data from xlsx file and inserting in oledb connection. i want to make it faster by adding thread or multiple thread if possible. here is my code ... any idea please i need help

 Public Sub readEEdata()
    Dim eedatapath As String = MainForm.TxtEEData.Text
    Dim tempinfo As New infocls
    Dim fi As IO.FileInfo = New IO.FileInfo(MainForm.TxtEEData.Text)
    Using excelPackage As New ExcelPackage(fi)
            Dim firstWorksheet As ExcelWorksheet = excelPackage.Workbook.Worksheets(1)
            Dim colCount As Integer = firstWorksheet.Dimension.End.Column
            Dim rowCount As Integer = firstWorksheet.Dimension.End.Row
            For row As Integer = 2 To rowCount                   
                With tempinfo
                    MainForm.LblStatus.Text = "Importing EE data: " & row & " " & GetValue(firstWorksheet, "A" & row.ToString)
                    .ID = GetValue(firstWorksheet, "A" & row.ToString)
                    .Fname = GetValue(firstWorksheet, "D" & row.ToString)
                    .lname = GetValue(firstWorksheet, "B" & row.ToString)
                    .mname = GetValue(firstWorksheet, "E" & row.ToString)
                    .tinum = GetValue(firstWorksheet, "F" & row.ToString)
                    If .Fname <> Nothing AndAlso .Fname.Contains("'") Then .Fname = .Fname.Replace("'", "´")
                    If .lname <> Nothing AndAlso .lname.Contains("'") Then .lname = .Fname.Replace("'", "´")
                    If .mname <> Nothing AndAlso .mname.Contains("'") Then .mname = .Fname.Replace("'", "´")
                End With
                If tempinfo.ID <> Nothing And tempinfo.Fname <> Nothing Then
                    saveEEData(tempinfo)
                End If
            Next
    End Using


  Public Sub saveEEData(ByVal infoclass As infocls)
    masterConnection = New OleDb.OleDbConnection(connString)
    masterConnection.Open()
    masterCommand.Connection = masterConnection
    masterCommand.CommandText = "Insert into EEData Values('" & infoclass.ID & "', '" & infoclass.lname & "', '" & infoclass.Fname & "','" & infoclass.mname & "','" & infoclass.tinum & "')"
    masterCommand.ExecuteNonQuery()
    masterConnection.Close()
  End Sub

Didn't you notice in the preview pane that halt of your code was not formatted properly? — Mary, Nov 18 '19 at 07:31
Empty Catches are the devils workshop. They just swallow errors and you don't know what went wrong. — Mary, Nov 18 '19 at 07:42
Before you resort to threads, determine exactly where the bottleneck is. Is the database local? Are the inserts slow or some other part of the code? — Mary, Nov 18 '19 at 07:47
Is the target database Access? There should be a way to insert all or multiple rows at once to speed it up https://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c. Should be even faster to query the excel data and insert it directly without it going trugh your application in the middle. — Slai, Nov 18 '19 at 08:37
You're seriously opening and closing a database connection 10,000 times?! Com one! At the very least, open the connection once at the start and then close it once when you're done. What might also help a little is creating a single command with parameters and then simply setting the parameter values each time. Even better might be top populate a `DataTable` and then save the lot in one go with a data adapter. — jmcilhinney, Nov 18 '19 at 08:38
@jmcilhinney Ike and Mike, we think alike. I was writing the same thing in my answer at the same time you were adding your comment. I feel smart when I say the same as you. :-) — Mary, Nov 18 '19 at 08:46

score 0 · Accepted Answer · answered Nov 18 '19 at 08:41

0

I don't know what you are doing with that label but it just keeps getting overwritten in you loop. The only thing you will see is the last iteration.

There is no need to assign the data to the properties of a class. Just assign them directly to the parameters' values.

Using...End Using block ensure that your database objects are closed and disposed even it there is an error. Always use parameters in you sql statements. You will need to check in your database for the correct datatypes and field sizes. The parameters are added once outside the loop and only the values change inside the loop.

You don't have to worry about single quotes in names when you are using parameters.

This might speed things up because you were opening and closing the connection 10,000 times! Argh!

Public Sub readEEdata()
    Dim eedatapath As String = MainForm.TxtEEData.Text
    'Don't access the text box a second time, you already have the value 
    Dim fi As IO.FileInfo = New IO.FileInfo(eedatapath)
    Using excelPackage As New ExcelPackage(fi)
        Try
            Dim firstWorksheet As ExcelWorksheet = excelPackage.Workbook.Worksheets(1)
            Dim colCount As Integer = firstWorksheet.Dimension.End.Column
            Dim rowCount As Integer = firstWorksheet.Dimension.End.Row
            Using cn As New OleDbConnection(connString),
                cmd As New OleDbCommand("Insert into EEData Values(@ID, @Lname, @Fname,@Mname,@tinum);", cn)
                With cmd.Parameters
                    .Add("@ID", OleDbType.Integer)
                    .Add("@Lname", OleDbType.VarChar, 100)
                    .Add("@Fname", OleDbType.VarChar, 100)
                    .Add("@Mname", OleDbType.VarChar, 100)
                    .Add("@tinum", OleDbType.VarChar, 100)
                End With
                cn.Open()
                For row = 2 To rowCount
                    cmd.Parameters("@ID").Value = GetValue(firstWorksheet, "A" & row.ToString)
                    cmd.Parameters("@Lname").Value = GetValue(firstWorksheet, "B" & row.ToString)
                    cmd.Parameters("@Fname").Value = GetValue(firstWorksheet, "D" & row.ToString)
                    cmd.Parameters("@Mname").Value = GetValue(firstWorksheet, "E" & row.ToString)
                    cmd.Parameters("@tinum").Value = GetValue(firstWorksheet, "F" & row.ToString)
                    cmd.ExecuteNonQuery()
                Next
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Using
End Sub

Another approach would be to create and fill a datatable. Then use a DataAdapter to do the Update all at once.

answered Nov 18 '19 at 08:41

Mary

14,926
3
18
27

thanks it helps but it doesnt get all the rows im trying to insert. – Chao Nov 18 '19 at 08:53
Did you get an error? I am not familiar with EPPlus so I have no idea if rowcount is correct. What rows does it miss? – Mary Nov 18 '19 at 08:56
id didnt get any error but it only insert upto 10rows only. i think the row count is correct. – Chao Nov 18 '19 at 08:58
Are you sure of the value of rowcount? Put a breakpoint and check the value. – Mary Nov 18 '19 at 08:59
and what was the value of rowcount? – Mary Nov 18 '19 at 09:01
4102 is the rowcount – Chao Nov 18 '19 at 09:02
@Chao seems more likely that the worksheet is not correct. `.Worksheets(1)` might not be the first tab visually – Slai Nov 18 '19 at 09:03
Did you add the message box to the catch? This is really not proper but at least it is something. – Mary Nov 18 '19 at 09:03
I noticed that. Most things in .net are zero based and that darn index seems to depend on the .net version and something else. Wow. Inconsistencies in EPPlus – Mary Nov 18 '19 at 09:05
before the time that im opening connection thousand thime (lol). my insert code is correct but very slow. so i think rowcount is not the error – Chao Nov 18 '19 at 09:11
Where is the database? I assume it is not on the computer where the code is running. How remote is it. Can there be network problems? – Mary Nov 18 '19 at 09:13
the database is in the current directory – Chao Nov 18 '19 at 09:16
So it is on the same machine that is running the code. Nothing else is accessing this database. Can you comment out the try...catch...messagebox...end try. See it it crashes. – Mary Nov 18 '19 at 09:20
Put in the break point again and step through to see what happens after 10 records. Check all the values. – Mary Nov 18 '19 at 09:22
i knew it some rows doesnt have a value – Chao Nov 18 '19 at 09:25

threading in oledb connection, i dont know how to make a thread in this situation. its very slow

1 Answers1