0

Actually i have a method where the user is able to choose data from excel file and choose between some headers name which are set statically and are equals to MySQL columns name in a table, then i just collect that data in a DataTable.

Now i would be able to store that data in MySQL database but which would be the best method to insert that data from a DataTable into MySQL? The rows of DataTable could contain more than 50.000/100.000 items.

That's how the DataTable could look's like (The header is choosen dynamically by the user from a combobox) enter image description here

So in this Insert method which would be the best way to add that data? Should i just loop throw dataTable items and make multiple inserts?

Private Sub InsertDB()
    Dim dataTable = ExcelToDT(TxtUpload.Text, ColumnHeader(PanelColumns, Column))
    Dim dbCon As New Odbc.OdbcConnection
    Dim conStr As String = "DSN=WRRPT"
    dbCon.ConnectionString = conStr
    dbCon.Open()




    Dim cmd As New Odbc.OdbcCommand
    With cmd
        .CommandText = "INSERT INTO ? VALUES ?"
        .Connection = dbCon
    End With

    Dim reader As Odbc.OdbcDataReader
    reader = cmd.ExecuteReader

    dbCon.Close()
    reader = Nothing
    cmd = Nothing


End Sub
NiceToMytyuk
  • 3,644
  • 3
  • 39
  • 100
  • Look into using data adapters, both to retrieve the data and save it. – jmcilhinney Aug 12 '19 at 08:02
  • Possible duplicate of [Most efficient way to insert Rows into MySQL Database](https://stackoverflow.com/questions/25323560/most-efficient-way-to-insert-rows-into-mysql-database) – RobertBaron Aug 12 '19 at 18:40

0 Answers0