Tags

, , , , ,

CE should have been installed as part of SQL Server Express, but you’ll require the assembly references if developing a Windows application that uses an embedded database. The CE server wasn’t listed anywhere on my laptop, and I couldn’t use it without knowing the connection string or name. There is a command for getting information about the local SQL Server configuration, though:
sqllocaldb.exe info

For my project I chose the MSSQLLocalDB instance, and started that using the following command:
sqllocaldb.exe start MSSQLLocalDB

Then got information about it using:
sqllocaldb.exe info MSSQLLocalDB

The Command Prompt returns something like:

From this, we need the server name, and possibly the instance pipe name.

Creating a Database and Table
Now we want to create a database and at least one table. Databases can be administrated in Visual Studio’s the SQL Server Object Explorer, but I find it easier to work with SQL Server Management Studio.
To connect the SQL Server Management Studio to the local server, enter ‘(localdb)\MSSQLLocalDB‘ as the Server name. The Object Explorer will list the features available for the database server, and it’s here that tables, views and stored procedures can be created.

Right-click and select ‘Generate Change Script…‘, if you want to save the SQL code for replicating the schema to additional tables. It isn’t obvious how to commit the changes to the database, though – to do this, right-click the current tab, and select ‘Save Table_1‘.
Table rows can be added and modified by selecting ‘Edit Top 200 Rows‘, if you’re not versed in MS SQL scripting.

Adding Reference Assemblies to a Project
The chances are SQL Server CE was already installed along with Visual Studio, but the DLLs must be dowloaded and installed to enable the addition of the assembly references to a project. After installation, you might need to browse for the DLLs in Reference Manager. They should be stored in ‘C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop‘. From this you’ll want the assembly reference for System.Data.SqlServerCe.dll, and the following import statement in the code:
using System.Data.SqlServerCe;

Since I’m only testing this as a data source, I’ve created a basic Windows Form with a DataGridView element. After creating a connection to (localdb)\MSSQLLocalDB) in the Data Source Configuration Wizard, I used the database as the data grid’s source.

This adds a connection string to the project, and we can select the table/view to be displayed in the data grid.

private void Form1_Load(object sender, EventArgs e)
{ this.allphrasesTableAdapter.Fill(this.phrasesDataSet.allphrases);
}

And we can add several text boxes and a button to update the database table.


private void button2_Click(object sender, EventArgs e)
{
// Insert new record
this.allphrasesTableAdapter.Insert(00, txtEnglish.Text, txtGerman.Text, txtNote.Text, cmbPhraseCategory.Text);
}

My next post will explore the use of stored procedures and Web Services for decoupling client applications from the database.

Advertisements