Getting started with SQL Server Everywhere

• Download demo project - 13.4 Kb

Introduction

This article explains how to programmatically use SQL Server Everywhere (embedded) in a C# program.

Background

Microsoft has released an embedded version of SQL Server, called SQL Server Everywhere. It is currently available in a CTP version (Community Technical Preview), and thus not ready for production code. It's a small version of SQL Server that can be embedded into a program. It's actually a SQL Server Mobile edition that can be used everywhere (no longer limited to the mobile platform).

Installing SQL Server Everywhere

First download SQL Server Everywhere from its product homepage. As of this writing, there is a link in the top right corner to the CTP version. There is also some documentation in a separate download. Simply download and install as usual.

There is a blog for SQL Server Everywhere. There is also a FAQ.

Creating a project

For this sample, I created a C# windows application project in Visual Studio. Then, I added some components such as a TextBox for the SQL query, and a DataGridView to show the query results in.

Adding support for SQL Server Everywhere

Visual Studio needs to know how to access SQL Server Everywhere. This is done by adding a reference to the "System.Data.SqlServerCe.dll" file in the Solution Explorer window. The DLL file is located in the folder where SQL Server Everywhere is installed (on my machine, this is "E:\Program Files\Microsoft SQL Server Everywhere\v3.1").

Once the reference is added, SQL Server Everywhere elements can be accessed in the usual ways:

// By including the namespace:
using System.Data.SqlServerCe;

// By direct qualification:
System.Data.SqlServerCe.<something>

Creating a database

SQL Server Everywhere creates database files to hold databases. A single database is placed in a single file, with the extension ".sdf". It is possible for a program to access multiple databases in multiple files at once. There are even locking facilities that allow multiple processes and/or programs to access the same database file at the same time (and with the usual concurrency issues).

SqlCeEngine

The class SqlCeEngine is used to manage the database. It allows you to create, modify, and destroy the database file.

For this sample, I use it to create a database:

SqlCeEngine engine = new SqlCeEngine("Data Source='Test.sdf';");
if (!(File.Exists("Test.sdf")))
    engine.CreateDatabase();

The first line creates an instance of the engine, and then associates it with a database file "Test.sdf".

If the file does not exist already, then the second line creates it using the engine.CreateDatabase() call.

Using the database

These are the usual steps involved in using a database that is already created:

• Connect to the database.
• Create and execute command.
• Read results.

Connecting with SqlCeConnection

The class SqlCeConnection is used to create a connection to the database.

SqlCeConnection connection = 
        new SqlCeConnection(engine.LocalConnectionString);
connection.Open();

The first line creates a connection to the database using the same connection string as were used for the engine. If the database is known to exist, there is actually no need to create the engine object just to access the data in the database.

The second line opens the connection so that commands can be issued.

Remember to close the connection again when you are finished with it so that it doesn't unnecessary hold on to resources until garbage collection.

Executing commands with SqlCeCommand

The class SqlCeCommand is used to send commands to the database through the connection.

SqlCeCommand command = connection.CreateCommand();
command.CommandText = "SELECT count(*) FROM customer";
int result = (System.Int32)(command.ExecuteScalar());

The first line uses the connection to create a command. This way, the command gets associated with the connection.

The second line sets the SQL command to perform.

Finally, the command is executed against the database. The command.ExecuteScalar() call can be used when the query returns a simple value, like int in this example.

Reading complex results with SqlCeDataReader

The class SqlCeDataReader can be used for query results with multiple rows and/or columns.

command.CommandText = "SELECT * FROM customer";
SqlCeDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
    for (int i = 0; i < dataReader.FieldCount; i++)
    {
        string value = dataReader.GetValue(i).ToString();
    }
}

Data must be read one row at a time. The dataReader.Read() call returns true as long as a new row can be obtained. Each row contains dataReader.FieldCount columns. Each cell value can be read in various formats according to the results (an Int32 value must be read with dataReader.GetInt32(), and so on).

for (int i = 0; i < dataReader.FieldCount; i++)
{
    string column = dataReader.GetName(i);
}

The column names are read with dataReader.GetName().

Points of interest

The database file "Test.sdf" is stored in the "\bin\Debug" folder but it is possible to change it. Changes made to the database are persistent since the program only creates the database file when it doesn't exist. To start over with a fresh database, just delete the database file.

The database is stored in a single file, and is not dependent on other files or anything. This makes it possible to easily copy the database to another place for use in another program.

This article deals only with directly using SQL Server Everywhere from C# source, and not with any GUI component use. Even with an embedded SQL Server, we want to use the tools and components that we are used to for other databases. This should be possible, but is left for another article.

History

• July 7 2006 - Original article.