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?
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.