Quickly Export Datagrid to SQL Table

[slpefanis]

Hi All

I am writing an import program to import data from an access database to a SQL database. The table structure in the SQL and Access are the exact same with the same field names.
I was wondering if there was a quick way to get the data into the SQL table without have to loop through each record?

Currently:
1. User selects the file
2. Use a oledb connection i load the data into a DataAdapter
3. Datadapter is bound to the Datagrid

Normally i would loop through each record in the datagrid and insert it into the table but i was wondering seeing as the column names are the same if i could save the messing about with loops and insert statements using 30 column names.

Code to load the Data is below

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & database & ";Jet OLEDB:Database Password=bekdemir"
            sql = "SELECT * FROM Dockets"
            connection = New OleDbConnection(ConnectionString)
            command = New OleDbCommand(sql, connection)
            Data = New OleDbDataAdapter(sql, connection)

            Try
                connection.Open()
                Data.Fill(Resultset, "WBData")
                btnImport.Enabled = True
            Catch ex As Exception
                MessageBox.Show(ex.ToString, "Error Loading Data", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                If Not (connection Is Nothing) Then connection.Dispose()
                connection = Nothing
                If Not (command Is Nothing) Then command.Dispose()
                command = Nothing
                If Not (Data Is Nothing) Then Data.Dispose()
                Data = Nothing
            End Try
            dgdata.DataSource = Resultset.Tables("WBData")

EDIT: Thinking something like the SQLBULKCOPY but unsure on how to take it from my datadapter so a datareader (based on the code i found at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx)

EDIT2: Would be nice if i could do it from the Datagrid as the users may want to edit the data slightly before they import it.
Thanks

[lolafuertes]

Some times the thigs are easier, or not.

In SQL you can use an external rowset using OpenDataSource to obtain the data to be inserted into an existing table.

Your SQL Sentence can be replaced by some thing like:

sql = "INSERT INTO DestinationSQLTable SELECT * FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=""" & database & """;Jet OLEDB:Database Password=bekdemir;')"

Hope this helps

[slpefanis]

Thanks, that what I'm looking for to an extent. The users want to be able to "clean" the data in the datagrid before they import it to the SQL table. i.e. Import from the access db to the datagrid, clean the data then export to the SQL.

I have done it with a loop now, and it's not too slow as there is generally only 100 records or so. More curios if there's an easier way :)

[lolafuertes]

Not from my knowledge.

Do you issue an insert command for each record in the datagrid, using a transaction to isolate it inb case of duplicates?

I would suggest to create a long multi-insert SQL command, ie each 100 inserts to improve it.

Hope this helps