SQLite GUI

Introduction

This article describes how to create, insert, update SQLite database from GUI.

Description

SQLite GUI is a simple GUI for creating a new SQLite database and opening an existing SQLite database. It can also create, delete and update tables in the database. All the available tables in the database are shown. The records of various types including BLOB are displayed in the DataGridView. And the data in the DataGridView can be printed. The schema for all the available tables is displayed.

Dependencies

The SQLite.NET assembly is available here. Add the reference SQLite.Net.dll to the project and use the namespace Finisar.SQLite to work with SQLite.

Finisar.SQLite

Finisar.SQLite is an ADO.NET Data Provider for accessing SQLite-Databases using the .NET-Framework. In SQLite, a complete database is stored in a single disk file. SQLite is zero-configuration - no setup or administration needed. SQLite itself is "a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine" (quoted from the sqlite.org Website). The Finisar.SQLite Data Provider is an easy way to use the SQLite-Database in .NET languages like C# and VB.NET.

To Do

Update cannot be performed on table without primary key. The SelectCommand must also return at least one primary key or unique column. If none are present, an InvalidOperation exception is generated, and the commands are not generated. The alternative way is create select, insert and update command for that particular table. Refer to MSDN. It will be available in the next version of SQLite GUI.

Once the database is accessed by a user, the whole database will be locked. Only one user is allowed to access it at a time.
So, if there are multiple user want to access the database, they need to take turns.
The 2nd user needs to wait for 1st user to finish the job. 3rd user will wait for 2nd user.

What to do when update complains that "The connection must be open to call ExecuteReader?" All tables have primary keys and update works until I switch away from the table and switch back. I.e., when I edit table A and update it, switch to table B, edit and update, switch back to table A and edit, update is when I get the above message.

Just ran the application on several databases of mine and I am unable to make any changes. It keeps saying "attempt to write a readonly database". With other tools (Gears, SQLite Admin) it works but SQLite GUI is not having it.

Whenever you do an sqlite_open() you are really opening at least
two database files - the one you named as an argument to sqlite_open()
and a separate file used to store TEMP tables. It is quite possible
for the main database to be read-only and the TEMP database to be
read-write. You can also add additional files using the ATTACH
command. Each such file can be either read-only or read-write.
You can have up to 12 databases open all at once. Some can be
read-only while others are read-write.

If you want to see if a particular table is read-only, try
executing SQL like this:

UPDATE table SET rowid=0 WHERE 0;

If you get back an SQLITE_OK that means the table is read-write.
If you get back SQLITE_READONLY that means the table is read-only.
And because the WHERE clause is always false, nothing in the
database will be changed.

After playing a while I found a solution that does works:
- You always need a refference to the sqlite.net dll file
- Depending on database version (below 3 or version 3) you also need to copy de sqlite3.dll or sqlite.dll in the root directory of your project.
- You may also need to change the sqlite connection string (changing the version number)

Yes I had to copy the SQLLite3.dll into the bin folder to get the app to run (terrific little article by the way. I'm not sure what the update button does though, as I can't edit the dataGrid. Mixed in some code from http://www.codeproject.com/KB/cs/SQLiteCSharp.aspx so I could).

I am trying to make a very simple application using the sqlite.NET.dll in Microsoft Visual Studio 2013. I am writing an app with .NET3.5 framework. I have tried to copy the sqlite3.dll and sqlite.dll into my debug folder, but still get the same error as described above, fiddling with the version in the connection string sometimes get me a similar message, only this time the exception is complaining about the sqlite.dll (which is there allready), can someone please point me in the right direction to solve this?