Revision as of 14:32, 19 July 2008

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.

@@ Line 87: / Line 87: @@
 <span class="S10">}</span></span>
+=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.
 [[Category:CSharp]]

SQLite and CSharp

From eqqon