DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Building Scalable Real-Time Apps with AstraDB and Vaadin
Register Now

Trending

  • The Role of AI and Programming in the Gaming Industry: A Look Beyond the Tables
  • Database Integration Tests With Spring Boot and Testcontainers
  • Send Email Using Spring Boot (SMTP Integration)
  • Mainframe Development for the "No Mainframe" Generation

Trending

  • The Role of AI and Programming in the Gaming Industry: A Look Beyond the Tables
  • Database Integration Tests With Spring Boot and Testcontainers
  • Send Email Using Spring Boot (SMTP Integration)
  • Mainframe Development for the "No Mainframe" Generation
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQLite Toolbox 4.0 – Visual Guide of Features

SQLite Toolbox 4.0 – Visual Guide of Features

Erik Ejlskov Jensen user avatar by
Erik Ejlskov Jensen
·
Aug. 26, 14 · Interview
Like (1)
Save
Tweet
Share
6.44K Views

Join the DZone community and get the full member experience.

Join For Free

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

Database SQLite sql File system Data (computing)

Published at DZone with permission of Erik Ejlskov Jensen, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Trending

  • The Role of AI and Programming in the Gaming Industry: A Look Beyond the Tables
  • Database Integration Tests With Spring Boot and Testcontainers
  • Send Email Using Spring Boot (SMTP Integration)
  • Mainframe Development for the "No Mainframe" Generation

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: