SQLite & XML Editor

By: Matt McClaskey www.macs-site.net/SQLEditor

General

SQLite & XML Editor started as basically a GUI based console for directly accessing the databases that my programs were running on top of. Over time I slowly added new features until I had something similar to what it is today minus a few features. I decided to write it from scratch in C#, previously written in VB.NET.

The choice to use SQLite was pretty easy for me, no size limitations and completely portable. The problem for Windows programmers is that we are used to having a nice GUI to build our databases and construct our SQL statements in, or at least I am.

XML is another great option for a portable data solution. If you are working with small datasets it is even better than SQLite in my opinion. XML is just a flat text file and requires no dlls. In fact SQLite XML Editor uses an XML file to store all the reserved words, context highlighting keywords, and data types. You can edit this file, for example, to add data types to the GUI. With SQLite XML Editor you can create a SQLite database, build the datasets in the GUI and then export in XML for your applications. You can also import XML documents into an existing SQLite database which is an easy way to upgrade your application from XML to SQLite.

Requirements

· System.Data.SQLite.dll – This is the ADO.NET provider for accessing SQLite Databases. (included)

· Microsoft .NET Framework 2.0 or higher – To check what framework you can type the following in Internet Explorer: javascript:alert(navigator.userAgent) Look for the highest .NET CLR listed, in this case 3.5.30729. You must have at least 2.0.

Links

· Support Forum - https://sourceforge.net/forum/forum.php?forum_id=987901

· Website – www.macs-site.net/SQLEditor

· SourceForge – http://sqliteeditor.sourceforge.net/

· Download Executable – https://sourceforge.net/projects/sqliteeditor/files/

· Download Source Code - https://sourceforge.net/projects/sqliteeditor/files/

Key Features

· Create and execute SQL commands manually and with the GUI

· Create and delete tables and columns through the GUI

· Standalone exe, no installation required

· Syntax highlighting

· UPDATE, INSERT, and DELETE from the data grid view (can be very powerful)

· Find and replace text that is in the data grid view, you can search for text or using regular expressions which makes this a very handy tool

· History of used commands that have been executed

· Automatic warnings if you attempt to create a table or column with a reserved word as the name

· Export to CSV and XML

· Import from XML, automatically creates tables and fills the data

· And best of all, this is an active open source project so this is just the beginning. I will continue to add new features and take input for new features. You can even add your own features if you are a .NET programmer.

The Basics

The basics are pretty self explanatory so I will quickly go over them.

1. Create a database – File >> Create Database, enter the name of your database.

2. Open a database – File >> Open Database, browse to location

3. Create a Table – Click the Add table button and enter name, it will warn you if you try to create a table with a reserved word as the name.

4. Delete a Table – Select one or more tables from the table list and click the Delete Table button, to the left of the Add Table button.

5. Creating Fields – Click the Add field button in the Fields box. You will notice there are only a few data types to choose from by default. You can add more by modifying the DataTypes table in SQLiteEditor.xml. This will be covered later. When you press the “Create”

6. Deleting Fields – This is tricky, SQLite does not support deleting fields. Instead you have to create a temporary table with all the fields except the one you want to remove, copy all the data over to the temp table, delete the entire table, create a new table with all the fields except the one being deleted with the same table name as the original, and copy everything from the temp table into the new table. The delete field button will create the SQL statements and put them in to the SQL text editor box for you to review. Important as of this release SQLite Editor does not know what field you may have as the primary key, you need to review the created SQL statements and make sure that the data types and restrictions are accurate before executing. This is an example of the commands created by deleting a field. Notice that BillID is an Integer Primary Key. In the Create Table portion in knows that it is an Integer but does not know the column restrictions. I need to add Primary Key ASC after “BillID INTEGER”.

Creating SQL commands

SELECT ALL from a table

Right clicking on a table and pressing “SELECT ALL” will create the SQL command with all the field names listed as shown above.

Joining Tables

By selecting multiple tables (hold control and click on the desired tables) it will list the fields of each table with the respective table names appended on the front of the fields as shown above. You can select the two columns that join the tables and press the join on button. It will produce the INNER JOIN section, in this case “Billing INNER JOIN USERS ON Billing.BillID=Users.BillID”.

You can add more fields by double clicking on the fields in the Fields list. Just be sure to place the cursor in the desired position on the SQL command editor.

Data Grid View

UPDATES

The datagridview is where your SELECT Queries are displayed. It is more than just a view of your queries though, you can edit the cells and update. Above I changed the user’s last name and did an update on UserID. Notice that in the lower left corner it tells me that 1 out of 1 commands were executed. I could have made many changes, selected all cells that I made changes on, right clicked on one of them and made a whole batch of changes on the UserID. The UPDATE ON is on a per cell basis, so you must select all cells where you made changes. The field you select to do the updates on is used to construct the WHERE clause of the SQL command, in most cases you would want to select the primary key, but you could also update on say, the last name. Lets say there was an address field and all 4 Shoesters lived in the same house. If they moved you could change one of the addresses and update on the lastname field.

INSERTS

By right clicking on a cell and pressing INSERT you are inserting a row into the table with the data of all fields displayed in the Data Grid View. In this case I am displaying the primary key so it will not work. If I were to rerun the SQL commands without the UserID field, right click on one of the user’s row and click Insert, it would make a copy of that user. The reason you have to omit the primary key when you do inserts is because the primary key is restricted to being unique so you can’t copy an existing value into a new row.

Instead of copying an existing row you can go to the last row which is always empty and start editing it, when you are finished you can right click on it and press INSERT.

You can select multiple rows, it will INSERT all selected rows. Note that if you select two cells in the same row it will only INSERT it once.

DELETE

Right clicking on a cell and pressing DELETE will create the SQL command and put it in the SQL editor for you. You must execute it yourself. The where portion of the DELETE command is constructed using the field that you right clicked on. So if you right click on one of the Shoester’s last name and pressed DELETE it would produce “DELETE FROM Users WHERE LastName = 'Shoester'”. This would delete all users with the last name of Shoester, in most cases you would delete on the primary key row, unless you actually wanted to delete all the Shoesters.

Find and Replace

Find and replace will search all cells shown in the Data Grid View, replace the text, and then highlight the cell for you. After it is done you need to right click on one of the highlighted cells and click on UPDATE ON, and select the primary key. Be sure to include the primary key in your SELECT Query.

Exporting

Results from a SELECT query

You can export the Data Grid View by using the Export button in the Data Grid View area after running a SELECT query. You can export to CSV and XML.

Exporting entire tables or database

You can export to XML an entire table or multiple tables. Hold control and select each table you wish to export in the tables list and go to File>>Export>>XML.

Importing

From XML

You can import XML into an existing database, make sure you open or create a new database first. Go to File>>Import>>XML. It will create all the tables and insert all the data for you.

DataTypes

Visit SQLite.org for more information on data types in SQLite. By default SQLite Editor only has 5 data types: Integer, Real, Text, Blob, and Boolean. Notice that the only one that isn’t a real SQLite data type is Boolean. From what I understand SQLite will parse the data type when you create a column, if it doesn’t match anything it will default to an number field, however, it retains the boolean data type name. I assume this because my applications will treat the field as Boolean fields, just create a boolean field and do a SELECT all on that table and you will notice the Data Grid View displays that column as check boxes.

Adding your own data types.

If you want to add your own data types just open up SQLiteEditor.xml in an xml editor and add your data types to the “DataTypes” table under the field name of “DataType.

SQLiteEditor.xml

This is where I store datatypes, context highlighting keywords, and reserved words. If the file does not exist when the application is launched it is created. You can edit the document, add your own keywords to be highlighted, add data types, and add or remove reserved words. I decided to use xml to store this information to make it easier for the user to add capabilities to the program. In the future there may be even more data stored in this document to add even more flexibility to the program.