ちょっとしたデータを保存するのであれば、テキストファイルを使うのが手軽で簡単です。
でも、数十～数十ギガバイトのデータから、特定の条件のものを抜き出したり集計したりする場合、データベースが不可欠です。
データベースと一言で言っても、商用データベースで有名なOracleやSQLServer、無料ながら業務で使われているPostgreSqlやMySqlなど、数多くの製品が存在しますが、ちょっとした用途で使うには向いていません。
そんな時重宝されるのがSQLiteです。
ということで、今回はC#からSQLiteを使うためのインストール方法と使い方について解説したいと思います。
SQLiteの使い方を解説しているサイトはたくさん存在しますが、他のサイトでは解説していない「SQLiteを使うために必要な手順の全体像」と「関数化した機能単位のサンプル」で構成しています。
SQLiteの概要
SQLite はクライアントPCにインストールすることなく、単一のファイルの中に複数のテーブルを保存且つ管理できる、組み込み型のフリーデータベースです。
複数端末から同時にデータを更新することは出来ませんが、標準的なSQLが使えるので、一般的なデータベースと同様の処理を行うことが可能です。
詳しい内容がお知りになりたい方、制限事項や注意点について把握しておきたい方は、こちらの記事に記載しましたので、是非ご一読下さい。
SQLiteのインストール方法
SQLiteを使う場合、NuGetを使ってProjectにインストールする必要があります。
NuGetの使い方については こちら に詳しく記載していますので、必要に応じてご参照下さい。
簡単に説明すると Visual Studio のメニューから「ツール」⇒「NuGetパッケージマネージャ」⇒「ソリューションのNuGetパッケージの管理」で次の画面が表示されますので、sqlte で検索します。
表示された一覧の中から、「System.Data.Sqlite.Core」をクリックし、インストール先のプロジェクト（下記のサンプル画面ではSQLiteTest）にチェックを入れて、「インストール」をクリックします。
次の画面が表示されますので、画面下の「OK」をクリックします。
以上でインストールは完了です。
SQLiteを扱うために必要な手順の全体像
今回解説する手順は、SQLiteに限らず、OracleやSQLite、PostgreSql、MySqlなど、多くのデータベースに共通する手順です。
接続文字列
1行目の SQliteConnection() メソッドの引数に記載された「接続文字列」とは、接続先を指定するための記述です。
SqlLite の場合は ”Data Source = ファイル名” と記述します。
例えば、Dドライブのルートに存在するSQLiteTest.DBというデータベースファイルを指定したい場合、
“Data Source = D:\SQLiteTest.DB”
という具合です。
接続を開く
SQliteConnectionのインスタンスを生成したら、Open() メソッドを呼んで接続を開きます。
このメソッド以降は、SQLiteのデータベースに接続されたままになります。
コマンドの実行
SQLを実行する場合は、SQLCommandクラスのインスタンスを生成し、CommandTextプロパティに実行したいSQL を代入します。
この図の例では、 ExecuteNoneQuery() というメソッドを呼んでSQLを実行していますが、この部分をExecuteScalarやExecuteReaderに変更することで、様々な機能を使う事ができます。
具体的な例は「機能別サンプル」に記載しています。
接続を閉じる
最後には必ずデータベースの接続を閉じる⇒「Close()メソッドを呼び出す」必要があります。
これを忘れると、一般的なデータベースの場合は接続が残ったままになり、何度もプログラムを起動・終了するうちに、データベースに接続できなくなります。
SQLiteの場合はデータベースファイルが掴まれた状態になり、データベースファイルを削除したり、テーブルの中身を変更することが出来なくなりますので、ご注意ください。
DataTableを使った更新
DataTableクラスはデータベースとの相性が抜群です。
そして、DataTableは一覧を表示するDataGridView（WindowsForm）やDataGrid（WPF)のようなコントロールとも相性が良いという特徴があります。
つまり、DataGridViewやDataGridに表示している内容を変更し、テーブルに保存したり、また逆に検索結果をDataGridViewやDataGridに表示するという処理が簡単に出来るという事です。
次の図は、今までに説明してきたSQLの実行方法と、DataTableを使った実行方法の比較を表しています。
具体的な例は「機能別サンプル」に記載していますので、そちらに譲るとして、ここではDataTableの内容を簡単にデータベースに保存したり、逆に読み出したりすることが出来るという点だけ押さえておいて下さい。
機能別サンプル
それでは、機能別のサンプルを紹介していきたいと思います。
その前に、少しだけ注意点を記述しておきます。
まず、冒頭の参照設定（using） に次の1行を追加しておいて下さい。
|
1 |
using System.Data.SQLite; |
次に、ConnectString という文字列変数を定義し、接続文字列を設定しておいて下さい。
|
1 |
private string ConnectString {get;set;} = @"Data Source = D:\SQLiteTest.db"; |
接続文字列を記述しておけば、そこにデータベースファイルが存在していなくても、データベースにテーブルを保存した段階で、自動的にデータベースファイルが出来上がります。
最後に、ここから紹介する機能別サンプルは全て関数（メソッド）として記述しています。
として、第1引数に接続文字列を必ず指定るようにしています。
これが面倒な場合、全てのサンプルを１つのクラスに入れて、ConnectString をメソッド内で参照するように変更するなどの処置を行って下さい。
データベースファイルの作成
データベースファイルを明示的に作成するには、SQLiteConnection.CreateFile メソッドを使います。
第一引数にファイル名を指定すると、その名前でデータベースファイルが作成されるのですが、既に存在する場合は中身が削除され、0バイトのデータベースファイルが再作成されます。
誤ってCreateFileをしてしまうとデータが丸ごと消えてしまうため、注意が必要です。
|
1 2 |
SQLiteConnection.CreateFile(ファイル名); |
任意のSQLを実行
データの更新や削除など、抽出以外のSQLを実行する場合のサンプルです。
using ステートメントを使う事で、全ての処理が終了して時点で SQLiteConnection のClose()メソッドが自動的に呼ばれるため、接続を閉じる処理を明示的に記述する必要がありません。
このため、メソッド全体的がコンパクトにまとまっています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
/// <summary> /// SQLの実行 /// </summary> /// <param name="connectString"></param> /// <param name="sqls"></param> public void ExecuteNoneQuery(string connectString, string sql) { using (SQLiteConnection connection = new SQLiteConnection(connectString)) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } |
トランザクション付きSQLの実行
複数のSQLを実行する際、全てのSQLが成功した時のみ、結果をデータベースに反映するトランザクション処理は、次のような記述になります。
SQLの文字列配列を第2引数に渡すことで、それらすべてがトランザクションの傘下に入ります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
/// <summary> /// SQLの実行 /// </summary> /// <param name="connectString"></param> /// <param name="sqls"></param> public void ExecuteNoneQueryWithTransaction(string connectString, string[] sqls) { using (SQLiteConnection connection = new SQLiteConnection(connectString)) { connection.Open(); SQLiteTransaction trans = connection.BeginTransaction(); try { foreach (string sql in sqls) { using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.Transaction = trans; cmd.CommandText = sql; cmd.ExecuteNonQuery(); cmd.Dispose(); } } trans.Commit(); } catch { trans.Rollback(); throw; } } } |
トランザクションの機能としては他のデータベースと同じですが、１つだけ異なる点があります。
それは処理速度です。
例えば、何万件もの insert や update 文を実行した場合を考えましょう。
SQLiteでトランザクションを掛けない場合、1件ずつ結果をデータベースファイルに書き込むことになるため、膨大なファイルIO（ファイルオープン⇒更新⇒クローズ）が発生します。
一方、トランザクションを掛けると、ひとまとまりの結果をデータベースファイルに書き込むため、少なくともファイルオープンとクローズのオーバーヘッドが激減します。
実際にはデータベースファイル内にある管理情報の変更など、多くのファイルIOが発生するため、トランザクションを掛ける掛けないでは数百倍くらいの差になってくるのです。
一般的なデータベースだと、単にデータの整合性を保つという目的でしか使いませんが、SQLiteの場合は処理速度をアップさせる目的として必須になるため、その点はご留意ください。
スカラー（単一データ）の取得
スカラー文（答えが１つだけ返されるQuery文）を事項する場合のサンプルになります。
メソッドの戻り値は object 型なので、使う時はデータ型に応じた型変換が必要です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
/// <summary> /// スカラーによる単一データの取得 /// </summary> /// <param name="connectString"></param> /// <param name="sql"></param> /// <returns></returns> public object ExecuteScalar(string connectString, string sql) { object result = null; using (SQLiteConnection connection = new SQLiteConnection(connectString)) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = sql; result = cmd.ExecuteScalar(); } } return result; } |
データの検索
データの検索には ExecuteReader メソッドを使います。
ファイルを読み込むのと同じ感覚で、検索結果をが1行ずつ、列で分解された配列として返されますので、それをListやDataTableに読み込んで使います。
このサンプルでは List<object[]> として返しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
/// <summary> /// DataReaderを使ったデータの取得 /// </summary> /// <param name="connectString"></param> /// <param name="sql"></param> /// <returns></returns> public List<object[]> ExecuteReader(string connectString, string sql) { List<object[]> result = new List<object[]>(); using (SQLiteConnection connection = new SQLiteConnection(connectString)) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { //SQLの設定 cmd.CommandText = sql; //検索 using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { object[] data = Enumerable.Range(0, reader.FieldCount).Select(i => reader[i]).ToArray(); result.Add(data); } } } } return result; } |
DataTableを使ったデータの取得
検索結果をDataTableとして返して、一気にDataGridViewやDataGridに表示したい場合、この方法が便利です。
SQLiteDataAdapter というクラスのインスタンスを生成し、Fillメソッドを呼び出すと、Fillメソッドの第一引数に検索結果が返されます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/// <summary> /// DataTableを使ったデータの取得 /// </summary> /// <param name="connectString"></param> /// <param name="sql"></param> /// <returns></returns> public DataTable GetData(string connectString, string sql) { DataTable dt = new DataTable(); using (SQLiteConnection connection = new SQLiteConnection(connectString)) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = sql; // DataAdapterの生成 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); // データベースからデータを取得 da.Fill(dt); } } return dt; } |
DataTableを使った更新
DataTableに対してデータの書き換え、行の挿入、削除を行った場合、その操作はDataTableに保存されています。
具体的には、各行（DataRow）のRowStateに、挿入、更新、削除という状態が保存されています。
SQLiteCommandBuilderクラスを使うと、RowStateの情報をもとに、insert文/update文/delete文が自動生成されます。
この自動生成されたSQLは、引数で渡されたSQLiteDataAdapterのインスタンスにセットされるようになっていて、SQLiteDataAdapterのUpdateメソッドを呼び出すことでデータベースが更新できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
/// <summary> /// DataTableの内容をデータベースに保存 /// </summary> /// <param name="connectString"></param> /// <param name="tableName"></param> /// <param name="dt"></param> /// <returns></returns> public DataTable SetData(string connectString,string tableName,DataTable dt) { using (SQLiteConnection connection = new SQLiteConnection(connectString)) { connection.Open(); SQLiteTransaction trans = connection.BeginTransaction(); try { using (SQLiteCommand cmd = connection.CreateCommand()) { //書き込み先テーブルの列名と型を取得するためのSQLをCommandに登録 cmd.CommandText = "select * from " + tableName; // DataAdapterの生成 SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); //Insert、Delete、Update コマンドの自動生成 SQLiteCommandBuilder bulider = new SQLiteCommandBuilder(da); //DataTableの内容をデータベースに書き込む da.Update(dt); //コミット trans.Commit(); } } catch { trans.Rollback(); throw; } } return dt; } |
ちなみに、SQLiteCommandBuilder から自動生成される insert、update、delete 文は、次の様に記述することで取得出来ます。
|
1 2 3 4 |
SQLiteCommandBuilder builder = new SQLiteCommandBuilder(da); string insert = builder.GetInsertCommand().CommandText; string update = builder.GetUpdateCommand().CommandText; string delete = builder.GetDeleteCommand().CommandText; |
今回はSQL文を自動生成していますが、自分でinsert文、update文、delete文を記述して、SQLを自動生成させることも可能ですが、少し複雑になるので、ここでは割愛します。
さらに、もう1点のポイントがあって、トランザクション処理を行っている所です。
トランザクション処理を行わないと、更新がとんでもなく遅くなりますので、必ず記述するようにしましょう。
テーブルの存在チェック
テーブルの存在チェックは、sqlite_master から指定したテーブル名を検索することで実現できます。
この例ではテーブルとビューのどちらも存在チェックの対象にしています。
|
1 2 3 4 5 6 |
public bool Exists(string connectString, string tableName) { object val = ExecuteScalar(connectString, "select count(*) from sqlite_master where type in ('table','view') and name='" + tableName + "'"); return (int.Parse(val.ToString()) == 0) ? false : true; } |
テーブルの一覧取得
テーブルの存在チェックと同様に sqlite_master から情報を取得します。
ちなみに、このテーブルにはCreate文も格納されています。
|
1 2 3 4 5 6 |
public string[] GetTableList(string connectString) { var dt = GetData(connectString,"select tbl_name from sqlite_master where type in ('table','view') "); return dt.AsEnumerable().Select(i => i[0].ToString()).ToArray(); } |
カラム名（列名）の一覧取得
カラム名は、先述したGetDataメソッドを使うことで取得可能です。
この方法は、.NETからアクセスできるデータベース全て共通に利用できます。
|
1 2 3 4 5 6 |
public string[] GetColumnNames(string connectString, string tableName) { var dt = GetData(connectString, "select * from " + tableName + " where 1=2"); return dt.Columns.Cast<DataColumn>().Select(i=>i.ColumnName).ToArray(); } |
SQLite限定であるなら、次のプラグマ（SQLiteへの指示命令）が使用できます。
|
1 |
PRAGMA table_info('テーブル名'); |
テーブルの作成
テーブル作成はCreate文を発行します。
Create Table テーブル名(Column1 type1 ,column2 type2, ～) PrivaryKey（column1,column2,～）
データタイプ(type) は TEXT、REAL、INTEGER、BLOB の4種類になります。
カラム名(column)には使える文字と使えない文字があります。
例えば、数字から始まったり、＋やｰ等の演算記号、半角の( ) 、insert やdeleteなどの予約語は使えません。
カラム名をダブルクォートで括ると全ての文字が使えるようになりますが、いちいちダブルクォートを付けるのは面倒なので、極力使える文字列でカラム名を命名することをお勧めします。
|
1 2 3 4 5 6 7 8 |
public void CreateTable(string connectString, string tableName,string fieldList,string primaryKeyList) { var primary = (primaryKeyList == "") ? "" : (",primary key (" + primaryKeyList + ")"); var sql = string.Format("create table {0}({1} {2})", tableName, fieldList, primary); ExecuteNoneQuery(connectString, sql); } |
DataTableから自動的にカラム名を取り出し、Create文を作成するサンプルを以下に示します。
この例だと全ての型がTEXTになります。
DataColumnの型からSQLiteの型に変換するようにすれば、型を指定したCreate文を作成することも可能です。
|
1 2 3 4 5 6 7 8 |
public void CreateTable(string connectString, string tableName,DataTable dt,string primaryKeyList) { var fileds = dt.Columns.Cast<DataColumn>().Select(i => i.ColumnName).ToArray(); var sql = "create table " + tableName + "(" + string.Join(",", fileds); sql += (primaryKeyList == "") ? ")" : ",primary key (" + primaryKeyList + "))"; ExecuteNoneQuery(connectString,sql); } |
テーブルの削除
テーブルを削除するには、SQLで Drop Table を実行します。
次はテーブル名を指定することで、Drop Tableを自動生成して実行するサンプルです。
|
1 2 3 4 5 |
public void DropTable(string connectString, string tableName) { ExecuteNoneQuery(connectString, "drop table " + tableName); } |
肥大化したデータベースファイルをスリム化する
SQLiteのデータベース内に存在するテーブルやレコードを削除しても、一旦巨大化したデータベースファイルのサイズが小さくなることはありません。
そのため、使われていない領域を解放してデータベースファイルを縮小するための命令が用意されています。
それが VACCUM です。
通常のSQLのと同じ方法で実行すれば、データベースファイルをスリム化することが出来ます。
下記がそのサンプルソースになります。
|
1 2 3 4 |
public void Vacuum(string connectString) { ExecuteNoneQuery(connectString, "VACUUM"); } |
まとめ
SQLiteの構造や型の解説と、C#からSQLiteに接続する方法、そしてSQLの実行やデータの取得方法など、実用上使われるであろう一通りの機能について、サンプル付きで解説しました。
SQLiteは数十～数百ギガバイトのデータをローカルで扱うことが出来、しかも高速に動作する使いやすいデータベースです。
とんどん活用して行きましょう。
皆さんのプログラミングライフに、この記事がお役に立てれば幸いです。