Create SQL Server CE database file programmatically

Question

How can I create a new SQL Server Compact database file (.sdf) programmatically, without having an existing template file to copy from?

score 77 · Accepted Answer · answered Sep 28 '09 at 16:01

77

There is some good info here: Create a SQL Server Compact Edition Database with C#

string connectionString = "DataSource=\"test.sdf\"; Password=\"mypassword\"";
SqlCeEngine en = new SqlCeEngine(connectionString);
en.CreateDatabase();

answered Sep 28 '09 at 16:01

D'Arcy Rittich

167,292
40
290
283

22

Surprisingly, creating a 0-byte file is good enough for SQL CE to start treating it as a database. This is useful for times when you want to use `Path.GetTempFileName()`, which creates a 0-byte file for you. – Phil Nov 02 '12 at 21:14
the DataSource must be surrounded with double quotes. The password, on the other hand, must be surrounded with single quotes. – Ghasem Oct 30 '14 at 08:23

score 9 · Answer 2 · edited Feb 23 '13 at 07:02

First, go click on the Browse tab. Now navigate to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1. Now pick the System.Dadta.SqlServerCe.dll, click on it, then click on OK to pull in the reference.

Now let’s write some code. First, go to the head of the class, whoops I mean header of your class and let’s create a using reference.

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

  string connectionString;
  string fileName = "aminescm.sdf";
  string password = “aminescm”;

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

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

You can find more and more about this, it's really an amazing web site:

http://arcanecode.com/arcane-lessons/

Now you can just add a reference to the NuGet package for SqlCe instead of referencing a local assembly. — Kilhoffer, Dec 10 '13 at 23:02
it's `System.Data.SqlServerCe.dll`, you have a typo there in the third sentence — Alex P., Jun 09 '21 at 11:18

Create SQL Server CE database file programmatically

2 Answers2

Linked

Related