From eqqon

Latest revision as of 07:14, 11 September 2010

The easiest way for an application to set up a database back-end is SQLite. Here is a small sample that creates a table, inserts some data and queries them back in C#. We use LiteX which is a nice .Net wrapper for SQLite 3.

Example SQLite-DB access with LiteX.dll

using System;
using System.Collections.Generic;
using System.Text;
using LiteX;

namespace sqlite3
{
    class Program
    {
        static void Main()
        {
            SQLiteConnection db = new SQLiteConnection("test.db");
            db.BeginTransaction();
            db.ExecuteNonQuery("drop table if exists test;");
            db.ExecuteNonQuery("create table test( nr int primary key, name varchar(30), text varchar(30) );");
            
            db.ExecuteNonQuery("insert into test values (1, \"hello\", \"world!\n\");");
            db.ExecuteNonQuery("insert into test values (2, \"hello\", \"eqqon!\");");
            db.Transaction.Commit();

            using (SQLiteDataReader data = db.ExecuteReader("SELECT * FROM test;"))
            {
                while (data.Read())
                {
                    for (int pos = 0; pos < data.FieldCount; pos++)
                    {
                        Console.WriteLine(data.GetName(pos));
                        Console.WriteLine(data.GetFieldType(pos));
                        Console.WriteLine(data.GetValue(pos));
                        Console.WriteLine();
                    }
                }
            }
            Console.In.ReadLine();
        }
    }
}

Alternative Way: ADO.NET and System.Data.SQLite

using System;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
namespace test
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a connection and a command
            using (DbConnection cnn = new SQLiteConnection("Data Source=test.db3"))
            using (DbCommand cmd = cnn.CreateCommand())
            {
                // Open the connection. If the database doesn't exist,
                // it will be created automatically
                cnn.Open();
                // Create a table in the database
                cmd.CommandText = "CREATE TABLE FOO (ID INTEGER PRIMARY KEY, MYVALUE VARCHAR(50))";
                cmd.ExecuteNonQuery();
                // Create a parameterized insert command
                cmd.CommandText = "INSERT INTO FOO (MYVALUE) VALUES(?)";
                cmd.Parameters.Add(cmd.CreateParameter());
                // Insert 10 rows into the database
                for (int n = 0; n < 10; n++)
                {
                    cmd.Parameters[0].Value = "Value " + n.ToString();
                    cmd.ExecuteNonQuery();
                }
                // Now read them back
                cmd.CommandText = "SELECT * FROM FOO";
                using (DbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(String.Format("ID = {0}, MYVALUE = {1}", reader[0],
                        reader[1]));
                    }
                }
            }
            Console.ReadKey();
        }
    }
}

SQLite Nuts and Bolts

Query what's in the database

using sqlite3.exe from command line

'.tables' delivers a list of all tables.

Every SQLite database stores their tables in a internal special table called SQLITE_MASTER. The SQLITE_MASTER table is read only, you can not use UPDATE, INSERT or DELETE commands with this table, and the table is automatically updated by CREATE TABLE, CREATE INDEX, DROP TABLE, and DROP INDEX commands. Temporary tables do not appear in the SQLITE_MASTER table. At this time there is no way to get a listing of temporary tables and indices. SQLITE_MASTER's schema looks like this:

CREATE TABLE sqlite_master ( type TEXT, name TEXT, tbl_name TEXT, rootpage INTEGER, sql TEXT );

You can query SQLITE_MASTER table to get the tables and index names of all other tables and indexes this database contains. For tables, the type field will always be 'table' and the name field will be the name of the table. For indices, type is equal to 'index', name is the name of the index and tbl_name is the name of the table to which the index belongs. For both tables and indices, the sql field is the text of the original CREATE TABLE or CREATE INDEX statement that created the table or index. For automatically created indices (used to implement the PRIMARY KEY or UNIQUE constraints) the sql field is NULL.

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

SQLite and Types

SQLite is typeless. All data is stored internally as a null-terminated string. As a result of this typeless design you can omit most data type definitions from a CREATE TABLE statement without any difference in result. The only interpreted data type definition is INTEGER PRIMARY KEY, which really uses integers. Further you can insert strings to columns defined as integer and vice versa.

This has some strange effects in comparing data, especially of different types. All comparisons are done as strings. The SQLite API provides helper functions to convert from a internal representation of a datatype in C# to a string.

external links

11.9.2010: A SQLite Tutorial - This tutorial explores the power of sqlite3, showing common commands, triggers, attach statement with the union operation, introduced in a way that allows multiple tables, in separate databases, to be combined as one virtual table, without the overhead of copying or moving data. Last but not least Mike Chirico shows the power of the sign function and the amazingly powerful trick of using this function in SQL select statements to solve complex queries with a single pass through the data, after making a brief mathematical case for how the sign function defines the absolute value and IF conditions. Read on ...