Monday, August 25, 2014

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:

image

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)

image 

“Add SQLite Connection” will allow you to connect to an existing SQLite database file, or create a new blank one.

image

“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

image

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:

image

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:

image

“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:

image

“Create Database Documentation” will generate a html file with documentation of all tables in the database:

image

“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

image

(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.

image

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:

image

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.

image

“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

Monday, June 30, 2014

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:

image

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):

image

In Package Manager Settings, check that the official NuGet feed is configured and enabled (the AspNetVNext feed is added by the build process)

image

Now launch a VS 2013 Developer Command Prompt as Administrator:

image

Now navigate to the “EntityFramework” folder and run:

build initialize

If the build initialize process succeeds, you will see this message:

Build succeeded.

image

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:

image

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.

Wednesday, June 18, 2014

SQL Server Compact Toolbox 3.7.3 – Visual Guide of new features

After nearly 290.000 downloads, version 3.7.3 of my SQL Server Compact Toolbox extension for Visual Studio  “14”, 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.


New features

Export SQL Server database to SQLite script (beta)

This feature enables you to create a script of all tables and data in an existing SQL Server database, that can be run using the SQLite3.exe tool as described in my blog post here, allowing you to quickly migrate to SQLite, for example for use in Windows Store/Phone Apps.

SQLIteExport

“Set Password” option added to Maintenance menu

This feature enables you to set or change the password of an existing database.

setpw

Enable entry of multiline text in DataGridView

This feature allows you to enter multi line text in the data edit grid, by pressing Shift+Enter to get to a new line.

Support for VS “14” CTP

Support for VS “14” was actually already enabled, but there was a minor issue with registration of the simple DDEX providers, that has now been fixed.

UPDATE: Microsoft has blocked add-ins that claim to support VS 14 from VS 2013, so in the meantime you can download a build for VS “14” from here:

https://sqlcetoolbox.codeplex.com/releases/view/123666

image

 

Bug Fixes

Result Grid column headers were displayed without the first _ (underscore) character if they had one

image

Download Count now fetched async, so the About dialog opens faster.

image

Add Column - defaults to Allow Null

image

"Migrate" and "Export" features broken for large databases with multiple script files

image

image

Thursday, June 5, 2014

Entity Framework 6 and SQL Server Compact (9) –SqlCeFunctions and DbFunctions

One of the major improvements to the SQL Server Compact Entity Framework provider in version 6 is the addition of the SqlCeFunctions class, and enhanced support for the so-called “canonical” database functions (DbFunctions/EntityFunctions).

Just to repeat, the SQL Server Compact providers are delivered in the EntityFramework.SqlServerCompact (for 4.0) and EntityFramework.SqlServerCompact.Legacy (for 3.5 SP2) NuGet packages.

The DbFunctions (previously named EntityFunctions) in the System.Data.Entity namespace define a set of (CLR) methods that expose conceptual model canonical functions in LINQ to Entities queries.

Before EF6.1, the SQL Server Compact provider only supported the functions defined for Entity Framework 1, not it supports all the functions listed here, except the following: Date/Time functions with micro and nanosecond precision (as only datetime exists as a data type in SQL Server Compact), StDev, StDevP, Var, VarP, Reverse, CurrentUtcDateTime, CurrentDateTimeOffset, GetTotalOffsetMinutes.

This means you can now have the SQL Compact engine excute LINQ to Entities expressions like String.Contains, String.EndsWith, String.Left etc.

The System.Data.Entity.SqlServerCompact.SqlCeFunctions class allows you to call database specific functions directly in LINQ to Entities queries, and the following functions have been implemented (for documentation of these, see the equivalent functions for SQL Server listed here):

String functions
CharIndex
NChar
PatIndex
Replicate
Space
StringConvert
Stuff
Unicode

Math functions
Acos
Asin
Atan
Atan2
Cos
Cot
Degrees
Exp
Log
Log10
Pi
Radians
Rand
Sign
Sin
SquareRoot
Tan

Date functions
DateAdd
DateDiff
DateName
DatePart
GetDate,

Other
DataLength

So you can compose LINQ to Entities queries like:

var result = db.Album.Where(a => SqlCeFunctions.DataLength(a.Title) > 20).ToList();

And the resulting SQL will look like this:


SELECT
    [Extent1].[AlbumId] AS [AlbumId],
    [Extent1].[Title] AS [Title],
    [Extent1].[ArtistId] AS [ArtistId]
    FROM [Album] AS [Extent1]
    WHERE (DATALENGTH([Extent1].[Title])) > 20

Wednesday, May 21, 2014

Entity Framework 6 & SQL Server Compact (8) –Logging SQL statements

I previously blogged about how to enable logging of INSERT/UPDATE/DELETE statements with SQL Server Compact and Entity Framework 4. Keep in mind that there is no “SQL Profiler” equivalent for SQL Server Compact, which makes this feature a vital tool for debugging, understanding and improving queries and CUD operations.

In Entity Framework version 6.1, this has been made much simpler, thanks to the implementation of the new System.Data.Entity.Infrastructure.Interception.DatabaseLogger class. In version 6.0, you could enable logging in code by using

db.Database.Log = Console.Write;

In version 6.1, it is now possible to enable logging by adding entries to your app.config file, making it possible to add logging to a deployed application.


Enabling logging is as simple as adding the following section to your app.config/web.config file in the entityFramework section.

<interceptors> 
<interceptor type="System.Data.Entity.Infrastructure.Interception.DatabaseLogger, EntityFramework">
<parameters>
<parameter value="C:\Temp\LogOutput.txt"/>
<parameter value="true" type="System.Boolean"/>
</parameters>
</interceptor>
</interceptors>




The first parameter is the name of the file to log to (if this is not specified, logging will be sent to Console.Out). The second parameter specifies that the file should be appended to rather than being overwritten (default).


I am sure you will find this new feature useful.

Monday, May 12, 2014

Entity Framework ”reboot” – EF7 – Get a sneak peek via TechEd US live stream

The next version of Entity Framework has just been named “EF7” (code name EF EveryWhere). Before the presentation, which will be streamed live on Channel 9 during TechEd US on Wednesday May 14 at 1:30 PM CDT (Houston, Texas), allow me to summarize what we know so far about the next version of Entity Framework.

The Entity Framework team has already published some thoughts about the new Framework here, and based on this and other sources, we can summarize the following (some or maybe most of which is me guessing, of course):

- A completely new codebase, will not be based on the 1 million+ line codebase of EF6

- Will be open source, and accept pull requests and other community feedback

- Will support Windows Phone, Windows Store, Windows Desktop/Server/Cloud, and also support Mono/Xamarin platforms

- Will be based on a provider model, so SQL and NoSQL data sources can “plug in”. Will initially support SQLite on Phone and Store apps. Will also support SQL Server and Azure Table Storage

- Will only include a productive subset of the current, huge API – Code First Mappings, DbContext, POCO classes, and less mapping schemes. So current investments in DbContext, Code First/Second and POCO will be forward reusable.

- Will not contain ObjectContext, Entity SQL, EntityConnection, EntityCommand etc. Phew.

Sounds ambitious and great to me. Can’t wait to see some code and start playing!

UPDATE: The source code is now available on Github as part of ASP.NET vNext. And Rowan Miller, Program Manager on the EF team speaks about and demonstrates EF7 in this TechEd session available online.

Wednesday, April 23, 2014

“Shop Talk” with Eric Sink, Zumero for SQL Server founder

In my last blog post I introduced you to Zumero, an Enterprise solution, that allow you to replicate and sync SQL Server data across all your team's mobile devices.

I had the pleasure of meeting up with Zumero founder, Eric Sink, @eric_sink at the recent SQL Saturday event in Copenhagen.

image

We agreed to do a short interview by mail, so I could share my Zumero questions with my blog readers. So without further ado, let’s get started.

I know you have been involved with other software products (SourceGear). What is the story behind Zumero?

We think of Zumero as a new start-up that is incubating inside SourceGear, so there is much shared history.

We started SourceGear in 1997, initially as a contracting shop building custom software for mobile devices.  We did a lot of work with Blackberry devices and some very early Motorola smart phones.

Then we got into version control tools, which has been a great business for us over the last 15 years.  We are still serving a large customer base with on-going active development of SourceGear Vault.

But in the last few years, the world of mobile has been drawing us back in.  :-)

And from a certain point of view, our work on version control means that we have been doing "sync" for 15 years.

And SourceGear Vault has always been built on SQL Server.

So that's how we got the three main ingredients of Zumero:  SQL Server, mobile, and sync.

Some of the features that Merge Replication provide are: - Minimal client code, secure, compressed transfer over https, server side filtering, read-only tables, conflict handling. How does Zumero stack up?

I /think/ we stack up pretty well, although we still have many ideas for how to make our product better.

"minimal client code"  -- The Zumero Client SDK tries to make everything easy.  Just call the Zumero sync function and Zumero will synchronize changes in both directions.

"secure" -- Zumero is designed to work over SSL/https.  The protocol includes authentication.  And on the client, Zumero is compatible with bulk encryption libraries such as SQLCipher.

"compressed transfer over https" -- All packages exchanged between Zumero client and server are compressed.  In fact, the packages from server to client are often "deltified" to make them very small indeed.

"server side filtering" -- Zumero does support the ability to exclude tables, or columns from tables, or to filter rows on a custom WHERE clause.

"read only tables" -- This can be accomplished with Zumero's authentication and permissions.

"conflict handling" -- Zumero handle conflict resolution on the server.  The default rules seem to be appropriate for most cases, but those rules can be customized to do whatever is needed for a given situation.

In addition, Zumero adds a few things that SQL CE merge replication does not, most notably, support for iOS and Android devices.

OTOH, Zumero is built on SQLite, which does not resemble SQL Server as closely as SQL CE does.  We are constantly working to provide Zumero customers with a smooth experience by improving features, tooling and documentation.  My blog series on the differences between SQL Server and SQLite is a part of those efforts:

Rob Tiffany has demonstrated scaling Merge Replication. How does Zumero scale?

This is one area where Zumero's youth is evident, and I shall not pretend.  There is much more experiential knowledge about scaling with merge replication.  Rob Tiffany's "cheat sheet" is a gold mine of information:

And some of his advice would be applicable to Zumero as well.

However, we are quite happy with the scalability results we have achieved so far and we continue to push further.

(a)  We do a lot of "crowd testing", using lots of clients to abuse the Zumero server so we can tune it for reliability and performance.

(b) We are currently working with some customers who are integrating Zumero into an environment where merge replication is already in use (as a scaling solution for the SQL Server backend, not as a mobile sync solution).  We have some additional testing to do here, but we hope to able to publish some guidelines about the compatibility of Zumero with this kind of situation.

(c) Finally, we have tested and verified another way of scaling out, by having multiple Zumero servers talking to a single instance of SQL Server.  This can make a significant difference and is not difficult to set up behind a round robin DNS.

You are using Triggers and tracking tables on SQL Server, and not the built-in Change Tracking. Why?

The real reason is that Zumero's core sync code existed before we made the decision to make SQL Server the primary focus of the product.

That said, the documentation for Change Tracking raises questions for us.  We wonder if it would require changes to our sync algorithm.  We wonder if our support for tracking schema changes would still work.  We wonder about the apparent dependence on snapshot isolation.

We may explore this further in the future, but right now, we've got a solution which is robust, so we don't feel much pressure to change it.

Do you provide a Windows Mobile (.NET Compact Framework) client library?

Currently, no, but we are planning to do so.  The implementation work for Windows Mobile 6 support is done, but it has not yet been released as part of the product.  If a Zumero customer needs this, we would be ready to discuss and coordinate making it available.

What do you recommend for use as data access API towards SQLite on Windows platforms?

Right now, there is no API we can recommend without caveats.  Or rather, there are several good choices, but no clear winner.

For desktop Windows only, the core SQLite team products System.Data.SQLite, an ADO.NET provider which is fully compatible with EF6.  If you're not using mobile, this is almost certainly the best choice.

Frank Krueger's sqlite-net wrapper is quite popular, and for some very good reasons.  But last I knew, none of the various PCL forks have been folded back in, and that has been an issue for some.  Nonetheless, this is the wrapper we encounter most often.

WinRT without System.Data is a story with a big hole in it.  We remain hopeful that Microsoft has some more good stuff in the pipeline.  

Looking beyond Windows to include iOS and Android as well, things can get complicated.  Both of these other platforms include a [different] version of SQLite as part of the mobile OS.  Many apps use those.  Some bundle their own.  Some replace SQLite with SQLCipher.  Things can get tricky.  In addressing this set of problems with our customers, we've built some stuff that we are planning to make available to the community as open source (to be announced on my blog, soon, I hope). [Ed: Available as source code on Github, and NuGet packages coming soon]