Connect to a database not created yet?

Question

How do I create a SQL Compact Edition database? I have tried to figure it out, searched the internet and I cant find it (but then again, how do you search for this?!). Here is my code so far:

checkPasswordsMatch();
SqlCEConnection myconn = new SqlCEConnection();
SqlCeCommand myCommand = new SqlCeCommand(
    @"CREATE DATABASE ""pwdb.sdf"" DATABASEPASSWORD '" + textBox1.Text + "'");
myCommand.ExecuteNonQuery();

What do I put on the connection part? I dont understand.

UPDATE: I decided to create a database to incorporate with the application, and simply check if there was a value (admin Password) dedicated to the record Master.

Here is my new code:

Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        SqlCeConnection myconn = new SqlCeConnection("Data Source = pwdb.sdf;");
        SqlCeCommand checkpass = new SqlCeCommand("SELECT * from PW WHERE Name = Master;",myconn);
        try
        {
            myconn.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        if (checkpass.ExecuteNonQuery() != null)
        {
            Application.Run(new enterMasterPassword());
        }
        else
            Application.Run(new mainPassSet());

The problem is, when i execute this now, i get:

An unhandled exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Any ideas anyone??

I don't know if the same applies in CE, but in the full version, you connect to your server with the database `master`. Then run your create database script. — gunr2171, Oct 07 '13 at 16:51
have you looked at [this SO Q&A](http://stackoverflow.com/questions/1487845/create-sql-server-ce-database-file-programmatically) yet? — jltrem, Oct 07 '13 at 16:56

score 2 · Accepted Answer · answered Oct 07 '13 at 16:55

2

Try using the SqlCeEngine class. With that class you can create a new database:

using (SqlCeEngine engine = new SqlCeEngine())
{
    engine.LocalConnectionString = yourConnectionString;
    engine.CreateDatabase();
}

answered Oct 07 '13 at 16:55

mageos

1,216
7
15

but what would be the connection string, i dont know what to connect to? – tony b Oct 07 '13 at 17:08
Data Source=C:\data\mydatabase.sdf – ErikEJ Oct 07 '13 at 17:19
You should check out the ConnectionString documentation on MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring(v=vs.100).aspx There are a number of things that can be set like password (encryption of database), colation, auto shrink thresholds, etc. – mageos Oct 07 '13 at 17:46

score 0 · Answer 2 · answered Oct 07 '13 at 17:16

Since you likely only want to create the database once you may not want to do it in your code.

A couple suggestions:

1) if you're using Visual Studio you can open the Server Explorer (Under menu View->Server Explorer) and create it there.

2) Use one of the free SQL managers like linkpad or powershell + sqlcmd (there are others)

3) or... Don't know what data model you are using but EntityFramework with "code first" will create the database for you automatically based on your connection string and how you name your data context. Since you are using the sdf tag, maybe you are using EF...

Connect to a database not created yet?

2 Answers2