ZoppaDSqlMapper 1.0.3
dotnet add package ZoppaDSqlMapper --version 1.0.3
NuGet\Install-Package ZoppaDSqlMapper -Version 1.0.3
<PackageReference Include="ZoppaDSqlMapper" Version="1.0.3" />
<PackageVersion Include="ZoppaDSqlMapper" Version="1.0.3" />
<PackageReference Include="ZoppaDSqlMapper" />
paket add ZoppaDSqlMapper --version 1.0.3
#r "nuget: ZoppaDSqlMapper, 1.0.3"
#:package ZoppaDSqlMapper@1.0.3
#addin nuget:?package=ZoppaDSqlMapper&version=1.0.3
#tool nuget:?package=ZoppaDSqlMapper&version=1.0.3
説明
これは、ZoppaDSqlを更新した動的にSQLを置き換えるライブラリです。
以下のようにSQLを動的に置き換えます。
Dim answer = "" &
"select * from employees
{trim}
where
{trim both}
{if empNo}emp_no < 20000{/if} and
{trim}
({trim both}{if first_name}first_name like 'A%'{/if} or {if gender}gender = 'F'{/if}{/trim})
{/trim}
{/trim}
{/trim}
limit 10".Replase(New With {.empNo = (i And 1) = 0, .first_name = (i And 2) = 0, .gender = (i And 4) = 0})
SQLを記述した文字列内に {}
で囲まれた文が制御文になります。
メモ ZoppaDSql
では trim
で and
や or
を構文解析して、おかしな構文を自動的に削除していましたが、ZoppaDSqlMapper
では trim
は囲んだ範囲の後文字か前後の文字列(trim both
の場合)を削除するだけになります。
拡張メソッド Replase
にパラメータとなるクラスを引き渡して実行するとクラスのプロパティを読み込み、SQLを動的に構築します。
パラメータは全て False
なので、実行結果は以下のようになります。
select * from employees
limit 10
以下の例は、パラメータのseachId
が 0以外ならば、ArtistId
がseachId
と等しいことという動的SQLを query
変数に格納しました。
var query =
@"select
albumid, title, name
from
albums
inner join artists on
albums.ArtistId = artists.ArtistId
{trim}
where
{if seachId <> 0} albums.ArtistId = @seachId{/if}
{/trim}";
次に、SQLite
のIDbConnection
の実装であるSQLiteConnection
をOpen
した後、ExecuteRecordsSync
拡張メソッドを実行するとSQL
の実行結果がAlbumInfo
クラスのリストで取得できます。
using (var sqlite = new SQLiteConnection("Data Source=chinook.db")) {
sqlite.Open();
var query =
@"select
albumid, title, name
from
albums
inner join artists on
albums.ArtistId = artists.ArtistId
{trim}
where
{if seachId <> 0} albums.ArtistId = @seachId{/if}
{/trim}";
var answer = await sqlite.ExecuteRecordsSync<AlbumInfo>(query, new { seachId = 23 });
// answerにSQLの実行結果が格納されます
}
AlbumInfo
クラスの実装は以下のとおりです。
マッピングは一般的にはプロパティ、フィールドをマッピングしますが、ZoppaDSqlMapper
はSQL
の実行結果の各カラムの型と一致する コンストラクタ を検索してインスタンスを生成します。
// クラスを使用した場合
class AlbumInfo(long albumId, string title, string name)
{
public long AlbumId { get; } = albumId;
public string Title { get; } = title;
public string Name { get; } = name;
}
// recordクラスを使用した場合
record class AlbumInfo(long AlbumId, string Title, string Name);
以上、簡単な説明となります。ライブラリの詳細はGithubのページを参照してください。
更新について
- 1.0.0 初回リリース
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. net9.0 was computed. net9.0-android was computed. net9.0-browser was computed. net9.0-ios was computed. net9.0-maccatalyst was computed. net9.0-macos was computed. net9.0-tvos was computed. net9.0-windows was computed. net10.0 was computed. net10.0-android was computed. net10.0-browser was computed. net10.0-ios was computed. net10.0-maccatalyst was computed. net10.0-macos was computed. net10.0-tvos was computed. net10.0-windows was computed. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.0
- ZoppaDSqlReplace (>= 1.0.2)
- ZoppaLegacyFiles (>= 1.0.0)
- ZoppaLoggingExtensions (>= 1.0.2)
NuGet packages
This package is not used by any NuGet packages.
GitHub repositories
This package is not used by any popular GitHub repositories.