SQLite and CSharp

From eqqon

(Difference between revisions)
Jump to: navigation, search
m (→Example SQLite-DB access with LiteX.dll)
Line 3: Line 3:
== Example SQLite-DB access with LiteX.dll ==
== Example SQLite-DB access with LiteX.dll ==
-
<pre>
+
<span><span class="S5">using</span><span class="S0"> </span>System<span class="S10">;</span><br />
-
using System;
+
<span class="S5">using</span><span class="S0"> </span>System<span class="S10">.</span>Collections<span class="S10">.</span>Generic<span class="S10">;</span><br />
-
using System.Collections.Generic;
+
<span class="S5">using</span><span class="S0"> </span>System<span class="S10">.</span>Text<span class="S10">;</span><br />
-
using System.Text;
+
<span class="S5">using</span><span class="S0"> </span>LiteX<span class="S10">;</span><br />
-
using LiteX;
+
<br />
-
 
+
<span class="S5">namespace</span><span class="S0"> </span>sqlite3<br />
-
namespace sqlite3
+
<span class="S10">{</span><br />
-
{
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S5">class</span><span class="S0"> </span>Program<br />
-
    class Program
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">{</span><br />
-
    {
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S5">static</span><span class="S0"> </span><span class="S5">void</span><span class="S0"> </span>Main<span class="S10">()</span><br />
-
        static void Main()
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">{</span><br />
-
        {
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>SQLiteConnection<span class="S0"> </span>db<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span><span class="S5">new</span><span class="S0"> </span>SQLiteConnection<span class="S10">(</span><span class="S6">"test.db"</span><span class="S10">);</span><br />
-
            SQLiteConnection db = new SQLiteConnection("test.db");
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>db<span class="S10">.</span>BeginTransaction<span class="S10">();</span><br />
-
            db.BeginTransaction();
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>db<span class="S10">.</span>ExecuteNonQuery<span class="S10">(</span><span class="S6">"drop table if exists test;"</span><span class="S10">);</span><br />
-
            db.ExecuteNonQuery("drop table if exists test;");
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>db<span class="S10">.</span>ExecuteNonQuery<span class="S10">(</span><span class="S6">"create table test( nr int primary key, name varchar(30), text varchar(30) );"</span><span class="S10">);</span><br />
-
            db.ExecuteNonQuery("create table test( nr int primary key, name varchar(30), text varchar(30) );");
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><br />
-
           
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>db<span class="S10">.</span>ExecuteNonQuery<span class="S10">(</span><span class="S6">"insert into test values (1, \"hello\", \"world!\n\");"</span><span class="S10">);</span><br />
-
            db.ExecuteNonQuery("insert into test values (1, \"hello\", \"world!\n\");");
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>db<span class="S10">.</span>ExecuteNonQuery<span class="S10">(</span><span class="S6">"insert into test values (2, \"hello\", \"eqqon!\");"</span><span class="S10">);</span><br />
-
            db.ExecuteNonQuery("insert into test values (2, \"hello\", \"eqqon!\");");
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>db<span class="S10">.</span>Transaction<span class="S10">.</span>Commit<span class="S10">();</span><br />
-
            db.Transaction.Commit();
+
<br />
-
 
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S5">using</span><span class="S0"> </span><span class="S10">(</span>SQLiteDataReader<span class="S0"> </span>data<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span>db<span class="S10">.</span>ExecuteReader<span class="S10">(</span><span class="S6">"SELECT * FROM test;"</span><span class="S10">))</span><br />
-
            using (SQLiteDataReader data = db.ExecuteReader("SELECT * FROM test;"))
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">{</span><br />
-
            {
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S5">while</span><span class="S0"> </span><span class="S10">(</span>data<span class="S10">.</span>Read<span class="S10">())</span><br />
-
                while (data.Read())
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">{</span><br />
-
                {
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S5">for</span><span class="S0"> </span><span class="S10">(</span><span class="S5">int</span><span class="S0"> </span>pos<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span><span class="S4">0</span><span class="S10">;</span><span class="S0"> </span>pos<span class="S0"> </span><span class="S10">&lt;</span><span class="S0"> </span>data<span class="S10">.</span>FieldCount<span class="S10">;</span><span class="S0"> </span>pos<span class="S10">++)</span><br />
-
                    for (int pos = 0; pos < data.FieldCount; pos++)
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">{</span><br />
-
                    {
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>Console<span class="S10">.</span>WriteLine<span class="S10">(</span>data<span class="S10">.</span>GetName<span class="S10">(</span>pos<span class="S10">));</span><br />
-
                        Console.WriteLine(data.GetName(pos));
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>Console<span class="S10">.</span>WriteLine<span class="S10">(</span>data<span class="S10">.</span>GetFieldType<span class="S10">(</span>pos<span class="S10">));</span><br />
-
                        Console.WriteLine(data.GetFieldType(pos));
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>Console<span class="S10">.</span>WriteLine<span class="S10">(</span>data<span class="S10">.</span>GetValue<span class="S10">(</span>pos<span class="S10">));</span><br />
-
                        Console.WriteLine(data.GetValue(pos));
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>Console<span class="S10">.</span>WriteLine<span class="S10">();</span><br />
-
                        Console.WriteLine();
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">}</span><br />
-
                    }
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">}</span><br />
-
                }
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">}</span><br />
-
            }
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span>Console<span class="S10">.</span>In<span class="S10">.</span>ReadLine<span class="S10">();</span><br />
-
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">}</span><br />
-
            Console.In.ReadLine();
+
<span class="S0">&nbsp;&nbsp;&nbsp;&nbsp;</span><span class="S10">}</span><br />
-
        }
+
<span class="S10">}</span></span>
-
    }
+
-
}
+
-
</pre>
+
== Another Way to do it Via ADO.NET ==
== Another Way to do it Via ADO.NET ==

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

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

}
}
}
Retrieved from "http://eqqon.com/index.php/SQLite_and_CSharp"