Creating a SQL Compact Edition Database Programmatically
The previous section explains how to use a SQL Server Compact database that is pre-built. But occasionally, you may need to create one from scratch. programmatically. You can do so using the SqlCeEngine and the SqlCeCommand classes, as shown in Listing 3
Listing 3 creates a new SQL Server Compact database using the SqlCeEngine class' CreateDatabase() method. To create individual tables within the database, use the SqlCeCommand class. The above code creates two tables: Stores and Orders. The Stores table contains two fields: storeID (primary key) and storeName. The Orders table contains two fields: storeID and title_id.
The following code adds a record into the Stores table:
string storeID = "1";
string storeName = "Great BookStore";
"INSERT INTO Stores (storeID, " +
"storeName) VALUES (@storeID, " +
You are advised to use a Parameters
object when formulating SQL statements. If not, security risks like SQL-injection may occur (especially if the inputs are from end-users).
See my previous article about ASP.NET security for a discussion on SQL injection.
Besides programmatically creating the SQL Server Compact database, you can also use Visual Studio 2008 to create one visually.
In your project, add a new Database File (see Figure 2).
Figure 2. Create a Database Visually: Adding a database file to a project.
Figure 3. Create a Database Visually: Creating a new table.
After you've added the database file, the Data Source Configuration Wizard will appear. As there is no table to select (the database is empty at the moment), simply click Finish.
In the Server Explorer, you'll notice that the database just added is now listed under the Data Connections item (see Figure 3). Right-click on the Tables item and select Create Table.
The New Table window will appear (see Figure 4). You can name your new table as well as define the columns in this window. Once you are done, click OK.
Figure 4. Create a Database Visually: Defining a new table in the New Table window.
Figure 5. Create a Database Visually: Displaying the content of a table.
To populate the table with some records, right-click on the newly created table name in Server Explorer and select Show Table Data (see Figure 5). You can now populate your table with some rows.