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

43 comments:

FilerPro Developer said...

Is this tool also supports the SQL Server LocalDB?

ErikEJ said...

FilerPro dev: Many of the root level features support SQL Server incl LocalDB

Sirwan Afifi said...

it's amazing. thanks

Frank said...

Thanks for including Sqlite :) Will you also add "Add Entity Data Model (EDMX) to current Project" option for Sqlite?

ErikEJ said...

Frank: glad you like it. I will investigate if it is feasible, the System.Data.Sqlite guy is also trying to make it Work...

ErikEJ said...

frank: issue created https://sqlcetoolbox.codeplex.com/workitem/11855

Unknown said...

Hi Erik,

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.

Thanks,
Dan

ErikEJ said...

Dan: yes, read my blog post here: http://erikej.blogspot.dk/2012/04/using-linq-to-sql-with-sql-server.html - in particular the NOTE: paragraph

Unknown said...

Plz what is sqlite connection string
how to create it

ErikEJ said...

Gemo: Simply point to the file, which will result in a connection stŕing like: Data Source=c:\data\my.db

https://www.connectionstrings.com/sqlite/

Ronny said...

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

ErikEJ said...

Ronny: Hmmm... Is F: a network drive?

Ronny said...

Hi, Dan.
No F:\ is an external USB drive.
My application is using it without any problem.

Ronny said...

Actually I get the same error if i put it on D:\TikkTakk.sdf, AND if i set the connectionstring to point at a file that does not exist.
AND, the test is successfull no matter what...

Ronny said...

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

Ronny

Unknown said...

Hi,
I am new to VSExpress.
I am tryng to install your tool but..I can't. :(
The install log says:
08/01/2015 13:06:08 - Found installed product - Microsoft Visual Studio Express 2013 for Windows
08/01/2015 13:06:08 - Found installed product - Microsoft Visual Studio Express 2013 for Windows Desktop
08/01/2015 13:06:08 - Found installed product - Global Location
08/01/2015 13:06:08 - Found installed product - ssms
08/01/2015 13:06:08 - VSIXInstaller.NoApplicableSKUsException: This extension is not installable on any currently installed products.
in VSIXInstaller.App.InitializeInstall()
in System.Threading.Tasks.Task.InnerInvoke()
in System.Threading.Tasks.Task.Execute().
I have installed Management SQL too (and it works)
Thanks.
Mauro

ErikEJ said...

Hi Mauro, you cannot install vsix extension to any Express edition - but install Visual Studio 2013 Community Edition, free but full featured edition

Unknown said...

Thanks a lot!

sihikahi said...

Hi,
I used your VS addin and converted SQL databases to .sdf files, but my primary keys are misisng - what am i doing wrong?

Thanks in advance

ErikEJ said...

Sihikahi: Pls see my reply on MSDN forum

Omer said...

Hi Erik,
Thanks for the SQLite Toolbox. Helps me a lot in my project.
I am trying the following query:

Table:
-- Script Date: 10-Sep-15 5:54 PM - ErikEJ.SqlCeScripting version 3.5.2.56
CREATE TABLE [accounts] (
[account] nvarchar(10) NOT NULL
, [account_desc] nvarchar(100) NULL
, [parent_account] nvarchar(10) NOT NULL
, CONSTRAINT [sqlite_autoindex_accounts_1] PRIMARY KEY ([account],[parent_account])
, FOREIGN KEY ([parent_account]) REFERENCES [account] ([]) ON DELETE CASCADE ON UPDATE CASCADE
);

Query:
with recursive mylist(account, account_desc, parent_account, level)
as
(
select distinct accounts.account, accounts.account_desc, accounts.parent_account, 0 level
from accounts
where accounts.account='X10000' AND accounts.parent_account is null
UNION ALL
SELECT DISTINCT accounts.account, accounts.account_desc, accounts.parent_account, mylist.level +1
from accounts join mylist on accounts.parent_account=mylist.account
)
select * from mylist order by level, parent_account;


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

ErikEJ said...

Omer: thank for reporting this, I have logged the issue here: https://sqlcetoolbox.codeplex.com/workitem/12203

ErikEJ said...

Omer: This has been fixed in the latest "daily" https://sqlcetoolbox.codeplex.com/releases/view/616898 - pls let me know if it works for you

Omer said...

I have downloaded vsix the file but unable to install it from nuget package manager console. I placed the file in the e:\ drive and run the following command:
get-package SqlceToolbox.4.3.0.6

Before running this command I have uninstalled the current version of SqlceToolbox and changed the package source to E:\

Sorry, but I have never before installed the nuget package from console.

ErikEJ said...

Omer: double click the file in server explorer!

Omer said...

I double click the file but its not working. The is associated with Microsoft Visual Studio Selector. I am running Windows 10 Pro 64 bit.

ErikEJ said...

Omer: if that does not work, I cannot help you further. What Visual Studio version?

Omer said...

I am using VS 2015 community.

Omer said...

I am using VS 2015 community.

Omer said...

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,

Unknown said...

Hi Erik,

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.

Thanks.

ErikEJ said...

Saba: I have logged a feature request here: https://sqlcetoolbox.codeplex.com/workitem/12292

Unknown said...

Hi Erik,

Do you have a standalone version of your Toolbox (not looking for a VS extension here) that includes SQLCE 3.5, 4.0 and SQLite all in one ?

Thanks!

ErikEJ said...

Eric: No, I have standalone editions for SQLCE 3.5 and 4.0 - for SQLite there are already a number of standalone tools https://github.com/planetopendata/awesome-sqlite

Dhanuka Lakmal Kodikara said...

Hi Erik, Thanks for you nice tool.

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?

Dhanuka Lakmal Kodikara said...

Hi Erik, Thanks for your nice tool.

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?

ErikEJ said...

Dhanuka: I will make it possible! You can follow progress here: https://github.com/ErikEJ/SqlCeToolbox/issues/156

ErikEJ said...

Dhanuka: Smaple zip available here: https://github.com/ErikEJ/SqlCeToolbox/issues/156 - pls let me knwo if it works for you!

Dhanuka Lakmal Kodikara said...

Thank you so much Erik. It is working perfectly. Thanks again for your prompt response. All the best for your good work.

Unknown said...

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.dll
The function .Open() for connection doesn't work. Please guide me. A newbie here.

ErikEJ said...

Yusra: Please ask in the Github issue tracker or on StackOverflow - you are using the wrong ADO.NET provider, but you need to share your code

Unknown said...

Hi Erik, This tool is great. Off late I started to use a bit datatype and do not see any error while creating the table. I see errors while using Select top 100 menu or even creating the database diagram. I am on the latest version 4.7.534.0. Not sure if you ever come across this type. I can write a select statement and it works fine.
Can you please let me know if I missing something.
Thanks Sai

ErikEJ said...

@sai: pls post an issue with more information on the GitHub repository, then we can work together to solve the issue