Jiifureit.Dapper.OutsideSql 0.10.0

dotnet add package Jiifureit.Dapper.OutsideSql --version 0.10.0
NuGet\Install-Package Jiifureit.Dapper.OutsideSql -Version 0.10.0
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Jiifureit.Dapper.OutsideSql" Version="0.10.0" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Jiifureit.Dapper.OutsideSql --version 0.10.0
#r "nuget: Jiifureit.Dapper.OutsideSql, 0.10.0"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Jiifureit.Dapper.OutsideSql as a Cake Addin
#addin nuget:?package=Jiifureit.Dapper.OutsideSql&version=0.10.0

// Install Jiifureit.Dapper.OutsideSql as a Cake Tool
#tool nuget:?package=Jiifureit.Dapper.OutsideSql&version=0.10.0

Dapper.OutsideSql

Dapper non-public extension for outside sql file.


Overview

Dapper.OutsideSql reads sql statement from text file, formats and passes it to Dapper according to the parameters.
See format examples below. DapperLog is another version Dapper.OutsideSql, that don't read text file.

Description

Dapper.OutsideSql does not extend Dapper. The sql statement passing to Dapper is created from Text file .
Dapper.OutsideSql performs the correspondence charge account of the bind variable of the SQL sentence using comment such as /**/ or --. After having done the correspondence charge account, we can use that sql sentence such as SQL Server Management Studio because of sql comment.You should bury comment for it first if you carry out an sql sentence with the tool of the SQL file and come to output a result just as wanted.

Usage

You can add Dapper, NLog, Microsoft.CodeAnalysis.CSharp.Scripting to your project by NuGet library.
Next, add Dapper.OutsideSql to you project reference.

You can create text files, add to you project.
You should write only one sql sentence to one text file.

SQL statement comments

Sql statement comments follows that of S2Dao.NET.

Bind variable

You can describe bind variable comments in the sql sentence to use the value of the parameters to construct across Dapper in the sql sentence. The bind variable comments and the leterals are replaced with the value of the parameters automatically and are carried out.
The bind variable comments are writed as follows.

/*Parameter name*/literal

Example:

SELECT * FROM emp WHERE empno = /*empno*/7788

IN phrase

The bind variable comments are writed as follows.

IN /*Parameter name*/(..)

In the case of IN phrase, the right side literal (dummy data) of parameter name becomes required. Please describe as follows.

IN /*names*/('aaa','bbb')

C#

var names = new string[]{"SCOTT", "SMITH", "JAMES"};

IF comment

By the IF comment, You can change an sql sentence to carry out depending on a condition. the IF comment is described as follows.

/*IF condition */.../*END*/

Example:

/*IF hoge != null*/hoge = /*hoge*/'abc'/*END*/

As for the IF comment, in the case of the true, a part among /IF/ and /END/ is estimated as condition. In the case of the above, partial (hoge = /hoge/'abc') surrounded by the IF comment is used only when parameter hoge is not null. In addition, ELSE comment is prepared for as processing in case of the false. When a condition becomes false, the part which describing after "ELSE" is used. The ELSE comment is described as follows.

/*IF hoge != null*/hoge = /*hoge*/'abc'
  -- ELSE hoge is null
/*END*/

BEGIN comment

You can use BEGIN comment when you do not want to output WHERE phrase in itself, when all IF comments not to include ELSE comment in the WHERE phrase become false, BEGIN comment is used in conjunction with IF comment as follows.

/*BEGIN*/WHERE phrase /*END*/

Example:

/*BEGIN*/WHERE
  /*IF job != null*/job = /*job*/'CLERK'/*END*/
  /*IF deptno != null*/AND deptno = /*deptno*/20/*END*/
/*END*/

In the case of the above, the WHERE phrase is not output when job, deptno is null.

Example 1

Text File:

select mb.MEMBER_ID
     , mb.MEMBER_NAME
     , mb.BIRTHDATE
     , stat.MEMBER_STATUS_NAME
  from MEMBER mb
    left outer join MEMBER_STATUS stat
      on mb.MEMBER_STATUS_CODE = stat.MEMBER_STATUS_CODE
 /*BEGIN*/
 where
   /*IF memberId != null*/
   mb.MEMBER_ID = /*memberId*/3
   /*END*/
   /*IF memberName != null*/
   and mb.MEMBER_NAME like /*memberName*/'S%' -- // keyword for prefix search
   /*END*/
   /*IF birthdate != null*/
   and mb.BIRTHDATE = /*birthdate*/'1966-09-15' -- // used as equal
   /*END*/
 /*END*/
 order by mb.BIRTHDATE desc, mb.MEMBER_ID asc

C#:

var path = "<text file path>";
var memberList = conn.QueryOutsideSql<Hoge>(path, new { memberId = 1, memberName = "hoge%" });

Example 2

C#:

var path = "<text file path>";
var param = new DynamicParameters();
param.Add("memberId", 1);
param.Add("memberName", "hoge%");
memberList = conn.QueryOutsideSql<Hoge>(path, param);

Log

Dapper.OutsideSql outputs sql which included parameters are replaced to real values, to Log, after reading file. DapperLog also outputs sql to Microsoft.Extensions.Logging. But, DapperLog don't read sql file. To use DapperLog, we can use QueryLog<T>, QueryFirstOrDefaultLog<T>, ExecuteLog, etc.

When Use NLog, Serilog, etc, you can use Log framwork's extension Library, for example Nlog.Extensions.Logging.

Example 3

C#:

var path = "<nlog.config path>";
Jiifureit.Dapper.OutsideSql.Log.Logger.Factory.AddProvider(new NLogLoggerProvider());
Jiifureit.Dapper.OutsideSql.Log.Logger.Factory.AddProvider(new DebugLoggerProvider());
NLog.LogManager.Setup().LoadConfigurationFromFile(path);
var logger = Jiifureit.Dapper.OutsideSql.Log.Logger.CreateLogger<HogeTest>();

var sql = "select EMP.EMPNO EmpNo,EMP.ENAME Enam from EMP where EMPNO >= /*Empno1*/500 and EMPNO <= /*Empno2*/1000";
var memberList = conn.QueryLog<Hoge>(sql, new { Empno1 = 7900, Empno2 = 7940 });

Log:

 DEBUG Jiifureit.Dapper.OutsideSql.DapperLogExtension._LogSql select EMP.EMPNO EmpNo,EMP.ENAME Enam from EMP where EMPNO >= 7900 and EMPNO <= 7940

Example 4

C#:

var path = "<nlog.config path>";
Jiifureit.Dapper.OutsideSql.Log.Logger.Factory.AddProvider(new NLogLoggerProvider());
Jiifureit.Dapper.OutsideSql.Log.Logger.Factory.AddProvider(new DebugLoggerProvider());
NLog.LogManager.Setup().LoadConfigurationFromFile(path);
var logger = Jiifureit.Dapper.OutsideSql.Log.Logger.CreateLogger<HogeTest>();

IDbTransaction tran = conn.BeginTransaction();
var sql = "insert into EMP (EMPNO, ENAME) values (/*EmpNo*/1, /*Ename*/'NM50')";
var param = new[] { new { EmpNo = 100, Ename = "Name1" }, new { DeptNo = 200, Dname = "Name2" } };
var ret = conn.ExecuteLog(sql, param, tran);

Log:

 DEBUG Jiifureit.Dapper.OutsideSql.DapperLogExtension._LogSql insert into EMP (EMPNO, ENAME) values (100, 'Name1')
 DEBUG Jiifureit.Dapper.OutsideSql.DapperLogExtension._LogSql insert into EMP (EMPNO, ENAME) values (200, 'Name2')

DB Providers

  • DB2
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server
  • SQLite (Microsoft.Data.Sqlite and System.Data.SQLite)

are tested successfully.

  • odbc is not tested.

License

Dapper.OutsideSql is licensed under the Apache license. See the LICENSE file for more details.

Thanks, Frameworks

Dapper.OutsideSql forks S2Dao.NET.
Thanks Dapper, Seasar project and DBFlute project.

Author

Hiroaki Fujii

Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 is compatible.  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. 
.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 is compatible.  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. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
0.10.0 146 2/23/2024
0.9.0 8,777 9/24/2022
0.8.0 2,843 3/13/2021
0.7.1 1,537 2/4/2020
0.7.0 492 1/21/2020
0.6.0 592 1/12/2020
0.5.1 939 7/16/2018
0.5.0 863 7/14/2018
0.5.0-beta01 731 7/8/2018
0.4.0 918 7/3/2018
0.4.0-beta04 732 6/27/2018
0.4.0-beta03 787 6/25/2018
0.4.0-beta02 720 6/22/2018
0.4.0-beta01 738 6/19/2018
0.3.0-beta02 740 6/18/2018
0.3.0-beta01 718 6/18/2018

0.10.0           new support for Dapper 2.0.151,
                    new support for Sqlite in both Microsoft.Data.Sqlite and System.Data,
                    upgrade down Microsoft.Extensions.Logging to 2.1.1 due to 2.2 deprecated,
                    add DB2 test class.
0.9.0             new support for Dapper 2.0.123. support for argument of List, Array, etc.
0.8.0             add .NET core 3.1, 5.0 support.
                    new support for Dapper 2.078, but no new features.
0.7.1             gradedown Microsoft,Extensions.Logging to 2.2
0.7.0             add asynchronous methods.
0.6.0             new feature, textfile stream to parameters.
0.5.1             bugfix . initializing error when Logger,Category is null.
0.5.0             release,
                    add class method that returns ILogger<T>.
0.5.0-beta01 changes logging framework  to Microsoft.Extensions.Logging. now, nlog is optional.
0.4.0              first release.
0.4.0-beta04 bugfix.
                    update output to log when arugement parameter is null.
0.4.0-beta03 adds function writng sql to log, replacing parameters to values by using NLog.
0.4.0-beta02 supports DynamicParameters in argument.
0.4.0-beta01 changes from Rosyln to Jint script engine because performance improvements of parsing SQL file .
0.30              first beta release.
                    add .NET framwork 4.7 support.