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:

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) fileSave As: Save the text in the editor as a SQL fileExecute: 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 statementsSearch: 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.

I am using your standalone SQL Server Compact Toolbox for runtime 4.0 (great tool BTW!). I have a CE 4.0 database and have generated the LINQ to DataContext class using your tool. I added it to my Visual Studio 2013 Express project. Everything compiles fine, but when I run it I get an SqlCeException "Incompatible Database Version [Db version = 4000000,Requested version = 3505053]". It appears that it is trying to use CE 3.5 when my sdf file is 4.0. Can you tell me how do I make it use CE 4.0. I can't find how to specify the provider.

Trying to open a SQL CE 3.5 file i get this error when trying to add the connection:"Format of the initialization string does not conform to specification starting at index 0.Parameter name: value"My Connection string looks like this:Data Source=F:\TikkTakk.sdf

Hi, again.My mistake, I tried to add a new connection from the Server Explorer, not from the Toolbox...When I tried to connect the way it is supposed to be done, it worked just fine...Sorry for scaring you :-)

The query is not returning any rows while running from SQLite Toolbox.Later I open the database in SQLite Browser and run the query which return the records. Can you please help me in resolving the issue.Thanks & regards,Omer

Today I have downloaded the vsix file on another system with OS Windows 8 Pro and VS 2015 Community. The file installed successfully. The recursive query which I mentioned in my earlier post is now giving the same number of records as produced by DB Browser SQLite. Thanks for your support.Best Regards,

I've installed SQL Server Compact/SQLite toolbox version 4.4.0.1. When I right click a table and select the option edit top 200 rows, I am not able to copy an entire row and paste it at the end of the same table. Any help regarding this would be great.

I need to run SQL CE Toolbox Standalone on a client machine. Can you run, without installing SQL CE runtime into GAC? May be keeping necessary assemblies inside the same folder... Is there any possibility to do this?

I need to run SQL CE Toolbox Standalone on a client machine. Can I run, without installing SQL CE runtime into GAC? May be keeping necessary assemblies inside the same folder... Is there any possibility to do this?

Hey Erik, I am working on a c# app using VS 2013 and Sql Server Compact Toolbox 4.0. I have created a database with some tables but I am unable to get the correct connection string. Error:An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dllThe function .Open() for connection doesn't work. Please guide me. A newbie here.