(.Net,SQLite)データベースにSQLiteを使ってみた

.Netアプリでスタンドアロンデータベースを使うときはいつもMDBを使ってたんですが、今回はじめてSQLiteを使ってみました。

.Netアプリケーションから SQLite にアクセスする方法はいくつかあるらしいですが、今回はVB.NETから組み込みデータベースのSQLiteを操作する方法 | ラシカル開発記を参考にさせてもらい System.Data.SQLite を使ってみました。

環境設定

System.Data.SQLite は http://sourceforge.net/projects/sqlite-dotnet2/files/からダウンロードできます。
setup版と、バイナリ版がありますが、バイナリ版をダウンロードし、System.Data.SQLite.DLL と System.Data.SQLite.XML を VisualStudioのプロジェクトフォルダに置けばOKです。(System.Data.SQLite.Linq.dllもあるので、これを使えばLINQでアクセスできるのかもしれません)

VS側では、System.Data.SQLite.DLL を参照設定してやります。

ローカルのSQLiteの中身をWindowsPC上で編集したりデータ覗いたりするのはPupSQLite、もしくはTkSQLiteが便利です。(WEBだと、SQLiteManagerが定番らしいですが。。)

なお、Windows用のコマンドは、http://www.sqlite.org/download.htmlからダウンロードできます。

接続

System.Data.SQLite はよくできており他のデータプロバイダとほぼ同じ感覚で使うことができます。接続は以下のような感じで出来ます。

using System.Data.SQLite

//コネクションオブジェクト生成
SQLiteConnection sqlite_con = new SQLiteConnection();
//SQLite接続文字列定義
sqlite_con.ConnectionString = "Version=3;Data Source=test.sqlite;Compress=True;";
//DB接続
sqlite_con.Open();

//クエリ実行

//DBクローズ
sqlite_con.Close();

接続文字列についてです。

"Data Source=":SQLiteのデータベーファイルへのパスを指定。
"New=":Trueを入れると、新規データベーファイルを作成。(既にファイルが存在するとエラーになります) Falseなら既に存在するデータベーファイルを開くことになる。
"Version=":3固定。
"Password=":データベースのパスワードを指定。
"Synchronous=": 同期の設定。Normalだとディスク書き込み中にクラッシュした場合確率は低いがデータベースが壊れる可能性有り。 Fullだと最も安全だが低速。Offだと高速だけどクラッシュすると壊れる可能性高い。
"Max Pool Size=":接続プールの最大接続数。この値を超えた接続要求があると、接続が空くまで待ち状態。
"Read Only=": Trueだと読み取り専用で開く。

詳しくは、System.Data.SQLiteの接続文字列で使用できる引数の説明 | ラシカル開発記で詳しく説明されています。

データベースファイル、テーブル作成

データベースファイルを新規に作成し、テーブルを作るサンプルはこんな感じです。

using System.Data.SQLite

//コネクションオブジェクト生成
SQLiteConnection sqlite_con = new SQLiteConnection();
//SQLite接続文字列定義
sqlite_con.ConnectionString = "Version=3;Data Source=test.sqlite;New=True;Compress=True;";
//DB接続
sqlite_con.Open();
//コマンドオブジェクト生成
SQLiteCommand sqlite_cmd = sqlite_con.CreateCommand();

//テーブル作成クエリ
sqlite_cmd.CommandText  = "CREATE TABLE hoge (  id INTEGER , name TEXT , value  REAL)";
//クエリ実行
sqlite_cmd.ExecuteNonQuery();


//データ挿入
sqlite_cmd.CommandText  = 

sqlite_cmd.CommandText = "INSERT INTO hoge ( id , name , value ) VALUES ( @id , @name , @value  )
//クエリのパラメータ設定
sqlite_cmd.Parameters.Add("id", DbType.Int32);
sqlite_cmd.Parameters["id"].Value = 1;
sqlite_cmd.Parameters.Add("name", DbType.String);
sqlite_cmd.Parameters["name"].Value = "test";
sqlite_cmd.Parameters.Add("value", DbType.Double);
sqlite_cmd.Parameters["value"].Value = 0.002;

//挿入処理実行。戻り値は影響を受けた行数
int ret = sqlite_cmd.ExecuteNonQuery();

//DBクローズ
sqlite_con.Close();

データ参照クエリ

selectする場合はこんな感じ。

SQLiteConnection sqlite_con = new SQLiteConnection();
//SQLite接続文字列定義
sqlite_con.ConnectionString = "Version=3;Data Source=test.sqlite;Compress=True;";
//DB接続
sqlite_con.Open();
SQLiteCommand sqlite_cmd = sqlite_con.CreateCommand();
sqlite_cmd.CommandText = "SELECT id , name , value FROM tbl WHERE name LIKE @nameparam ";
//クエリのパラメータ設定
sqlite_cmd.Parameters.Add("nameparam", DbType.String);
sqlite_cmd.Parameters["nameparam"].Value = "hoge%";
//クエリ実行　結果はDataReaderで取得
SQLiteDataReader reader = sqlite_cmd.ExecuteReader();

 DataReaderでループして読み込む場合は以下のようにする。
while (reader.Read())
{
    Console.WriteLine(reader["id"] + "," + reader["name"] + "," + reader["value"] );
}

//接続終了
sqlite_con.Close();