ちょっとしたデータを保存するのであれば、テキストファイルを使うのが手軽で簡単です。
でも、数十~数十ギガバイトのデータから、特定の条件のものを抜き出したり集計したりする場合、データベースが不可欠です。
データベースと一言で言っても、商用データベースで有名なOracleやSQLServer、無料ながら業務で使われているPostgreSqlやMySqlなど、数多くの製品が存在しますが、ちょっとした用途で使うには向いていません。
そんな時重宝されるのがSQLiteです。
ということで、今回はC#からSQLiteを使うためのインストール方法と使い方について解説したいと思います。
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
//接続文字列の作成(Data Source = ファイルのパス) var connectString = @"Data Source = D:\SQLiteTest.DB"; // インスタンスの生成(処理終了後、自動でClose) using (SQLiteConnection connection = new SQLiteConnection(connectString)) { // DBのオープン connection.Open(); // コマンドのインスタンス生成 using (SQLiteCommand cmd = connection.CreateCommand()) { // SQLをコマンドに登録 cmd.CommandText = sql; // コマンドの実行 cmd.ExecuteNonQuery(); } } |
SQLiteの「接続文字列」は次の形式です。
Data Source = ファイル名
例えば、Dドライブのルートに存在するSQLiteTest.DBというデータベースファイルを指定したい場合、次のようになります。
"Data Source = D:\SQLiteTest.DB"
機能別プログラムサンプル
プログラムを実行する場合は、次の2点を記述してください。
まず、冒頭の参照設定(using) に次の1行を追加します。
1 |
using System.Data.SQLite; |
次に、ConnectString という文字列変数を定義します。ファイルのパスは適宜ご自身の環境に置き換えてください。
1 |
string connectString = @"Data Source = D:\SQLiteTest.db"; |
ここから紹介する機能別サンプルは全て関数(メソッド)として記述しています。また、第1引数には必ず接続文字列を指定するようになっています。
データベースファイルの作成
SQLiteのデータベースファイルは、データベースにテーブルを保存した時点で自動的に作成されるため、明示的に作成する必要はありません。
ただし、明示的に作りたい場合や、既に存在するデータベースファイルを一旦0バイトにしたい場合は、CreateFileメソッドを使います。
誤ってCreateFileをしてしまうとデータが丸ごと消えてしまうため、くれぐれも注意が必要です。
1 2 3 4 5 6 7 8 9 10 11 12 |
/// <summary> /// 空のデータベースファイルを作成 /// </summary> /// <param name="connectString"></param> /// <param name="fileName"></param> public void Create(string connectString, string fileName) { using (SQLiteConnection connection = new SQLiteConnection(connectString)) { SQLiteConnection.CreateFile(fileName); } } |
任意の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; } } } |
トランザクションの機能としては他のデータベースと同じですが、1つだけ異なる点があります。
それは処理速度です。
例えば、何万件もの insert や update 文を実行した場合を考えましょう。
SQLiteでトランザクションを掛けない場合、1件ずつ結果をデータベースファイルに書き込むことになるため、膨大なファイルIO(ファイルオープン⇒更新⇒クローズ)が発生します。
一方、トランザクションを掛けると、ひとまとまりの結果をデータベースファイルに書き込むため、少なくともファイルオープンとクローズのオーバーヘッドが激減します。
実際にはデータベースファイル内にある管理情報の変更など、多くのファイルIOが発生するため、トランザクションを掛ける掛けないでは数百倍くらいの差になってくるのです。
一般的なデータベースだと、単にデータの整合性を保つという目的でしか使いませんが、SQLiteの場合は処理速度をアップさせる目的として必須になるため、その点はご留意ください。
スカラー(単一データ)の取得
スカラー値(答えが1つだけ返される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クラスはデータベースとの相性が抜群です。
そして、DataTableは一覧を表示するDataGridView(WindowsForm)やDataGrid(WPF)のようなコントロールとも相性が良いという特徴があります。
つまり、DataGridViewやDataGridに表示している内容を変更し、テーブルに保存したり、また逆に検索結果をDataGridViewやDataGridに表示するという処理が簡単に出来るという事です。
次の図は、今までに説明してきたSQLの実行方法と、DataTableを使った実行方法の比較を表しています。
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 その他の便利機能
テーブルの存在チェック
テーブルの存在チェックは、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への指示命令)が使用できます。
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 |
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 |
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 DBアクセスクラス
これまでに紹介したサンプルソースを合体させて、SQLite DBのアクセスクラスを作ってみました。クラス化に合わせて一部ソースコードを変更しています。
使い方
ネームスペースは CommonClass、クラス名は SQLiteUtil にしています。
このまま使うのであれば、次の参照設定を先頭に記述して下さい。
1 |
using CommonClass; |
では、実際にSQLを実行するサンプルソースを紹介します。
たとえば、商品マスターを全件読み込むのであれば、次の様に記述します。
1 2 3 |
SQLiteUtil sqlite = new SQLiteUtil(".\sqlite.db"); DataTable dt = sqlite .ExecuteReader("select * from 商品マスター"); DataGridView.DataSource = dt; |
1行目はインスタンスの生成ですが、引数に SQLiteのファイル名を指定しています。
2行目はSQLを実行して、結果をDataTableで受け取るメソッドです。
SQLiteに限らずデータベースからデータを受け取る場合はDataTableと相性がいいので、結果をDataTableで返すようにしました。
3行目はDataTableの内容を表示するため、DataViewViewにセットしています。
リファレンス
ネームスペース | CommonClass |
---|---|
クラス名 | CsvUtil |
機能 | SQLiteのデータベースファイルを作成します。 既に存在する場合、中身は削除されます。 |
---|---|
メソッド名 | CreateDatabase() |
戻り値の型 | なし |
機能 | 引数で指定したSQLを実行します。 |
---|---|
メソッド名 | ExecuteNoneQuery(string sql) |
戻り値の型 | なし |
機能 | 複数のSQLを、トランザクション付きで実行します。 |
---|---|
メソッド名 | ExecuteNoneQueryWithTransaction(string[] sqls) |
戻り値の型 | なし |
機能 | SQLを実行し、結果をobject で返します。 SQLは必ず答えを1つだけ返すように記述する必要があります。 |
---|---|
メソッド名 | ExecuteScalar(string sql) |
戻り値の型 | object |
機能 | 問い合わせ文を実行し、結果をDataTable形式で返します。 |
---|---|
メソッド名 | ExecuteReader(string sql) |
戻り値の型 | DataTable |
機能 | 問い合わせ用のSQLを実行し、結果をDataTable形式で返します。 内部でSQLiteDataAdapterというクラスを使っているのがDataReaderとの違いです。 |
---|---|
メソッド名 | GetData(string sql) |
戻り値の型 | DataTable |
機能 | 引数で指定したDataTableの内容で、指定したテーブルを更新します。 レコードの挿入、更新、削除が出来ますが、テーブルが存在しない場合はエラーになります。 |
---|---|
メソッド名 | SetData(string tableName, DataTable dt) |
戻り値の型 | DataTable 引数で渡されたDataTableをそのまま返しています。 |
機能 | 指定したテーブルのカラム名を文字列配列で返します。 |
---|---|
メソッド名 | GetColumnNames(string tableName) |
戻り値の型 | string[] |
機能 | 指定したテーブルのカラム情報をDataTableで返します。 DataTableに返されたカラムと内容は次の通りです。 "cid" 0から始まる連番 "name" カラム名 "type" カラムのデータ型 "notnull" NULLが許可されていないカラムは1 "dflt_value" カラムの初期値 "pk" プライマリキーに設定されている場合は1 |
---|---|
メソッド名 | GetColumnInfo(string tableName) |
戻り値の型 | DataTable |
機能 | 現在登録されているテーブルとビューを文字列配列で返します。 |
---|---|
メソッド名 | GetTableList() |
戻り値の型 | string[] |
機能 | 指定したテーブルが存在すれば true 存在しなければ false を返します。 |
---|---|
メソッド名 | Exists(string tableName) |
戻り値の型 | bool |
機能 | 指定したテーブルが存在すれば削除します。 |
---|---|
メソッド名 | DropTable(string tableName) |
戻り値の型 | なし |
機能 | レコードやテーブルの削除で使われなくなった領域を解放します。 |
---|---|
メソッド名 | Vacuum() |
戻り値の型 | bool |
機能 | テーブルを作成します。 カラム名を指定する方法と、DataTableの中身を解析してテーブルを作成する方法があります。 カラム名、プライマリキーはカンマ区切りで指定して下さい。 例:CreateTable("testdb","item1 integer,item2 real,item3 text","item1,item2) |
---|---|
メソッド名 | CreateTable(string tableName, string fieldList, string primaryKeyList = "") |
戻り値の型 | なし |
機能 | 指定したテーブルに格納された値から、カラム名とデータタイプの一覧を作成します。 |
---|---|
メソッド名 | CreateFieldList(DataTable dt) |
戻り値の型 | string[] |
ソースコード
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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 |
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.IO; using System.Data; using System.Data.SQLite; using System.Windows.Forms; using System.Drawing.Imaging; namespace CommonClass { public class SQLiteUtil { //接続文字列 public string ConnectString { get; set; } //データベースファイルのフルパス public string SQLitePath { get; set; } /// <summary> /// コンストラクタ /// </summary> /// <param name="path"></param> public SQLiteUtil(string path) { SQLitePath = path; ConnectString = "Data Source = " + SQLitePath; } /// <summary> /// DBファイルの作成(既に存在する場合、中身はクリアされる) /// </summary> public void CreateDatabase() { SQLiteConnection.CreateFile(SQLitePath); } /// <summary> /// SQLの実行 /// </summary> /// <param name="sqls"></param> public void ExecuteNoneQuery(string sql) { using (SQLiteConnection connection = new SQLiteConnection(ConnectString)) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } /// <summary> /// スカラーの実行 /// </summary> /// <param name="sql"></param> /// <returns></returns> public object ExecuteScalar(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; } /// <summary> /// SQLの実行 /// </summary> /// <param name="sqls"></param> public void ExecuteNoneQueryWithTransaction(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; } } } /// <summary> /// DataReaderを使ったデータの取得 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable ExecuteReader(string sql) { DataTable dt = new DataTable(); using (SQLiteConnection connection = new SQLiteConnection(ConnectString)) { connection.Open(); using (SQLiteCommand cmd = connection.CreateCommand()) { //SQLの設定 cmd.CommandText = sql; //検索 using (SQLiteDataReader reader = cmd.ExecuteReader()) { create_columns(dt, reader); while (reader.Read()) { object[] data = Enumerable.Range(0, reader.FieldCount).Select(i => reader[i]).ToArray(); dt.Rows.Add(dt.NewRow().ItemArray = data); } } } } return dt; //列名が重複した場合、列名に連番を付加した上でDataColumnを追加 void create_columns(DataTable p_dt, SQLiteDataReader p_reader) { Dictionary<string,int> l_dic = new Dictionary<string,int>(); for(int i = 0;i < p_reader.FieldCount; i ++) { string p_name = p_reader.GetName(i); if (l_dic.ContainsKey(p_name)) { int p_cnt = l_dic[p_name] ++; p_dt.Columns.Add(p_name + p_cnt.ToString()); } else { p_dt.Columns.Add(p_name); l_dic.Add(p_name, 1); } } } } /// <summary> /// DataAdapterを使ったデータの取得 /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataTable GetData(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; } /// <summary> /// DataTableの内容をデータベースに保存 /// </summary> /// <param name="tableName"></param> /// <param name="dt"></param> /// <returns></returns> public DataTable SetData(string tableName, DataTable dt) { using (SQLiteConnection connection = new SQLiteConnection(ConnectString)) { connection.Open(); SQLiteTransaction trans = connection.BeginTransaction(); try { using (SQLiteCommand cmd = connection.CreateCommand()) { cmd.Transaction = trans; //書き込み先テーブルの列名と型を取得するための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; } /// <summary> /// カラム一覧の取得 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public string[] GetColumnNames(string tableName) { var dt = GetData("select * from " + tableName + " where 1=2"); return dt.Columns.Cast<DataColumn>().Select(i => i.ColumnName).ToArray(); } /// <summary> /// カラム情報の取得 /// </summary> /// <param name="tableName"></param> /// <returns></returns> public DataTable GetColumnInfo(string tableName) { return GetData("PRAGMA table_info('" + tableName + "')"); } /// <summary> /// テーブル一覧の取得 /// </summary> /// <returns></returns> public string[] GetTableList() { var dt = GetData("select tbl_name from sqlite_master where type in ('table','view') "); return dt.AsEnumerable().Select(i => i[0].ToString()).ToArray(); } /// <summary> /// テーブル/Viewの存在チェック /// </summary> /// <param name="tableName"></param> /// <returns></returns> public bool Exists(string tableName) { object val = ExecuteScalar("select count(*) from sqlite_master where type in ('table','view') and name='" + tableName + "'"); return (int.Parse(val.ToString()) == 0) ? false : true; } /// <summary> /// テーブルの削除 /// </summary> /// <param name="tableName"></param> public void DropTable(string tableName) { ExecuteNoneQuery("drop table if exists " + tableName); } /// <summary> /// 未使用エリアの開放 /// </summary> public void Vacuum() { ExecuteNoneQuery( "VACUUM"); } /// <summary> /// テーブルの作成 /// </summary> /// <param name="tableName"></param> /// <param name="fieldList"></param> /// <param name="primaryKeyList"></param> public void CreateTable(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(sql); } /// <summary> /// DataTableからカラムと型を推測する /// </summary> /// <param name="dt"></param> /// <returns></returns> public string[] CreateFieldList(DataTable dt) { List<string> fields = new List<string>(); foreach (DataColumn dr in dt.Columns) { int num = dt.AsEnumerable().Count(i => int.TryParse(i[dr.ColumnName].ToString(), out int val1)); int dbl = dt.AsEnumerable().Count(i => double.TryParse(i[dr.ColumnName].ToString(),out double val2)); string type = (num == dt.Rows.Count) ? "INTEGER" : (dbl == dt.Rows.Count) ? "REAL" : "TEXT"; fields.Add(dr.ColumnName + " " + type); } return fields.ToArray(); } } } |
まとめ
SQLiteの構造や型の解説と、C#からSQLiteに接続する方法、そしてSQLの実行やデータの取得方法など、実用上使われるであろう一通りの機能について、サンプル付きで解説しました。
SQLiteは数十~数百ギガバイトのデータをローカルで扱うことが出来、しかも高速に動作する使いやすいデータベースです。
とんどん活用して行きましょう。
皆さんのプログラミングライフに、この記事がお役に立てれば幸いです。