Creating and Managing a SQLite Database

Webbo provides support for the open-source SQLite database and includes an ASP.NET data provider and a command line management utility. This tutorial demonstrates how to create and maintain a SQLite database within an ASP.NET application.

Creating the application

Start Webbo, create a new folder named 'SQLite Demo' and make it the root folder in the Explorer view. From the templates menu create a new 'Website' from the 'ASP.NET/Projects' submenu. Add a new folder to the root of the project and name it 'App_Data'. For the rest of this tutorial we will be mainly concerned with the database but it is useful to create it in the context of an application which will serve as the basis for the next tutorial which demonstrates accessing the data from the application.

Creating the database

When using databases within Webbo we need to make a distinction between providing access for our applications and providing access for Webbo itself. We provide access for Webbo so that we can use it's built-in SQL development tools to create and maintain our application databases.

Before we can do anything with a database we need to define a connection string. In this tutorial we will create a simple database for Formula 1 racing World Champions. The database is named 'F1Champs.sqlite' and will be located in the 'App_Data' folder, it contains a single table 'Champions'.

Defining the connection string

First make sure the root of the site is the current directory. This is indicated by the open folder icon; if the root is not the current directory make it so by right-clicking the root folder and selecting 'Set as Current Directory'. The importance of this will be explained shortly.

Next access the 'Tools' menu and select 'Options'. Select 'SqlManager' in the options editor and right-click the list view that appears in the main panel on the right of the form. A context menu will appear, select 'New...' to create a new connection.

New Connection

In the connection editor enter the details as follows:

New Connection Details

Click the 'Test Connection' button (the test should be successful) then 'Ok' to acknowledge the result, 'Ok' to create the connection and finally 'Save' to update the options. Refresh the Explorer view and open the 'App_Data' folder, you should see the newly created SQLite database.

New Database

By default when a connection to a SQLite database is requested the SQLite data provider will create it if it doesn't already exist. When we clicked 'Test Connection' a connection was attempted to a non-existent database so the provider created it for us. The provider determines the location of the database from the path provided in the 'Data Source' field of the connection string. If this is an absolute path the database location is unambiguous but where the path is relative (as in our case) the full path is determined from the current directory. We made sure the project root was the current directory so that it would provide the correct path when combined with the path in the connection string.

Having the database automatically created gives us a nice way of creating a new database but can create problems if we use a relative path in the connection string. If we were to test the connection while working on a different project we would unintentionally create a new version of the database in that project. One way to prevent this is to simply use an absolute path but this would prevent us from moving the project to a new location without having to update the connection string. A simpler way is to prevent the database from being created automatically once the database has been created.

To do this update the connection string to the following:

Data Source=App_Data\F1Champs.sqlite; FailIfMissing=true

Accessing the database

Now that we have a database we need to be able to apply SQL statements to it. We can do this in two ways. First we can use the built-in SQLite manager. Simply double-click the database in the Explorer view (or right-click and select 'Open') to open the database in the SQLite command-line management tool. Alternatively we can use Webbo's SQL editor.

To use the editor we must first select and activate the connection. Open the connection list on the main toolbar and select the connection from the list.

Selecting a Connection

This will enable the 'Activate Connection' button but the connection will not become available until it is activated. Click the button to activate the connection. This will check the connection is valid and gather metadata from the database for use within the SQL editor.

Create a new 'SQL File' from the 'New From Template' menu; an empty SQL file will appear in the editor. This will also enable the 'Run SQL Query' button (the red exclamation mark) on the toolbar.

Creating the table

The 'Champions' table is simple and can be created from the following SQL statement:

create table Champions (
    Season integer,
    Driver varchar(50),
    Country varchar(30),
    Team varchar(50)
);

Copy this into the editor and press F5 or click the 'Run SQL Query' button. The statement will be sent to the database and the table created.

When a connection is made to a database, Webbo will read and store database metatdata for use in in the SQL editor. This allows the Code Assist system to provide support for database entities such as tables and columns when formulating SQL statements. By creating the 'Champions' table we have changed the database schema and the metatdata stored for the connection will now be out of date. To update it simple reactivate the connection by clicking the 'Activate Connection' button twice. Do this to make the 'Champions' table available to the SQL Code Assist.

Running SQL statements

Now clear the text from the editor (or save it for future reference) and type 'select' followed by a space. Press ctrl+space to list the available entities. At this stage you will be presented with a list of tables, select 'Champions', type '.' and press ctrl+space again. This time the list will contain the columns of the selected table, allowing the column names to be entered in a table-qualified format. Select 'Season' followed by a space and the 'from' keyword. Type another space and hit ctrl+space again to recall the table list. Select 'Champions' to complete our simple query and run it using F5 or the toolbar.

The results will appear in the lower part of the editor, or at least they would if we had any data. An empty result set should be visible with a single column header 'Season'.

Download this file and open it in the editor. Run it to populate the 'Champions' table. Return to the previous editor and update the query to return all the columns:

select * from Champions

Run this to see the data in the results window.

To see more Code Assist options move the editing position to the '*', delete it and press ctrl+space. Now that we have the 'from' part of the query in place Webbo can provide better support and will allow column names to be selected directly. Without entering any columns add a table alias after the table name:

select  from Champions c

Move back to the select keyword and press ctrl+space where the column names should appear. The list will now include the alias, select 'c', add '.' and press ctrl+space again to get the column names for the table.

Now that we have some data we need to extend the application to display it. See this tutorial to learn how.