【C#】SQLite用のアクセスクラスを部品化しました！

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のインストール方法は、こちらの記事に記載しています。

また、Nugetが初めての方は、こちらの記事もご一読下さい。

使い方

基本的に、こちらの記事に記載しているサンプルを、少し使い勝手を良くして１つのクラスに詰め込んだものになります。

ネームスペースは CommonClass、クラス名は SQLiteUtil にしています。

このまま使うのであれば、次の参照設定を先頭に記述して下さい。

using CommonClass;

1	using CommonClass;

では、実際にSQLを実行するサンプルソースを紹介します。

たとえば、商品マスターを全件読み込むのであれば、次の様に記述します。

SQLiteUtil sqlite = new SQLiteUtil(".\sqlite.db");
DataTable dt = sqlite .ExecuteReader("select * from 商品マスター");
DataGridView.DataSource = dt;

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 sqls)
戻り値の型	なし

機能	複数の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[]

ソースコード

以下が全体のソースコードになります。

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();
        } 
    }
}

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 を簡単に使うために作ったクラスについて、備わっているメソッドの仕様とソースコード全体を掲載しました。

一通りの事は出来ますので、そのままコピペしてお使い頂けますが、あくまでも簡易版です。

ご自身で必要な機能を追加したり、変更してお使いください。