Exception when attempting to connect to SQL Server CE from C#

Question

I'm an newbie who is trying to get learn some web programming. I'm making my site in VS 2012 using C#. I've got a database connected in the App_Data folder, using SQL Server CE 4.0. I'm attempting to connect to it as follows:

SqlCeCommand cmd1 = new SqlCeCommand("SELECT Admin FROM SystemUsers WHERE Email=" + user.Email);
SqlCeDataReader admin = null;
SqlCeConnection conn = new SqlCeConnection();
conn.ConnectionString = "Data Source=MyData.sdf;Persist Security Info=False;";
conn.Open();
admin = cmd1.ExecuteReader();

When I execute this, I get the following error:

An exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll but was not handled in user code

Any thoughts as to what I'm doing wrong? I've been trying to figure this out for hours.

Answer 1

You said that your database is in the APP_Data directory, but your connection string assumes that is in the root directory of your site.

Try with

conn.ConnectionString = @"Data Source|DataDirectory|\MyData.sdf;Persist Security Info=False;";

The |DataDirectory| is a placeholder string that the NET Framework changes to a predefined location where you data files are supposed to stay

And this is how I would change your code

using(SqlCeConnection conn = new SqlCeConnection(@"Data Source|DataDirectory|\MyData.sdf;Persist Security Info=False;"))
using(SqlCeCommand cmd1 = new SqlCeCommand("SELECT Admin FROM SystemUsers WHERE Email=@mail", conn))
{
    conn.Open();
    cmd1.Parameters.AddWithValue("@mail", user.Email);
    SqlCeDataReader admin = cmd1.ExecuteReader();
    while(admin.Read())
    {
      .....
    }
}

As you can see, I have made this changes:

• Added a using statement around the creation of the connection and of the command. This will ensure proper closing and disposing of the two objects.
• Added a parameterized query to avoid problems in parsing text strings and Sql Injections