Create SQLCE database programmatically

Question

[EDITED - with answer]

Following is my code to create SQL CE database programmatically:

/* get the Path */
var directoryName = System.IO.Path.GetDirectoryName(Assembly.GetEntryAssembly().Location);
var fileName = System.IO.Path.Combine(directoryName, "Foo2Database.sdf");

/* check if exists */
if (File.Exists(fileName))
    File.Delete(fileName);

string connStr = @"Data Source = " + fileName;

/* create Database */
SqlCeEngine engine = new SqlCeEngine(connStr);
engine.CreateDatabase();

/* create table and columns */
using (SqlCeConnection conn = new SqlCeConnection(connStr))
{
    using (SqlCeCommand cmd = new SqlCeCommand(@"CREATE TABLE FooTable (Foo_ID int, FooData NVARCHAR(200))", conn))
    {
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        finally
        {
            conn.Close();
        }

    }
}

If you get such a strange error, you will get the exact code line number in your strack trace. please provide more details like complete exception message and code position. — ibram, Jun 01 '11 at 06:51

score 31 · Accepted Answer · answered Jun 01 '11 at 05:26

31

I have worked with SQLCE 3.1 and SharpDevelop, Try this code and see if this is what you want:

string connStr = "Data Source = FooDatabase.sdf; Password = SomePassword";

if (File.Exists("FooDatabase.sdf")) 
    File.Delete("FooDatabase.sdf");  

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

SqlCeConnection conn = null;


try 
{
    conn = new SqlCeConnection(connStr);
    conn.Open();

    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandText = "CREATE TABLE FooTable(col1 int, col2 ntext)";
    cmd.ExecuteNonQuery();
}
catch 
{

}
finally 
{
    conn.Close();
}

Note that the database is just a file, so you can check if the database exists by looking if the file exists, also you can delete the database by deleting the file. Hope this helps.

answered Jun 01 '11 at 05:26

midhunhk

5,560
7
52
83

4

You should use the using-statement on SqlCeEngine, SqlCeConnection and of course SqlCeCommand. – ba__friend Jun 01 '11 at 06:17
I worked on this thing a few years back, and i wanted to close the connection like in this try catch, finally method. And sadly being self taught in C#.net, i am not too familiar with new practices used :). that said, i guess i think you guys can use this code in the proper way and think of my example as an algorithm of sorts :D – midhunhk Jun 01 '11 at 06:54
thanks. according to what you've given, I've re-written my code and edit my question with answer. – KMC Jun 01 '11 at 08:24

Create SQLCE database programmatically

1 Answers1

Linked