Transfering data from SQL to Access using C#

Question

For my current project in c# I need to transfer data from a SQL-Database to an Access-Database. For now I load the data into a DataSet using a SqlDataAdapter. After that I loop through the entries and insert them into the Access-DB using OleDb:

// Load data from SQL
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter("select goes here", sqlConnection);
adapter.Fill(ds);

// Prepare the Insert Command
oleDBCommand = "Insert into...";
oleDBCommand.Parameters.Add(new OleDbParameter(...));

// Insert every row from the DataSet
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    // Update Parameters and Execute
    oleDBCommand.Parameters[0].Value = ds.Tables[0].Rows[i].ItemArray[0];
    oleDBCommand.ExecuteNonQuery();
}

This approach works fine, however it feels clumsy and slow. So I was wondering if there is another better way to transfer data from one DB to another.

You're going from SQL to Access (which isn't supported anymore)... it's maybe a good thing that it feels clumsy and slow ;) — Nathan
– Nathan, Commented May 5, 2011 at 7:20
It feels as if there has to be a better solution than just loop through every row and call an insert Statement :s — Syjin
– Syjin, Commented May 5, 2011 at 7:56
@Nathan: what does the parenthetical "isn't supported anymore" mean? Access is certainly supported, as is interoperation with SQL Server. — David-W-Fenton
– David-W-Fenton, Commented May 6, 2011 at 2:18

icaptan · Accepted Answer · 2011-05-07 07:47:50Z

1

Use SqlDataReader: SqlDataReader runs faster than SqlDataAdapter
Use Transaction: use a transaction and bind each command to this transaction. after finishing the insert commands, commit the transaction, which may run faster.

edited May 7, 2011 at 7:47

answered May 5, 2011 at 7:42

icaptan

1,5651 gold badge19 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Syjin Over a year ago

Thank you. It was helpful and I'll go with it as an answer, although it wasn't exactly what I was looking for...

Christian Specht · Accepted Answer · 2011-05-10 20:30:07Z

0

Another idea:
If it's always the same Access database and you import your data always from the same table(s), you can link the SQL Server tables in Access once.

If a SQL Server table is linked, you can use it in the MDB just like a local table.
Then, you can just insert directly from the linked table into the local table by running this query via OleDB in the Access database:

insert into LocalAccessTable (Column1, Column2)
select Column1, Column2
from LinkedSqlServerTable

answered May 10, 2011 at 20:30

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Collectives™ on Stack Overflow

Transfering data from SQL to Access using C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related