Accessing a SQLite Database from ASP.NET

In the previous tutorial we created a basic ASP.NET application and added a SQLite database of Formula 1 racing world champions. In this tutorial we will extend the application to present the data.

Enabling the SQLite data provider

Webbo is able to access SQLite databases because it comes complete with its own built in data provider 'System.Data.SQLite.dll' which is located in the Webbo installation directory (usually 'C:\Program Files\Webbo'). To allow our application to access the database we need to give it access to this provider. First, in the application root directory create a new folder named 'bin' and copy 'System.Data.SQLite.dll' into it. The project should now look like this:

Project Contents

Next, edit 'Web.config' and add the following code between the end of the section named 'system.web' and the start of the section named 'system.codedom':

<system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.SQLite"/>
        <add name="SQLite Data Provider"
             invariant="System.Data.SQLite"
             description=".Net Framework Data Provider for SQLite"
             type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>
    </DbProviderFactories>
</system.data>

This allows ASP.NET to identify the provider and load the assembly when data access is requested.

Creating a connection

To allow our application to access the SQLite database we need to define a connection string. In 'Web.config' add a new element to the connection string section so that it looks like this:

<connectionStrings>
    <add name="F1Champs" 
         connectionString="data source=|DataDirectory|F1Champs.sqlite"
         providerName="System.Data.SqlLite"/>
</connectionStrings>

This creates a connection named 'F1Champs' and instructs ASP.NET to open the 'F1Champs.sqlite' database located in the data directory ('App_Data') using the SQLite data provider. Note the shortcut name for 'App_Data' and the provider name we defined earlier. Save the file, start the ASP.NET development server and preview the site by pressing Ctrl+F9 or clicking the toolbar button: a blank page should appear. Now let's add some controls to present the data.

Adding the data controls

First we need to add a SqlDataSource control to provide a link to the database. This defines the data provider, connection string and SQL command to be used to retrieve the data for the page. Open 'Default.aspx' and create a new line between the form tags. Type '<asp:', press ctrl+space and select 'SqlDataSource' from the list. Press enter to insert the control and complete the ID attribute with the name 'F1Champs'. Add a reference to the connection string and provide the SQL command to select the data we want to see. The control tag should look like this:

<asp:SqlDataSource ID="F1Champs" 
    ConnectionString="<%$ ConnectionStrings:F1Champs %>"
    ProviderName="System.Data.SQLite"
    SelectCommand="select * from Champions where Season >= 2000 order by Season desc"
    runat="server">
</asp:SqlDataSource>

Note the shortcut for referencing the connection string: use this format to reference any item in the 'connectionStrings' section of 'Web.config'. We also need to specify the provider name; unless told otherwise the control will attempt to access a SQL Server so we need to specify SQLite instead. Finally, the 'SelectCommand' defines the SQL query we need to retrieve the data for our page. In this case we are requesting the data for this century, to be listed in reverse chronological order.

Now we have some data we need a presentation control to make it visible on the page. After the SqlDataSource control start a new line, access Code Assist and insert a GridView control. Provide an ID and use the Code Assist to complete the remaining attributes of the control. After the ID type a space and press ctrl+space, a list of attributes should appear. Select 'DataSourceID' and set its value to the ID of the SqlDataSource control ('F1Champs'). Type another space and use Code Assist to insert the 'AutoGenerateColumns' attribute, set its value to 'true'.

Preview the page by pressing ctrl+F9; the data should appear in the grid. The page should look like this:

<%@ Page Language="C#" AutoEventWireup="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head runat="server">
    <title></title>
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
    <link type="text/css" rel="stylesheet" href="Site.css" />
</head>
<script runat="server">

    private void Page_Load(object sender, EventArgs e)
    {
    }

</script>
<body>
    <form id="form1" runat="server">
        <asp:SqlDataSource ID="F1Champs" 
            ConnectionString="<%$ ConnectionStrings:F1Champs %>"
            ProviderName="System.Data.SQLite"
            SelectCommand="select * from Champions where Season >= 2000 order by Season desc"
            runat="server">
        </asp:SqlDataSource>
        <asp:GridView ID="F1ChampsView" 
            DataSourceID="F1Champs"
            AutoGenerateColumns="true"
            runat="server">
        </asp:GridView>
    </form>
</body>
</html>