c# - How can I connect to a SDF database? No connection string I try seems to work

Question

I've tried literally 50+ different attempts at my connection string for my local database and nothing seems to work. I'm essentially just trying to open a connection the database file so I can dump in the data I've pulled out of my excel spreadsheet. I'm using Visual C# making an offline winform application.

No matter what connection string I try in my app.config, it always fails when it tries to write "dReader" to the database.

The error is usually this depending on what string I try:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I've gone through many online examples and resources and none seem to work. I'm hoping someone here can point out why it's failing.

Here is my app.config in its latest form:

<connectionStrings>
    <add name="DDP_Project.Properties.Settings.DDP_DatabaseConnectionString"
        connectionString="Data Source=E:Other DDP ProjectsDDP_Project_SDFDDP_ProjectDDP_Database.sdf;"
        providerName="Microsoft.SqlServerCe.Client.3.5" />
</connectionStrings>

Here is my form code:

    private void Profiles_Click(object sender, EventArgs e)
    {
        profilesDialog.FileName = "[YOUR_UPLOAD_FILE_HERE]";
        var result = profilesDialog.ShowDialog();

        if (result == DialogResult.OK)
        {
            HandleFileSelection();
        }
    }

    private void HandleFileSelection()
    {
        var file = profilesDialog.FileName;


         // Create a connection to the file datafile.sdf in the program folder
    string dbfile = new System.IO.FileInfo(System.Reflection.Assembly.GetExecutingAssembly().Location).DirectoryName + "\DDP_Database.sdf";
    SqlCeConnection connection = new SqlCeConnection("datasource=" + dbfile);


    string strConnection = ConfigurationManager.ConnectionStrings["DDP_Project.Properties.Settings.DDP_DatabaseConnectionString"].ConnectionString;


        //Create connection string to Excel work book
        string excelConnectionString = string.Format(
            @"Provider=Microsoft.Jet.OLEDB.4.0;
            Data Source=""{0}"";
            Extended Properties=""Excel 8.0;HDR=YES;""", file
        );

        //Create Connection to Excel work book
        OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
        OleDbCommand cmd = new OleDbCommand("SELECT [ID],[STATUS],[FAN_NUM],[PROFILE_NAME],[DESTINATION_HOST],[USER_ID],[USER_PASSWORD],[PROTOCOL],[PORT],[PATH],[CONTACT_NAME],[CONTACT_EMAIL],[CONTACT_PHONE],[CONTACT_ALT_PHONE],[CONTACT_CITY],[CONTACT_STATE],[CONTACT_CONTACT_TIME] FROM [Sheet1$]", excelConnection);

        excelConnection.Open();
        OleDbDataReader dReader;
        dReader = cmd.ExecuteReader();

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);

        sqlBulk.DestinationTableName = "Profiles";
        sqlBulk.ColumnMappings.Add("ID", "ID");
        sqlBulk.ColumnMappings.Add("STATUS", "STATUS");
        sqlBulk.ColumnMappings.Add("FAN_NUM", "FAN_NUM");
        sqlBulk.ColumnMappings.Add("PROFILE_NAME", "PROFILE_NAME");
        sqlBulk.ColumnMappings.Add("DESTINATION_HOST", "DESTINATION_HOST");
        sqlBulk.ColumnMappings.Add("USER_ID", "USER_ID");
        sqlBulk.ColumnMappings.Add("USER_PASSWORD", "USER_PASSWORD");
        sqlBulk.ColumnMappings.Add("PROTOCOL", "PROTOCOL");
        sqlBulk.ColumnMappings.Add("PORT", "PORT");
        sqlBulk.ColumnMappings.Add("PATH", "PATH");
        sqlBulk.ColumnMappings.Add("CONTACT_NAME", "CONTACT_NAME");
        sqlBulk.ColumnMappings.Add("CONTACT_EMAIL", "CONTACT_EMAIL");
        sqlBulk.ColumnMappings.Add("CONTACT_PHONE", "CONTACT_PHONE");
        sqlBulk.ColumnMappings.Add("CONTACT_ALT_PHONE", "CONTACT_ALT_PHONE");
        sqlBulk.ColumnMappings.Add("CONTACT_CITY", "CONTACT_CITY");
        sqlBulk.ColumnMappings.Add("CONTACT_STATE", "CONTACT_STATE");
        sqlBulk.ColumnMappings.Add("CONTACT_CONTACT_TIME", "CONTACT_CONTACT_TIME");

        sqlBulk.WriteToServer(dReader);
        sqlBulk.Close();
        excelConnection.Close();

    }

    private void profilesDialog_FileOk(object sender, EventArgs e)
    {


    }
}

}

Answer 1

I think the problem you are seeing is that you are trying to use a SqlConnection to connect to a SQL Compact database. The .sdf is a compact database and you have to use the SqlCeConnection to connect to it. You create the connection using this but then you don't use it. Instead you pass in the connection string to the SqlBulkCopy object which implicitly creates a SqlConnection from that string. I'm assuming it is on that line where you are getting the error. If you notice the namespace of the SqlBulkCopy is System.Data.SqlClient. The reason you are seeing the error is that its trying to go through SQL Server to make the connection and cannot resolve your connection string to a SQL Server database. Unfortunately, I don't think the System.Data.SqlServerCe has the equivalent to the SqlBulkCopy. Stick to using classes in System.Data.SqlServerCe and things should work as expected. You just will have to do the processing in a more manual fashion.