Introduction
SQL Server Everywhere is a new member in the SQL Server Family. It is a Light-weight, free database
which is ideal for small applications for a single user. It is much lighter than databases like MS Access or SQL Server Express
and have a disk foot print that is below 2 MB.
SQL Server Everywhere does not have a Server Process at all. The database engine is loaded as an Inprocess component
from System.Data.SqlServerCe.dll. This makes deployment and distribution of the application super easy! Being Inprogress
would give better performance most of the times.
How is it different from SQL Server Express?
- SQL Server Express is a Multi-user, Out-of-process database server which runs as a system service, where as SQL Server Everywhere is an
Inprocess component that loads within the client application's memory space
- The database engine of SQL Server Everywhere is based on SQL Server 2005 Mobile Edition.
- The maximum database size of an SQL Server Everywhere database is limited to 4GB.
- SQL Server Everywhere does not support Stored Procedures. It does not support XML data type.
- SQL Server Everywhere does not support CLR integration.
- SQL Server Everywhere database files have an .sdf extension.
Please note that the above list is not complete. I have noted down only a few points that I thought would be relevant for this
introductory session.
SQL Server Compact Edition
Microsoft has recently renamed SQL Server Everywhere to SQL Server Compact Edition.
Download
A basic introduction and download links are available here.
Hello World Application
Now it is time to see a demo application. Let us get a Hello World application created, which shows SQL Server CE in action. I would
like to present a basic application which connects to a SQL Server Compact Edition database file and displays some data on a Grid View control.
Let us create a new Windows Forms Application. After the project is created, add a reference to System.Data.SqlServerCe. During installation,
the installer will add the assembly to GAC. Hence you can find it in the .NET tab.
Now that we have a reference to the required assembly, let us create a data source for our sample application. Click on the Data menu
and select Add new data source.
At the next step, select Database and click Next.
Click New Connection at the next step.
In the Add Connection dialog, click Change and select Microsoft SQL Server Compact Edition as the Data Source. In the
Connection Properties section, click Browse and select C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\Northwind.sdf. Please note
that the location of the above file can be different based upon the settings that you selected during installation. Once the database file is selected,
click on Test Connection to make sure that everything is working. Then click OK to save the data source.
Click Next on the Data Source Configuration Wizard. You might see the following message box when you click on Next.
Click Yes to have a copy of the sample database copied to your local project folder.
Click Next button and have a connection string created and stored in the application's configuration file
At the next step, select the table Customers and click Finish.
When the wizard completes, a Typed Dataset will be created for the Customers table. You will notice that Northwind.sdf and
NorthwindDataSet.xsd will be added to your project.
Now open app.config and you will notice that the following entry is added to your configuration file.
5 <connectionStrings>
6 <add name="SQLCE_hello_world.My.MySettings.NorthwindConnectionString"
7 connectionString="Data Source ="|DataDirectory|\Northwind.sdf""
8 providerName="Microsoft.SqlServerCe.Client" />
9 </connectionStrings>
Now, let us add a Grid view control to the form. Configure the Data Source property to point to the Customers Data Set.
We are ready to run the application now. Compile and run the application and you will see that the application will load customer data
on the Grid View control we added on the form.
Conclusions
The above application gives a basic introduction to SQL Server Compact Edition. The purpose of the above demonstration is to show the basics
of working with SQL Server Compact Edition by creating a data source, connecting to the data base and retrieving the data.
