/// <summary>
/// 指定された型情報から、対象となるテーブルのレコードを指定されたプロパティにマッピングされている列に絞って更新するクエリを生成します。
/// </summary>
/// <param name="targetDatabase">対象データベース</param>
/// <param name="type">テーブルの型</param>
/// <param name="propertyNames">プロパティ名のコレクション。指定がない場合はすべての列を抽出対象とします。</param>
/// <param name="setIdentity">自動採番のID列に値を設定するかどうか</param>
/// <returns>生成されたSQL</returns>
public static string CreateUpdate(DbKind targetDatabase, Type type, IEnumerable <string> propertyNames = null, bool setIdentity = false)
{
if (type == null)
{
throw new ArgumentNullException(nameof(type));
}
if (propertyNames == null)
{
propertyNames = Enumerable.Empty <string>();
}
var prefix = targetDatabase.GetBindParameterPrefix();
var table = TableMappingInfo.Create(type);
var columns = table.Columns.Where(x => setIdentity ? true : !x.IsAutoIncrement);
if (propertyNames.Any())
{
columns = columns.Join(propertyNames, x => x.PropertyName, y => y, (x, y) => x);
}
var setters = columns.Select(x => $" {x.ColumnName} = {prefix}{x.PropertyName}");
var builder = new StringBuilder();
builder.AppendLine($"update {table.FullName}");
builder.AppendLine("set");
builder.Append(string.Join($",{Environment.NewLine}", setters));
return(builder.ToString());
}
/// <summary>
/// 指定された型情報から対象となるテーブルにレコードを挿入するクエリを生成します。
/// </summary>
/// <param name="targetDatabase">対象データベース</param>
/// <param name="type">テーブルの型</param>
/// <param name="useSequence">シーケンスを利用するかどうか</param>
/// <param name="setIdentity">自動採番のID列に値を設定するかどうか</param>
/// <returns>生成されたSQL</returns>
public static string CreateInsert(DbKind targetDatabase, Type type, bool useSequence = true, bool setIdentity = false)
{
if (type == null)
{
throw new ArgumentNullException(nameof(type));
}
var prefix = targetDatabase.GetBindParameterPrefix();
var table = TableMappingInfo.Create(type);
var columns = table.Columns.Where(x => setIdentity ? true : !x.IsAutoIncrement);
var values = columns.Select(x =>
{
if (useSequence)
{
if (x.Sequence != null)
{
switch (targetDatabase)
{
case DbKind.SqlServer: return($"next value for {x.Sequence.FullName}");
case DbKind.Oracle: return($"{x.Sequence.FullName}.nextval");
case DbKind.PostgreSql: return($"nextval('{x.Sequence.FullName}')");
}
}
}
return($"{prefix}{x.PropertyName}");
})
.Select(x => " " + x);
var columnNames = columns.Select(x => " " + x.ColumnName);
var builder = new StringBuilder();
builder.AppendLine($"insert into {table.FullName}");
builder.AppendLine("(");
builder.AppendLine(string.Join($",{Environment.NewLine}", columnNames));
builder.AppendLine(")");
builder.AppendLine("values");
builder.AppendLine("(");
builder.AppendLine(string.Join($",{Environment.NewLine}", values));
builder.Append(")");
return(builder.ToString());
}
/// <summary>
/// 条件を表す式からSQLを生成します
/// </summary>
/// <typeparam name="T">テーブルの型</typeparam>
/// <param name="targetDatabase">対象となるデータベース</param>
/// <param name="predicate">条件式</param>
/// <returns>条件SQL</returns>
public static This From <T>(DbKind targetDatabase, Expression <Func <T, bool> > predicate)
{
//--- 解析実行
var root = PredicateParser.Parse(predicate);
//--- SQL文 / パラメーター生成の定義
uint index = 0;
var columnMap = TableMappingInfo.Create <T>().Columns.ToDictionary(x => x.PropertyName);
var prefix = targetDatabase.GetBindParameterPrefix();
var parameterCount = root.DescendantsAndSelf().Count(x =>
{
return(x.Operator != PredicateOperator.AndAlso &&
x.Operator != PredicateOperator.OrElse &&
x.Value != null);
});
var digit = (parameterCount - 1).ToString().Length;
var digitFormat = $"D{digit}";
IDictionary <string, object> parameter = new ExpandoObject();
Func <PredicateElement, string> sqlBuilder = null;
sqlBuilder = element =>
{
if (element.HasChildren)
{
var left = sqlBuilder(element.Left);
var right = sqlBuilder(element.Right);
if (element.Operator != element.Left.Operator && element.Left.HasChildren)
{
left = $"({left})";
}
if (element.Operator != element.Right.Operator && element.Right.HasChildren)
{
right = $"({right})";
}
if (element.Operator == PredicateOperator.AndAlso)
{
return($"{left} and {right}");
}
if (element.Operator == PredicateOperator.OrElse)
{
return($"{left} or {right}");
}
throw new InvalidOperationException();
}
else
{
var builder = new StringBuilder();
builder.Append(columnMap[element.PropertyName].ColumnName);
switch (element.Operator)
{
case PredicateOperator.Equal:
if (element.Value == null)
{
builder.Append(" is null");
return(builder.ToString());
}
builder.Append(" = ");
break;
case PredicateOperator.NotEqual:
if (element.Value == null)
{
builder.Append(" is not null");
return(builder.ToString());
}
builder.Append(" <> ");
break;
case PredicateOperator.LessThan: builder.Append(" < "); break;
case PredicateOperator.LessThanOrEqual: builder.Append(" <= "); break;
case PredicateOperator.GreaterThan: builder.Append(" > "); break;
case PredicateOperator.GreaterThanOrEqual: builder.Append(" >= "); break;
case PredicateOperator.Contains: builder.Append(" in "); break;
default: throw new InvalidOperationException();
}
var parameterName = $"p{index.ToString(digitFormat)}";
++index;
parameter.Add(parameterName, element.Value); //--- cache parameter
builder.Append($"{prefix}{parameterName}");
return(builder.ToString());
}
};
//--- 組み立て
return(new This(sqlBuilder(root), parameter as ExpandoObject));
}