wwwtandemlabtech

[wwwtandemlabtech]

SQLite & SQL Server Compact Toolbox 4.1 – Visual Guide of new features

After nearly 340.000 downloads, version 4.1 of my SQLite & SQL Server Compact Toolbox extension for Visual Studio  2015, 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. Improved SQLite integration

"Add SQLite Connection" dialog improved

The “Add SQLite Connection” dialog has been enhanced to include all the available ADO.NET connection string options, by using SQLiteConnectionStringBuilder. This allows you to easily specify advanced “PRAGMA” statements to be configured, for example: PRAGMA foreign_keys=ON;

Enumerate SQLite Server Explorer connections

If you have the SQLite support for Server Explorer installed (requires Visual Studio Community or higher, will not work with Express). See my blog post here for more info on the SQLite DDEX provider. Those connections are now also listed in the Toolbox list of connections:

Enumerate SQLite views and triggers

SQLite allows you to define triggers and views, and these are now listed under each SQLite database file, and you can create CREATE and DROP statements for these object types.

SQL Compact runtime no longer required

The Toolbox no longer requires any version of SQL Server Compact to be installed (useful if you only work with SQLite database files) Other Added button to "About" to re-register DDEX providers (see screenshot above) Added option to ask for modified scripts to be saved Added option to enable using multi-line text in "Edit Table Data" Fixes and improvements - horisontal scrollbar in Explorer window - less obtrusive handling of DDEX providers installation - no longer using MessageBox.Show - update to latest SQLite ADO.NET provider (1.0.94) - improved parsing/separation of SQL statements spilt with GO in Editor

[wwwtandemlabtech]

SQLite Toolbox 4.0 – Visual Guide of Features

After more than 300.000 downloads, version 4.0 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio  “14”, 2013, 2012 and 2010 is now available for download and can be install from the Tools/Extensions and Updates dialog 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. The focus for this release is support for SQLite databases, and this is also the focus of this blog post. Overview This release includes extensive support for SQLite databases, but please bear in mind, that this is v1 in terms of support for SQLite, so there is most likely both room for additional SQLite related features and improvements to the current. I plan to release a “bug fix” update before or around the VS “14” release. Getting used to SQLite has also been a steep learning curve for me! Root level SQLite features The about dialog has been enhanced with SQLite related information: The version of the SQLite ADO.NET provider included with the Toolbox is displayed, and the presence of the DbProvider indicates that SQLite is installed in GAC (not required by the Toolbox) “Add SQLite Connection” will allow you to connect to an existing SQLite database file, or create a new blank one. “Script SQL Server Database Schema and Data for SQLite” will create a SQLite compatible script (.sql file) from a SQL Server database, allowing you to migrate a database from SQL Server to SQLite. For how to use the generated script, see my blog post here. Database level SQLite features When you right click (or press Shift+F10) at the database level, you will get the options above. Let us go through them one by one (notice that all these features are also available for SQL Server Compact database files!) “Open SQL editor” – will open a SQL editor, where you can execute ad hoc SQL statements, and this editor is also used for any scripts created at the table level 8see below). The editor has a toolbar with various buttons: Open: Open a saved script (.sql) file Save As: Save the text in the editor as a SQL file Execute: Run the commands in the editor, and display results below – results can be displayed as either text or in a grid (grid is slower) – set via Options in the Toolbox, Estimated plan: Will run EXPLAIN QUERY PLAN for the statements Search: Search for text in the editor window The editor status bar displays: Query duration, number of rows returned, and SQLite engine version in use. “Build Table” gives you a UI to generate CREATE TABLE statements: “Script Database” will generate various .sql files that you can run using sqlite3.exe. “Create Database Graph” will generate a DGML interactive graph of your tables and their relations and columns: “Create Database Documentation” will generate a html file with documentation of all tables in the database: “Add sqlite-net model.cs to current Project” will code generate a model.cs file with classes for each table in the database, for use with the sqlite-net Nuget package. I will blog in detail about this later, think of it a basic productivity “scaffolding” in this release. “Database information” generates a script with basic database information in the SQL editor. ”Copy database file” will allow you to paste the file from the file system into your project, for example if you want to include a database file as content with your app. ”Remove connection” will remove the connection from the Toolbox (will not affect the underlying file).   Table level SQLite features (Notice that all these features are also available for SQL Server Compact database files!) "Edit Top x Rows” will open the table in a grid, and allow you to edit and add data to the table, provided it has a primary key. In addition to standard Navigation, Add, Delete and Save buttons, the bottom toolbar also contains a Quick Search and free text SQL feature. You can change the limit of rows via Options. “View Data as Report” will open a Microsoft Report Viewer with the table data. In addition to view and print the data, you can also export as PDF, Excel and Word: To use this feature, you may have to install the Report Viewer, which you can download from here. “Script as …” will generate a DML (data manipulation language) and DDL (data definition language) script in the SQL editor for the selected table. In addition, Script as Data (INSERTs) will script all data in the table as INSERT statements in the SQL editor. “Import Data from CSV” will import a CSV file, that has heading that matches the column names in the current table, and generate INSERT statements. ”Rename” will (unsurprisingly) rename the current table. Other fixes and improvements Support for VS "14" Improved saving of connections with "complex" passwords Improved handling of missing MS ReportViewer dll files

[wwwtandemlabtech]

Getting and building the Entity Framework 7 alpha bits – step by step

The Entity Framework version 7 bits are slowly coming together, for a demo of some features, look at the presentation by Rowan Miller from the Entity Framework Team at the recent dotNetConf. This blog post will show you how to get the source code and build it on your own machine, in order to better understand EF 7, and maybe give it a early run. Keep in mind that a number of features are still broken/not implemented, keep an eye on the list: https://github.com/aspnet/EntityFramework/wiki/Getting-Started-with-Nightly-Builds There are instructions on Getting and Building the Code available on the EF7 Wiki: https://github.com/aspnet/EntityFramework/wiki/Getting-and-Building-the-Code But I have stumbled upon a few issues, that might also hit you. 1: Get the code You can either do this as clone of the repository as stated in the link above, you you can simply download a .zip file via the Download .zip button on this page. If you download a .zip, DO use a tool like 7Zip to unpack the file to a folder, do NOT use the built-in Windows zip extractor, in order to avoid files being marked as “Blocked”. Either way, you will now have a folder called “EntityFramework” on your system, with contents like this: 2: Initialize This will download all the required NuGet packages that EF7 depends on, and reference them from the projects. Before you do this, launch Visual Studio 2013 and verify that: You are running VS 2013 Update 2 (check Help, About): In Package Manager Settings, check that the official NuGet feed is configured and enabled (the AspNetVNext feed is added by the build process) Now launch a VS 2013 Developer Command Prompt as Administrator: Now navigate to the “EntityFramework” folder and run: build initialize If the build initialize process succeeds, you will see this message: Build succeeded. If the message does not appear, double check the VS 2013 required settings. 3: Build and run tests The next step will build the EntityFramework projects, and run all the tests in the solution. Before today, running unit tests were not possible on non-US systems, but I and MrJingle have had a few pull requests accepted to enable this (this, this and this). Let me know if you encounter any related issues, and I will be happy to submit a pull request to get it fixed. Again, from a VS 2013 Administrator command prompt, run: build If the build process succeeds, you will see this message: 4: Work in Visual Studio You can of course also open the solution in Visual Studio and build there. In order to run tests in Visual Studio, I found that the built-in Test Window did not detect any tests on my PC, but TestDriven.NET worked well. You can now add some unit tests of your own in order to give EF7 a run! Hope you manage to build, and good luck exploring the EF7 source.

[wwwtandemlabtech]

SQL Server Compact ADO.NET data access performance – part 2: INSERTs

In this second part of this series, I will show by examples how to use the two available ADO.NET APIs in the SQL Server Compact ADO.NET provider for INSERTing data. I will also show some informal performance measurements, but keep in mind that your scenario may give different results. In the sample solution, I will create a data access library for maintaining the following table, which you could be imagine could be used in a caching library: CREATE TABLE CacheElement (     uniqueidentifier NOT NULL,     Tag NVARCHAR(4000) NULL,     Value image NOT NULL,     CreatedAt DATETIME NOT NULL,     ExpirationAt DATETIME NOT NULL); ALTER TABLE     ADD CONSTRAINT PRIMARY KEY (); This table will represent the following class: public class CacheElement {     public Guid Key { get; set; }     public string Tag { get; set; }     public Byte Value { get; set; }     public DateTime CreatedAt { get; set; }     public DateTime ExpirationAt { get; set; } } I will implement the following interface methods in the two possible ways, and then compare implementation and timings, by calling the library from a Unit test project:     public interface IDataAccess     {         void InsertElement(CacheElement element);         void InsertElements(List elements);     } (I will add methods and implement further in the upcoming blog posts) First let us look at the implementation of the T-SQL based INSERT, using best practices with explicit parameters (in the ClassicDal class) : public void InsertElement(CacheElement element) {     using (var command = _connection.CreateCommand())     {         command.Parameters.Add("Key", SqlDbType.UniqueIdentifier).Value = element.Key;         command.Parameters.Add("Value", SqlDbType.Image).Value = element.Value;         command.Parameters.Add("CreatedAt", SqlDbType.DateTime).Value = element.CreatedAt;         command.Parameters.Add("ExpirationAt", SqlDbType.DateTime).Value = element.ExpirationAt;         if (String.IsNullOrWhiteSpace(element.Tag))         {             command.Parameters.Add("Tag", SqlDbType.NVarChar, 4000).Value = DBNull.Value;         }         else         {             command.Parameters.Add("Tag", SqlDbType.NVarChar, 4000).Value = element.Tag;         }         command.CommandText = @"INSERT INTO CacheElement             (,Tag,Value,CreatedAt,ExpirationAt)             VALUES                (@Key, @Tag, @Value, @CreatedAt, @ExpirationAt)";         command.ExecuteNonQuery();     } } For all tests, I am using best practices for SQL Compact connection handling, and passing an already open connection. This avoids measuring the overhead of loading the SQL Compact engine DLL files, and opening the database file. Notice that for NULLable values, special handling has to be made. Now lets us look at the implementation that uses the “raw” APIs for INSERTs (in the RawDAL class) : public void InsertElement(CacheElement element) {     using (var command = _connection.CreateCommand())     {         command.CommandType = CommandType.TableDirect;         command.CommandText = "CacheElement";         using (var resultSet = command.ExecuteResultSet(ResultSetOptions.Updatable))         {             SqlCeUpdatableRecord record = resultSet.CreateRecord();             record.SetGuid(0, element.Key);             if (String.IsNullOrWhiteSpace(element.Tag))             {                 record.SetValue(1, DBNull.Value);             }             else             {                 record.SetString(1, element.Tag);             }             record.SetBytes(2, 0, element.Value, 0, element.Value.Length);             record.SetDateTime(3, element.CreatedAt);             record.SetDateTime(4, element.ExpirationAt);             resultSet.Insert(record);         }     } } Notice the following special pattern: Setting the CommandType to TableDirect, the CommandText is the table name, we use the CreateRecord() method to get a SqlCeUpdateableRecord with “slots” that match our table columns. You have to know the exact “ordinal position” of your columns, you can get that by scripting a CREATE TABLE statement with my Toolbox, or inspecting the INFORMATION_SCHEMA.COLUMNS table. There are typed SetXxx methods that must match the datatype of your columns. Finally, call the Insert method to add the “record”. In the sample code, I have also implemented methods to insert many rows in a single method invocation, thus saving the overhead of recreating a number of objects for each INSERT. This is similar to the way I insert data with my SqlCeBulkCopy library. First the timings for a single INSERT: Raw: 31 ms, Classic: 29 ms – hardly any difference. Then 1000 single INSERTs, minimal object reuse: Raw: 2548 ms, Classic: 1936 – in this case not any advantage of the raw api. Then finally 1000 INSERTs, maximum object reuse: Raw: 73 ms, Classic: 354 ms. A significant difference, if that is a pattern you have in your application, ie inserting many rows in a single call. You can download the code sample from here, and stay tune for the next installment: SELECT (using SetRange and Seek)

[wwwtandemlabtech]

SQL Server Compact ADO.NET data access performance – part 1: Overview

In this five part series on SQL Server Compact performance, I will demonstrate and measure the performance of the two available data access APIs provider by the SQL Server Compact ADO.NET provider (System.Data.SqlServerCe.dll). The information in this blog post applies to both version 3.5 and 4.0. I will not cover usage of the unmanaged OLEDB interfaces (using C and C++) As mentioned, the ADO.NET provider provides two APIs for data access (CRUD) operations: One: The T-SQL standards based APIs - SqlCeCommand: ExecuteReader, ExecuteNonQuery, ExecuteScalar, that allows you to use text based SQL statements, including parameterized SQL. Most ORM implementations that support SQL Server Compact, like LINQ to SQL and Entity Framework, use this API, with the exception of the high perfromant OpenNETCF.ORM Framework, which is able to take advantage of Two: The “table direct” APIs that allow you to bypass the T-SQL parsing and interpretation process, and that alone provides an obvious saving in processing. These include: SqlCeResultSet (that inherits from SqlCeDataReader) - in particular when used with a SqlCeCommandType of TableDirect SqlCeUpdateableRecord SqlCeCommand.ExecuteResultSet SqlCeDataReader.Seek SqlCeCommand.SetRange As you can see, none of these is available with the SQL Server ADO.NET objects, and they are particular to SQL Server Compact. This blog post series will demonstrate how to use these as alternatives to the “standard” methods. The diagram from Laxmi’s blog post here explains the advantage graphically: The “table direct” APIs bypass the Query Processor layer, and instead uses an ISAM like access pattern. In the next blog post in this series, I will compare INSERT with SqlCeResultSet.Insert( SqlCeUpdateableRecord ) and demonstrate how to use the “Table Direct” APIs for INSERTs.

[wwwtandemlabtech]

SQL Server Compact & SQLite Toolbox 4.2 – Visual Guide of new features

After more than 370.000 downloads, version 4.2 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the CodePlex issue tracker The main features in this release have been improved integration with SQL Server & SQLite, and a number of improvements to the SQL editor. This version is a recommended update for anyone using this tool. Improved SQL Server integration I have previously used this old tip from Jon Galloway for prompting users for a connection to a SQL Server/SQL LocalDB/SQL Azure database, but this caused a number of issues when connecting to LocalDB and SQL Azure, so for this release, you will use Server Explorer to create SQL Server connections, and then I will refer to these from the Toolbox. So to interact with SQL Server from the Toolbox (for example for scripting, generating DGML files or Exporting a full database for SQL Server to SQLite, first connect to the SQL Server via Server Explorer: Then launch the Toolbox (you can do that directly from Server Explorer), and select the relevant connection from Server Explorer from the dropdown list:  Export SQLite to SQL Server (and LocalDB/Express) It is now possible to migrate a SQLite database to SQL Server without running creating any intermediate scripts. Simply right click your SQLite database in the Toolbox , and select the “Migrate to SQL Server” option: Select your Server Explorer SQL Server connection (as described above), and you can follow the progress of the export via the Visual Studio status bar: Many thanks to @thughesit for help with improving this feature. As SQLite is much more loosely type than SQL Server, a new option have been added that will truncate string (causing data loss) if a string has been defined with a length like nvarchar(255) and the length of the data stored in SQLite actually exceeds this. This option is disabled by default. Any truncations will be logged to %temp%SQLiteTruncates.log You can now also migrate the other way, from SQL Server to SQLite, without any intermediate scripts. Right click the root “Data Connections” node of the Toolbox and select “Export SQL Server to SQLite (beta)” Choose the Server from the dropdown list: Choose the tables to Export: And specify the name for the new SQLite database file, and you can follow the progress of the Export via the Visual Studio status bar. SQL editor improvements The query editor has received a number of highly requested improvements: Improved document handling: There is now both a “Open” (1) “Save” and “Save As” (2) buttons, and each editor window is aware of its underlying document. If there is a document open, it is indicated in the window caption, and a * symbol indicates if the document has unsaved changes: Keyboard shortcuts: Keyboard shortcuts have been added for "Execute" query: F5, Save: Ctrl+S and Open: Ctrl+O – a long standing request finally implemented Export results as CSV: A new button has been added, that will export the first results as a csv (Excel) file, saving you having to copy and paste the results to Excel. SQLite PRAGMAs: It is now possible to execute PRAGMA commands with SQLite Other Improvements Migrating a SQL Compact or SQLite database to SQL Server no longer blocks the UI. Exporting a database from SQL Server no longer blocks the UI. No more Error Reporting dialog, I am now using Exceptionless for error reporting instead. Improved formatting of DataAcccess.cs sqlite-net code Improved initial directory when adding SQLite and SQL Compact connections Improved error information when SQLite datetime parsing errors occur (SQLite allows you to put anything in a column defined as datetime, apparently!) Improved error information when SQL Server script execution errors occur (would mainly happen when exporting from SQLite to SQL Server) Closing the Edit Data grid now closes the connection to the database, meaning that the Toolbox will have no open connection to the database file when all Edit Grids are closed. Update to version 1.0.96 of SQLite ADO.NET provider Bug fixes Migrate to SQL Server feature was broken Merge Replication Subscription creation was broken Edit column should not show "Primary Key" column, and not allow editing of column name Opening the SQL Editor sometimes crashed VS FormatException in Explorer window

[wwwtandemlabtech]

Entity Framework 6 (and SQL Server Compact) (5)–Entity Framework 6 extensions

This is a list of some of all the nice Entity Framework 6 extensions out there that expand the functionality of the Entity Framework 6 runtime. Code generator tools and Frameworks using Entity Framework are not included here, only libraries that extend DbContext or similar. The EF team has lists of Tools and 3rd party EF providers here: http://msdn.microsoft.com/en-us/data/ee712907 Please let me know if I have missed anything, and I will add it to the list. Store Functions for EntityFramework CodeFirst  Why: This project uses the basic building blocks to build end to end experience allowing using TVFs in Linq queries and invoking stroed procedures without having to drop down to SQL. (By EF Team member) Project: https://codefirstfunctions.codeplex.com/ NuGet: Coming soon Interactive Pre-Generated Views for Entity Framework 6 Why: An alternative is a solution where views are created dynamically only when needed (i.e. views don't exist or are out dated) and then persisted and made available for use by other instances of the same application (be it running in parallel or the same application after a restart). This is exactly the problem Interactive Pre Generated Views project is trying to solve. (By EF Team member) Project: https://efinteractiveviews.codeplex.com/ NuGet: https://www.nuget.org/packages/EFInteractiveViews Second Level Cache for Entity Framework 6.1 Why: Entity Framework does not currently support caching of query results. A sample EF Caching provider is available for Entity Framework version 5 and earlier but due to changes to the provider model this sample provider does not work with Entity Framework 6 and newer. This project is filling the gap by enabling caching of query results for Entity Framework 6.1 applications.  (By EF Team member) Project: https://efcache.codeplex.com/ NuGet: http://www.nuget.org/packages/EntityFramework.Cache Entity Framework 6 Contrib Why: https://ef6contrib.codeplex.com/documentation Project: https://ef6contrib.codeplex.com/ NuGet: https://www.nuget.org/packages/EF6.Contrib/ Entity Framework Extended Library Why: Batch Update and Delete, Future Queries, Query Result Cache, Audit Log Nuget: http://www.nuget.org/packages/EntityFramework61.Extended (forked) and http://www.nuget.org/packages/EntityFramework6.Extended (forked) and https://www.nuget.org/packages/EntityFramework.Extended/ Project: https://github.com/loresoft/EntityFramework.Extended and https://github.com/AdvancedREI/EntityFramework.Extended EntityFramework.Utilities Why: EntityFramework.Utilities provides some batch operations for using EF that the EF team hasn't yet added for us. (CUD) Project: https://github.com/MikaelEliasson/EntityFramework.Utilities NuGet: https://www.nuget.org/packages/EFUtilities/ EntityFramework.BulkInsert Why: Bulk insert extension for EntityFramework. Insert large amount of data over 20 times faster than regular insert. Supports DB first and code first. Project: https://efbulkinsert.codeplex.com/ NuGet: https://www.nuget.org/packages/EntityFramework.BulkInsert-ef6 Trackable Entities Why: Visual Studio 2012 and 2013 project templates and NuGet packages for client-side entity change-tracking and server-side persistence with Entity Framework extensions. Project: http://trackableentities.github.io/ NuGet: https://www.nuget.org/packages/TrackableEntities.EF.6/ EntityFramework.MappingAPI Why: EntityFramework mapping API gives you ability to know the database info behind the context. Project: https://efmappingapi.codeplex.com/ NuGet: https://www.nuget.org/packages/EntityFramework.MappingAPI EntityFramework.Filters Why: Create and configures filters applied to queries across a DbContext Project: https://github.com/jbogard/EntityFramework.Filters NuGet: https://www.nuget.org/packages/EntityFramework.Filters/ EntityFramework.DynamicFilters Why: allow you to create global and scoped filters for Entity Framework queries. The filters are automatically included in every query and can be used to support use cases such as Multi-Tenancy, Soft Deletes, Active/Inactive, etc. Project: https://github.com/jcachat/EntityFramework.DynamicFilters NuGet: https://www.nuget.org/packages/EntityFramework.DynamicFilters/ EntityFramework.Seeder Why: Using EntiyFramework.Seeder, you can seed your database from CSV files. Project: https://github.com/dpaquette/EntityFramework.Seeder/ NuGet: https://www.nuget.org/packages/EntityFramework.Seeder.EF6/ EntityFramework.Triggers Why: Add triggers to your entities with insert, update, and delete events. There are three events for each: before, after, and upon failure. Project: https://github.com/NickStrupat/EntityFramework.Triggers NuGet: https://www.nuget.org/packages/EntityFramework.Triggers/ EntityFramework.IndexingExtensions Why: Adds fluent API to model indexes Project: https://github.com/mj1856/EntityFramework.IndexingExtensions NuGet: https://www.nuget.org/packages/EntityFramework.IndexingExtensions/ Z.EntityFramework.Plus.EF6 Why: Entity Framework Bulk Operations & Utilities (EF Bulk SaveChanges, Insert, Update, Delete, Merge | LINQ Query Cache, Deferred, Filter, IncludeFilter, IncludeOptimize | Audit) Project: https://github.com/zzzprojects/EntityFramework-Plus NuGet: https://www.nuget.org/packages/Z.EntityFramework.Plus.EF6/

[wwwtandemlabtech]

Using Exceptionless.com for error logging and feature usage tracking with a Visual Studio add-in

In the next release of the SQL Server Compact & SQLite Toolbox, which is currently available as a Release Candidate, I will start using ExceptionLess.com for Error Reporting and Feature Usage tracking. In this blogpost, I will describe the few steps required to integrate the Exceptionless client with a Visual Studio extension. I have used RedGate SmartAssembly until now for this, but will stop doing so for a few reasons: - The build process is cumbersome with SmartAssembly, as it is mainly an obfuscation tool, and therefore modifies the add-in assembly itself during build (Link to my blog post) - The desktop client is clunky, and only runs from a single PC - The underlying web service is slow if you have a large number of error and feature usage reports Exceptionless.com is a hosted web service, that allows you to collect error, feature and log reports online from a variety of clients. Simply sign up, get your API key and install the NuGet package for your client and you are ready to go. All your exceptions and logs are then available on the portal. The project is Open Source, and you can host it on your own servers with Azure or on premise. There are both free and paid plans available.

Invoking Error Reporting and Feature Tracking in Code

As the Toolbox is an add-in, I prefer not to catch any unhandled Visual Studio exceptions, but would still like to be able to report any errors occurring in the Toolbox, in order to be able to improve it. In addition I would like to track “feature usage” information, in order to know which features are most popular, and also track statistical information like the version of Visual Studio in use (as the Toolbox runs under the following Visual Studio versions: 2010, 2012, 2013, 2015. The Exceptionless client easily allows you to do this. First sign up at the portal to get your API key, you can start with the free plan to try out the solution, and later upgrade to a paid plan. Install the signed WPF package in your extension project (you must use the signed package with a Visual Studio extension) Install-Package Exceptionless.Wpf.Signed Alternatively download the NuGet packages, unzip and extract the following three files and include them in your lib folder and reference them from your project. (I did that to avoid NuGet Packages in my project): Exceptionless.Extras.dll, Exceptionless.Portable.dll, Exceptionless.Wpf.dll In your overridden package Initalize method, register your API key: using Exceptionless; … ExceptionlessClient.Default.Configuration.ApiKey = "your api key"; Then to log handled exceptions, use the ToException() extension method: using Exceptionless; … var ver = SqlCeToolboxPackage.VisualStudioVersion.ToString(2); ex.ToExceptionless() .AddObject(ver, "VsVersion") .Submit(); Notice the fluent API that allows you to add custom information and objects to the error report. In addition, you can log feature usage as follows: using Exceptionless; … ExceptionlessClient.Default.SubmitFeatureUsage(feature); Where feature is the name (key) you have assigned to the feature, see screenshot above. All in all a very simple and painless integration. You can now monitor exceptions and potentially fix almost before your users report them, and use feature counts to prioritize future feature work. Happy exception monitoring!

[wwwtandemlabtech]

SQL Server Data Tools for Visual Studio – versions, downloads and compatibility

In Visual Studio, SQL Server enables four different project categories under the umbrella of “SQL Server Data Tools” that target various components in the SQL Server product. There is a lot of confusing advice and no single overview of the various versions and downloads available. This blog post hopes to remedy this. Project types The four project types are: SQL Server Analysis Services (SSAS) for creation of various types of Analysis Services models SQL Server Reporting Services (SSRS) for creation of reports and related artifacts SQL Server Integration Service (SSIS) for creation of data integration and workflow solution, including ETL (extract, transform, load) workflows The three project types above are distributed together under the name: SQL Server Data Tools – Business Intelligence (SSDT-BI) SQL Server Data Tools  (SSDT) database project and SQL Server Object Explorer (SSOX) provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. Database developers can use SSOX in VS to easily create or edit database objects and data, or execute queries. Releases An overview of current releases download links are available on the MSDN page here. The following packages are available: SSDT-BI for Visual Studio 2012 (SSDT-BI 2012) SSDT-BI for Visual Studio 2013 (SSDT-BI 2013) SSDT for Visual Studio 2012 (SSDT 2012) SSDT for Visual Studio 2013 (SSDT 2013) Version support Once you have determined what kind of project is relevant for your task at hand, you need to decide which download and Visual Studio version to use, based on the SQL Sever version that you target: SQL Server platform 2005 / 2008 2012 2014 Azure SQL DB Tool/Project         Reporting         SSDT-BI 2012 SSRS No Yes No N/A SSDT-BI 2013 SSRS No Yes Yes N/A Analysis         SSDT-BI 2012 SSAS No Yes No N/A SSDT-BI 2013 SSAS No Yes Yes N/A Integration         SSDT-BI 2012 SSIS No Yes No N/A SSDT-BI 2013 SSIS No No Yes N/A SQL DB Projects and SSOX         SSDT 2012 Yes Yes Yes Yes SSDT 2013 Yes Yes Yes Yes NOTE: For SQL Server 2008 based SSRS, SSAS and SSIS projects, you use Business Intelligence Development Studio, which is installed from the SQL Server installation disk. NOTE: If you open a SSIS project created in SSDT-BI 2012 with SSDT-BI 2014, a one way upgrade will occur, so be careful and use source control. More info here. Hope this helps

[wwwtandemlabtech]

Using SQLite with the Entity Framework 6.1.x designer - a troubleshooting guide

Many users would like to be able to use the SQLite database with the Entity Framework 6 EDM Wizard and Designer, but looking at Stack Overflow, they often face problems attempting to do that. This blog post describes the steps required to do that, and gives some troubleshooting advice and tips. Install the SQLite DDEX provider UPDATE: This blog post describes how to generate and use an EDMX file with SQLite. I have not tested “Code First from Database”, and “Generate Database from Model” does not work with SQLite. In addtion, Code First Migrations are not supported with SQLite. If you want these additional capabilities in an embedded database, use the SQL Server Compact provider. If you encounter issues with the SQLite provider, suggest you post an issue here. In order to use the Entity Data Model Wizard to generate an EDMX Model from an existing database, you must be able to connect to the database from Server Explorer in Visual Studio. In order to do that, a so called DDEX provider for the database and Visual Studio version in use must be installed.  For SQLite you must download the provider from http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki - this page is an unreadable mess, sadly. For Visual Studio 2013, the download you need is named: sqlite-netFx451-setup-bundle-x86-2013-1.0.96.0.exe  for the current version of the ADO.NET provider, so 1.0.96 might change to 1.0.97 or higher in the future. It is important that the DDEXprovider version matches the version of the current SQLite EF6 NuGet package that you use in your project! Make sure to enable Visual Studio integration and GAC registration during installation. You can check which version of the DDEX provider you have installed in “Add or Remove Programs”, and also check that no older versions of the DDEX provider are present: Once installed, you can add a connection to your SQLite database from Server Explorer: Add the correct SQLite EF6 NuGet package to your project To demonstrate using the SQLite EF6 provider, let’s go through creating a console app in Visual Studio 2013, that uses the Chinook sample database that we connected to above. To add the SQLite EF6 provider to your project, select the System.Data.SQLite.EF6 package: This package will install all other required dependencies, including Entity Framework 6.1.2. After installation of this package in a project that target .NET 4.5, your packages.config should look like this (notice that with version 1.0.95 of the SQLite provider, only a single package is required); And the system.data section of your app.config file should be modified to look like this (notice the added and statements); Important: Now BUILD your project! You should now be able to add a EDMX based model to your project, using Add New Item. Data, ADO.NET Entity Data Model: Add some code to the Main method to test that the application also works in runtime:             using (var db = new ChinookEntities())             {                 var albums = db.Albums.ToList();                 foreach (var item in albums)                 {                     Console.WriteLine(item.Title);                 }             }             Console.ReadKey(); If you are unable to see the connection to the SQLite database we made earlier, here are a couple of tips: Make sure to build the project and possibly changing the build configuration to x86 has worked for me. Installing the latest Entity Framework 6.1.2 Visual Studio tools have worked very well for me (the version 6.1.1 Tools are included in Visual Studio 2013 Update 4) – download link in the blog post here. I think it is due to this fix in the Tools. Hope this helps! 

[wwwtandemlabtech]

“Database First” with SQLite in a Universal App using the SQLite Toolbox and sqlite-net

In this blog post I will describe how to use the reverse engineering feature of the SQLite Toolbox to generate code for use with the sqlite-net codebase in a Universal App. A Universal App is a special solution template, that allows you to shared code and resource assets between a Windows Phone 8.1 and Windows Store 8.1 Application, in order to minimize code duplication and increase code reuse, if your application targets both these platforms. My fellow MVP Nicolò Carandini has a couple of great blog posts on how to get started with SQLite in a Universal App. Since he posted this, the SQLite extension SDKs have been updated and are now directly available in the Extension Manager in Visual Studio 2013, so go and install these two downloads – go to Tools, Extensions and Updates, select Online and search for “sqlite”: Install these three extensions: - SQLite for Windows Phone 8.1 - SQLite for Windows Runtime (Windows 8.1) - SQL Server Compact/SQLite Toolbox The two other SQLite extensions are for version Windows/Windows Phone 8.0. Notice that in order to use the SQLite Toolbox, currently you must have SQL Server Compact 4.0 or 3.5 installed, this will not be required in the next release. Now restart Visual Studio. Follow the steps in Nicolo’s blog until you have the two SQLite-net files available in the .Shared “project”: Instead of typing the table classes by hand, the SQLite Toolbox can help you generate the required classes for you based on an existing SQLite database file. You could even then include the SQLite database file with your application, for example if any of the tables contain reference data. I describe how to handle reference data in my previous blog post here. Notice that the sqlite-net classes are not a full blown OR-M, but rather a simple SQL to class translator. It does not support for example change tracking, relationships (foreign keys), Unit of Work, multi column primary keys etc. Those features will be present and available for Universal apps in the new Entity Framework release coming beginning of 2015, EF7. Open the SQLite Toolbox from Server Explorer or the Tools menu, and connect to an existing SQLite database. For an overview of all SQLite features in the Toolbox, see my blog post here. Select the shared project in Solution Explorer, then right click the database and select “Add sqlite-net Model.cs to current project” The generated code (Model.cs) contains a SQLiteDb class with a method to create all the tables in the database (if you just want to use the existing database file as a template), and class definitions for each table in the database: public partial class Album { public Int64 AlbumId { get; set; } public String Title { get; set; } public Int64 ArtistId { get; set; } } As you can see, attributes like MaxLength, Index and NotNull help define the table. Hope you find this timesaving feature and the other SQLite features in the Toolbox helpful, and please provide any feedback here.

[wwwtandemlabtech]

Entity Framework 6 and SQL Server Compact 4.0 – “Proper” private desktop deployment

About a year after I published the blog post Entity Framework 6 & SQL Server Compact 4 (2)–Easy Private Desktop Deployment it has occurred to me that the solution proposed in that blog post is far from perfect – my apologies! The suggestions for location of the database file etc. are still valid points, however. TL;DR – Use my new NuGet package for Private Desktop deployment with SQL Server Compact 4.0 and Entity Framework 6, and make sure that: - The binding redirect is set to: oldVersion="4.0.0.0-4.0.0.1" - Prefer32Bit project option is disabled The goal First, let’s clarify what the goal behind “private desktop deployment” with SQL Server Compact is: The goal is to be able to drop a folder of files on any PC and just run  a .NET application that includes a self-contained relational database and a sophisticated ORM over that database. This goal is achievable with SQL Server Compact 4.0 and Entity Framework 6, as long as the PC in question has .NET Framework 4.0 installed. The issues First, let us look at the issues with the approach in the above blog post: 1: This approach uses the Microsoft.SqlServer.Compact package, which explicitly uses the non-private assembly version of System.Data.SqlServerCe.dll (which is 4.0.0.0). And it assumes that your project targets AnyCPU. This means that if you have SQL Server Compact installed in GAC, then the version from there will be picked up. That will work fine, as long as you have the exact same build of SQL Server Compact in both GAC and your application folder. But this leads us to issue number 2! 2: The EntityFramework.SqlServerCompact package does not depend explicitly on a release version of the Microsoft.SqlServer.Compact NuGet package and will pick up build 8854, which is 4.0 SP1 CTP1, instead of build 8876, which is 4.0 SP1 proper. (I wish that the 8854 builds were no longer visible on NuGet) – I am working on a fix for this, which is planned for EF 6.1.2. So the chances of not having the same version in GAC and locally are relatively big, and this will cause an error to be thrown, preventing your app from working: Possible file version mismatch detected between ADO.NET Provider and native binaries of SQL Server Compact which could result in an incorrect functionality. Manual fix If you would like to ensure that your desktop application (console, Windows Forms or WPF) app can run anywhere use proper private deployment, follow these steps (in outline): 1: Install EntityFramework.SqlServerCompact 2: Copy files from Private folder in C:Program Files (x86) to the project folder (as described in my Private deployment with LINQ to SQL blog post) 3: Make project x86 only (or at least remove the “Prefer 32 bit” option if you include all unmanaged dll files  4: Disable post build event (via Project Properties/Build events tab) to prevent the 4.0.0.0 dll from being deployed 5: Modify app.config a) add assembly redirect, as the EntityFramework.SqlServerCompact.dll references version 4.0.0.0, and we want to use 4.0.0.1: b) With version 4.0.0.0 => 4.0.0.1 in DbProviderFactories section:   “Automatic” fix I have published a NuGet package EntityFramework.SqlServerCompact.PrivateDeployment  that performs most of the actions above, specifically 1, 2 (via a clever package that performs a build step), 3 (via an additional command in install.ps1), 4, and partly 5 (for some reason NuGet generates an incorrect bindingRedirect, so it must be fixed manually). Currently in pre-release, please provide feedback!

This package includes both the EntityFramework.SqlServerCompact provider for EF 6.1.1 and the SQL Server Compact 4.0 files for private deployment. I intend to update the package with the upcoming version 6.1.2 release of Entity Framework.

[wwwtandemlabtech]

Comparison of SQL Server Compact, SQLite, SQL Server Express and LocalDB

Now that SQL Server 2014 and SQL Server Compact 4 has been released, some developers are curious about the differences between SQL Server Compact 4.0 and SQL Server Express 2014 (including LocalDB) I have updated the comparison table from the excellent discussion of the differences between Compact 3.5 and Express 2005 here to reflect the changes in the newer versions of each product. Information about LocalDB comes from here and SQL Server 2014 Books Online. LocalDB is the full SQL Server Express engine, but invoked directly from the client provider. It is a replacement of the current “User Instance” feature in SQL Server Express. Deployment/ Installation Features SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQLite, incl. ADO.NET Provider SQL Server Express 2014 SQL Server 2014 LocalDB Installation size 2.5 MB download size 12 MB expanded on disk 2.5 MB download size 18 MB expanded on disk 10 MB download, 14 MB expanded on disk 120 MB download size > 300 MB expanded on disk 32 MB download size > 160 MB on disk ClickOnce deployment Yes Yes Yes Yes Yes Privately installed, embedded, with the application Yes Yes Yes No No Non-admin installation option Yes Yes Yes No No Runs under ASP.NET No Yes Yes Yes Yes Runs on Windows Mobile / Windows Phone platform Yes No Yes No No Runs on WinRT (Phone/Store Apps) No No Yes No No Runs on non-Microsoft platforms No No Yes No No Installed centrally with an MSI Yes Yes Yes Yes Yes Runs in-process with application Yes Yes Yes No No (as process started by app) 64-bit support Yes Yes Yes Yes Yes Runs as a service No – In process with application No - In process with application No - In process with application Yes No – as launched process Data file features SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQLite, incl. ADO.NET Provider SQL Server Express 2014 SQL Server 2014 LocalDB File format Single file Single file Single file Multiple files Multiple files Data file storage on a network share No No No No No Support for different file extensions Yes Yes Yes No No Database size support 4 GB 4 GB 140 TB 10 GB 10 GB XML storage Yes – stored as ntext Yes - stored as ntext Yes, stored as text Yes, native Yes, native Binary (BLOB) storage Yes – stored as image Yes - stored as image Yes Yes Yes FILESTREAM support No No No Yes No Code free, document safe, file format Yes Yes Yes No No Programmability SQL Server Compact 3.5 SP2 SQL Server Compact 4.0 SQLite, incl. ADO.NET Provider SQL Server Express 2014 SQL Server 2014 LocalDB Transact-SQL - Common Query Features Yes Yes No Yes Yes Procedural T-SQL - Select Case, If, features No No Limited Yes Yes Remote Data Access (RDA) Yes No (not supported) No No No ADO.NET Sync Framework Yes No (not supported) No Yes Yes LINQ to SQL Yes No (not supported) No Yes Yes ADO.NET Entity Framework 4.1 Yes (no Code First) Yes Yes Yes Yes ADO.NET Entity Framework 6 Yes (fully) Yes (fully) Yes (limited) Yes Yes Subscriber for merge replication Yes No No Yes No Simple transactions Yes Yes Yes Yes Yes Distributed transactions No No No Yes Yes Native XML, XQuery/XPath No No No Yes Yes Stored procedures, views, triggers No No Views and triggers Yes Yes Role-based security No No No Yes Yes Number of concurrent connections 256 (100) 256 Unlimited Unlimited Unlimited (but only local) There is also a table here that allows you to determine which Transact-SQL commands, features, and data types are supported by SQL Server Compact 3.5 (which are the same a 4.0 with very few exceptions), compared with SQL Server 2005 and 2008.

[wwwtandemlabtech]

SQL Server Compact ADO.NET data access performance – part 3: SELECTs

In the third instalment in this series, I will demonstrate how to implement SELECTs using the two available APIs in the SQL Server Compact ADO.NET provider. Keep in mind that the “alternative” method of SELECTing requires a corresponding index, so it will on work for simple scenarios where you are looking up on the primary key, for example – luckily, this is quite a common scenario. I will implement the following interface method, which finds a cacheelement by it’s ID: CacheElement FindElementByKey(Guid key); Let’s first look at the implementation of the classic T-SQL SELECT based method: public CacheElement FindElementByKey(Guid key) {     using (var command = _connection.CreateCommand())     {         command.Parameters.Add("@key", SqlDbType.UniqueIdentifier).Value = key;         command.CommandText = @"SELECT , , ,             FROM CacheElement             WHERE = @key;";         using (var reader = command.ExecuteReader())         {             if (reader.Read())             {                 var result = new CacheElement();                 result.Key = key;                 result.Tag = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);                 result.Value = (byte)reader;                 result.CreatedAt = reader.GetDateTime(2);                 result.ExpirationAt = reader.GetDateTime(3);                 return result;             }             else             {                 return null;             }         }     } } This implementation uses a standard T-SQL based parameterized SELECT query. Nothing new here. Now let us have a closer look at the implementation using TableDirect mode: public CacheElement FindElementByKey(Guid key) {     using (var command = _connection.CreateCommand())     {         command.CommandType = CommandType.TableDirect;         command.CommandText = "CacheElement";         command.IndexName = "PK_CacheElement";         using (var reader = command.ExecuteReader())         {             reader.Seek(DbSeekOptions.FirstEqual, key);             if (reader.Read())             {                 var element = new CacheElement();                 element.Key = key;                 element.Tag = reader.GetValue(1) == DBNull.Value ? null : reader.GetString(1);                 element.Value = (byte)reader.GetValue(2);                 element.CreatedAt = reader.GetDateTime(3);                 element.ExpirationAt = reader.GetDateTime(4);                 return element;             }         }         return null;     } } Notice the required differences: The CommandType is set to TableDirect, the CommandText is set to the name of the table we want to query, and the IndexName property is set to the name of the index to be used. The Seek command will force a call to read to find the first matching column matching the key value. Notice that the key parameter is an params array, allowing you to use multi-column indexes. In terms of performance, the classic SELECT appears to be 3 times slower than the TableDirect Seek, but of course your mileage may vary. You can download the sample code from here.

[wwwtandemlabtech]

SQL Server Compact & SQLite Toolbox 4.3 – Visual Guide of new features

After more than 415.000 downloads, version 4.3 of my SQL Server Compact & SQLite Toolbox extension for Visual Studio 2010 and later is now available for download and available via Tools/Extensions and Updates in Visual Studio. This blog post is a visual guide to the new features, improvements and bug fixes included in this release, many suggested by users of the tool via the CodePlex issue tracker This new version was released in July at the time that Visual Studio 2015 went live, and is of tested with Visual Studio 2015 – keep in mind that all extensions now work with the free Visual Studio Community Edition. This release has a couple of new features that improves on the SQLite support originally added in version 4.0

Scan solution for SQLite database files

The “Add Connections from Solution” feature has been enhanced to scan all projects in the current solution for any SQL Server Compact and SQLite files. If any are found (based on the file patterns defined in Options), the Toolbox will attempt to add them as new connections. The current file patterns are defined as shown below, but you can change them to anything you like:

Rename Connection name

The names of connections currently defaults to simply the file name of the database file. This becomes an issue, if you have many same named files in different folders connected. Therefore, you can now rename the Connection name:

SQLite-net DataAccess.cs improvements

The SQLIite-net code generation feature has received a couple of small updates based on user feedback: The generated classes will also contain any views defined in the SQLite database, and nullable values will be mapped accordingly (for example int?).

NULL value display

NULL values are now displayed as 'NULL' in Query results text and grid. Remember that you can see query results both as text and in a grid, the default is text for performance reasons, but this can easily be changed in Options: Also notice that if you prefer the previous behaviour, this can be changed here, via the ShowNullValuesAsNULL option.

Other improvements

Unique indexes now always scripted as UNIQUE constraints (a long standing omission, which hit me when I was creating the reverse engineering for the SQL Server Compact providers for Entity Framework 7) As usual, any feedback and comments are welcome, and if you like my free tools, I am always very grateful for a review on Visual Studio Gallery

[wwwtandemlabtech]

Getting started with the SQL Server Compact and other providers for Entity Framework 7 beta 6

In connection with the recent release of Entity Framework 7 (EF7) beta 6 on NuGet, I have contributed two SQL Server Compact providers that work with this beta release (in close collaboration with and with great support from the EF7 team.) Notice that the general “EntityFramework” package for EF7 is no longer available. Going forward you always install the desired provider packages as needed. For example EntityFramework.SqlServer The SQL Server Compact providers The SQL Server Compact providers are fully featured providers that support all required Core APIs (maybe with the exception of provider specific metadata). This includes support for returning IDENTITY values after INSERTs, Migrations, database object creation, and modelling via both the fluent API and Data Annotations. It also includes support the for Reverse-Engineer command, allowing you to create an EF7 model from an existing database. For a walkthrough of how to get started with the SQL Server Compact providers, see the wiki article here. Initial Entity Framework 7 documentation is available here (a work in progress). As always feedback is very much appreciated, suggest you use the Github issue tracker for bug reports, feature suggestions or even Pull Request discussions and submissions! Other providers With the current EF7 beta, the following providers are available: EntityFramework.SqlServer Provider for SQL Server/Azure SQL Database - supplied by Microsoft. This provider will be able to take advantage of the .NET Core support for System.Data.SqlClient on Unix and Mac. Currently only SQL Server 2012 and later is supported, but support for SQL Server 2008 is planned EntityFramework.Sqlite Provider for SQLite, mainly for devices (tablet and phone) - supplied by Microsoft. Will also work with Xamarin (Andriod and IOs) EntityFramework.InMemory In-memory provider for use with testing, supplied by Microsoft All the providers above are hosted here on Github. EntityFramework.Npgsql Provider for PostgressSQL - also works on Mono. Supplied by Shay Rojansky and others (expected to be released with beta7) – Github EntityFramework.SqlServerCompact40 Provider for SQL Server Compact 4.0, supplied by yours truly – Github EntityFramework.SqlServerCompact35 Provider for SQL Server Compact 3.5 SP2, also supplied by yours truly – Github The current plan is to release EF7 at the same time as ASP.NET 5, which is currently scheduled (no guarantees) to beginning of next year.

[wwwtandemlabtech]

Handy - SQL Scripts to show high CPU usage

The first query will order the results based on the queries that have used the most CPU time since the SQL Server instance has been restarted (or the server has been rebooted). The second query orders the results based upon the average CPU time that each query takes. — Find queries that take the most CPU overall SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads --The worst reads, disk reads ,MemoryReads = qs.total_logical_reads -- Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time DESC— Find queries that have the highest average CPU usage SELECT TOP 50 ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + ‘.’ + OBJECT_NAME(qt.objectid, qt.dbid) ,TextData = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,TotalCPUTime = qs.total_worker_time ,AverageCPUTime = qs.total_worker_time/qs.execution_count ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY qs.total_worker_time/qs.execution_count DESC