Introduce SQL Server Compact Note

Embeded Engine base on set of dlls

            -not need other installation required except framework

            -not run as a service

   -In-proc   

What it is?

            -very smallembeded(~1MB), in-proc database

            -nice subset of SQL Server types

                        -Does not have: store proce, views, Xquery                  

Where it's deployed?

            -moblie,embeded devices

            -etc...       

Scenarios

            -localonline/offline cache

            -local,stand alone application database

     

Code in SQL Server Compact Edition

            -Add Reference:

                        -System.Data.SqlServerCe

            -ConnectionString

                        -DataSource:|Data Directory|dbname.sdf

                        -EncryptionMode

                        -Maxdatabase Size: 128(MB) default

                        -Mode:R/W (read/write)

           

Deploy SQL Server Compact Edition

-Traditional Windows Installer

-To use Windows Installer technology for ClickOnce deployment of an application that contains a SQL Server Compact 3.5 database

-notifies the installer to check whether the SQL Server Compact 3.5 runtime existsand to install it from the Internet if it is not found.

-Private File–Based Deployment

-Does not require to install SQL Server Compact 3.5, http://msdn.microsoft.com/en-us/library/aa983326(v=vs.90).aspx

            -Change"System.Data.SqlServer.dll" reference to Copy Local

            -Copy"sqlce*.dll" from installation directory and set Copy Local to true

 

Create Database and Populate Schema

            -normally not deploy *.sdf file to project

            -1)createdatabase

                        e.g.:

                                    UsinglocalConnection As New SqlCeConnection(My.Setting.LocalConnectionString)

                                                IfNot System.IO.File.Exists(localConnection.Database) Then

                                                            UsingsqlCeEngine As New SqlCeEngine(connectionString)

                                                                        slCeEngine.CreateDatabase()

                                                            EndUsing

                                                EndIf

                                    EndUsing

            -2)executecommand base on "SqlCeCommand"

                        e.g.:

                                    cmd.CommandText="..."

                                    cmd.ExecuteNonQuery()

 Sync Services for SQL Server Compact Edition 3.5 in Visual Studio 2008   

-http://keithelder.net/2007/09/23/sync-services-for-sql-server-compact-edition-3-5-in-visual/