innovation-and-technology21-blog

[innovation-and-technology21-blog]

FAQ: Why is opening my SQL Server Compact database slow?

You may experience, that opening a connection to a SQL Server Compact database file using SqlCeConnection.Open() (or doing this via EntityFramework or LINQ to SQL) can be unexpectedly slow (up to 30 seconds, for example). In this blog post I will detail the known reasons for this to occur, and outline solutions and workarounds. 1: The database has been created on another platform One of the most common issues is that the database file has been created and populated on another platform than the one where the app is running .Open(). For example: a database file is included with the app, a database file is downloaded from a server or similar. Different platforms are for example Windows 8 versus Windows XP, and Windows 7 versus Windows Mobile. Notice, that Windows XP, Server 2003 and Windows Mobile are compatible platforms. And Windows 8 and Windows Phone appear t be compatible in most cases also. The product team did a blog post about the issue many years ago, but the issue is otherwise poorly documented, and therefore pops up again and again. There is no solution to the issue, only workarounds. Workarounds: – Create the database file on the platform after app installation, using for example my scripting APIs. – Include a database file per target platform, that is already built/opened on the target platform (in particular an issue for read-only databases, see my blog post here) - Avoid indexes with string based keys (probably not possible in most cases) 2: The ACL (Access Control List) on the RSA folder is corrupt This issue appears to happen only on Windows XP system and only with encrypted databases. The ACL on the C:Documents and SettingsAll UsersApplication DataMicrosoftCryptoRSAMachineKeys folder are corrupted, and users are unable to access file in this central folder. Solution: Reset the ACL to allows all users Read access, either using the UI or via the xcacls command line tool. 3: Invalid Internet Proxy configuration This appears to occur for SQL Server Compact 4.0 on any system with an invalid proxy configuration, as a Certificate Revocation List check is run each time the engine is loaded (which happens on the first call to .Open()). Solution: To avoid this delay, which probably affects any signed app on the system in question, you must fix the configuration or disable the check. The check can be disabled via UI or via registry settings, as described here. Hope this helps you troubleshooting slow database opening with SQL Server Compact.

[innovation-and-technology21-blog]

Tuning the Performance of Backup Compression in SQL Server

Here is a great read on Tuning the Performance of Backup Compression in SQL Server http://sqlcat.com/sqlcat/b/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx And a cheeky query to help give you a guide on the size of your backup if it was compressed ! SELECT b.database_name 'Database Name', CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)', CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)', CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) / CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio', DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)' FROM msdb.dbo.backupset b WHERE DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0 AND b.backup_size > 0 ORDER BY b.backup_finish_date DESC Table 1 shows the output of the above query after taking an uncompressed and a compressed backup. Database NameUncompressed Backup Size (MB)Compressed Backup Size (MB)Compression RatioBackup Time (Seconds)CommentsBCTEST292705959073.051705Compressed backupBCTEST29270529270513348Uncompressed backup

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #18 : handling SqlCeExceptions

Often I see developers complain that the error messages from SQL Server Compact does not provide any information about the error (in SqlCeEcxeption.Message). This is due to to way that the SqlCeException object is shaped, as it contains a SqlCeErrors collection and other properties you must inspect in order to get “full disclosure” on the error information. Getting this complete information of course helps a lot in troubleshooting the root causes for the errors. (The exact same pattern applies to SqlException, which also has a collection of SqlCeErrors) Remember in your error handling to catch any specialized exceptions (like SqlCeException) before the more general Exception object: try { //Data access code here } catch (SqlCeException scx) { } catch (Exception ex) { } For getting full error information, I use the code below: public static string ShowErrors(System.Data.SqlServerCe.SqlCeException e) { System.Data.SqlServerCe.SqlCeErrorCollection errorCollection = e.Errors; StringBuilder bld = new StringBuilder(); Exception inner = e.InnerException; if (!string.IsNullOrEmpty(e.HelpLink)) { bld.Append("nCommand text: "); bld.Append(e.HelpLink); } if (null != inner) { bld.Append("nInner Exception: " + inner.ToString()); } // Enumerate the errors to a message box. foreach (System.Data.SqlServerCe.SqlCeError err in errorCollection) { bld.Append("n Error Code: " + err.HResult.ToString("X", System.Globalization.CultureInfo.InvariantCulture)); bld.Append("n Message : " + err.Message); bld.Append("n Minor Err.: " + err.NativeError); bld.Append("n Source : " + err.Source); // Enumerate each numeric parameter for the error. foreach (int numPar in err.NumericErrorParameters) { if (0 != numPar) bld.Append("n Num. Par. : " + numPar); } // Enumerate each string parameter for the error. foreach (string errPar in err.ErrorParameters) { if (!string.IsNullOrEmpty(errPar)) bld.Append("n Err. Par. : " + errPar); } } return bld.ToString(); }

[innovation-and-technology21-blog]

Hekaton Explained

Here are some good sessions I found on SQL Server 2014 and the In-Memory OLTP (codenamed ‘Hekaton’) feature. Microsoft SQL Server In-Memory OLTP: Overview of Project "Hekaton". This session introduces the motivations and high-level design of the in-memory OLTP system, and Rick Kutschera (from BWin) sharing their experience migrating to the new ecosystem. Microsoft SQL Server In-Memory OLTP Project "Hekaton": App Dev Deep Dive. Here, Sunil presents the developer side of In-Memory OLTP, this talk is very useful if you are planning to migrate parts of your workload to Hekaton. Microsoft SQL Server In-Memory OLTP Project "Hekaton": Management Deep Dive. This session goes into depth about the storage structures, durability, transaction logging, backup / restore etc. AMAZING stuff!

[innovation-and-technology21-blog]

SQL Server Compact Toolbox 3.4–Visual Guide of new features

After nearly 190.000 downloads, version 3.4 of my free, open source SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker Data compare The major new feature of this release is the table based data compare feature, that will generate a script with INSERT, UPDATE and DELETE statements to make two tables contains the same data. The two tables must have compatible schemas and same names. The feature works across both SQL Server and SQL Server Compact tables. To try out this new (beta) feature, right click on a table and select “Compare Data…”: Select the target database: A script with the required statements will then open in the SQL editor.

Database Information

This feature will script information about the selected database in the SQL editor, both general information about the database, including Locale ID and case sensitivity, and also list number of rows for all user tables.

Maximum column width in Edit grid

This new option allows you to set a limit on the column width in the edit grid, useful if you have some columns with very long text string, and you want them all to be visible. After setting the option to for example 200 pixels:

Visual Studio 2013 support

Server Explorer in Visual Studio 2013 no longer supports SQL Server Compact 4.0, and other tools that depend on Server Explorer (DDEX) will no longer work with SQL Server Compact 4.0. However, you can still use the SQL Server Compact Toolbox in Visual Studio 2013, both with version 3.5 and 4.0 database files. The only requirement is that you have the relevant SQL Compact runtime MSIs installed. In addition, the Toolbox supports code generation of LINQ to SQL DataContext classes, both for Windows Phone 7.5/8 and Desktop apps. For Entity Framework, no code generation is required provided you use the Code First workflow.

Other improvements and bug fixes

Validate Connections improved Merge Replication save properties fixed CSV import unicode issue fixed + improved error message milliseconds included in datetime in text results SQLite script contained GO separators, they are no longer there Avoided scripting spatial indexes (SQL Server scripting)

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #17 : using wildcards with a parameterized query

This “week”’s code snippet simply demonstrates how to use a parameterized query with LIKE and a search string containing wildcards. The simple solution is basically to add the wildcard character (% or ?) directly to the search string.public static List GetCompletionList(string prefixText = "%orch%") { //TODO Add error handling List Names = new List(); using (SqlCeConnection con = new SqlCeConnection(@"Data Source=C:projectsChinookChinook40.sdf")) { con.Open(); using (SqlCeCommand cmd = new SqlCeCommand("SELECT Name FROM Artist WHERE Name LIKE @Name", con)) { cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 120).Value = prefixText; using (SqlCeDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Names.Add(reader.ToString()); } } } } return Names; }

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #16 : detect Entity Framework database type

This weeks code snippet is prompted by a StackOverflow question, regarding how to detect if the database that was backing an Entity Framework DbContext was SQL Server Compact. My proposed check simply uses the "is" operator to determine if the Database.Connection property is compatible with the SqlCeConnection class. so my check is:context.Database.Connection is SqlCeConnection

[innovation-and-technology21-blog]

INSERTing many rows with Entity Framework 6 beta 1 and SQL Server Compact

In this blog post I will demonstrate a couple of improvements for adding many entities to a Entity Framework based database. You can read more about the beta 1 release here, and Julie Lerman highlights some of the features that were available in the alpha here. For all full list of EF 6 features, see the list here. Here we will look at getting started with Entity Framework 6 beta 1, and a couple of improvements that makes adding many rows to a SQL Server Compact database via Entity Framework feasible, and also have look at using my SqlCeBulkCopy library to do the same. I will use a console app for this project in order to focus on the Entity Framework code. To get started, launch Visual Studio, and create a new Console Application. Lets call it EF6Test. Now let’s add Entity Framework 6 beta 1 SQL Server Compact package. Launch the NuGet Package Mangager Console (from Tools, Other Windows) and run this command: PM> Install-Package EntityFramework.SqlServerCompact -Pre (The –Pre switch allows you to install pre-release packages) You should now see several messages in the window, the last one being: Successfully added 'EntityFramework.SqlServerCompact 6.0.0-beta1-20603' to EF6Test. This process has added a number of DLL references to the project, and added an app.config file to the project, with an entityFramework section that specifies the SQL Server Compact default connection factory:                               Now add a using statement: using System.Data.Entity; And add the following 2 classes before “class Program”, these define our single test table and our DbContext: public class Student { public int StudentId { get; set; } public string Name { get; set; } } public class StudentContext : DbContext { public DbSet Students { get; set; } } Now add the following code to the Main method: 1: Stopwatch sw = new Stopwatch(); 2: bool useSqlCeBulkCopy = false; 3: var students = CreateStudents(); 4:   5: Database.SetInitializer(new DropCreateDatabaseAlways()); 6: 7: using (var db = new StudentContext()) 8: { 9: db.Database.Initialize(true); 10: if (!useSqlCeBulkCopy) 11: { 12: sw.Restart(); 13: //AddRange rulez, no need for db.Configuration.AutoDetectChangesEnabled = false; 14: db.Students.AddRange(students); 15: sw.Stop(); 16: 17: Console.WriteLine( 18: "Added 8000 entities in {0}", sw.Elapsed.ToString()); 19: 20: sw.Restart(); 21: int recordsAffected = db.SaveChanges(); 22: sw.Stop(); 23:   24: Console.WriteLine( 25: "Saved {0} entities in {1}", recordsAffected, sw.Elapsed.ToString()); 26: 27: } 28: Console.ReadKey(); 29: } 30: } 31:   32: private static List CreateStudents() 33: { 34: var students = new List(); 35: for (int i = 0; i < 8000; i++) 36: { 37: var student = new Student { Name = Guid.NewGuid().ToString() }; 38: students.Add(student); 39: } 40: return students; 41: } The CreateStudents method simply creates a List object with 8000 Student objects. A new database is created on each run (line 5) and the students are added to the StudentContext DbContext, using the excellent new AddRange method, similar to the LINQ to SQL InsertAllOnSubmit method. With EF5 you only had the Add method, and to get reasonable performance, you had to use the cryptic db.Configuration.AutoDetectChangesEnabled = false statement. With SQL Server Compact and EF5, inserting 8000 rows takes about 58 seconds on my PC, and it may even time out on yours… Thanks to the fact that Entity Framework is now open source on CodePlex I was able to submit  a bug fix, which  got accepted for EF6, so the process now takes about 8 seconds on my PC. To compare, let’s add the SqlCeBulkCopy NuGet package and perform the same process using that. In the Package Manager Console. type PM> Install-Package ErikEJ.SqlCeBulkCopy Before Console.ReadKey, paste the following code: else { using (SqlCeBulkCopy bcp = new SqlCeBulkCopy(db.Database.Connection.ConnectionString)) { bcp.DestinationTableName = "Students"; sw.Restart(); bcp.WriteToServer(students); sw.Stop(); Console.WriteLine( "Saved {0} entities using SqlCeBulkCopy in {1}", students.Count, sw.Elapsed.ToString()); } } And set useSqlCeBulkCopy = true.On my machine this takes about 150 ms! So despite the improvements made in EF6 beta 1, for larger data loads, I suggest you use SqlCeBulkCopy, and as you can see from the code above, it is very easy to integrate in an Entity Framework context. You can download the completed project from here: http://sdrv.ms/18NaRmW

[innovation-and-technology21-blog]

Virtual Infrastructure SQL Server won't start after SP Install

As your virtual infrastructure is growing and changing rapidly a result is that the configuration of your SQL Servers could be changing also. Virtual infrastructure growth can result in additional drives and drive letter changes for our SQL servers. Unfortunately, if the default Data and Log paths are not updated when the drive letters change, there will be errors after installing (or uninstalling) a Service Pack or Cumulative Update. The GUI portion of the install will complete successfully, but there are updates that occur on the initial service start after the GUI portion of the install is complete. One update that occurs that that initial service start is database upgrades. If your default paths for data and log files are not correct the upgrade of the master database will fail and the SQL Server service will crash. In a production setting, this can induce fear, panic, and mass hysteria (or at the very least mass Google searching, which is likely how you arrived at this page to begin with). However, the solution is pretty simple as the default paths are stored in the Windows registry. Launch Registry Editor (regedit.exe) and navigate to the following section of the registry tree: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server{SQL Version}MSSQLServer Note: If you are running a 32-bit version of SQL Server on a 64-bit OS, you'll need to navigate to the Wow6432Node tree (HKEY_LOCAL_MACHINESoftwareWow6432NodeMicrosoftMicrosoft SQL Server{SQL Version}MSSQLServer) SQL 2000 {SQL Version} = MSSQLServer SQL 2005 {SQL Version} = MSSQL.1 SQL 2008 {SQL Version} = MSSQL10.MSSQLSERVER SQL 2008 R2 {SQL Version} = MSSQL10_50.MSSQLSERVER SQL 2012 {SQL Version} = MSSQL11.MSSQLSERVER Then update the paths specified in the following keys: DefaultData DefaultLogs Once the paths are correct, the master database upgrade will complete successfully (as SQL Server will be able to create the temporary files in the default paths) and once all other system and user DBs are upgraded you should be back up and running. Note: The database upgrades will take some time if you have a lot of user DBs, but you can monitor the ERRORLOG to observe the progress.

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #15 : flush data to disk immediately

Under normal operation, SQL Server Compact keeps all pending disk writes in memory, and flushes them to disk at least every 10 seconds. The connection string property to control this is called “Flush Interval”, and valid values are between 1 and 1000 seconds. But you may want to flush to disk immediately under certain circumstances, and this weeks code snippet demonstrates how to do just that. This is possible via the CommitMode property on the SqlCeTransaction object Commit method, as demonstrated below: using (SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:dataAdventureWorks.sdf;")) {     conn.Open();     // Start a local transaction; SQL Server Compact supports the following     // isolation levels: ReadCommitted, RepeatableRead, Serializable     using (SqlCeTransaction tx = conn.BeginTransaction(IsolationLevel.ReadCommitted))     {         using (SqlCeCommand cmd1 = conn.CreateCommand())         {             // To enlist a command in a transaction, set the Transaction property             cmd1.Transaction = tx;             try             {                 cmd1.CommandText = "INSERT INTO FactSalesQuota " +                     "(EmployeeKey, TimeKey, SalesAmountQuota) " +                     "VALUES (2, 1158, 150000.00)";                 cmd1.ExecuteNonQuery();                 // Commit the changes to disk immediately, if everything above succeeded;                 // Use Deferred mode for optimal performance; the changes will                 // be flashed to disk within the timespan specified in the                 // ConnectionString 'FLUSH INTERVAL' property (default 10 seconds);                 //                 tx.Commit(CommitMode.Immediate);             }             catch (Exception)             {                 tx.Rollback();             }         }     } }  

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #14 : script all data in a table

Another entry in the scripting API samples, you will find an overview of getting started with the API here. This weeks entry demonstrates the general pattern for scripting smaller chunks of SQL based on a single table, in this case a INSERT statement for each row in the table. using (IRepository repository = new DBRepository(@"Data Source=C:Northwind.sdf")) {     Generator generator = new Generator(repository, null);     foreach (var tableName in repository.GetAllTableNames())     {         generator.GenerateTableContent(tableName, false);     }     System.IO.File.WriteAllText(@"C:script.sqlce", generator.GeneratedScript); } For SQL Server Compact 4.0, the scripting library is now available on NuGet, making it even easier to get started. https://nuget.org/packages/ErikEJ.SqlCeScripting

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #13 : reseed (reset) an IDENTITY column

A question that keeps re-appearing in the forum is, how can I reseed/reset an IDENTITY column in SQL Server Compact, as no DBCC command is available. You can simply use a special syntax of the ALTER TABLE command to do this, as follows: ALTER TABLE ALTER COLUMN IDENTITY (1, 1) This will make the value of the next generated IDENTITY value 1 and increment with 1. Notice that you do not specify the column type, only the IDENTITY specification.

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #12 : get the SQL Server Compact runtime version

Your app/web site may require a specific build version of the SQL Server Compact runtime, due to dependency on a bug fix, for example. This weeks code snippet will demonstrate how to get that information. I have an older blog post here, that goes into deep details about the various ways to get SQL Server Compact related version information. To get the SQL Server Compact build version for 3.5 SP2 and 4.0, you can simply use: var ver = new System.Data.SqlServerCe.SqlCeConnection().ServerVersion; MSDN docs here. For SQL Server Compact 4.0 SP1, this returns: 4.0.8876.1. And for SQL Server Compact 3.5 SP2 CU6, the value would be: 3.5.8088.0

[innovation-and-technology21-blog]

Generate a Windows Phone 8 Local Database DataContext from an existing database

Despite my tooling for this having been available for more than 18 months, I have never posted a dedicated blog post for this subject. This post intends to remedy this. The Local Database (a SQL Server Compact database accessed via LINQ to SQL) is a data access API available on Windows Phone 7.5 and 8. The Microsoft documentation (listed here on my blog, together with many other useful Windows Phone Local Database links) always describes a Code First workflow, which makes it cumbersome to reuse existing effort in creating a SQL Server Compact database schema for Windows Mobile or desktop, and  also makes it hard to distribute a database prepopulated with data together with your app. My tooling, which is available with the SQL Server Toolbox Visual Studio add-in, and also available in a simpler form with the new standalone edition of the Toolbox for SQL Server Compact 3.5 (currently in alpha), enables both scenarios. The standalone edition is useful for VS Express users and when you do not wish to install Visual Studio on a PC (it is a single .exe file, so very simple to distribute) In the following walkthrough, using Visual Studio 2012, I will demonstrate how to use the SQL Server Compact Toolbox to take an existing SQL Server database and use it as an included Windows Phone database in an new (or existing) Windows Phone 8 App. The process to do this requires these steps: - Create the SQL Server Compact database from the server database and add it to the Windows Phone project - Generate the LINQ to SQL DataContext and releated classes. - Use the database from code I assume you have Visual Studio 2012 Pro or higher with the Windows Phone 8 SDK installed. Create the SQL Server Compact database I have created a new Windows Phone Databound App for this sample, and selected Windows Phone OS 8.0 as the target OS.

I then use the Toolbox to create a new SQL Server Compact 3.5 database in the folder where the Phone project resides, (you can determine the folder from by using the “Open Folder in File Explorer” context menu item).

I then click Create, navigate to the project folder, and type PostCodes.sdf, press OK.

Click OK, and a new, empty database will be added to the database list in the Toolbox:

Now we need to connect to the SQL Server database, and script it, then run the script against the new, empty database.

Create and save the database script using the Toolbox menu item above, and then open the SQL Editor against the PostCodes.sdf database file:

Use the Open button in the editor to load the script, and the press the Execute button to run the script.

Now the database contains a PostCode table (the script is available here), which has all Danish postcodes. The final step is adding the database file to the Phone project. In Solution Explorer, select “Show all files”, and include PostCodes.sdf. In this sample scenario, we would like the database to become writable on the Phone, so include it a “Embedded Resource” – it could also be included as Content, if it was just a read-only database, read more here.

  Generate the LINQ to SQL DataContext In order to generate the DataContext based on the database, right click it in the Toolbox, and select “Add Windows Phone DataContext to current project”. If this menu item is disabled, verify that the database file is in 3.5 format, and that the SQL Server Compact 3.5 SP2 runtime is properly installed, you can check this via the About dialog. “Yes” is required in both places, if that is not the case, repair/re-install.

Let’s walk through the various options on this dialog: Context name: The name of the generated DataContext class Namespace: Allows you to specify another namespace for the generated code Language: You can generate C# or VB code. Pluralize: If checked, will rename tables (Person => People) etc. Create a file per table: Normally, just a single file is created Advanced options: Add schema version table: If you would like to include the database file a a writeable file, and allow use of the DatabaseSchemaUpdater class in a future app version select this option . Add rowversion column to all tables: Checking this will ensure that all tables have a rowversion column (formerly timestamp), which enhances performance when doing UPDATE and DELETE (see my blog posts here and here) Include ConnectionStringBuilder: Will add a LocalDatabaseConnectionStringBuilder class to the project, to help with building connection strings in a strongly typed fashion. For this sample project, just click OK, and a PostCodesContext.cs file will be added to the project, and we are done.

Use the database from code Finally, to demonstrate that we are able to include data with the app, alter the DataTemplate in MainPage.xaml as follows:                 Replace the OnNavigatedTo event handler in MainPage.xaml.cs with the following code: protected override void OnNavigatedTo(NavigationEventArgs e) { using (PostCodesContext ctx = new PostCodesContext(PostCodesContext.ConnectionString)) { ctx.CreateIfNotExists(); ctx.LogDebug = true; MainLongListSelector.ItemsSource = ctx.PostCode.ToList(); } } This code initialize a new PostCodesContext instance (embraced in “using”, as it is Disposable). The CreateIfNotExists method extracts the PostCodes.sdf embedded resource from the project, and copies it to isolated storage (feel free to look at the code). Setting LogDebug to true will show all SQL statements as text in the Debug window while debugging: SELECT ., ., ., ., ., ., . AS , . AS FROM AS And finally, calling ToList() will execute the SELECT and return a list of PostCode objects, that is the bound to the ItemsSource property of the LongListSelector. Result:

Summary Let us finish with a summary of advantages of this approach: - Use desktop database tools for data population and schema design - Saves time doing 1:1 mapping between database tables and DataContext classes - DataContext class and entity classes are partial and can be extended - The generated DataContext contains Index definitions (which SqlMetal does not support, as this is a Windows Phone extension) - The generated DataContext contains the CreateIfNotExists method, that optionally extracts an included database (prepopulated with data) to Isolated Storage - The generated DataContext includes the LogDebug property, that allows you to see all SQL statements generated on the device in the debug window - Optionally split the generated Data Context classes into multiple files - Optionally add a Version table if you include the table with your app, and want to enable use of the schema updater functionality. - Optionally add rowversion columns to improve UPDATE and DELETE performance - Optionally include a ConnectionStringBuilder class to build a valid connection string in a strongly typed way,  using advanced connection string options (see some of my Phone blog posts for candidates) Hope you find it useful. UPDATE: Comments have now been closed, please contact me for any issues via the Codeplex issue tracker here: http://sqlcetoolbox.codeplex.com/issues/list

[innovation-and-technology21-blog]

SQL Server Compact Code Snippet of the Week #11 : detect if SQL Server Compact is available

It can sometimes by useful to find out if SQL Server Compact is available on a system, for example in installers and if your application supports several database products. This week’s code snippet allows you to detect if the ADO.NET Provider is available and properly installed (it does not detect if the unmanaged DLL files are present). The code detects both if the AD.NET Provider is present, and if the DbProvider interface is properly registered (either in machine.config or in a local config file)public bool IsV40Installed() { try { System.Reflection.Assembly.Load("System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"); } catch (System.IO.FileNotFoundException) { return false; } try { var factory = System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0"); } catch (System.Configuration.ConfigurationException) { return false; } catch (System.ArgumentException) { return false; } return true; } Replace Version=4.0.0.0 with Version=3.5.1.0 and use System.Data.SqlServerCe.3.5 for the DbProvider invariant name for SQL Server Compact 3.5

[innovation-and-technology21-blog]

Entity Framework 6 & SQL Server Compact (3)–Getting started with the SQL Server Compact 3.5 provider (and Merge Replication)

As you may know, SQL Server Compact version 4.0 does not have support for Merge Replication and Remote Data Access (you can still use RDA, however). This was a showstopper if you wanted to use the latest version of Entity Framework, as up until now, only SQL Server Compact version 4.0 was supported with Entity Framework 5 and 6. But now a SQL Server Compact 3.5 provider for Entity Framework 6 is available, currently as pre-release on NuGet. The new 3.5 provider is based on the exact same codebase as the 4.0 provider, and thus has all the new features (SqlCeFunctions, Migrations etc.), bug fixes and performance improvements included in the 4.0 provider. In addition, this new provider supports IDENTITY Keys, just like the 4.0 provider. (A showstopper for many with the present 3.5 provider). As it is based on the same codebase as the 4.0 provider, any future improvements and bug fixes will also be included with this provider. This blog post will describe how you can get started with the new 3.5 provider with Entity Framework 6, and will also include some pointers on getting started with Merge Replication. I will assume that you have Visual Studio 2012 or 2013 Professional or higher installed, and also have my SQL Server Compact Toolbox add-in installed. If you are using Visual Studio 2012 and Database First, you must also have the Entity Framework 6.0.2 Tools installed, download from here. And of course you must have the SQL Server Compact 3.5 SP2 Desktop runtime installed. So let us create a new console app, that uses Entity Framework 6 and SQL Server Compact 3.5, and which could potentially be a Merge Replication subscriber. Create a new Console project: For this walkthrough, we will use Database First, but you can of course also use Code First. Add the EntityFramework.SqlServerCompact.Legacy NuGet package to the project (remember to allow pre-releases), by right clicking the project and selecting Manage NuGet Packages… This will install Entity Framework 6.1-alpha1 and the 3.5 provider and add the required registration in app.config. Build the project. Connect/create the database that you want to use in the SQL Server Compact Toolbox, right click it, and select “Add Entity Data Model to current Project” Just click OK: This will add Chinook.edmx and invoke code generation that builds a DbContext derived class and POCO classes for each table in your database. You can now add Merge Replication to your solution, you can start by installing my Merge Replication client helper library via NuGet, http://www.nuget.org/packages/ErikEJ.SqlCeMergeLib/, read more about it here: https://sqlcemergelib.codeplex.com/ In order to configure Merge Replication on your SQL Server database and web server, I have a brief blog post here, but otherwise I can highly recommend the book by Rob Tiffany. Notice that if you want to add Merge Replication to a SQL Server 2012 database, you need SP1 and CU4 or later, and you will need a recent build (8088 or newer) of the SQL Server Comapct 3.5 runtime installed, as listed in my blog post here. Hopefully you will now be able to get started with this combination of the latest Microsoft data access technology and  “legacy” technologies like Merge Replication and Sync Framework. If you have any questions, please ask in the MSDN forum or Stack Overflow.

[innovation-and-technology21-blog]

SQL Server Compact Toolbox 3.7–Visual Guide of new features

After more than 235.000 downloads, version 3.7 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download (and available via Tools/Extensions in Visual Studio). This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker. A major “theme” for this release has been to improve the experience for Visual Studio 2013 users, which has no Server Explorer/DDEX support for SQL Server Compact at all. Add Column, Index and Foreign Key to table As Server Explorer support is completely missing in VS 2013, and only available for 4.0 in VS 2012, I have added dialogs for building script to add columns, indexes and foreign keys to a table. Right click any table, and select the desired dialog:    Note that currently the Index and Foreign Key dialogs only support a single column, I am aware of this, so please vote here and/or here. Generate Entity Framework 6 Entity Data Model with VS 2012 and SQL Compact 3.5 The code generation menu items have now been moved to a sub-menu: In addition, as the Entity Framework Tools are now available for Visual Studio 2012 as well as Visual Studio 2013, it is now possible to generate a “database first” Entity Framework model (EDMX) based on a SQL Server Compact database in VS 2012. And I have just contributed an Entity Framework SQL Server Compact 3.5 provider (available as prerelease on NuGet), that allows you to use SQL Server Compact 3.5 with Entity Framework 6.  Copy database file It is now possible to copy a database file, and then for example paste it into your project, should you so desire, simply by pressing Ctrl+C on the selected database, or selecting the context menu item: Data only export to SQL Server The current data export feature has been enhanced to support SQL Server and IDENTITY columns by using DBCC CHECKINDENT instead of the unsupported ALTER TABLE statements currently used. New "ALTER column" context menu item To help you modify columns, a new context menu to script ALTER COLUMN statements has been added: “Database designer” I am now hosting a web based database designer based on http://code.google.com/p/wwwsqldesigner/ (which implements only the SQL Server Compact column data types). Current state is “alpha” (and there are known issues with IE 11)! Server DGML now has table selection When generating a database diagram for a SQL Server database, you can now select which tables to include in the graph, which can be helpful when diagramming databases with many tables. I have already blogged about this earlier. Other improvements and bug fixes Missing database files indicated in tree view No longer depends on the SQL Server Compact 3.5 runtime, and no longer depends on DbProviderFactory, simply requires either 3.5 or 4.0 in GAC Each Query opens in new window BUG: "Upgrade to 4.0" was blocked in Visual Studio 2013 BUG: Not ideal support for Dark Theme in VS 2012/2013 (improved slightly) BUG: EF6 EDMX code generation with VB was broken