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()
1 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.