SQLite Database

Last modified April 22, 2015

Required

Tutorials: Accessing folders and files

Introduction

According to the SQLite homepage, "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world". GHI provides a driver for SQLite so that you can have access to a SQL database that resides entirely in a simple file on a persistant storage device.

A simple SQLite example

The below code is a simple example where a database file is created in RAM (using SD cards and USB drives is possible as well). A table is created that is filled with some initial rows and then this data is read from the database. This data is then iterated over and printed out. ColumnOriginNames returns the names of each of the columns.

using System;
using System.Collections;
using Microsoft.SPOT;
using GHI.SQLite;
public class Program
{
    public static void Main()
    {
        // Create a database in memory,
        // file system is possible however!
        Database myDatabase = new GHI.SQLite.Database();
        myDatabase.ExecuteNonQuery("CREATE Table Temperature" +
        " (Room TEXT, Time INTEGER, Value DOUBLE)");
        //add rows to table
        myDatabase.ExecuteNonQuery("INSERT INTO Temperature (Room, Time, Value)" +
        " VALUES ('Kitchen', 010000, 4423)");
        myDatabase.ExecuteNonQuery("INSERT INTO Temperature (Room, Time, Value)" +
        " VALUES ('Living Room', 053000, 9300)");
        myDatabase.ExecuteNonQuery("INSERT INTO Temperature (Room, Time, Value)" +
        " VALUES ('bed room', 060701, 7200)");
        // Process SQL query and save returned records in SQLiteDataTable
        ResultSet result = myDatabase.ExecuteQuery("SELECT * FROM Temperature");
        // Get a copy of columns orign names example
        String[] origin_names = result.ColumnNames;
        // Get a copy of table data example
        ArrayList tabledata = result.Data;
        String fields = "Fields: ";
        for (int i = 0; i < result.RowCount; i++)
        {
            fields += result.ColumnNames[i] + " |";
        }
        Debug.Print(fields);
        object obj;
        String row = "";
        for (int j = 0; j < result.RowCount; j++)
        {
            row = j.ToString() + " ";
            for (int i = 0; i < result.ColumnCount; i++)
            {
                obj = result[j, i];
                if (obj == null)
                    row += "N/A";
                else
                    row += obj.ToString();
                row += " |";
            }
            Debug.Print(row);
        }
        myDatabase.Dispose();
    }
}

The details on SQLite

All details on SQLite are found at the offcial SQLite website http://www.sqlite.org/

Leave feedback about this document.
Let us know if the information presented here was accurate, helpful and if you have any suggestions.
Leave feedback about this document.
Let us know if the information presented here was accurate, helpful and if you have any suggestions.

* Indicates required fields.
This form is only for feedback not support.
Review our how to find information guide on locating helpful resources.