Syntax

Public Class DSQLParser
    ''' <summary>
    ''' DBコマンドを作成します
    ''' </summary>
    ''' <param name="cn">DBコネクション</param>
    ''' <param name="dsql">DynamicSQL</param>
    ''' <param name="prefix">パラメータ接頭語</param>
    ''' <param name="params">パラメータ値辞書</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function CreateDbCommand(
        cn As IDbConnection, 
        dsql As String, 
        prefix As String, 
        params As Dictionary(Of String, Object)) As IDbCommand

cn

is database connection class, implemented IDbConnection.

dsql

is sql string, written in Dynamic SQL Syntax Comment.

prefix

is parameter prefix string.(ex.sql server = "@", oracle = ":")

params

is parameter value dictionary.

Parameter value dictionary

"key" is parameter name.
"value" is parameter value (item or array).

Item

vb.net
Dim dic As New Dictionary(Of String, Object)
dic.Add("minage", 20)
dic.Add("maxage", 29)
…
Dim cmd As IDbCommand = DSQLParser.CreateDbCommand(cn, sql, "@", dic)

sql
SELECT
	*
FROM
	Employees
WHERE
	/*ds minage*/20 <= Age AND
	Age <= /*ds maxage*/29

runtime sql
SELECT
	*
FROM
	Employees
WHERE
	@minage <= Age AND
	Age <= @maxage
--minage=20, maxage=29

Array

vb.net
Dim dic As New Dictionary(Of String, Object)
dic.Add("age", {20, 21, 22, 30, 31, 32, 40, 41, 42})
…
Dim cmd As IDbCommand = DSQLParser.CreateDbCommand(cn, sql, "@", dic)

sql
SELECT
	*
FROM
	Employees
WHERE
	Age IN /*ds age*/(20,21,22)

runtime sql
SELECT
	*
FROM
	Employees
WHERE
	Age IN (@age_0, @age_1, @age_2, @age_3, @age_4, @age_5, @age_6, @age_7, @age_8)
--age_0=20, age_1=21, age_2=22, age_3=30, age_4=31, age_5=32, age_6=40, age_7=41, age_8=42

Not specified?

Parameter name is required by default.
Please use the Dynamic SQL IF Block If you omit.

vb.net
Dim dic As New Dictionary(Of String, Object)
'Not specifiedDim cmd As IDbCommand = DSQLParser.CreateDbCommand(cn, sql, "@", dic)

Sample1

sql
SELECT
	*
FROM
	Employees
WHERE
	/*ds minage*/20 <= Age AND
	Age <= /*ds maxage*/29

runtime sql (not working)
SELECT
	*
FROM
	Employees
WHERE
	/*ds minage*/20 <= Age AND
	Age <= /*ds maxage*/29

Sample2

sql
SELECT
	*
FROM
	Employees
/*ds begin*/
WHERE
/*ds if minage != null*/
	/*ds minage*/20 <= Age AND
/*ds end if*/
/*ds maxage != null*/
	Age <= /*ds maxage*/29
/*ds end if*/
/*ds end begin*/

runtime sql
SELECT
	*
FROM
	Employees

Last edited Sep 7, 2014 at 2:00 AM by mk3008, version 3