Connecting C# to SQL Server Compact database

Question

Hi I'm trying to connect an SQL server compact database to my program and I want a button that deletes all entries from the database, when I Press said button the program throws an exception and gives the following error message "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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

Help Please? =]

Sorry, Code is Below =]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlServerCe;


namespace Booking_system_Final
{
    public partial class PendingJobs : Form
    {
        SqlConnection sc = new SqlConnection("Data Source=C:\\Users\\Administrator\\My Documents\\BMS_Data.sdf");
        public PendingJobs()
        {
            InitializeComponent();
        }

        private void PendingJobs_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'bMSDataSet.Bookings' table. You can move, or remove it, as needed.
            this.bookingsTableAdapter.Fill(this.bMSDataSet.Bookings);
            // TODO: This line of code loads data into the 'bMS_DataDataSet1.Bookings' table. You can move, or remove it, as needed.


        }

        private void button1_Click(object sender, EventArgs e)
        {
            sc.Open();
            SqlCommand cmd = new SqlCommand("DELETE FROM Bookings");
            cmd.Connection = sc;
            cmd.ExecuteNonQuery();
            sc.Close();
            MessageBox.Show("Database Cleared");


        }
    }
}

Solution 1

Try use SqlCeConnection class rather than SqlConnection:

SqlCeConnection sqlConnection1 = new SqlCeConnection();
sqlConnection1.ConnectionString = "Data Source = C:\\Users\\Administrator\\My Documents\\BMS_Data.sdf;Persist Security Info=False";

Solution 2

If you want to connect to SQL Server Compact, use SqlCeConnection, SqlCeCommand etc. Add a reference to the SQL Server Compact ADO.NET provider, System.Data.SqlServerCe.dll

Comments

[Captain_Custard]

thanks for the detailed answer, I have found the source of the problem which is that SQL server for some reason cannot connect and I am now getting an error saying "the remote procedure call failed"

[Jens H]

@ReeceCottam: I am not completely familiar with this, is a remote procedure the same as a stored procedure? If so, this is a feature that might possibly NOT supported by the CE edition: Have a look at the bottom of this feature comparison table: (Comparison of SQL Server Compact, SQL Server Express 2012 and LocalDB )[erikej.blogspot.de/2011/01/… where it says "Stored procedures, views, triggers: No"

[Zeeshan]

How to modify the data source in case of LAN? Let's say, the .sdf is located on shared network as \\xyz-PC\db\dbfile.sdf