C# BulkCopy with SQL Server Compact Edition, is it possible?

Question

My intent is to copy excel data into SQL Server Compact Edition.

I have read the difference between SQL Server Compact and SQL Server from here but couldn't find it out.

Also, know that constructor of SqlBulkCopy takes parameter of SqlConnection and not SqlCeConnection.

Here is my code:

public void importDataFromExcel(string excelFilePath)
{
        string sSQLTable = "Inflations";
        string myExcelDataQuery = "Select CUSIP,FM,TNT,CPI_INDEX from [Sheet1$]";
        string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";
        string sSqlConnectionString = "Data Source=C:\\Users\\abc\\Documents\\datafile.sdf;Password=12345;";

        OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
        OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
        OleDbConn.Open();
        OleDbDataReader dr = OleDbCmd.ExecuteReader();

        SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
        bulkCopy.DestinationTableName = sSQLTable;

        while (dr.Read())
       {
            bulkCopy.WriteToServer(dr);
        }
        OleDbConn.Close();
}

score 2 · Answer 1 · answered May 10 '13 at 12:32

2

You can use my SqlCeBulkCopy library, available via NuGet for SQL CE 4.0, or otherwise available here: https://sqlcebulkcopy.codeplex.com/

answered May 10 '13 at 12:32

ErikEJ

40,951
5
75
115

score 0 · Accepted Answer · edited May 23 '17 at 12:12

0

I think this thread is related to your problem. You may also look at library, proposed for using also in that conversation.

edited May 23 '17 at 12:12

Community

1
1

answered May 10 '13 at 12:30

Mr Zak

361
2
10

C# BulkCopy with SQL Server Compact Edition, is it possible?

2 Answers2