This article explains how to programmatically use SQL Server Everywhere (embedded) in a C# program.
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:
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 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';");
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
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
SqlCeConnection is used to create a connection to the database.
SqlCeConnection connection =
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
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
SqlCeDataReader can be used for query results with multiple rows and/or columns.
command.CommandText = "SELECT * FROM customer";
SqlCeDataReader dataReader = command.ExecuteReader();
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
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.
- July 7 2006 - Original article.