Transferring Data from SQL Server Compact to Full SQL Server

Data and Databases
1

Hey everyone! I’m working on a project where I need to move data from SQL Server Compact (.sdf files) to a full SQL Server database. My setup includes:

  • Visual Studio 2008
  • SQL Server 2005
  • SQL Server Compact Framework 3.5
  • Windows CE 6.0 devices

The mobile devices use .sdf databases, and I need to transfer this data to our main SQL Server when the workers return to the office. Right now, I’m doing it like this:

public void MoveData()
{
    using (var ceReader = new SqlCeCommand("SELECT * FROM Workers", compactDb).ExecuteReader())
    {
        var serverCmd = new SqlCommand("", mainDb);
        while (ceReader.Read())
        {
            serverCmd.CommandText = $"INSERT INTO Workers (Name, Hours) VALUES ('{ceReader["Name"]}', {ceReader["Hours"]})";
            serverCmd.ExecuteNonQuery();
        }
    }
}

Is there a more efficient way to do this? Maybe some built-in tools or methods I’m not aware of? Thanks for any tips!

2

hey swiftcoder, have u thought about using sqlbulkcopy? it’s way faster for moving lotsa data. just make sure ur tables match up. also, parameterized queries r a good idea to avoid injection risks. might wanna look into that too. good luck with ur project!

3

i was wondering if u tried sql server integration services (ssis)? it’s a nifty tool for migrating data. also, i noticed the curent approach might be prone to sql injection. have you explored using parameterized queries? curious, what performance changes did you see after these tweaks?

4

I’ve encountered a similar scenario in my work with mobile data synchronization.

One approach that significantly improved performance was implementing a batch processing method. Instead of inserting records one by one, you could accumulate multiple records in memory and perform bulk inserts. This reduces the number of database round-trips and can dramatically speed up the process.

Additionally, consider using a data access layer or ORM like Entity Framework. These tools often provide optimized methods for data transfer and can handle the intricacies of connection management and query optimization. They also typically include built-in protection against SQL injection, enhancing the security of your data transfer process.

Lastly, if your data volume is substantial, you might want to explore asynchronous processing to prevent blocking the main thread during transfers.