SQLite and CSharp

From eqqon

(Difference between revisions)

Revision as of 09:33, 13 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();
        }
    }
}

Alternative Way: ADO.NET and 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();
}
}
}

@@ Line 41: / Line 41: @@
 <span class="S10">}</span></span>
-== Alternative Way: System.Data.SQLite.dll ==
+== Alternative Way: ADO.NET and System.Data.SQLite.dll ==
 <span><span class="S5">using</span><span class="S0"> </span>System<span class="S10">;</span><br />
 <span class="S5">using</span><span class="S0"> </span>System<span class="S10">.</span>Data<span class="S10">;</span><br />