How to use sqlbulkcopy?

Question

0.00/5 (No votes)

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:

VB

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

VB

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 3-Sep-19 18:43pm

Gruja82

Updated 4-Sep-19 11:59am

Patrice T

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RickZeeland · Accepted Answer · 2019-09-03T20:13:00

Solution 1

You can use the .NET SQLBulkCopy class, see article: Transferring Data Using SqlBulkCopy[^]

Posted 3-Sep-19 20:13pm

RickZeeland

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

Gruja82 · Accepted Answer · 2019-09-04T07:52:00

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