Import records from CSV file into SQL Server 2008 R2 using VB .Net 2010

[Eager_Beever]

I need to import records from a comma delimited CSV file into an existing table in SQL Server R2 database using Visual Basic .Net 2010. Existing records in the table are to be deleted prior to import. I have been able to create an in-memory temporary DataTable and populate the records from CSV file using TextFieldParser. I have checked it by binding the in-memory DataTable to a DataGridView. But I am clueless in the second part i.e. how to insert records into the SQL table from the in-memory DataTable.

I have done the following:

        Dim TextFileReader As New TextFieldParser("C:\csvtosql\StockVB\VFPFiles\ExpSysusers.csv")
        
        TextFileReader.TextFieldType = FileIO.FieldType.Delimited
        TextFileReader.SetDelimiters(",")

        Dim TextFileTable As DataTable = Nothing

        Dim Column As DataColumn
        Dim Row As DataRow
        Dim UpperBound As Int32
        Dim ColumnCount As Int32
        Dim CurrentRow As String()

        Dim intCount As Integer
        intCount = 0
        CurrentRow = TextFileReader.ReadFields() ' Ignore the header
        While Not TextFileReader.EndOfData
            Try
                CurrentRow = TextFileReader.ReadFields()

                If Not CurrentRow Is Nothing Then
                    ''# Check if DataTable has been created
                    If TextFileTable Is Nothing Then
                        TextFileTable = New DataTable("TextFileTable")
                        ''# Get number of columns
                        UpperBound = CurrentRow.GetUpperBound(0)
                        ''# Create new DataTable
                        For ColumnCount = 0 To UpperBound
                            Column = New DataColumn()
                            Column.DataType = System.Type.GetType("System.String")
                            Column.ColumnName = "Column" & ColumnCount
                            Column.Caption = "Column" & ColumnCount
                            Column.ReadOnly = True
                            Column.Unique = False
                            TextFileTable.Columns.Add(Column)
                        Next
                    End If
                    intCount = intCount + 1
                    Row = TextFileTable.NewRow
                    For ColumnCount = 0 To UpperBound
                        Row("Column" & ColumnCount) = CurrentRow(ColumnCount).ToString
                    Next
                    TextFileTable.Rows.Add(Row)

                End If

            Catch ex As Exception
                MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
            End Try
        End While
        TextFileReader.Dispose()
        DataGridView1.DataSource = TextFileTable
        MsgBox(intCount & " Records Read.")

Can anybody please help/guide me??

[jmcilhinney]

Use a data adapter to save all the data in the DataTable to the database. For an example, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

[Eager_Beever]

I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.

For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
        Dim Column0 As String = TextFileTableDataRow("Column0")
        Dim Column1 As String = TextFileTableDataRow("Column1")
        Dim Column2 As Int16 = TextFileTableDataRow("Column2")
        Dim Column3 As Boolean = TextFileTableDataRow("Column3")

        Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
        Dim SqlconnectionString As String = gcconnect

        Using connection As New SqlClient.SqlConnection(SqlconnectionString)
            Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)

            ' create command objects and add parameters
            With cmd.Parameters
                .AddWithValue("@Operator", Column0)
                .AddWithValue("@OpPassword", Column1)
                .AddWithValue("@SecurityLevel", Column3)
                .AddWithValue("@LoggedOn", Column7)
            End With

            Dim adapter As New SqlClient.SqlDataAdapter()
            adapter.InsertCommand = cmd

            '--Update the original SQL table from the datatable
            Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
        End Using
    Next

I am getting the following error: "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."

[techgnome]

Fastest way is to use the SQLBulkCopy class and the WriteToServer method...

-tg

[jmcilhinney]

I tried the following code to read the records from the DataTable and insert into the SQL Table. But it seems that only the first record is being added.

For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
        Dim Column0 As String = TextFileTableDataRow("Column0")
        Dim Column1 As String = TextFileTableDataRow("Column1")
        Dim Column2 As Int16 = TextFileTableDataRow("Column2")
        Dim Column3 As Boolean = TextFileTableDataRow("Column3")

        Dim strSqlQry As String = "INSERT INTO Personnel (Operator,OpPassword,SecurityLevel,Active) VALUES (@Operator,@OpPassword,,@SecurityLevel,@Active)"
        Dim SqlconnectionString As String = gcconnect

        Using connection As New SqlClient.SqlConnection(SqlconnectionString)
            Dim cmd As New SqlClient.SqlCommand(strSqlQry, connection)

            ' create command objects and add parameters
            With cmd.Parameters
                .AddWithValue("@Operator", Column0)
                .AddWithValue("@OpPassword", Column1)
                .AddWithValue("@SecurityLevel", Column3)
                .AddWithValue("@LoggedOn", Column7)
            End With

            Dim adapter As New SqlClient.SqlDataAdapter()
            adapter.InsertCommand = cmd

            '--Update the original SQL table from the datatable
            Dim iRowsInserted As Int32 = adapter.Update(TextFileTable)
        End Using
    Next

I am getting the following error: "Violation of PRIMARY KEY Constraint 'PK_Personnel'. Cannot insert duplicate key in object 'dbo.Personnel'."

You don't call Update in a loop. The whole point of a data adapter is to save an entire DataTable worth of changes in one go. Did you read my CodeBank thread? If not, why not? If so, why are you not doing what it does?

[Eager_Beever]

@jmcilhinney. I am sorry. I did went through your CodeBank link but got confused between handling a DataTable (temporary in-memory table) and a regular (physical) Table in a Database. I re-read the thread again and will now try to follow your lead. Now my aim is to read the DataTable (all records) and insert the records into the table. Hopefully I will be able to solve my problem. I think I am also facing problem due to the presence of a primary key in the target table. But my input DataTable contains unique records only.

Sorry again for being a pain. I am a newbie in VB.Net and also facing health issues (Thyroid induced difficulty in concentration and a little memory lapse).

Thanks again for your patience.

[Eager_Beever]

@jmcilhinney. I followed your example code #7 in your CodeBank (Retrieving and Saving Data in Databases) and did the following:

For Each TextFileTableDataRow As DataRow In TextFileTable.Rows
            Dim Column0 As String = TextFileTableDataRow("Column0")
            Dim Column1 As String = TextFileTableDataRow("Column1")
            Dim Column2 As String = TextFileTableDataRow("Column2")
            Dim Column3 As Int16 = TextFileTableDataRow("Column3")
            Dim Column4 As String = TextFileTableDataRow("Column4")
            Dim Column5 As Int16 = TextFileTableDataRow("Column5")
            Dim Column6 As Boolean = TextFileTableDataRow("Column6")
            Dim Column7 As Boolean = TextFileTableDataRow("Column7")

            Dim SqlconnectionString As String = gcconnect
            Using connection As New SqlClient.SqlConnection(SqlconnectionString)
                Using command As New SqlCommand("INSERT INTO Personnel ([Operator], OpPassword, OpName, SecurityLevel, BriefCode, Branch, Active, LoggedOn) VALUES (@Operator, @OpPassword, @OpName, @SecurityLevel, @BriefCode, @Branch, @Active, @LoggedOn)", connection)
                    command.Parameters.AddWithValue("@Operator", Column0)
                    command.Parameters.AddWithValue("@OpPassword", Column1)
                    command.Parameters.AddWithValue("@OpName", Column2)
                    command.Parameters.AddWithValue("@SecurityLevel", Column3)
                    command.Parameters.AddWithValue("@BriefCode", Column4)
                    command.Parameters.AddWithValue("@Branch", Column5)
                    command.Parameters.AddWithValue("@Active", Column6)
                    command.Parameters.AddWithValue("@LoggedOn", Column7)

                    connection.Open()
                    command.ExecuteNonQuery()
                End Using
            End Using
        Next

Now my code is working OK, though I know it needs refining. I also noticed that the SQL Table field name "Operator" is a reserved word in Visual Basic and therefore I enclosed it within square braces.

Is there a better approach (more efficient) to do the same??

Thanks again for your patience and guidance.

[jmcilhinney]

That is NOT what you should be doing. You should be following one of the examples in post #3 of that thread. You populate a DataTable with ALL the data and you save it in a single batch. That post in that thread includes an example using a data adapter and another using SqlBulkCopy, which tg recommended earlier. There's no real advantage unless you have a large amount of data though.