初めに

先日「【C#】C#でSqliteをささっと使いたい時の備忘録」を書きました。

「使い捨てデータ加工」であればこれくらいでよいのですが、
思わぬ発展を見せた時に「SQLベタで書くと間違えるし、補完が聞くわけじゃないイヤだよー。」と嘆きが発生します。

とはいえ重量級ORマッパーの出番ではない・・・という時にSqlKata重宝しています。

SqlKataとは

並んでいるソースを見るだけで、なんとなく
「QueryでDB名指定して、Whereとかに相当する拡張メソッドが定義してありそう・・・。」
な予感がします。

叩いてみたほうが早いということで進めます。

環境

.Net 4.6.2 + C#、コンソールアプリ。

前提

理解するためのサンプルソースということでエラー処理は皆無です。
参考にする場合はエラー処理の配慮を各自お願いします。

SQLについて説明皆無です。

GitHub

まるっと、はこちらをCloneしてください。

ソース全景

ソースコードを表示（折りたたみ）

Program.cs

using System;
using SqlKata.Compilers;
using SqlKata.Execution;
using System.Data.SQLite;

// 実行時に「System.DllNotFoundException: DLL 'SQLite.Interop.dll' を読み込めません:指定されたモジュールが 見つかりません。 (HRESULT からの例外:0x8007007E)」
// が出る場合は
// packages\Stub.System.Data.SQLite.Core.NetFramework.1.0.116.0\build\net46 の中のx64,x86フォルダーを中身(SQLite.Interop.dll)ごと
// ビルドされたbin以下にコピーしてください。

namespace Fu.SqlKataSimpleSample
{
	internal class Program
	{
		// 入れ物的なクラス
		public class IDNameValue
		{
			public int ID { get; set; }
			public string Name { get; set; }
			public int Value { get; set; }

			// 結果出力用
			public override string ToString()
			{
				return $"ID={ID} Name={Name} Value={Value}";
			}
		}

		static void Main(string[] args)
		{
			// SQLiteをオンメモリで作成
			var config = new SQLiteConnectionStringBuilder()
			{
				DataSource = ":memory:",
			};

			using (var conn = new SQLiteConnection(config.ConnectionString))
			{
				// DB Open
				conn.Open();

				// QueryFactory作成
				var compiler = new SqliteCompiler();
				var db = new QueryFactory(conn, compiler);

				// 作成されたクエリを標準出力に出力
				db.Logger = compiled => 
					{
						Console.WriteLine("■発行されたSQL");
						Console.WriteLine("SQL:" + compiled.Sql.ToString());
						Console.WriteLine("Parameters:" + string.Join(",",compiled.Bindings));
					};

				Console.WriteLine("■SQL発行");

				// 素のSQL実行 create発行
				db.Statement("create table IDNameValue(ID integer primary key, Name text, Value integer);");

				// insert単品
				db.Query("IDNameValue").Insert(new {ID = 1, Name = "Hoge1", Value = 101 });
				// insertまとめて
				db.Query("IDNameValue").Insert(new[] {"ID", "Name", "Value" },
														new[] {
															new object[] { 2, "Hoge2", 102},
															new object[] { 3, "Hoge3", 103},
															new object[] { 4, "Hoge4", 104}
														});
				// update
				db.Query("IDNameValue").Where("ID", 4).Update(new { Value = 904 });

				// delete
				db.Query("IDNameValue").Where("ID", "<=", 2).Delete();

				// select
				Console.WriteLine("■最終結果");
				foreach(var idnamevalue in db.Query("IDNameValue").Get<IDNameValue>())
				{
					Console.WriteLine(idnamevalue.ToString());
				}

				// close
				conn.Close();
			}
		}
	}
}

Nuget

SqlKataとSqlKata.Executionを取得します。
DapperをベースにしているのでDapperなども一緒に取得されます。

今回はSQLiteでお試しなのでSystem.Data.SQLite.Coreも取得。

ソース解説

Mainの中で順次実行していきます。

クラス定義
DB作成
接続
QueryFactory作成
作成したクエリを標準出力へ
create table
insert x 1
insert x 複数
update
delete
select

(1)クラス定義

creat文は後程ですが、ID,Name,ValueのあるIDNameValueテーブル用のクラスを作成します。

Program.cs

// 入れ物的なクラス
public class IDNameValue
{
	public int ID { get; set; }
	public string Name { get; set; }
	public int Value { get; set; }

	// 結果出力用
	public override string ToString()
	{
		return $"ID={ID} Name={Name} Value={Value}";
	}
}

(2)DB作成

SqlKataではなくSQLiteのお話ですが、今回はお気軽にメモリ上に作成しましょう。
他のDBを使いたい方はNuGetで使いたいDB用のを取得して書き換えてください。

Program.cs

// SQLiteをオンメモリで作成
var config = new SQLiteConnectionStringBuilder()
{
	DataSource = ":memory:",
};

(3)接続

SQLiteをOpenします。

Program.cs

using (var conn = new SQLiteConnection(config.ConnectionString))
{
	// DB Open
    conn.Open();

(4)QueryFactory作成

ここからSqlKataのお話です。
実際にクエリを作成してくれるQueryFactoryクラスを作成します。

「Sql Server, SQLite, MySql, PostgreSql, Oracle and Firebird.」
用のCompilerがあるそうですが今回はSQLite、他は多分使えるんだろうと信じて今回は試しません。

Program.cs

// QueryFactory作成
var compiler = new SqliteCompiler();
var db = new QueryFactory(conn, compiler);

(5)作成したクエリを標準出力へ

動かす上で必須ではないですが、今回どんなクエリが作成されているかを確認するために標準出力に作成されたクエリを出力します。

Program.cs

// 作成されたクエリを標準出力に出力
db.Logger = compiled => 
	{
		Console.WriteLine("■発行されたSQL");
		Console.WriteLine("SQL:" + compiled.Sql.ToString());
		Console.WriteLine("Parameters:" + string.Join(",",compiled.Bindings));
	};

(6)create table

Statementメソッドで素のSQLを発行します。
まずはcreateでテーブル作成。

Program.cs

// 素のSQL実行 create発行
db.Statement("create table IDNameValue(ID integer primary key, Name text, Value integer);");

(7)insert x 1

ここからSqlKataっぽくなってきます。
Queryでテーブル名を指定、Insertメソッドでinsert文を発行します。

Program.cs

// insert単品
db.Query("IDNameValue").Insert(new {ID = 1, Name = "Hoge1", Value = 101 });

(8)insert x 複数

Queryでテーブル名を指定するのは変わらず、
Insertメソッドにカラム名と複数値を与えてinsert文を作成、発行します。

Program.cs

// insertまとめて
db.Query("IDNameValue").Insert(new[] {"ID", "Name", "Value" },
										new[] {
											new object[] { 2, "Hoge2", 102},
											new object[] { 3, "Hoge3", 103},
											new object[] { 4, "Hoge4", 104}
										});

(9)update

Queryでテーブル名を指定するのは変わらず、
Whereメソッドで「ID = 4」の対象を指定して、Updateメソッドに更新値を与えます。

Program.cs

// update
db.Query("IDNameValue").Where("ID", 4).Update(new { Value = 904 });

(10)delete

Queryでテーブル名を指定するのは変わらず、
Whereメソッドで「ID <= 2」の対象を指定してDeleteを発行します。

Program.cs

// delete
db.Query("IDNameValue").Where("ID", "<=", 2).Delete();

(11)select

Queryでテーブル名を指定するのは変わらず、
Getメソッドに定義したクラスを与えて値を取得します。

Program.cs

// select
foreach(var idnamevalue in db.Query("IDNameValue").Get<IDNameValue>())
{
	Console.WriteLine(idnamevalue.ToString());
}

出力結果

1行insert
3行insert
ID=4の対象をupdate
ID<=2の対象をdelete
select -> 2行出力

その他いろいろやりたい場合

SqlKataの公式ドキュメントです。
英語のドキュメントではありますが、簡素なコード例もあるので読みやすいと思います。

補足

SqlKataではなく今回使ったSQLiteのお話ですが、
GitHubからサンプルをClone～ビルドして「System.DllNotFoundException: DLL 'SQLite.Interop.dll' を読み込めません」と例外が発生した場合は

package以下にSQLite.Interop.dllが入っているx86,x64フォルダーがあるのでexeが作成されるフォルダーにまるっとコピーをお願いします。