Start With SQL Compact Edition (SQL CE)

Hi Further on my previous article for introduction to SQLCE (Check it), i am going to walk you through to SQLCE with a very simple application.This sample will guide you from adding a local database to creating a connection and some CRUD operations.

Adding a SQLCE database to Project

• Add a new Item to the project .

• Select the DATA option and select  local Database option.Provide a database name and click OK

• You can find the local Database as bellow.

• You need to have a reference to the SqlServerce.dll .

• To browse the Database , Right Click on Model.sdf and click Open.Then you will be able to get the table details.To add a new table Right click on Tables and click on Add table.In this case i have created 2 tables as shown in figure.

Creating a connection from C# project

The System.Data.SqlServerCe namespace is the .NET Compact Framework Data Provider for SQL Server CE.  WithSystem.Data.SqlServerCe, you can create and manage SQL Server Mobile databases on a smart device and also establish connections to SQL Server databases.

1. public void Connect()

2.  {

3. 	 string FileName="Model.sdf";

4. 	SqlCeConnection con=new SqlCeConnection(@"Data Source=|DataDirectory|\"+ filename 	+".sdf;Password=123"          );

5. 	con.Open();

6. }

Retrieving Data

The following code perform simple Data retrieval from the SDF file based on the sqlcecommand.

	

 private void RetriveData()
	

        {
	

            string FileName="Model.sdf";
	

            SqlCeConnection con = new SqlCeConnection(@"Data Source=|DataDirectory|\" + FileName + ".sdf;Password=123");
	

            con.Open();
	

            SqlCeCommand comm = new SqlCeCommand("Select * from EmployeeMaster", con );
	

            SqlCeDataAdapter  sqlceda = new SqlCeDataAdapter(comm);
	

            DataSet dsData=new DataSet("EMPLOYEE");
	

            sqlceda.Fill(dsData);
	

            gvEmpMaster.DataSource = ds.Tables[0];
	

        }

Get the sample Application code

The sample application stimulate a typical windows based application using sql CE.This application consist of a wireframe database (model.sdf) which will be used to create other files and based on it the user can insert data and later retrieve it.

Sample Application Download

SQL Server CE (compact Edition)

Good news for small application developers , Microsoft released compact version of SQLSERVER (SQLCE 4.0 CTP1 ) for desktop ,web as well as for mobiles.This embedded database format is a huge relief from cumbersome and hectic installation of sql server .Although this was from long back still its reliability and features introduced in 4.0 CTP1 is awesome.I would like to introduce the some review and remarks on it in a very short manner.

Sql Server Compact Edition(SQLCE) is a embedded database for Mobile,Desktop and Asp.Net based projects.

For Whom :

• If you are going to develop small/Mid level office application.
• If you want hassle free installation of Database (Copy and Paste option)

What is the Latest Version :

Latest Version : 4.00 CTP1 Download

Last Release : 3.5 SP 2 Download

Some Facts -:

• Supports Data up to 4 GB.
• File based code free Database
• Supports concurrent multiple connection from .
• Runs in process of Application (Other MS sql Editions used to have separate services)
• SQL CE databases reside in a single .sdf file.
• The .sdf files / Database can be password protected as well as encrypted.
• SQL CE databases are ACID-compliant. Like Microsoft SQL Server,
• SQL CE supports transactions, referential integrity constraints, locking as well as multiple connections to the database store

Advantages

• Very Small footprint(2 to 2.05 MB).
• File based Database with enhanced security option.
• All you need is Copying of binary to your installation path.
• Easily Upgradeable to higher versions (MSSQL Express etc..)

Limitations

• Doesn’t support Stored Procedures, also they don’t have plan for it. Go through the nice article from Steve.Lasker Read it.

Just have a look at the detailed comparison matrix with sqlExpress Edition.

Feature	SQL Server Compact Edition	SQL Server Express Edition
Deployment/Installation Features
Installation size	1.7mb download size 1.8mb expanded on disk	53.8mb download size ~197mb expanded on disk
ClickOnce deployment
Privately installed, embedded, with the application
Non-admin installation option
Runs on Windows Mobile platform
Installed centrally with an MSI
Runs in-process with application
64-bit support		Windows on Windows (WOW)
Runs as a service	In process with the application	N + 1
Data file features
File format	Single file	Multiple files
Data file storage on a network share
Support for different file extensions
Database size support	4GB	4GB
XML storage	stored as nText
Code free, document safe, file format
Programmability
Transact-SQL Common Query Features
Procedural T-SQL Select Case, If, features
Remote Data Access (RDA)
ADO.NET Sync Framework		Planed support with a future version
Subscriber for merge replication
Simple transactions
Distributed transactions
Native XML, XQuery/QPath
Stored procedures, views, triggers
Role-based security
Number of concurrent connections	256	Unlimited

Hi World ..

Hi all , this is my first blog after a long persue from my friends .It is always good to start late instead of not doing it.Ok my self Manas as you can find from domain name , i  am working in MNC , India as Technology Analyst and loves dancing.

Before i took up blogging i asked my self so many time times  “Why should i blog” .

As i got the answers after a lot of  in fight  with my consensus i want to share with you. As a human and as a engineer you need to improve and you need to excel in what ever you do and this improvement can be achieved if you analyse the work you have done and the experience you learned. I would like to share one quote :

“If you re-read your work, you can avoid a great deal of repetition and can achieve  improvement in future endeavor ”

So thats it 🙂 now i am ready to share it with me as well as  all of you .

I am going to write about my work experience related to Technology , life , business and what ever i feel i need to share with all.Of course i need you feed back and one line comment that will help me to do better .

Keep Reading .