Creating and Populating a Local Database in Visual Studio 2010 using Microsoft SQL Server CE

1) Create a new project (File->New Project->Visual C# (or Visual Basic) ->Windows Form Application) or open an already created .NET project.

2) Create a local database: Data - Add New Data Source.

Adding a new data source

3) Choose Database and press Next.

Selecting a database

4) Now you are presented two options: Dataset and Entity Data Model.

If you choose the Dataset options, you will have to create a BindingSource object and a Dataset object to access the database in your program. In terms of performance, the Dataset is better than the Entity DataModel.

If you choose and Entity Data Model, the tables and records will be mapped as classes and objects and you will be able manipulate them more easily. In terms of rapid development, a Entity Data Model is better than a Dataset.

In this example we shall choose to access our data through a Dataset.

Dataset vs Entity Data Model

4) In your new window choose New Connection. You will be presented with several options for configuring the connection to your database.

Configuring the connection

5) Click the Change button. Now you will be presented with a selection of several databases type (Access, Oracle, SQL Server, etc). To use a local SQL Server, select the option SQL Server Compact 3.5 option.

Selecting a SQL Server Compact 3.5 database

6) Now you will observe that the layout of your window changed a little bit. Now you can choose to create a new database or provide the location of an already created database. In this tutorial we shall create a new database by pressing the button Create.

Configuring the database

7)Now you must provide a path for your database. You can also choose to encrypt your database by providing a password or change the database's character set (so you could use locale-specific characters).

Creating the database

8)After you created the database, it is generally good practice to test if you can connect to it. This can be done by pressing the button Test Connection. If you done the things right, you will receive a message like the one bellow.After the test press OK again to advance to the next dialog.

Testing the connection

9)In the new window, you can see your connection string in the bottom textbox. Press Next to proceed. Now you will be asked if you want to copy your database file into the current project.

If you choose Yes, your database will appear in Solution Explorer, but any changes you do to the database (like inserting, updating or deleting records), will be lost when you close your application. This happens because every time you run your application a copy of the database will be made in the Debug/Release folder who contains your executable, a copy who will be lost when you close and restart your application. You can modify your database by using Visual Studio's tools, but not your application. This is a good thing if you want to distribute your database along with your application and will protect the database from bugs in your software. 

If you want to modify the database using your application and keep the changes choose No.

Choosing if the database will be added to the project

10)After doing this you will be asked if you want to save your Connection String to the application configuration file. Check Yes and press Next.

Saving the connection string

11)Now you will receive a message that your database contains no objects. This is normal since it was just created. To the bottom you will see the name of your Dataset. This dataset will be added to the Solution explorer. Press Finish.

Saving your database and its dataset

12)Now that you created your database its time to add some tables and some record. To do that open the Server Explorer window (Ctrl + W, L). There you can see your database file. To create a table you simply need to right-click on it and select the option Create Table.

The Server Explorer perspective

13)Now you can give a name to the table and begin configuring the fields.

Creating a Table Schema

By repeating 12) and 13) you can create multiple tables.

14) In order to populate your table, right click on the newly created table and select the option Show Table Data. This will open a window where you can insert new records, modify the existing ones and even delete older records.

Populating the database