VB.net SQL match against Excel data to datagridview

Question

I am importing an Excel sheet where the first column will contain product numbers. I match those numbers to my SQL database and display the product number and description of the matching products to a datagridview.

The code works, but if there are a lot of products it can take a long time. What I'd really like to do is have it read the Excel sheet and add the description directly into the Excel sheet after the last column with data. Or at very least optimize the code to run quicker.

    Dim v As New FormRec
    Dim x As New FormRec
    Dim MyConnection As OleDb.OleDbConnection
    Dim DtSet As DataSet
    Dim MyCommand As OleDb.OleDbDataAdapter
    MyConnection = New OleDb.OleDbConnection(String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", workbook))
    MyCommand = New OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
    MyCommand.TableMappings.Add("Table", "match")
    DtSet = New DataSet
    MyCommand.Fill(DtSet)
    v.dgv.DataSource = DtSet.Tables(0)
    MyConnection.Close()

    Dim col As New DataGridViewTextBoxColumn
    col.HeaderText = "Product Number"
    col.Name = "prodnmbr"
    col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
    col.DefaultCellStyle.Font = New Font("Arial", 14)
    x.dgv.Columns.Add(col)

    Dim co2 As New DataGridViewTextBoxColumn
    co2.HeaderText = "Description"
    co2.Name = "descrip"
    co2.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
    co2.DefaultCellStyle.Font = New Font("Arial", 14)
    x.dgv.Columns.Add(co2)

    For index As Integer = 0 To v.dgv.RowCount - 1
        Using conn As New SqlConnection(myconnection)
            Dim commandText As String = "select productnmbr,descr from database where productnmbr = @prodnmbr"
            Using command As SqlCommand = New SqlCommand(commandText, conn)
                conn.Open()
                With command.Parameters
                    .AddWithValue("@prodnmbr", v.dgv.Rows(index).Cells(0).Value.ToString)
                End With
                Using sqlread As SqlDataReader = command.ExecuteReader
                    If sqlread.Read Then
                        Dim match As String = sqlread.GetString(0)
                        If match = v.dgv.Rows(index).Cells(0).Value Then
                            x.dgv.Rows.Add(v.dgv.Rows(index).Cells(0).Value.ToString, sqlread.GetString(1))
                            x.dgv.Rows(index).DefaultCellStyle.BackColor = Color.LightGreen
                        End If
                    Else
                        x.dgv.Rows.Add(v.dgv.Rows(index).Cells(0).Value.ToString, "N/A")
                        x.dgv.Rows(index).DefaultCellStyle.BackColor = Color.Red
                    End If
                End Using
            End Using
        End Using
    Next
    x.ShowDialog()

Answer 1

You are repeatedly opening the DB connection in a loop:

For index As Integer = 0 To v.dgv.RowCount - 1
    Using conn As New SqlConnection(myconnection)
        Dim commandText As String = "select productnmbr,descr from database where productnmbr = @prodnmbr"
        Using command As SqlCommand = New SqlCommand(commandText, conn)
            conn.Open()
            With command.Parameters
                .AddWithValue("@prodnmbr", v.dgv.Rows(index).Cells(0).Value.ToString)
            End With
            ...
        End Using
    End Using
Next

There is overhead involved when opening a connection. You could just open it once, leave it open, do your stuff, then dispose of it:

Using conn As New SqlConnection(myconnection)
    For index As Integer = 0 To v.dgv.RowCount - 1
        ' do something
    Next
End Using

Just moving code a bit should improve performance.

Something else: no need to read values from the DGV. This could even freeze your UI. Read the rows from the dataset instead, that is the one table it contains (match).

I see that you are also adding rows to the DGV but the better way would be to feed the underlying source, that is your datatable, possibly a bindingsource. Then let the DGV refresh itself.

I am not familiar with your SQL table but if the product table is not too large, I might be tempted to load it to a second datatable, then compare both datatable with LINQ for example (you will find code samples on Stack Overflow). The benefit is that all your data will be preloaded to memory and comparison should be quite fast if you're not doing it row by row. It depends on how much data there is.

Or do it differently: load your Excel file to a temporary table in your SQL server. Then compare the tables by doing a join, a stored procedure if you like.