SQLite and CSharp
From eqqon
m (→Example SQLite-DB access with LiteX.dll) |
m (→Another Way to do it Via ADO.NET) |
||
| Line 41: | Line 41: | ||
<span class="S10">}</span></span> | <span class="S10">}</span></span> | ||
| - | == | + | == Alternative Way: System.Data.SQLite.dll == |
| - | < | + | <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>Data<span class="S10">;</span><br /> |
| + | <span class="S5">using</span><span class="S0"> </span>System<span class="S10">.</span>Data<span class="S10">.</span>Common<span class="S10">;</span><br /> | ||
| + | <span class="S5">using</span><span class="S0"> </span>System<span class="S10">.</span>Data<span class="S10">.</span>SQLite<span class="S10">;</span><br /> | ||
| + | <span class="S5">namespace</span><span class="S0"> </span>test<br /> | ||
| + | <span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">class</span><span class="S0"> </span>Program<br /> | ||
| + | <span class="S0"> </span><span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">static</span><span class="S0"> </span><span class="S5">void</span><span class="S0"> </span>Main<span class="S10">(</span><span class="S5">string</span><span class="S10">[]</span><span class="S0"> </span>args<span class="S10">)</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// Create a connection and a command</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">using</span><span class="S0"> </span><span class="S10">(</span>DbConnection<span class="S0"> </span>cnn<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">"Data Source=test.db3"</span><span class="S10">))</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">using</span><span class="S0"> </span><span class="S10">(</span>DbCommand<span class="S0"> </span>cmd<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span>cnn<span class="S10">.</span>CreateCommand<span class="S10">())</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// Open the connection. If the database doesn't exist,</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// it will be created automatically</span><br /> | ||
| + | <span class="S0"> </span>cnn<span class="S10">.</span>Open<span class="S10">();</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// Create a table in the database</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>CommandText<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span><span class="S6">"CREATE TABLE FOO (ID INTEGER PRIMARY KEY, MYVALUE VARCHAR(50))"</span><span class="S10">;</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>ExecuteNonQuery<span class="S10">();</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// Create a parameterized insert command</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>CommandText<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span><span class="S6">"INSERT INTO FOO (MYVALUE) VALUES(?)"</span><span class="S10">;</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>Parameters<span class="S10">.</span>Add<span class="S10">(</span>cmd<span class="S10">.</span>CreateParameter<span class="S10">());</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// Insert 10 rows into the database</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">for</span><span class="S0"> </span><span class="S10">(</span><span class="S5">int</span><span class="S0"> </span>n<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>n<span class="S0"> </span><span class="S10"><</span><span class="S0"> </span><span class="S4">10</span><span class="S10">;</span><span class="S0"> </span>n<span class="S10">++)</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>Parameters<span class="S10">[</span><span class="S4">0</span><span class="S10">].</span>Value<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span><span class="S6">"Value "</span><span class="S0"> </span><span class="S10">+</span><span class="S0"> </span>n<span class="S10">.</span>ToString<span class="S10">();</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>ExecuteNonQuery<span class="S10">();</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">}</span><br /> | ||
| + | <span class="S0"> </span><span class="S2">// Now read them back</span><br /> | ||
| + | <span class="S0"> </span>cmd<span class="S10">.</span>CommandText<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span><span class="S6">"SELECT * FROM FOO"</span><span class="S10">;</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">using</span><span class="S0"> </span><span class="S10">(</span>DbDataReader<span class="S0"> </span>reader<span class="S0"> </span><span class="S10">=</span><span class="S0"> </span>cmd<span class="S10">.</span>ExecuteReader<span class="S10">())</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span><span class="S5">while</span><span class="S0"> </span><span class="S10">(</span>reader<span class="S10">.</span>Read<span class="S10">())</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">{</span><br /> | ||
| + | <span class="S0"> </span>Console<span class="S10">.</span>WriteLine<span class="S10">(</span>String<span class="S10">.</span>Format<span class="S10">(</span><span class="S6">"ID = {0}, MYVALUE = {1}"</span><span class="S10">,</span><span class="S0"> </span>reader<span class="S10">[</span><span class="S4">0</span><span class="S10">],</span><br /> | ||
| + | <span class="S0"> </span>reader<span class="S10">[</span><span class="S4">1</span><span class="S10">]));</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">}</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">}</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">}</span><br /> | ||
| + | <span class="S0"> </span>Console<span class="S10">.</span>ReadKey<span class="S10">();</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">}</span><br /> | ||
| + | <span class="S0"> </span><span class="S10">}</span><br /> | ||
| + | <span class="S10">}</span></span> | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
[[Category:CSharp]] | [[Category:CSharp]] | ||
Revision as of 09:31, 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: 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();
}
}
}