開発時、パラメーター設定を楽にしたい。。。
簡単な検証したが、
実用性・複雑なものに頼られるかどうか要検証
SqlClause
/* 開発時、パラメーター格納処理
*
* ※ 検索値の型が自動判定され、CommandのDbTypeに設定する。（日付型使用は要注意）
* ※ GetSql取得する前、AddReplaceConditionに設定されたものが先に実行される
*
*
* ADD（name,val）メソッド:SQLに設定されるパラメーター名・検索値
* name：パラメーター名。
* 使用説明、先頭に”:”が必要です（:name）
* 同じ名で追加された場合、取得時に追加されたパラメーター名+C連番でパラメーター名を置き換える
* （:name→:nameC001、:nameC002...）、使用タイミングはIN句
*
* val：検索値。
* IN句、追加された値かずで上記で作成したパラメーター名に応じて設定する
* LIKE句、あいまい検索する場合、”%”を検索値に追加すること
*
*
* AddReplaceCondition(oldStr, newStr)メソッド:SQLに設定された文字を置き換える
*
* GetSql(sql)メソッド:ベースSQLを実行時SQLに変更し、取得する
*
* GetParameters()メソッド:整理したパラメーター名・検索値リスト取得
*/
/// <summary>
/// SQLパラメーター格納（storage）
/// </summary>
public class SqlClause
{
/// <summary>
///
/// </summary>
private StringBuilder BaseSql { get; set; } = new StringBuilder();
/// <summary>
/// パラメーターリスト（ユーザー用）
/// </summary>
private IDictionary<string, object> storagelist = new Dictionary<string, object>();
/// <summary>
/// LINK JOIN文字
/// </summary>
private string joinmark = @",";
/// <summary>
/// コンストラクタ
/// </summary>
public SqlParameterClause(string sql)
{
BaseSql.Append(sql);
}
/// <summary>
/// リストタイプ判定
/// </summary>
private bool IsList(object obj)
{
if (obj == null)
return false;
if (obj.GetType().Name.IndexOf("List`") < 0) // if(Value.GetType() == typeof(List<>)) がうまく判定できず
return false;
return true;
}
/// <summary>
/// パラメーター追加
/// </summary>
/// <param name="name">パラメーター名</param>
/// <param name="val">値</param>
public void Add(string name, object val)
{
if (storagelist.ContainsKey(name))
{
var objitem = storagelist[name];
if (IsList(objitem))
((IList<object>)objitem).Add(val);
else
{
var temp = new List<object>
{
objitem,
val
};
storagelist[name] = temp;
}
}
else
storagelist.Add(name, val);
}
/// <summary>
/// パラメーター除去
/// </summary>
/// <param name="name">パラメーター名</param>
public void Remove(string name)
{
if (storagelist.ContainsKey(name))
storagelist.Remove(name);
}
/// <summary>
/// Has
/// </summary>
public bool Has()
{
if (storagelist.Count() == 0)
return false;
return true;
}
/// <summary>
/// パラメーター名置換リスト（自動作成用）
/// </summary>
private readonly IDictionary<string, string> replacenames = new Dictionary<string, string>();
/// <summary>
/// パラメーターリスト（自動作成用）
/// </summary>
private readonly IDictionary<string, object> parameters = new Dictionary<string, object>();
/// <summary>
/// カスタム置換設定
/// </summary>
public void AddReplaceCondition(string oldStr, string newStr)
{
replacenames.Add(oldStr, newStr);
}
/// <summary>
/// ユーザー追加したパラメーターを処理する
/// </summary>
private void Arrange()
{
if (parameters.Count() > 0)
return;
// 同じキーで複数な値がある場合、キーの連番で作成
foreach (var item in storagelist)
{
if (IsList(item.Value))
{
var i = 0;
var pn = new StringBuilder();
foreach (var e in (IList<object>)item.Value)
{
var key = string.Format(item.Key + "C{0:000}", i);
if (i > 0)
pn.Append(joinmark);
pn.Append(key);
parameters.Add(key, e);
i++;
}
replacenames.Add(item.Key, pn.ToString());
}
else
parameters.Add(item.Key, item.Value);
}
}
/// <summary>
/// SQL追加
/// </summary>
public void BaseSqlAppendLine(string sql)
{
BaseSql.AppendLine(sql);
}
/// <summary>
/// 実行用SQL
/// </summary>
public string GetSql()
{
Arrange();
var result = BaseSql.ToString();
foreach (var e in replacenames)
{
result = result.Replace(e.Key, e.Value);
}
return result;
}
/// <summary>
/// 実行用パラメーター
/// </summary>
public IDictionary<string, object> GetParameters()
{
Arrange();
return parameters;
}
/// <summary>
/// クリア
/// </summary>
public void Clear()
{
storagelist.Clear();
replacenames.Clear();
parameters.Clear();
BaseSql.Clear();
}
}
簡単なテスト例
設定：
public void Execute()
{
var sql = @"
SELECT COUNT(*)
FROM TESTTABLE
WHERE
ITEM IN (:ITEM)
/*CODE*/
AND (NAME IS NULL
OR NAME IN (:NAME))
";
var pars = new SqlClause(sql);
pars.Add(":ITEM", "001");
pars.Add(":ITEM", "002");
pars.Add(":ITEM", "003");
pars.Add(":ITEM", "004");
pars.Add(":ITEM", "005");
pars.Add(":NAME", "N0003");
pars.Add(":NAME", "N0005");
pars.Add(":CODE", "C0003");
pars.Add(":CODE", "C0001");
pars.AddReplaceCondition("/*CODE*/", "AND CODE IN (:CODE)");
var result = GetResult(pars);
Console.WriteLine(result.ToString());
}
パラメーター設定
private object GetResult(SqlClause @params)
{
OracleConnection con = new OracleConnection("connectionString");
con.Open();
object result = null;
OracleCommand cmd = new OracleCommand("dummy-sql",con)
{
CommandText = @params.GetSql(),
BindByName = true
};
foreach (var e in @params.GetParameters())
{
var prm = cmd.CreateParameter();
prm.ParameterName = e.Key;
prm.DbType = GetDbType(e.Value);
prm.Value = e.Value ?? DBNull.Value;
cmd.Parameters.Add(prm);
}
result = cmd.ExecuteScalar();
return result;
}
GetDbType
public static DbType GetDbType(object value)
{
DbType result;
if ((value == null))
result = DbType.String;
else if ((value.GetType() == typeof(string)))
result = DbType.String;
else if ((value.GetType() == typeof(Int32)))
result = DbType.Int32;
else if ((value.GetType() == typeof(Int32?)))
result = DbType.Int32;
else if ((value.GetType() == typeof(Int64)))
result = DbType.Int64;
else if ((value.GetType() == typeof(Int64?)))
result = DbType.Int64;
else if ((value.GetType() == typeof(DateTime)))
result = DbType.DateTime;
else if ((value.GetType() == typeof(DateTime?)))
result = DbType.DateTime;
else if ((value.GetType() == typeof(decimal)))
result = DbType.Decimal;
else if ((value.GetType() == typeof(decimal?)))
result = DbType.Decimal;
else
{
var type = value.GetType();
throw new Exception(string.Format("対応されていない型 {0} があります。", type));
}
return result;
}