SQLite はコンパクト且つ高速で、標準的なSQLに対応した使い勝手の良いデータベースです。
複数のパソコンから同時アクセスは出来ませんが、1台のPC内で使うには十分すぎるほどの性能を誇っています。
こんなに手軽で便利なSQLiteですが、いざC#から使おうとすると、Oracle、SQLServer、MySQL、PostgreSQLなどの本格的なリレーショナルデータベースと同様に、それなりの手順が必要となります。
DIYプログラミングとしては、「そんなところまで１から作りたくない」というのが本音ですよね。
SQLiteをよりサクッとつかえるようにするために、ひと通りの機能をクラスにまとめましたので、今回はそれについて解説したいと思います。
目次
準備
このクラスを使うには、はじめにSQLite関連のライブラリを NuGet から入手して、プロジェクトにインストールしなければなりません。
簡単に説明すると Visual Studio のメニューから「ツール」⇒「NuGetパッケージマネージャ」⇒「ソリューションのNuGetパッケージの管理」に移動し、「sqlite」と言うキーワードで検索します。
懸隔結果の中に「Microsoft.Data.Sqlite.Core」が表示されると思いますので、これをインストールしてく下さい。
NuGetからの、より具体的なSQLiteのインストール方法は、「【すぐに使える】C#でSQLiteを扱うための解説とサンプル集 | 初心者DIYプログラミング入門 」の記事に記載しています。
また、Nugetが初めての方は、「Visual Studio のパッケージ管理機能 NuGetとは？ | 初心者DIYプログラミング入門」の記事もご一読下さい。
使い方
本記事で紹介するソースコードは、「【すぐに使える】C#でSQLiteを扱うための解説とサンプル集 | 初心者DIYプログラミング入門」の記事に記載しているサンプルを、少し使い勝手を良くして１つのクラスに詰め込んだものになります。
ネームスペースは 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は必ず答えを１つだけ返すように記述する必要があります。 |
|---|---|
| メソッド名 | 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 を簡単に使うために作ったクラスについて、備わっているメソッドの仕様とソースコード全体を掲載しました。
一通りの事は出来ますので、そのままコピペしてお使い頂けますが、あくまでも簡易版です。
ご自身で必要な機能を追加したり、変更してお使いください。
コメント