【すぐに使える】C#でSQLiteを扱うための解説とサンプル集

プログラミングTips
この記事は約12分で読めます。

ちょっとしたデータを保存するのであれば、テキストファイルを使うのが手軽で簡単です。

でも、数十~数十ギガバイトのデータから、特定の条件のものを抜き出したり集計したりする場合、データベースが不可欠です。

データベースと一言で言っても、商用データベースで有名なOracleやSQLServer、無料ながら業務で使われているPostgreSqlやMySqlなど、数多くの製品が存在しますが、ちょっとした用途で使うには向いていません。

そんな時重宝されるのがSQLiteです。

ということで、今回はC#からSQLiteを使うためのインストール方法と使い方について解説したいと思います。

SQLiteの使い方を解説しているサイトはたくさん存在しますが、他のサイトでは解説していない「SQLiteを使うために必要な手順の全体像」と「関数化した機能単位のサンプル」で構成しています。

SQLiteの概要

SQLite はクライアントPCにインストールすることなく、単一のファイルの中に複数のテーブルを保存且つ管理できる、組み込み型のフリーデータベースです。

複数端末から同時にデータを更新することは出来ませんが、標準的なSQLが使えるので、一般的なデータベースと同様の処理を行うことが可能です。

詳しい内容がお知りになりたい方、制限事項や注意点について把握しておきたい方は、こちらの記事に記載しましたので、是非ご一読下さい。

SQLiteのインストール方法

SQLiteを使う場合、NuGetを使ってProjectにインストールする必要があります。

NuGetの使い方については こちら に詳しく記載していますので、必要に応じてご参照下さい。

簡単に説明すると Visual Studio のメニューから「ツール」⇒「NuGetパッケージマネージャ」⇒「ソリューションのNuGetパッケージの管理」で次の画面が表示されますので、sqlte で検索します。

表示された一覧の中から、「Microsoft.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行を追加しておいて下さい。

次に、ConnectString という文字列変数を定義し、接続文字列を設定しておいて下さい。

接続文字列を記述しておけば、そこにデータベースファイルが存在していなくても、データベースにテーブルを保存した段階で、自動的にデータベースファイルが出来上がります。

最後に、ここから紹介する機能別サンプルは全て関数(メソッド)として記述しています。

として、第1引数に接続文字列を必ず指定るようにしています。

これが面倒な場合、全てのサンプルを1つのクラスに入れて、ConnectString をメソッド内で参照するように変更するなどの処置を行って下さい。

データベースファイルの作成

データベースファイルを明示的に作成するには、SQLiteConnection.CreateFile メソッドを使います。

第一引数にファイル名を指定すると、その名前でデータベースファイルが作成されるのですが、既に存在する場合は中身が削除され、0バイトのデータベースファイルが再作成されます。

誤ってCreateFileをしてしまうとデータが丸ごと消えてしまうため、注意が必要です。

任意のSQLを実行

データの更新や削除など、抽出以外のSQLを実行する場合のサンプルです。

using ステートメントを使う事で、全ての処理が終了して時点で SQLiteConnection のClose()メソッドが自動的に呼ばれるため、接続を閉じる処理を明示的に記述する必要がありません。

このため、メソッド全体的がコンパクトにまとまっています。

トランザクション付きSQLの実行

複数のSQLを実行する際、全てのSQLが成功した時のみ、結果をデータベースに反映するトランザクション処理は、次のような記述になります。

SQLの文字列配列を第2引数に渡すことで、それらすべてがトランザクションの傘下に入ります。

トランザクションの機能としては他のデータベースと同じですが、1つだけ異なる点があります。

それは処理速度です。

例えば、何万件もの insert や update 文を実行した場合を考えましょう。

SQLiteでトランザクションを掛けない場合、1件ずつ結果をデータベースファイルに書き込むことになるため、膨大なファイルIO(ファイルオープン⇒更新⇒クローズ)が発生します。

一方、トランザクションを掛けると、ひとまとまりの結果をデータベースファイルに書き込むため、少なくともファイルオープンとクローズのオーバーヘッドが激減します。

実際にはデータベースファイル内にある管理情報の変更など、多くのファイルIOが発生するため、トランザクションを掛ける掛けないでは数百倍くらいの差になってくるのです。

一般的なデータベースだと、単にデータの整合性を保つという目的でしか使いませんが、SQLiteの場合は処理速度をアップさせる目的として必須になるため、その点はご留意ください。

スカラー(単一データ)の取得

スカラー文(答えが1つだけ返されるQuery文)を事項する場合のサンプルになります。

メソッドの戻り値は object 型なので、使う時はデータ型に応じた型変換が必要です。

データの検索

データの検索には ExecuteReader メソッドを使います。

ファイルを読み込むのと同じ感覚で、検索結果をが1行ずつ、列で分解された配列として返されますので、それをListやDataTableに読み込んで使います。

このサンプルでは List<object[]> として返しています。

DataTableを使ったデータの取得

検索結果をDataTableとして返して、一気にDataGridViewやDataGridに表示したい場合、この方法が便利です。

SQLiteDataAdapter というクラスのインスタンスを生成し、Fillメソッドを呼び出すと、Fillメソッドの第一引数に検索結果が返されます。

DataTableを使った更新

DataTableに対してデータの書き換え、行の挿入、削除を行った場合、その操作はDataTableに保存されています。

具体的には、各行(DataRow)のRowStateに、挿入、更新、削除という状態が保存されています。

SQLiteCommandBuilderクラスを使うと、RowStateの情報をもとに、insert文/update文/delete文が自動生成されます。

この自動生成されたSQLは、引数で渡されたSQLiteDataAdapterのインスタンスにセットされるようになっていて、SQLiteDataAdapterのUpdateメソッドを呼び出すことでデータベースが更新できます。

ちなみに、SQLiteCommandBuilder から自動生成される insert、update、delete 文は、次の様に記述することで取得出来ます。

今回はSQL文を自動生成していますが、自分でinsert文、update文、delete文を記述して、SQLを自動生成させることも可能ですが、少し複雑になるので、ここでは割愛します。

さらに、もう1点のポイントがあって、トランザクション処理を行っている所です。

トランザクション処理を行わないと、更新がとんでもなく遅くなりますので、必ず記述するようにしましょう。

テーブルの存在チェック

テーブルの存在チェックは、sqlite_master から指定したテーブル名を検索することで実現できます。

この例ではテーブルとビューのどちらも存在チェックの対象にしています。

テーブルの一覧取得

テーブルの存在チェックと同様に sqlite_master から情報を取得します。

ちなみに、このテーブルにはCreate文も格納されています。

カラム名(列名)の一覧取得

カラム名は、先述したGetDataメソッドを使うことで取得可能です。

この方法は、.NETからアクセスできるデータベース全て共通に利用できます。

SQLite限定であるなら、次のプラグマ(SQLiteへの指示命令)が使用できます。

テーブルの作成

テーブル作成はCreate文を発行します。

Create Table テーブル名(Column1 type1 ,column2 type2, ~) PrivaryKey(column1,column2,~

データタイプ(type) は TEXT、REAL、INTEGER、BLOB の4種類になります。

カラム名(column)には使える文字と使えない文字があります。

例えば、数字から始まったり、+やー等の演算記号、半角の( ) 、insert やdeleteなどの予約語は使えません。

カラム名をダブルクォートで括ると全ての文字が使えるようになりますが、いちいちダブルクォートを付けるのは面倒なので、極力使える文字列でカラム名を命名することをお勧めします。

DataTableから自動的にカラム名を取り出し、Create文を作成するサンプルを以下に示します。

この例だと全ての型がTEXTになります。

DataColumnの型からSQLiteの型に変換するようにすれば、型を指定したCreate文を作成することも可能です。

テーブルの削除

テーブルを削除するには、SQLで Drop Table を実行します。

次はテーブル名を指定することで、Drop Tableを自動生成して実行するサンプルです。

肥大化したデータベースファイルをスリム化する

SQLiteのデータベース内に存在するテーブルやレコードを削除しても、一旦巨大化したデータベースファイルのサイズが小さくなることはありません。

そのため、使われていない領域を解放してデータベースファイルを縮小するための命令が用意されています。

それが VACCUM です。

通常のSQLのと同じ方法で実行すれば、データベースファイルをスリム化することが出来ます。

下記がそのサンプルソースになります。

まとめ

SQLiteの構造や型の解説と、C#からSQLiteに接続する方法、そしてSQLの実行やデータの取得方法など、実用上使われるであろう一通りの機能について、サンプル付きで解説しました。

SQLiteは数十~数百ギガバイトのデータをローカルで扱うことが出来、しかも高速に動作する使いやすいデータベースです。

とんどん活用して行きましょう。

皆さんのプログラミングライフに、この記事がお役に立てれば幸いです。

タイトルとURLをコピーしました