Execute/Query against SQL Compact 4.0 in ASP.NET

Question

I'm writing a small little utility MVC app and I need to have the ability to execute ad-hoc queries against my one-table SQL Compact 4.0 .sdf file for management (Web Matrix isn't working right for development, and it won't be available on the PC this will ultimately be running on). Using Entity Framework code-first, everything is working fine, but to do an ad-hoc query, I figured I'd need to connect to it the way I would have in the pre-EF days (see below)

            cn = new SqlConnection("Data Source=|DataDirectory|LocalScanData.sdf");
            SqlCommand cmd = new SqlCommand(query, cn);

            if (cn.State != ConnectionState.Open) cn.Open();

            if (query.ToUpper().StartsWith("INSERT") || query.ToUpper().StartsWith("UPDATE") || query.ToUpper().StartsWith("DELETE"))
            {
                TempData["RowsAffected"] = cmd.ExecuteNonQuery();
                return RedirectToAction("SQL");
            }
            else
            {
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();

                da.Fill(dt);

                return RedirectToAction("SQL", dt);
            }

But when I try that, I get 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). So, the question, is how can I connect to a SQL CE 4.0 database the old fashioned way? I've also tried using System.Data.SqlServerCe but then I get errors that lead me to believe that only works for CE 3.5 databases.

Any help?

Answer 1

You cannot use SqlConnection with SQL Compact, you must use SqlCeConncection. Add a reference to: C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\System.Data.SqlServerCe.dll

See this blog post: http://erikej.blogspot.com/2010/07/getting-started-with-sql-server-compact.html