Saturday, August 27, 2016

Create Apps using SQL Server Compact Edition Version 4

Microsoft SQL Server Compact is an embedded(able) database ideal for desktop, (hand-held) mobile devices and web clients. It is free to download (also free to deploy and redistribute) and comes in the form of just one code-free file. Its small foot print makes it easy to  deploy to a variety of device sizes and requires no administration. It also supports a subset of T-SQL and a rich set of data types.

The latest version in Version 4.0.8482.1 and you can download from here:
https://www.microsoft.com/en-us/download/details.aspx?id=17876

New features in SQL Server Compact 4.0 (per Microsoft site):

  • SQL Server Compact 4.0 is the default database for Microsoft WebMatrix, which is a stack of web technologies for easily building and deploying websites on the Windows platform.
  • For enhanced development and debugging capabilities, including designer support, Visual Studio can be used to develop ASP.NET web applications and websites using SQL Server Compact 4.0.
  • Enabled to work in the medium or partial trust environments in the web servers, and can be easily deployed along with the website to the third party website hosting service providers.
  • Private deployment of SQL Server Compact further simplified by having all the required managed assemblies, and the x86 and x64 DLLs, including the Visual C++ 2008 runtime libraries (x86 and x64) SP1 in a single private folder in the install location of SQL Server Compact.
  • Stronger data security with the use of the SHA2 encryption algorithms for encrypting the databases.
  • T-SQL syntax enhancement by adding support for OFFSET and FETCH that can be used to write paging queries and new APIs like SqlCeConnection.GetSchema that provides the metadata information of the database.
  • Used with ADO.NET Entity Framework, SQL Server Compact now supports the columns that have server generated keys like identity, rowguid etc. and the code-first programming model.

    These article provides sufficient information for the previous version and some starting points:
Basics of SQL Server Compact 3.5:

http://hodentekmsss.blogspot.com/2010/07/sql-server-compact-35-basics-you-must.html

http://hodentek.blogspot.com/2009/11/connecting-to-sql-server-compact-from.html

http://hodentek.blogspot.com/2010/07/sql-server-compact-35-with-sql-server.html