SQL Server Compact 3.5 : Basics you must know

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. It can be used in creating desktop/web applications using Visual Studio 2008 and Visual Studio 2010.

The following two articles provide the basic  knowledge you should possess if you are planning to work with this database:

Managing SQL Server Compact 3.5 in SQL Server Management Studio

In this article managing SQL Server Compact 3.5 from the SQL Server Management Studio which is installed with SQL Server 2008 R2 Nov-CTP (or R2 RTM) will be described. Alternately one could also use SSMS bundled with SQL Server Express 2008. Creating databases, tabels, indexes and nested queries are also described.

http://www.sswug.org/articles/viewarticle.aspx?id=50315

Programmatic Connection to SQL Server Compact 3.5 using Visual Studio

Connection string is the entry point to develop any data-centric application that includes handheld or mobile platform. It really is the first step. If you are accessing SQL Server Compact then this article tells you all that you need to know about connecting to SQL Server Compact from Visual Studio, be it the latest VS2010 or an older data technology.


Check out this article and let me know if you liked it.
http://www.packtpub.com/article/connecting-microsoft-sql-server-compact-3.5-visual-studio

After connecting you can develop a Pocket PC forms application as shown in this article on the same site:

http://www.packtpub.com/article/pocket-pc-application-visual-studio-2005

SQL Server 2008 R2 and Data-Tier Applications

DATPACs
In the August CTP Microsoft introduced Data-Tier Applications and several new features were introduced in the Nov 2009 CTP. Registering; Viewing & comparing and upgrading Data-Tier applications were added.

A Data Application Component is an entity that integrates all data tier related objects used in authoring, deploying and managing into a single unit instead of working with them separately. Programmatically DACs belong to classes that are found in The Microsoft.SqlServer.Management.Dac namespace. DACs are stored in a DacStore and managed centrally. DACs can be authored and built using SQL Server Data-Tier Application templates in VS2010 (now in Beta 2) or using SQL Server Management Studio. This article describes creating DAC using SQL Server 2008 R2 Nov-CTP(R2 server in this article), a new feature in this version.

The following two articles treat DATPACs from two different angles. The first part is what you or a DBA can do in SQL Server Management Studio and in the second what a developer can do in Visual Studio.

Using SQL Server Management Studio
In the first part of this series [http://www.packtpub.com/article/working-with-data-application-components-sql-server-2008-r2] the author shows shows how you may extract a Data Application Component from a SQL Server 2008 and then deploy the component to an SQL Server 2008 R2 Server. The process is Wizard driven and very easy to understand. DAC files in the field of DATA are like *.msi files for applications. Just as you would install an application from an MSI file, you would install a database package with the DAC file with the extension DACPAC. You may even email a database. The cited article shows how all this is done.




Using Visual Studio
In  the second part of this series[http://www.sswug.org/articles/viewarticle.aspx?id=49844] the author shows how you may create a DATPAC from scratch in your Visual Studio IDE. You will start creating a DATPAC which has database with  a table and a view based on the table  even when not  connected to a server. You will then manually deploy the DATPAC to the server after connecting to it and complete the deployment.