Import csv into database datatable via openfiledialog

I am using Visual Basic Express 2010. I have added a SQL Server Compact 3.5 Database, a Dataset and a Datatable with 11 columns. While setting this up, VBE 2010 posted a DataTableBindingSource, DataTableAdapter, DataTableBindingNavigator, and TableAdapterManager. I added a Windows Form2 and dragged the DataTable onto Form2.

From a button click on Form1, how can I openfiledialog, select a csv file with 11 columns, and import the values into the Database DataTable on Form2? The csv file will always have 11 columns but will vary in the number of rows from 1 to several hundred thousand. Any assistance would be appreciated.

I believe I have the correct code for Button1 on Form1:

Code:

---

Private Sub ToolStripButton1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click

        Dim openFileDialog1 As New OpenFileDialog()
        openFileDialog1.InitialDirectory = "c:\"
        openFileDialog1.Filter = "txt files (*.csv)|*.csv|All files (*.*)|*.*"
        openFileDialog1.FilterIndex = 1
        openFileDialog1.RestoreDirectory = True

        If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

            Dim frm2 As New Form2(openFileDialog1.FileName)
            frm2.Show()

---

This is what I have so far on Form2 and where I am struggling to complete the code to load the datatable:

Code:

---

Imports System.IO
Imports System.Data.OleDb

Public Class Form2

    Public Sub New(ByVal fileName As String)

        InitializeComponent()

        Dim file As String = IO.Path.GetFileName(fileName)
        Dim path As String = IO.Directory.GetParent(fileName).FullName

        Try

            If IO.File.Exists(IO.Path.Combine(path, file)) Then
                Dim ConStr As String = _
                "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                path & ";Extended Properties=""Text;HDR=No;FMT=Delimited\"""
                Dim conn As New OleDb.OleDbConnection(ConStr)
                Dim da As New OleDb.OleDbDataAdapter("Select * from " & _
                file, conn)

---

Here is an article on doing bulk inserts

http://www.codeproject.com/Tips/3095...-amount-of-dat

I would only use this method if there are no chances of violating any constraints at the database level.

The article provided by kevininstructor is going to be the fastest if the file is very large. I ran into the same situation in this thread:

http://www.vbforums.com/showthread.p...ter&highlight=

SqlBulkCopy is only for SQL Server, not SQL Server CE.

If you've already got a Data Source then what you need to do is create an instance of the appropriate DataTable, populate it from your CSV file and then use the corresponding table adapter to save all the data from the DataTable to the database. That's going to look something like this:

Code:

---

Dim table As New ThingDataTable

Using connection As New OleDbConnection("connection string here"),
      inputAdapter As New OleDbDataAdapter("SQL query here", connection)
    'Keep the RowState of each DataRow as Added so they are ready to insert into the database.
    inputAdapter.AcceptChangesOnFill = False

    'Get the data.
    inputAdapter.Fill(table)
End Using

Dim outputAdapter As New ThingTableAdapter

'Save the data.
outputAdapter.Update(table)

---

You need to change the type names as required for your Data Source and add the appropriate connection string and SQL code. You'll also need some exception handling on the data retrieval portion unless you are absolutely guaranteed that the data conforms to your table schema.

Thank you, jmcilhinney, for letting me know that SQLBulkCopy is not available for my situation and providing me with the code example and explanation. That should get me on the right path. Thanks! :)