I want to implement the use of SqlBulkCopy to reduce the insertion times in a migration I'm doing from a system.
In StrArray(x)
I get the name of the columns as they are in the table where I want to insert the information, which I store in a GridView, and in StrArray(w)
the name of the columns is exactly the same as in the GridView, this is already stored in the .csv file.
Dim directorio as String = "C:\Proyect\Carga.csv"
Dim sr As StreamReader = New StreamReader(directorio)
Dim line As String = sr.ReadLine()
Dim strArray As String() = line.Split(",")
Dim dt As DataTable = New DataTable()
Dim row As DataRow
For Each s As String In strArray
dt.Columns.Add(New DataColumn())
Next
Do
row = dt.NewRow()
row.ItemArray = line.Split(",")
dt.Rows.Add(row)
i = i + 1
line = sr.ReadLine()
Loop While Not line = String.Empty
Dim bc As SqlBulkCopy = New SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, Nothing)
Dim x as string = 0
For w = 1 to StrArray - 1
bc.ColumnMappings.Add(strArray(w), strArray(x))
x = x + 1
Next
bc.DestinationTableName = "dbo." & Nombre_Tabla & ""
bc.WriteToServer(dt)
But, when trying to make the insert, it throws an exception:
La ColumnName 'ID_Carga' no coincide con ninguna columna del origen de datos.
Since StrArray(w)
and StrArray(x)
have exactly the same names in relation Table-Table when using ColumnMappings.Add , Why do you give me that error and how can I solve it?
I'm using SQL Server 2008 R2 in a VS2017 project.