[RESOLVED] SqlBulkCopy from DataGridView

[ConfusedAgain]

I have a .csv file that is selected using the OpenFileDialog that then populates a DataGridView. The .csv file is then visible for the user to check a number of things. This part works fine.

What I then need is once it is checked the user then needs to click a button to import this into my SQL Server table (CSV_Import). I have looked at SQLBulkCopy but am failing to get the data imported.

Here is some code I have picked up and I am guessing that it is the bc.WriteToServer(dt) needs correcting within the brackets but I am at a loss at this point.

bc.BatchSize = dt.Rows.Count
bc.DestinationTableName = "CSV_Import"
bc.WriteToServer(dt)

bc.Close()
myConnection.Close()

Please advise, thanks.

[jmcilhinney]

We could guess, but wouldn't it make more sense for you to tell us what actually happens when you run that code? If there's an error then there's an error message, provided as an aid to diagnosing the issue. If you want us to diagnose the issue then it would make sense to pass that aid on to us.

[ConfusedAgain]

Fair point and it may have been misleading what I have written.

If I run the code it comes up wth no error because it is not picking up any data. I picked this piece of code up from the net and am not sure if that is what I need or not.

I have data in a datagrid table that I want to import into a SQL Server table. I was told to use SQL BulkCopy. But I dont know what I am meant to do with it or how it works so if anyone can guide me through it that would be great.

My datagridview has 4 to 18 coloumns and populates fine with the csv file. The table I want to import this data into has 18 columns which is the maximum number it would be.

The user will click the button to import the data to the table. So on the click event I need to run sql bulkcopy.

[jmcilhinney]

Right then. In that code, 'dt' is a DataTable. You need to populate a DataTable. The obvious option is to use ADO.NET to read your CSV file straight into a DataTable and then bind that to the grid. Any edits the user makes are then pushed to the DataTable automatically.

Follow the Database FAQ link in my signature and check out some of the ADO.NET resources there see how to populate a DataTable. Note that you will use an OleDbDataAdapter and you can find the appropriate connection string for a CSV file at www.connectionstrings.com. Once populate, assign the Datatable to the DataSource of the grid to auto-populate it.

I think you'll find that you need to set the AcceptChangesDuringFill property of the adapter to False too, although I could be wrong about that.

[ConfusedAgain]

That may be obvious to you but not to me.

If I can read the CSV file into the DataTable I would then be able to copy that using the bc.WriteToServer(dt) code.

Can you advise how I populate the table with the csv file?

[ForumAccount]

How are you currently loading the file? I believe what jmcilhinney is getting at, is use an OleDbDataAdapter and fill a DataTable object. You will need to specify the a) command text (aka "SELECT * FROM FileName.txt") and b) the connection string which can be retrieved here.

[techgnome]

How did you get it into the GDV in the first place?

-tg

[ConfusedAgain]

Ok Ive done it. Here is the code on the button click event.

I have a datagridview and button on the form. By clicking the button this then imports data into a table that is already there with the maximum number of fields ever needed.

Thanks you all.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim myConnectionString As String = "Data Source=localhost;Initial Catalog=BulkIt;User Id=sa;"
        Dim myConnection As SqlConnection = New SqlConnection(myConnectionString)
        Dim csvInputFile As String = "C:\ Test\Bulk Import\filetest1.csv"
 
        Dim dt As New DataTable()
        Dim line As String = Nothing
        Dim i As Integer = 0
 
        Using sr As StreamReader = File.OpenText(csvInputFile)
            line = sr.ReadLine()
            Do While line IsNot Nothing
                Dim data() As String = line.Split(","c)
                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()
                DataGridView1.DataSource = dt
            Loop
 
        End Using
 
        myConnection.Open()
        Dim bc As New SqlBulkCopy(myConnection)
 
        MsgBox(dt.Rows.Count)
 
        bc.BatchSize = dt.Rows.Count
        bc.DestinationTableName = "CDR_Import"
        bc.WriteToServer(dt)
 
        bc.Close()
        myConnection.Close()
 
 
 
        DataGridView1.DataSource = dt
 
 
    End Sub