C#/.NET で SQLite を使う基本中のキホン

sqlite370_banner

C#/.NETで作っているプログラムでちょっとしたデータベースを使いたいと思ってて、でもプラットフォームがWindowsだからいつも使っている MySQL は敷居が高いと思ってあるところで相談したら、SQLite がいいよと教えてもらった。そこで、C#/.NET から SQLite を使うときの基本的な流れとプログラムの作り方をまとめておきたいと思います。今回試した環境は Window 7 で Visual Studio 2010 です。

お手軽DB “SQLite”

SQLite はクライアントサーバーまでは必要としない、お手軽データベースです。Windowsでは共有DLLを呼び出すだけでDBを扱うことができます。排他処理はコネクションレベルのみのサポートで、高スループットを求めたり、マルチスレッドでガシガシ読み書きするというのには向かないようですが、とても簡単に使えます。必要なら、DataGridViewにテーブルを表示するといったことも簡単にできます。

SQLiteの入手とインストール

SQLiteはパブリックドメイン宣言されたオープンソースのデータベースエンジンです。Cで書かれており、Windows、Windows Phone、Mac OS、Windows RT 等のバイナリが公開されています。もちろん Linux からも使えます。C言語がAPIの基本ですが、CPPやラッパークラスを使った他言語からのアクセスも可能になっています。NET のマネージド環境には、System.Data.SQLite というアセンブリが提供されています。

アセンブリは、.NET のバージョンごとに提供されているので、開発するプログラムと.NETのバージョンにあったモノをダウンロードします。今回は、Setups for 32-bit Windows (.NET Framework 4.0) をダウンロードしsetupします。.NET 2.0 のコードは .NET 4.0 でも動くはずなので、もっと低いバージョンにしてもかまわないでしょう。Setup がいやな人は、zip ファイルもダウンロードページの後半においてあります。sqlite3

http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

デフォルトでは、C:\Program Files\System.Data.SQLite\2010 にインストールされます。スタートメニューに、System.Data.SQLite というメニューができているので、そこから Test Application を右クリックして、管理者で実行します。System.Data.SQLite のテストができますので、辛抱強く終わるのを待ちましょう。

デバッグ中にSQLiteのデータベースファイルを確認するには、SQLite Database Browser が便利です。ダウンロードして使えるようにしておきましょう。なお、こいつでデータベースを開いているときに、他のプログラムからデータベースにアクセスすると、そこでプログラムがアクセス待ち状態になります。コネクションを張りっぱなしなのですね。

C#/.NET から DB を使う

インストールできたので、Visual Studio 2010 で使ってみましょう。新しいプロジェクトを作成して、System.Data.SQLite を参照に追加します。コードにも、

using System.Data.SQLite;

を加えておきましょう。DBに接続してtableを作成して終了する初期化のコードを書いてみましょう。id は INTERGER で AUTOINCRIMENT に指定しています。name、emailはテキスト(TEXT)です。SQLiteでは型を指定しなくてもいいことになっていますが、ちょっと気持ち悪いので型指定しましょう。SQLite内部では型をある程度管理していて、次にようになっているそうです。NULL は状態なので、それ以外の4種類のどれかを指定することになります。一般的なデータベースの型に比べるとかなり少ないですが、最低限のものはそろっているといってよいでしょう。

NULL
NULLという値
INTEGER
符号付き整数(1~8バイト)
REAL
8バイトIEEE浮動小数点数
TEXT
文字列
BLOB
バイナリデータ

http://www.sqlite.org/datatype3.html

なお、行のサイズやDB全体のサイズにもいろいろと制限がありコンパイル時に指定するようです。でも、足りなくなってから考えてもいいでしょう。データベースが巨大になるようなら、SQLite向きではないのかもしれませんし。

string db_file="sample.db";

using (var conn = new SQLiteConnection("Data Source="+db_file))
{
	conn.Open();
	using (SQLiteCommand command =conn.CreateCommand())
	{
	command.CommandText = "create table member(id INTEGER  PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)";
		command.ExecuteNonQuery();
	}
	conn.Close();
}

SQLiteConnection で DB と接続しOpen()、SQLiteCommandでSQLコマンドを生成して、ExecuteNonQuery() で実行、Close()するという流れです。using ステートメントを使ってインスタンスの寿命がはっきりする書き方を採用しています。

sqlite10

このままだと、2度目を実行するとすでにあるテーブルを作ろうとして例外が発生します。そこで、テーブルのリストを取得する関数を作ってみましょう。

テーブル一覧を取得する

データベースに含まれるテーブルは、管理用の特別なテーブル sqlite_master に含まれています。テーブル名は tbl_name というカラムに含まれていますので、それを読みだすことにしましょう。

List<string> tList = new List<string>();
using (var conn = new SQLiteConnection("Data Source=" + db_file))
{
	conn.Open();
	using (SQLiteCommand command = conn.CreateCommand())
	{
		command.CommandText = "SELECT * FROM sqlite_master WHERE type='table'";
		using (SQLiteDataReader reader = command.ExecuteReader())
		{
			while (reader.Read())
			{
				tList.Add(reader["tbl_name"].ToString());
			}
		}
	}
	conn.Close();
}

こんなコードを書けば、tList にテーブル名の一覧が得られます。SQLの実行結果が必要ない前の例では、command.ExecuteNonQuery() を実行しましたが、今回はクエリーの結果が必要なので、command.ExecuteReader() を実行して結果をとりこんでいます。

なお、カラム一覧を知りたいときは次のSQL文で取得できます。SQLite Database Browser で実行した結果を載せておきます。

string sql="PRAGMA table_info(member)";

sqlite13

データを書き込む

データを書き込むときは、insert ですよね。もう2つばかり例を見たので似たようにやればいいだけです。update するときは、指定した条件の行がないと例外を起こしますので、まず select して確認してから update をかけるほうがいいでしょう。いったん読みこんで処理をして書き戻すような場合は省略もできますが、念のためにコードには入れておきましょう。なお、id は AUTOINCREMENT ですので、 insert するときには指定しません。

mList は適当に作った構造体みたいなクラスで、初期化して適当なデータを入れてあります。

// ここはダミーデータを作っているだけ
private class MemberTable
{
	public string name;
	public string email;
	public MemberTable(string _name, string _email)
	{
		name = _name;
		email = _email;
	}
}
List<MemberTable> mList = new List<MemberTable>()
{
	new MemberTable(@"石田",@"ishoda@hoge.co.jp"),
	new MemberTable(@"山本",@"yamamo@hoge.co.jp"),
	new MemberTable(@"指原",@"sashy@hoge.co.jp"),
	new MemberTable(@"秋山",@"aki@hoge.co.jp"),
	new MemberTable(@"多田",@"tadar@hoge.co.jp"),
	new MemberTable(@"佐々木",@"sasaki@hoge.co.jp"),
	new MemberTable(@"佐藤",@"satoh@hoge.co.jp"),
	new MemberTable(@"轟",@"todo@hoge.co.jp"),
	new MemberTable(@"山下",@"yamachan@hoge.co.jp"),
	new MemberTable(@"堤",@"tutumin@hoge.co.jp"),
};
// ここから insert の関数
private void Insert(string db_file)
{
	using (var conn = new SQLiteConnection("Data Source=" + db_file))
	{
		conn.Open();
		using (SQLiteTransaction sqlt = conn.BeginTransaction())
		{
			using (SQLiteCommand command = conn.CreateCommand())
			{
				foreach (MemberTable m in mList)
				{
					command.CommandText = "insert into member (name,email) values('" + m.name + "', '" + m.email + "')";
					command.ExecuteNonQuery();
				}
			}
			sqlt.Commit();
		}
		conn.Close();
	}
}

これまで見てきたコードと少し違うのは、Openした後に conn.BeginTransaction() してから SQL コマンドを実行して、最後に Commit() してから Close() しています。SQLが1つだけならいいんですが、いくつかまとめるときはトランザクション処理を明示しないと速度が稼げません。実際に BeginTransaction() ありなしで 10 回 insert する条件で上のInsert関数の時間を比較すると、次のようになります。

BeginTransactionあり
平均 129ミリ秒
BeginTransactionなし
平均 1204ミリ秒

高々10回のクエリーを処理するのに、BeginTransaction() しないと1.2秒もかかっています。UIスレッドからこの関数を実行すれば、ウィンドウ操作はかなりギクシャクした感じになるでしょう。129ミリ秒だって速いとはいえません。頻繁に insert、updateを繰り返すような用途には、SQLiteは向いていないといえそうです。

sqlite11

なお、もうコードは書きませんが、update するときは、where 句をつけて書き換え対象行を指定してクエリーを実行します。

sql="UPDATE member SET 'name'='ほげほげ' , 'email'='hoge@hogege.ac.jp' WHERE id=12"

DBの読みだし

最後に select文 を使ったデータの読み出しを試しておきましょう。

// select の結果を返すためのクラス
private class SelectResult
{
	private int id;
	private string name;
	private string email;
	public SelectResult(int _id, string _name, string _email)
	{
		id = _id; name = _name; email = _email;
	}
}
// select の関数
private List<SelectResult> select(string db_file,string temail)
{
	List<SelectResult> result=new List<SelectResult>();
	using (var conn = new SQLiteConnection("Data Source=" + db_file))
	{
		conn.Open();
		using (SQLiteCommand command = conn.CreateCommand())
		{
			command.CommandText = "SELECT * from member WHERE 'email'='" + temail + "'";
			using (SQLiteDataReader reader = command.ExecuteReader())
			{
				while (reader.Read())
				{
					int id = Convert.ToInt32(reader["id"].ToString());
					string name = reader["name"].ToString();
					string email = reader["email"].ToString();
					result.Add(new SelectResult(id, name, email));
				}
			}
		}
		conn.Close();
	}
	return result;
}

select文のほうはデータを検索して読みだすだけなので一瞬(数ミリ秒)です。SQLiteDataReader は reader[0]、reader[1] のように読みだすこともできますが、カラム名を指定したほうが可読性が高まりよいでしょう。

まとめ

テーブルの作成方法、テーブル一覧の取得、カラムの一覧、データの Insert、Update、そして select でのデータの読み出しのコードを書いてみました。

ポイントは、Insert や Update の処理が非常に遅いということです。場合によっては Walker スレッドなどを使ってUIスレッドと切り離して処理を行う必要があるかもしれませんね。でも、あまりにも処理の負荷が重いようなら、もう少し本格的な DBサーバーも考えたほうがいいかもしれません。そんなことを考えると、MySQL などの他のDBも透過的に扱えるコードを書いたほうがいいかもしれませんね。今回は Syste.Data.SQLite.Linq を使いませんでしたが、そちらでやったほうが汎用性は高まるかもしれません。わたしは、Linq に詳しくないので、他の方にぜひサンプルをお願いしたいところです。

それから、サンプルですので例外処理やクエリーの実行結果をチェックするコードなども省略してあります。再利用されるときは注意してくださいね。

参考

  • SQLite
    http://www.sqlite.org/
  • System.Data.SQLite
    http://system.data.sqlite.org/index.html/doc/trunk/www/index.wiki
  • 【C#】SQLiteを使う その2 DataGridViewにテーブルの内容を表示する
    http://team-pag.interprism.co.jp/member/okazawa/blog/?p=1156

You can skip to the end and leave a response. Pinging is currently not allowed.
Subscribe to RSS Feed Twitter は Ume108 だよ