SQLite and CSharp

From eqqon

(Difference between revisions)

Revision as of 13:09, 10 December 2007

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();
        }
    }
}

Another Way to do it Via ADO.NET

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();

}
}
}