Create SQLCE database programmatically [duplicate]

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
– ibram, Commented Jun 1, 2011 at 6:51

midhunhk · Accepted Answer · 2011-06-01 05:26:49Z

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 1, 2011 at 5:26

community wiki

midhunhk

Sign up to request clarification or add additional context in comments.

3 Comments

ba__friend Over a year ago

You should use the using-statement on SqlCeEngine, SqlCeConnection and of course SqlCeCommand.

midhunhk Over a year ago

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

KMC Over a year ago

thanks. according to what you've given, I've re-written my code and edit my question with answer.

Collectives™ on Stack Overflow

Create SQLCE database programmatically [duplicate]

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related