Click here to Skip to main content
14,300,474 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi everyone. I created vb.net Windows Application which resides on SQL Server database for a perfume store. I need to import existing data from the store which is stored in Excel 97 file. My question is which way is faster: using Stored Procedure with parameters with values from worksheet cells which inserts rows in database table, or using SQLBulkCopy.
Worksheet has around 2000 records.
I have already tried the first solution and its working, but for the second i did not used SQLBulkCopy.
Data looks like this:
LONDA COLOR BOJA ZA KOSU 11 395,00 Ђ
SYOSS COLOR RAZNO 12 530,00 Ђ

What I have tried:

Dim xlapp As Excel.Application
Dim xlworkbook As Excel.Workbook
Dim xlworksheet As Excel.Worksheet
Dim newCulture As System.Globalization.CultureInfo
Dim OldCulture As System.Globalization.CultureInfo


xlapp = New Excel.Application

OldCulture = System.Threading.Thread.CurrentThread.CurrentCulture
newCulture = New System.Globalization.CultureInfo(xlapp.LanguageSettings.LanguageID(Microsoft.Office.Core.MsoAppLanguageID.msoLanguageIDUI))
System.Threading.Thread.CurrentThread.CurrentCulture = newCulture

xlworkbook = xlapp.Workbooks.Open(fileNamepetrovac)
xlworksheet = xlworkbook.Worksheets(1)

For i = 2 To xlworksheet.UsedRange.Rows.Count
    Me.cmduvozartikalapetrovac.Parameters("@naziv").Value = xlworksheet.Cells(i, 2).value.ToString
    Me.cmduvozartikalapetrovac.Parameters("@sifra").Value = xlworksheet.Cells(i, 4).value.ToString
    Me.cmduvozartikalapetrovac.Parameters("@cena").Value = Microsoft.VisualBasic.Left(xlworksheet.Cells(i, 5).value.ToString, Len(xlworksheet.Cells(i, 5).value.ToString) - 3)
    Me.cmduvozartikalapetrovac.Parameters("@plu").Value = xlworksheet.Cells(i, 4).value.ToString

    Me.cmduvozkasapetrovac.Parameters("@naziv").Value = xlworksheet.Cells(i, 2).value.ToString
    Me.cmduvozkasapetrovac.Parameters("@sifra").Value = xlworksheet.Cells(i, 4).value.ToString
    Me.cmduvozkasapetrovac.Parameters("@cena").Value = Microsoft.VisualBasic.Left(xlworksheet.Cells(i, 5).value.ToString, Len(xlworksheet.Cells(i, 5).value.ToString) - 3)
    Me.cmduvozkasapetrovac.Parameters("@ps").Value = "Ђ"

    Me.cmduvozkasapetrovac.ExecuteNonQuery()
    Me.cmduvozartikalapetrovac.ExecuteNonQuery()
Next
xlworkbook.Close()
xlapp.Quit()
System.Threading.Thread.CurrentThread.CurrentCulture = OldCulture


If xlworkbook IsNot Nothing Then
    Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook)
End If
If xlworksheet IsNot Nothing Then
    Runtime.InteropServices.Marshal.ReleaseComObject(xlworksheet)
End If
If xlapp IsNot Nothing Then
    Runtime.InteropServices.Marshal.ReleaseComObject(xlapp)
End If
Posted
Updated 4-Sep-19 11:59am
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

You can use the .NET SQLBulkCopy class, see article: Transferring Data Using SqlBulkCopy[^]
   
Comments
Gruja82 4-Sep-19 9:56am
   
Here is what i have tried. It works faster then 'my way' but i am having trouble with the Dim data() As String = line.Split(","). Problem is that this merges whole row in single record and inserts in first column. What should i use?

'Dim dt As New DataTable
'Dim line As String = Nothing
'Dim i As Integer = 0

''Using sr As StreamReader = File.OpenText("G:\KREZA\petrovac.xls")
'' line = sr.ReadLine
'' Do While line IsNot Nothing
'' Dim data() As String = line.Split(",")
'' If data.Length > 0 Then
'' If i = 0 Then
'' For Each item In data
'' dt.Columns.Add(New DataColumn())

'' Next item
'' i += 1
'' End If
'' Dim row As DataRow = dt.NewRow()
'' row.ItemArray = data
'' dt.Rows.Add(row)

'' End If
'' line = sr.ReadLine

'' Loop

''End Using

'Using sr As StreamReader = File.OpenText("G:\KREZA\petrovac.xls")
' line = sr.ReadLine
' Do While line IsNot Nothing
' Dim data() As String = line.Split(","c)
' If data.Length > 0 Then
' For Each item In data
' dt.Columns.Add(New DataColumn())

' Next item
' Dim row As DataRow = dt.NewRow
' row.ItemArray = data
' dt.Rows.Add(row)
' End If
' line = sr.ReadLine

' Loop
'End Using

'cnparfimerija.Open()
'Using copy As New SqlBulkCopy(cnparfimerija)
' copy.ColumnMappings.Add(0, 0)
' copy.ColumnMappings.Add(1, 1)
' copy.ColumnMappings.Add(2, 2)
' copy.ColumnMappings.Add(3, 3)
' copy.ColumnMappings.Add(4, 4)
' copy.ColumnMappings.Add(5, 5)
' copy.ColumnMappings.Add(6, 6)
' copy.ColumnMappings.Add(7, 7)
' copy.ColumnMappings.Add(8, 8)
' copy.ColumnMappings.Add(9, 9)
' copy.DestinationTableName = "Primer"
' copy.WriteToServer(dt)

'End Using
'cnparfimerija.Close()
Member 14577747 4-Sep-19 17:57pm
   
Grow Your popularity, Free
https://www.seoclerk.com/linkin/260825
Rate this:
Please Sign up or sign in to vote.

Solution 3

Grow Your popularity, Free
https://www.seoclerk.com/linkin/260825
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

I solved it:
Dim tabela As DataTable
tabela = New DataTable

For i As Integer = 0 To xlworksheet.UsedRange.Columns.Count
    tabela.Columns.Add(New DataColumn())
Next

For i As Integer = 1 To xlworksheet.UsedRange.Rows.Count
    tabela.Rows.Add(CInt(xlworksheet.Cells(i, 2).value.ToString), xlworksheet.Cells(i, 1).value.ToString, CInt(xlworksheet.Cells(i, 4).value.ToString), CDbl(Microsoft.VisualBasic.Left(xlworksheet.Cells(i, 3).value.ToString, Len(xlworksheet.Cells(i, 3).value.ToString) - 3)))

Next

xlworkbook.Close()
xlapp.Quit()

cnparfimerija.Open()
Using copy As New SqlBulkCopy(cnparfimerija)
    copy.ColumnMappings.Add(0, 1)
    copy.ColumnMappings.Add(1, 2)
    copy.ColumnMappings.Add(2, 3)
    copy.ColumnMappings.Add(3, 4)

    copy.DestinationTableName = "ArtikliPetrovac"
    copy.WriteToServer(tabela)
End Using


Thanks for help
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100