SQL Server Compact 4.0 CTP2 (V4.0.8435.01) released

Microsoft SQL Server Compact 4.0 Community Technology Preview for Windows Desktop released and available for download.

Link to Download –:SQLCE CTP 2 ( V 4.0.8435.01)

Changes and Enhancements

• SQL Server Compact 4.0 CTP is also installed as part of WebMatrix. Microsoft WebMatrix is an integrated suite of tools designed to make it easy for people of all skill levels to build dynamic and secure ASP.Net Web applications.
• Can run within the ASP.NET environment to provide the database functionality needed by the starter websites.
• Supports running in medium trust environments in the web server.
• Ability to write paging queries using the OFFSET and FETCH T-SQL Syntaxes
• Better Support for private deployment by having a single private folder (“%ProgramFiles(x86)%\Microsoft SQL Server Compact Edition\v4.0\Private”) containing both the AMD64 and X86 binaries
• Support for SqlCeConnection.GetSchema method to provide the metadata information of the database and its child objects.
• Better encryption facility with the use of the latest Encryption Algorithms (AES-256 and SHA2)
• Tested and certified to handle the starter website load effectively and efficiently without any hangs, crashes, exceptions etc.

Integrate/Install SQLServer Compact (SQLCE) with a ZIP file

As we know SqlCE database is a collection of dlls and can be distributed along with your applications with out any additional  installation.Now integrating sqlserver Compact  was never easy before Nuget ( formerly NuPack ) .It is open source ,completely free and comes as a VS add in.This post we will have a look into NuGet features and will integrate SQLCE to our project with out any installation.

• Download and install Nuget from codeplex.

Convert MS Access/SQLExpress Database to SQLCE

Converting the existing access database to SQLCE is a pain as the SQLCE does not support create table sql queries.So to make our work easier there is a tool which makes it seamlessly easy.

Yes DataPortWizard (Check Here) tool from PrimeWork provides a easier wizard based solution for migration. It Quickly and easily transfer databases from the desktop to the Mobile Device and back. Data Port Wizard will make full copies of Microsoft Access or Microsoft SQL Server databases into SQL Server Compact Edition 4.0 (CTP1), 3.5 and 3.0, and SQL CE 2.0 databases – and back.

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