SQLite and CSharp

From eqqon

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: System.Data.SQLite.dll

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