Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLCE Workshop III - Getting started with 3.5 Beta

By Jacob Sebastian,

Introduction

This is the third part of a series of articles presenting SQL SERVER CE, the new light-weight database engine released by Microsoft recently. In the last two articles we have had a basic overview of the product, its capabilities and functionalities. You can find the previous articles in this series here:

  1. Part I - An Introduction to SQL Server Everywhere
  2. Part II - A Closer look at SQL Server Compact Edition

In this session we will start working with SQLCE 3.5 Beta, officially named "Microsoft SQL Server Compact 3.5 for Windows Desktop Beta 2". You can download and install the product from here. The setup is just 1.8 MB and is one of the easiest setup I have ever seen. You can complete the setup in a few seconds and two-three mouse clicks.

SQLCE 3.5 Beta supports side-by-side installation with the previous versions. So if you have version 3.1 installed already, it is not a problem. Under default settings, the installer will install the binaries at C:\Program Files\Microsoft SQL Server Compact Edition\v3.5. If you look at the installed folder, you will see that we have the same set of DLLs but renamed with a "35" at the end, which indicates version 3.5.

In the previous version we had all DLLs suffixed with "30". One additional difference that you might note is that the DLL System.Data.SqlServerCe.dll is moved to the folder Desktop. The total size of all the DLLs of version 3.5 Beta is 1.75 where as the previous version was 1.61. Not a big change though.

Create a Database

The focus of this article is to get started with SQLCE 3.5 Beta. Let us start by creating a Database. There are different ways to create a database. Let us first look into SQL Server Management Studio and see how we can create a new database. Open SQL Server Management Studio and select SQL Server Mobile as Server Type.

If you are using the Management Studio of KATMAI, you will see SQL Server Compact Edition, instead of SQL Server Mobile.

Let us now go ahead and create a database named Employee. The default extension of an SQLCE database is sdf. However, you can change it to any extension that you would like. An SQLCE database has only data and no code. Hence the database will work even if you change the extension to something different. The other versions of SQLSERVER does not allow to change the extension of the database file. The data files those versions cannot take any extension other than ".mdf".

If you wish, you can protect your database file with a password. If you do not assign a password, SQL Server Management Studio will remind you about it with the following dialog. For the purpose of this example, you can click YES and go ahead without a password.

Now, click on the Connect button to establish a connection to the database we just created.

After you connect to the database, you will see the familiar Management Studio interface. Note that the tree view of the Object Explorer shows lesser number of objects than we used to see with other versions of SQL Server. That is true. In the previous sessions, we have seen that SQL CE does not support Views or Stored Procedures. Many of the stuff that you used to see in other versions of SQL Server are not available with SQLCE. We will look at the details on what is missing later on.

Create a table

At this stage, we will create a table that we will use in our examples. From SQL Server Management Studio, open object explorer and click on New Query. You will see the familiar Query Designer. Run the following code to create the Employees table. This version of the Employees table has only a minimum number of columns. We will enhance this schema as we go ahead with more detailed examples.

    1 CREATE TABLE Employees (

    2     EmployeeID INT IDENTITY(1,1),

    3     EmployeeName NVARCHAR(50),

    4     EmployeeCode NVARCHAR(10)

    5 )

DOT NET application

Next, let us try to create a small .NET application which connects to the SQLCE database and inserts a few records. Create a new VB.NET Console application project. Once the project is created, add a reference to the dll "System.Data.SqlServerCe.dll" located at the C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop folder.

Let us write the code to connect to the SQLCE database that we created previously and insert a new record to the Employees table. Here is the VB.NET code.

    1 'Import "SqlServerCe" Namespace

    2 Imports System.Data.SqlServerCe

    3 Module Module1

    4 

    5     Sub Main()

    6         'Create and open the connection

    7         Dim con As SqlCeConnection

    8         Dim str As String = "Data Source =""F:\employee.sdf"";"

    9         con = New SqlCeConnection(str)

   10         con.Open()

   11 

   12         'Execute an Insert Query

   13         Dim Qry As String

   14         Qry = "INSERT INTO Employees(EmployeeName, EmployeeCode) "

   15         Qry = Qry + String.Format("VALUES ('{0}','{1}')", "Jacob", "J001")

   16         Dim cmd As SqlCeCommand = New SqlCeCommand(Qry, con)

   17         cmd.ExecuteNonQuery()

   18 

   19         'Clean up

   20         con.Close()

   21         con.Dispose()

   22         cmd.Dispose()

   23     End Sub

   24 

   25 End Module

If you are a C# programmer, you might be more comfortable with C#. Create a C# console application project and add a reference to "System.Data.SqlServerCe". Here is the C# version of the above code. 

    1 using System;

    2 using System.Collections.Generic;

    3 using System.Text;

    4 //Import "SqlServerCe" namespace

    5 using System.Data.SqlServerCe;

    6 

    7 namespace ConsoleApplication1

    8 {

    9     class Program

   10     {

   11         static void Main(string[] args)

   12         {

   13             //Create and open the connection

   14             SqlCeConnection con;

   15             String str = "Data Source =\"F:\\employee.sdf\";";

   16             con = new SqlCeConnection(str);

   17             con.Open();

   18 

   19             //Execute an Insert Query

   20             String Qry;

   21             Qry = "INSERT INTO Employees(EmployeeName, EmployeeCode) ";

   22             Qry = Qry + String.Format("VALUES ('{0}','{1}')", "Jacob", "J001");

   23             SqlCeCommand cmd = new SqlCeCommand(Qry, con);

   24             cmd.ExecuteNonQuery();

   25 

   26             //Clean up

   27             con.Close();

   28             con.Dispose();

   29             cmd.Dispose();

   30         }

   31     }

   32 }

Conclusions

I hope this session has helped some of you to get started with SQLCE 3.5. We have discussed only elementary stuff in this chapter. The examples may not be good enough for a production application. The intention of the examples presented in this chapter was to get started with SQLCE programming. We will discuss more advanced usages and features of SQLCE in the coming sessions.

Resources:

Module1.vb | Program.cs | sql1.sql
Total article views: 1917 | Views in the last 30 days: 10
 
Related Articles
FORUM

SQLCE and WINCE device symbol mc3090

SQLCE directly to a SQL SERVER 2000

BLOG

Restoring a database from higher version to lower version SQL server

Restoring a database from higher version to lower version SQL server How to downgrade a database fr...

FORUM

Create a sql server 2005 database in a sql server 2008 server

create database with format of previous versions of sql server backward compatible replication betwe...

FORUM

Do temp tables exist in sqlce 2005?

Can you create temporary tables in sqlce 2005?

FORUM

Create Database

Create database taking forever

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones