Click here to Skip to main content
14,217,568 members
Rate this:
Please Sign up or sign in to vote.
I want to look at using SQL Compact Edition as the source for a text based game (yeah, yeah, very nostalgic) but I cannot get a database created. In Visual Studio 2008, I have tried this:

1. Right click Data Connections in the Server Explorer and select "Add Connection" from the menu.

2. Select "Microsoft SQL Server Compact 3.5" in the Choose Data Source and click Continue.

3. Under Connection Properties, click Create....

At that point, the Add Connection form closes and there is no interface for creating the database. Whether I provide a name for the database file or leave it blank, the result is the same. I have also tried going in through the menu item Data | Add New Data Source; the connection wizard takes me back to the same Choose Data Source interface with the same problem.

First off, is this sequence correct or am I missing something? Would it make a difference to attempt a reinstall of SQLCE? Or should I give up this approach entirely and go with a compacted XML file for my data?
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

The sequence is correct - when I try I get the "Create New SQL Server Compact Database" dialog after pressing the "Create" button.

However, I also have SQL Server 2008 R2 installed on my machine, so my configuration may not be the same as yours. I do vaugely remember having problems creating SqlCE fdatabases, but that was about two years ago, and I can't remember all the details of how I fixed it.

Things to check:

Can you create a connection to any other database via the Server Explorer?
Can you connect to an existing SQLCE database via Server Explorer?

If it all falls apart, you could do the following, and see if you can connect to that:
private void RebuildDB()
    {
    if (!File.Exists(strDB))
        {
        try
            {
            SqlCeEngine engine = new SqlCeEngine(strCon);
            engine.CreateDatabase();
            Log.AddLog("DataBase Created");
            }
        catch (System.Exception ex)
            {
            Log.AddLog("Error creating database: " + ex.ToString());
            }
        }
    using (SqlCeConnection con = new SqlCeConnection(strCon))
        {
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.TABLES", con);
        SqlCeDataReader r = cmd.ExecuteReader();
        Log.AddLog("Reading Tables");
        bool tablePresent = false;
        while (r.Read())
            {
            string tableName = (string) r[0];
            if (tableName == strTable)
                {
                tablePresent = true;
                }
            }
        if (tablePresent)
            {
            Log.AddLog("Removing table");
            cmd = new SqlCeCommand(string.Format("DROP TABLE {0}", strTable), con);
            cmd.ExecuteNonQuery();
            }
        Log.AddLog("Creating table");
        cmd.Dispose();
        cmd = new SqlCeCommand(string.Format("CREATE TABLE {0} (id int NOT NULL, name nvarchar(100), level nvarchar(10))", strTable), con);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cmd = new SqlCeCommand(string.Format("CREATE UNIQUE INDEX idx1 ON {0} (id)", strTable), con);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cmd = new SqlCeCommand(string.Format("INSERT INTO {0} (id, name, level) VALUES (@ID, @NM, @LV)", strTable), con);
        cmd.Parameters.AddWithValue("@ID", 1);
        cmd.Parameters.AddWithValue("@NM", "Joe Green");
        cmd.Parameters.AddWithValue("@LV", "User");
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 2;
        cmd.Parameters["@NM"].Value = "Pete Smith";
        cmd.Parameters["@LV"].Value = "User";
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 3;
        cmd.Parameters["@NM"].Value = "Miss Scarlet";
        cmd.Parameters["@LV"].Value = "User";
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 4;
        cmd.Parameters["@NM"].Value = "Mike Black";
        cmd.Parameters["@LV"].Value = "User";
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 5;
        cmd.Parameters["@NM"].Value = "The Boss";
        cmd.Parameters["@LV"].Value = "Admin";
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        con.Close();
        }
    }
That will create a small, single table DB, and populate it with a few values.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100