C# Sync MS Access database to sql server

Question

I am trying to set up a synchronization routine in C# to send data from a ms access database to a sql server. MS Access is not my choice it's just the way it is.

I am able to query the MS Access database and get OleDbDataReader record set. I could potentially read each individual record and insert it onto SQL Server but it seems so wasteful.

Is there a better way to do this. I know I could do it in MS Access linking to sql server and perform the update easy but this is for end users and I don't want them messing with access.

EDIT: Just looking at SqlBulkCopy I think that may be the answer if I get my results into DataRow[]

Answer 1

You should harness the power of SET based queries over RBAR efforts.

Look into a SSIS solution to synchronize the data and then schedule the package to run at regular intervals using SQL Server Agent.

You can call an SSIS package from the command line so you can effectively do it from MS Access or from C#.

Also, the SQL Server, the MS Access DB and the SSIS package do not have to be on the same machine. As long as your calling program can see the SSIS package, and the package can connect to the SQL Server and the MS Access DB, you can transfer data from one place to another.

It sounds like what you are doing is ETL. There are several tools that are built to do this and to me, there is little reason to reinvent the functionality. You have SQL Server, therefore you have SSIS. It has a ton of tools for automated transformations, cleanups, lookups, etc. that you can use out of the box.

Unless this is a real cut-and-dry data load and there is absolutely no scope for the complexity of the upload to increase later on (yeah, right!) I would go with a tried and tested ETL tool.

Answer 2

If SQL Server Integration Services isn't an option, you could write out to a temporary text file the data that you read from Access and then call bcp.exe to load it to the database.

Answer 3

I have done something like this before.

I used

OleDbConnection aConnection = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", fileName));
            aConnection.Open();

to open the access db. Then

OleDbCommand aCommand = new OleDbCommand(String.Format("select * from {0}", accessTable), aConnection);
                    OleDbDataReader aReader = aCommand.ExecuteReader();

to execute the read from the table. Then

int fieldCount = aReader.FieldCount;

to get the field count

while (aReader.Read())

to loop the records and

object[] values = new object[fieldCount];
                        aReader.GetValues(values);

to retrieve the values.

Answer 4

can you not transfer Access file to the server and delete it once sync is complete? You can create windows service for that..

Answer 5

I found a solution in .NET that I am very happy with. It allows me to give the access to the sync routine to any user within my program. It involves the SQLBulkCopy class.

private static void BulkCopyAccessToSQLServer
        (CommandType commandType, string sql, string destinationTable)
    {
        using (DataTable dt = new DataTable())
        {
            using (OleDbConnection conn = new OleDbConnection(Settings.Default.CurriculumConnectionString))
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
            {
                cmd.CommandType = commandType;
                cmd.Connection.Open();
                adapter.SelectCommand.CommandTimeout = 240;
                adapter.Fill(dt);
                adapter.Dispose();
            }

            using (SqlConnection conn2 = new SqlConnection(Settings.Default.qlsdat_extensionsConnectionString))
            {
                conn2.Open();
                using (SqlBulkCopy copy = new SqlBulkCopy(conn2))
                {
                    copy.DestinationTableName = destinationTable;
                    copy.BatchSize = 1000;
                    copy.BulkCopyTimeout = 240;
                    copy.WriteToServer(dt);
                    copy.NotifyAfter = 1000;
                }
            }
        }
    }

Basically this puts the data from MS Access into a DataTable it then uses the second connection conn2 and the SqlBulkCopy class to send the data from this DataTable to the SQL Server. It's probably not the best code but should give anyone reading this the idea.