When I came across a story on the newest version of SQL Server CE by Scott Guthrie, as well as a blog post in the SQL Server CE team blog, I was amazed that it was possible to run on a web server for ASP.NET application in the Medium Trust mode without any installation. This literally means that finally one can use SQL for storing data even though their hosting plan, for example, doesn’t allow having a SQL Server database.
For instance, I must admit that I store all my blogs posts in XML files simply because I decided to get a cheaper hosting account, while SQL Server CE 4.0 should allow me to store my data in a relational database. This can possibly attract casual web developer who are lurking for cheap solutions and simple tools a problem that is also solved by WebMatrix, but it is actually a completely different story and my present post isn’t about that marketing stuff.
Anyway, I decided to get my hands onto this version of the database. Lucky enough there is already a CTP version available and ready for download. The setup is pretty small – just 2 megabytes and the entire installation process just takes a few minute that is exceptionally well for a database server, albeit a compact one.
After the installation I didn’t manage to find any new items in the Program menu, so I headed straight to the directory it was installed to. It contained nothing more than DLL files and not a very useful readme file.
However, it was a right time to finally try it in action. But it was also a time of frustration because I realised that I didn’t know how to create a database and tables because the CTP simply lacks any tools for that. Fortunately, I came with a workaround.
In order to create a database it is just enough to create an empty .sdf file, well the same actually goes to any other version of SQL Server CE, but what’s then? Well, of course you can’t use SQL Management Studio or Visual Studio Server Explorer because they simply don’t support the newest version. But you can just create tables programmatically from C#.
Just launch Visual Studio 2010 and create a new console project. Then, don’t forget to add a reference to a SQL Server CE assembly, go to Solution Explorer –> References –> Right Click –> Add Reference –> Browse –> and then look up for a file called “System.Data.SqlServerCe.dll” in either the ‘Desktop’ or the ‘Private’ subfolder of the directory where you’ve installed SQL Server CE.
And then just copy-paste the following code that should be pretty self-explanatory.
using System;
using System.Data.SqlServerCe;
namespace SqlCe4Test
{
class Program
{
static void Main(string[] args)
{
//Establishing a connection with our database
SqlCeConnection conn = new SqlCeConnection(@"DataSource=C:\C#\SqlCe4Test\db.sdf");
conn.Open();
//Creating a table
SqlCeCommand cmdCreate = new SqlCeCommand("CREATE TABLE Products (Id int IDENTITY(1,1), Title nchar(50), PRIMARY KEY(Id))", conn);
cmdCreate.ExecuteNonQuery();
//Inserting some data...
SqlCeCommand cmdInsert = new SqlCeCommand("INSERT INTO Products (Title) VALUES ('Some Product #1')", conn);
cmdInsert.ExecuteNonQuery();
//Making sure that our data was inserted by selecting it
SqlCeCommand cmdSelect = new SqlCeCommand("SELECT Id, Title FROM Products", conn);
SqlCeDataReader reader = cmdSelect.ExecuteReader();
reader.Read();
Console.WriteLine("Id: {0} Title: {1}", reader["Id"], reader["Title"]);
reader.Close();
conn.Close();
}
}
}
That I was just a post on how to get started with SQL Server CE 4.0 and to avoid that moment of frustration when you don’t know to even create a table. In next posts I will cover topics like using SQL Server CE 4.0 for ASP.NET applications, show if it’s possible to use it with LINQ and the Entity Framework, as well as talk about some other stuff.
Then, you may be also interested in the tutorials I wrote about CE 3.5:
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way. My personal thoughts tend to change, hence the articles in this blog might not provide an accurate reflection of my present standpoint.
© Mike Borozdin