Can't use sql server ce 4.0 in asp.net in visual studio 2012

Question

At first it wrote this exception:

"SQL Server Compact is not intended for ASP.NET development."

and then I added:

AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

to the Global.aspx and it fixed it.

But now when I try to open the SqlCeConnection it gives me this exception:

Incompatible Database Version. If this was a compatible file, run repair. For other cases refer to documentation. [ Db version = 4000000,Requested version = 3505053,File name = \?\C:\Users\gal\Documents\Visual Studio 2012\WebSites\Project_Level_4\DB\PhoneBookWeb.sdf ]

Can anyone please help ?

Answer 1

Install the SQL CE 4.0 package: http://www.microsoft.com/en-us/download/details.aspx?id=17876

Try adding the reference in your IDE to "C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.dll"

Add the using clauses:

using System.Data.SqlServerCe;
using System.IO;

Then try something like this:

        string connectionString;
        string fileName = "test.sdf";
        string password = "test";

         if (File.Exists(fileName))
            File.Delete(fileName);

        connectionString = string.Format("DataSource=\"{0}\"; Password='{1}'", fileName, password);

        SqlCeConnection conn = null;

        try {

            SqlCeEngine engine = new SqlCeEngine(connectionString);
            engine.CreateDatabase();

            conn = new SqlCeConnection(connectionString);
            conn.Open();

            SqlCeCommand cmd = conn.CreateCommand();
            cmd.CommandText = "CREATE TABLE myTable (col1 int, col2 ntext)";
            cmd.ExecuteNonQuery();
        }
        catch(Exception ex) 
        {
            Console.WriteLine(ex.ToString());
        }
        finally {
            conn.Close();
        }