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

c# sqlbulkcopy sql-server-ce

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();
} 

Accepted Answer

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


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why