Create Table query with SQL Server CE

Question

Which is the best way to execute a query in C# + SQL Server CE? I need to create a table, but after 3 days of unsuccessful attempts, I realize that I really can't do it for myself... So I'm here asking for help.

I've tried these topics:

But all ways I tried show me an exception... I'm confused, where is my mistake?

My current code:

public void Connect()
{
    try 
    {
        string FileName = "DataBase";

        using (SqlCeConnection myConnection = new SqlCeConnection(@"Data Source=|DataDirectory|\" + FileName + ".sdf;Password=Z123")) 
        {
           string query = "create table EXP(ID int, source int)";
           int i;

           SqlCommand cmd = new SqlCommand(query, connection);
           cmd.con.open();
           i = cmd.ExecuteNonQuery();
       }
    }
    catch (SqlCeException ae)
    {
        MessageBox.Show(ae.Message.ToString());
    }
}

Many thanks!

What's the value of `FileName`? If you get it from `System.Io.File` it's already including the ".sdf". Also, whenever you have a question about an exception, **show us the exception**. — Dour High Arch, Dec 12 '13 at 16:56

marc_s · Accepted Answer · 2013-12-12T17:04:41.140

If you're using SQL Server CE then you need to use a SqlCeCommand (not a SqlCommand - that's for the full-blown SQL Server).

So change your code to:

using (SqlCeConnection myConnection = new SqlCeConnection(@"Data Source=|DataDirectory|\" + FileName + ".sdf;Password=Z123")) 
{
    string query = "create table EXP(ID int, source int)";

    // use SqlCeCommand here!! Also: use the "myConnection" SqlCeConnection you're 
    // opening at the top - don't use something else.....
    SqlCeCommand cmd = new SqlCeCommand(query, myConnection);

    myConnection.open();
    cmd.ExecuteNonQuery();
    myConnection.Close();
}

Create Table query with SQL Server CE

1 Answers1