3流プログラマのメモ書き

元開発職→現社内SEの三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。(jehupc.exblog.jpから移転中)

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

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

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

環境設定

System.Data.SQLitehttp://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();

使ってみた感想としては十分MDBの代わりになるんじゃないかと思います。ただ、今回DLLを直接参照しただけなので、VisualStudioのデータセットデザイナのウィザードからデータベースを参照することは出来ませんでした。インストール版であればいけるのかもしれません。

参考:
VB.NETから組み込みデータベースのSQLiteを操作する方法 | ラシカル開発記
[C#][SQLite]System.Data.SQLiteを試してみた その2「:memory:」
MSDN Magazine: 働くプログラマ - SQLite の内部
C#/.NET で SQLite を使う基本中のキホン | うめつる開発室
System.Data.SQLiteデータベース基本操作 その1 基本コマンド(C#) | 夏研ブログ