public override void PrepareUpdateStatement(IDbCommand dbCmd, T item, bool excludeDefaults = false)
{
CopyParamsTo(dbCmd);
var setFields = new StringBuilder();
foreach (var fieldDef in ModelDef.FieldDefinitions)
{
if (UpdateFields.Count > 0 && !UpdateFields.Contains(fieldDef.Name) || fieldDef.AutoIncrement)
continue; // added
var value = fieldDef.GetValue(item);
if (excludeDefaults
&& (value == null || (!fieldDef.IsNullable && value.Equals(value.GetType().GetDefaultValue()))))
continue;
if (setFields.Length > 0)
setFields.Append(", ");
var param = DialectProvider.AddParam(dbCmd, value, fieldDef.ColumnType);
setFields
.Append(DialectProvider.GetQuotedColumnName(fieldDef.FieldName))
.Append("=")
.Append(param.ParameterName);
}
if (setFields.Length == 0)
throw new ArgumentException("No non-null or non-default values were provided for type: " + typeof(T).Name);
dbCmd.CommandText = string.Format("UPDATE {0} SET {1} {2}",
DialectProvider.GetQuotedTableName(ModelDef), setFields, WhereExpression);
}
/// <inheritdoc />
protected override StatementPart VisitParameter(ParameterExpression parameterExpression)
{
var fn = _modelDefinition.FieldDefinitions.First(
x => x.Name.ToLower() == parameterExpression.Name.ToLower());
return(new ColumnAccessPart(DialectProvider.GetQuotedColumnName(parameterExpression.Name), fn.FieldType));
}
private StatementPart VisitSqlMethodCall(MethodCallExpression m)
{
var args = VisitSqlParameters(m.Arguments);
var quotedColName = args.Dequeue().ToString();
string statement;
switch (m.Method.Name)
{
case "As":
statement = $"{quotedColName} As {DialectProvider.GetQuotedColumnName(args.Dequeue().ToString())}";
break;
case "Sum":
case "Count":
case "Min":
case "Max":
case "Avg":
statement =
$"{m.Method.Name}({quotedColName}{(args.Count == 1 ? $",{args.Dequeue()}" : string.Empty)})";
break;
default:
throw new NotSupportedException();
}
return(new StatementPart(statement + " AS " + quotedColName));
}
protected virtual string GetQuotedColumnName(string memberName)
{
var fd = _modelDefinition.FieldDefinitions.FirstOrDefault(x => x.Name.ToLower() == memberName.ToLower());
if (fd == null)
{
throw new OrmException($"Column name '{memberName}' not found in type '{_modelDefinition.Name}'");
}
return(fd.IsComputed ? fd.ComputeExpression : DialectProvider.GetQuotedColumnName(fd.FieldName));
}
/// <summary>
/// Update an entity into table "Ts" and returns number .
/// </summary>
/// <param name="connectionString"></param>
/// <param name="updateOnly"></param>
/// <param name="id"></param>
/// <returns></returns>
public virtual int UpdateOnly(string connectionString, Expression <Func <TEntity> > updateFields, TPrimaryKey id)
{
if (updateFields == null)
{
return(-1);
}
if (string.IsNullOrEmpty(PrimaryFieldName))
{
return(-1);
}
string whereExpression = DialectProvider.GetQuotedColumnName(PrimaryFieldName) + "=@Id";
return(UpdateOnly(connectionString, updateFields, whereExpression, new { Id = id }));
}
/// <summary>
/// Update an entity into table "Ts" and returns number .
/// </summary>
/// <param name="connectionString"></param>
/// <param name="entityToUpdate">Entity to Update</param>
/// <param name="id"></param>
/// <param name="updateFields">需要更新的列</param>
/// <param name="excludeDefaults">是否排除更新默认值</param>
/// <returns></returns>
public virtual int Update(string connectionString, TEntity entityToUpdate, TPrimaryKey id, ICollection <string> updateFields = null, bool excludeDefaults = false)
{
if (entityToUpdate == null)
{
return(-1);
}
if (string.IsNullOrEmpty(PrimaryFieldName))
{
return(-1);
}
string whereExpression = DialectProvider.GetQuotedColumnName(PrimaryFieldName) + "=@Id";
return(Update(connectionString, entityToUpdate, whereExpression, new { Id = id }, updateFields, excludeDefaults));
}
/// <summary>
/// Update an entity into table "Ts" and returns number .
/// </summary>
/// <param name="connectionString"></param>
/// <param name="entityToUpdate">Entity to Update</param>
/// <returns></returns>
public virtual int UpdateNonDefaults(string connectionString, TEntity entityToUpdate, TPrimaryKey id)
{
if (entityToUpdate == null)
{
return(-1);
}
if (string.IsNullOrEmpty(PrimaryFieldName))
{
return(-1);
}
string whereExpression = DialectProvider.GetQuotedColumnName(PrimaryFieldName) + "=@Id";
return(Update(connectionString, entityToUpdate, whereExpression, new { Id = id }, null, true));
}
private string GetQuotedColumnName(string memberName)
{
var fd = _modelDefinition.FieldDefinitions.First(x => x.Name.ToLower() == memberName.ToLower());
var fn = fd?.FieldName ?? memberName;
var operand = fd.IsComputed ? fd.ComputeExpression : DialectProvider.GetQuotedColumnName(fn);
if (_addAliasSpecification && fn != fd.Name)
{
return(operand + " AS " + fd.Name);
}
return(operand);
}
public override string ToString()
{
var text = DialectProvider.GetQuotedColumnName(ColumnName);
if (!string.IsNullOrEmpty(QuotedTableAlias))
{
text = QuotedTableAlias + "." + text;
}
if (!string.IsNullOrEmpty(Alias))
{
text += " AS " + DialectProvider.GetQuotedName(Alias);
}
return(text);
}
public override string ToUpdateStatement(T item, bool excludeDefaults = false)
{
var setFields = new StringBuilder();
foreach (var fieldDef in ModelDef.FieldDefinitions)
{
if (fieldDef.ShouldSkipUpdate())
{
continue;
}
if (fieldDef.IsRowVersion)
{
continue;
}
if (UpdateFields.Count > 0 &&
!UpdateFields.Contains(fieldDef.Name) ||
fieldDef.AutoIncrement)
{
continue; // added
}
var value = fieldDef.GetValue(item);
if (excludeDefaults &&
(value == null || (!fieldDef.IsNullable && value.Equals(value.GetType().GetDefaultValue()))))
{
continue;
}
fieldDef.GetQuotedValue(item, DialectProvider);
if (setFields.Length > 0)
{
setFields.Append(", ");
}
setFields
.Append(DialectProvider.GetQuotedColumnName(fieldDef.FieldName))
.Append("=")
.Append(DialectProvider.GetQuotedValue(value, fieldDef.FieldType));
}
if (setFields.Length == 0)
{
throw new ArgumentException("No non-null or non-default values were provided for type: " + typeof(T).Name);
}
return(string.Format("UPDATE {0} SET {1} {2}",
base.DialectProvider.GetQuotedTableName(ModelDef), setFields, WhereExpression));
}
public override void PrepareUpdateStatement(IDbCommand dbCmd, T item, bool excludeDefaults = false)
{
CopyParamsTo(dbCmd);
var setFields = StringBuilderCache.Allocate();
foreach (var fieldDef in ModelDef.FieldDefinitions)
{
if (UpdateFields.Count > 0 && !UpdateFields.Contains(fieldDef.Name) || fieldDef.AutoIncrement)
{
continue; // added
}
var value = fieldDef.GetValue(item);
if (excludeDefaults &&
(value == null || (!fieldDef.IsNullable && value.Equals(value.GetType().GetDefaultValue()))))
{
continue;
}
if (setFields.Length > 0)
{
setFields.Append(", ");
}
var param = DialectProvider.AddUpdateParam(dbCmd, value, fieldDef);
setFields
.Append(DialectProvider.GetQuotedColumnName(fieldDef.FieldName))
.Append("=")
.Append(param.ParameterName);
}
var strFields = StringBuilderCache.ReturnAndFree(setFields);
if (strFields.Length == 0)
{
throw new ArgumentException("No non-null or non-default values were provided for type: " + typeof(T).Name);
}
dbCmd.CommandText = $"UPDATE {DialectProvider.GetQuotedTableName(ModelDef)} SET {strFields} {WhereExpression}";
}
protected virtual StatementPart VisitSqlMethodCall(MethodCallExpression m)
{
var args = VisitExpressionList(m.Arguments);
switch (m.Method.Name)
{
case "As":
//the columnName has been added as a parameter, we have to remove it;
var asName = Parameters[args[1].ToString()].ToString();
Parameters.Remove(args[1].ToString());
return(new StatementPart($"{args[0]} As {DialectProvider.GetQuotedColumnName(asName)}"));
case "Sum":
case "Count":
case "Min":
case "Max":
case "Avg":
return(new StatementPart($"{m.Method.Name}({args[0]})"));
}
throw new NotSupportedException();
}
public string SelectInto <TModel>()
{
if ((CustomSelect && OnlyFields == null) || (typeof(TModel) == typeof(T) && !PrefixFieldWithTableName))
{
return(ToSelectStatement());
}
useFieldName = true;
var sbSelect = StringBuilderCache.Allocate();
var selectDef = modelDef;
var orderedDefs = tableDefs;
if (typeof(TModel) != typeof(List <object>) &&
typeof(TModel) != typeof(Dictionary <string, object>) &&
typeof(TModel) != typeof(object) && //dynamic
!typeof(TModel).IsValueTuple())
{
selectDef = typeof(TModel).GetModelDefinition();
if (selectDef != modelDef && tableDefs.Contains(selectDef))
{
orderedDefs = tableDefs.ToList(); //clone
orderedDefs.Remove(selectDef);
orderedDefs.Insert(0, selectDef);
}
}
foreach (var fieldDef in selectDef.FieldDefinitions)
{
var found = false;
if (fieldDef.BelongToModelName != null)
{
var tableDef = orderedDefs.FirstOrDefault(x => x.Name == fieldDef.BelongToModelName);
if (tableDef != null)
{
var matchingField = FindWeakMatch(tableDef, fieldDef);
if (matchingField != null)
{
if (OnlyFields == null || OnlyFields.Contains(fieldDef.Name))
{
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
if (fieldDef.CustomSelect == null)
{
if (!fieldDef.IsRowVersion)
{
sbSelect.Append($"{GetQuotedColumnName(tableDef, matchingField.Name)} AS {SqlColumn(fieldDef.Name)}");
}
else
{
sbSelect.Append(DialectProvider.GetRowVersionSelectColumn(fieldDef, DialectProvider.GetTableName(tableDef.ModelName)));
}
}
else
{
sbSelect.Append(fieldDef.CustomSelect + " AS " + fieldDef.FieldName);
}
continue;
}
}
}
}
foreach (var tableDef in orderedDefs)
{
foreach (var tableFieldDef in tableDef.FieldDefinitions)
{
if (tableFieldDef.Name == fieldDef.Name)
{
if (OnlyFields != null && !OnlyFields.Contains(fieldDef.Name))
{
continue;
}
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
var tableAlias = tableDef == modelDef // Use TableAlias if source modelDef
? TableAlias
: null;
if (fieldDef.CustomSelect == null)
{
if (!fieldDef.IsRowVersion)
{
sbSelect.Append(tableAlias == null
? GetQuotedColumnName(tableDef, tableFieldDef.Name)
: GetQuotedColumnName(tableDef, tableAlias, tableFieldDef.Name));
if (tableFieldDef.RequiresAlias)
{
sbSelect.Append(" AS ").Append(SqlColumn(fieldDef.Name));
}
}
else
{
sbSelect.Append(DialectProvider.GetRowVersionSelectColumn(fieldDef, DialectProvider.GetTableName(tableAlias ?? tableDef.ModelName)));
}
}
else
{
sbSelect.Append(tableFieldDef.CustomSelect).Append(" AS ").Append(tableFieldDef.FieldName);
}
found = true;
break;
}
}
if (found)
{
break;
}
}
if (!found)
{
// Add support for auto mapping `{Table}{Field}` convention
foreach (var tableDef in orderedDefs)
{
var matchingField = FindWeakMatch(tableDef, fieldDef);
if (matchingField != null)
{
if (OnlyFields != null && !OnlyFields.Contains(fieldDef.Name))
{
continue;
}
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
var tableAlias = tableDef == modelDef // Use TableAlias if source modelDef
? TableAlias
: null;
sbSelect.Append($"{DialectProvider.GetQuotedColumnName(tableDef, tableAlias, matchingField)} as {SqlColumn(fieldDef.Name)}");
break;
}
}
}
}
var select = StringBuilderCache.ReturnAndFree(sbSelect);
var columns = select.Length > 0 ? select : "*";
SelectExpression = "SELECT " + (selectDistinct ? "DISTINCT " : "") + columns;
return(ToSelectStatement());
}
public void Does_generate_valid_sql_when_param_contains_dollar_char()
{
using (var db = OpenDbConnection())
{
var model = new Poco
{
Id = 1,
Name = "Guest$"
};
var sql = db.ToUpdateStatement(model);
Assert.That(sql, Is.EqualTo($"UPDATE {DialectProvider.GetQuotedTableName("Poco")} SET {DialectProvider.GetQuotedColumnName("Name")}='Guest$' WHERE {DialectProvider.GetQuotedColumnName("Id")}=1"));
}
}
public string SelectInto <TModel>()
{
if ((CustomSelect && OnlyFields == null) || (typeof(TModel) == typeof(T) && !PrefixFieldWithTableName))
{
return(ToSelectStatement());
}
var sbSelect = StringBuilderCache.Allocate();
var selectDef = typeof(TModel).GetModelDefinition();
var orderedDefs = tableDefs;
if (selectDef != modelDef && tableDefs.Contains(selectDef))
{
orderedDefs = tableDefs.ToList(); //clone
orderedDefs.Remove(selectDef);
orderedDefs.Insert(0, selectDef);
}
foreach (var fieldDef in selectDef.FieldDefinitions)
{
var found = false;
if (fieldDef.BelongToModelName != null)
{
var tableDef = orderedDefs.FirstOrDefault(x => x.Name == fieldDef.BelongToModelName);
if (tableDef != null)
{
var matchingField = FindWeakMatch(tableDef, fieldDef);
if (matchingField != null)
{
if (OnlyFields == null || OnlyFields.Contains(fieldDef.Name))
{
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
if (fieldDef.CustomSelect == null)
{
sbSelect.AppendFormat("{0} as {1}",
DialectProvider.GetQuotedColumnName(tableDef, matchingField),
SqlColumn(fieldDef.Name));
}
else
{
sbSelect.Append(fieldDef.CustomSelect + " AS " + fieldDef.FieldName);
}
continue;
}
}
}
}
foreach (var tableDef in orderedDefs)
{
foreach (var tableFieldDef in tableDef.FieldDefinitions)
{
if (tableFieldDef.Name == fieldDef.Name)
{
if (OnlyFields != null && !OnlyFields.Contains(fieldDef.Name))
{
continue;
}
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
if (fieldDef.CustomSelect == null)
{
sbSelect.AppendFormat("{0}.{1}",
SqlTable(tableDef),
tableFieldDef.GetQuotedName(DialectProvider));
if (tableFieldDef.Alias != null)
{
sbSelect.Append(" AS ").Append(SqlColumn(fieldDef.Name));
}
}
else
{
sbSelect.Append(tableFieldDef.CustomSelect + " AS " + tableFieldDef.FieldName);
}
found = true;
break;
}
}
if (found)
{
break;
}
}
if (!found)
{
// Add support for auto mapping `{Table}{Field}` convention
foreach (var tableDef in orderedDefs)
{
var matchingField = FindWeakMatch(tableDef, fieldDef);
if (matchingField != null)
{
if (OnlyFields != null && !OnlyFields.Contains(fieldDef.Name))
{
continue;
}
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
sbSelect.AppendFormat("{0} as {1}",
DialectProvider.GetQuotedColumnName(tableDef, matchingField),
SqlColumn(fieldDef.Name));
break;
}
}
}
}
var select = StringBuilderCache.ReturnAndFree(sbSelect);
var columns = select.Length > 0 ? select : "*";
SelectExpression = "SELECT " + (selectDistinct ? "DISTINCT " : "") + columns;
return(ToSelectStatement());
}
public string SelectInto <TModel>()
{
if (CustomSelect || (typeof(TModel) == typeof(T) && !PrefixFieldWithTableName))
{
return(ToSelectStatement());
}
var sbSelect = new StringBuilder();
var selectDef = typeof(TModel).GetModelDefinition();
var orderedDefs = tableDefs;
if (selectDef != modelDef && tableDefs.Contains(selectDef))
{
orderedDefs = tableDefs.ToList(); //clone
orderedDefs.Remove(selectDef);
orderedDefs.Insert(0, selectDef);
}
foreach (var fieldDef in selectDef.FieldDefinitions)
{
var found = false;
foreach (var tableDef in orderedDefs)
{
foreach (var tableFieldDef in tableDef.FieldDefinitions)
{
if (tableFieldDef.Name == fieldDef.Name)
{
found = true;
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
sbSelect.AppendFormat("{0}.{1}",
SqlTable(tableDef),
tableFieldDef.GetQuotedName(DialectProvider));
if (tableFieldDef.Alias != null)
{
sbSelect.Append(" AS ").Append(SqlColumn(fieldDef.Name));
}
break;
}
}
if (found)
{
break;
}
}
if (!found)
{
// Add support for auto mapping `{Table}{Field}` convention
foreach (var tableDef in orderedDefs)
{
var matchingField = tableDef.FieldDefinitions
.FirstOrDefault(x =>
string.Compare(tableDef.Name + x.Name, fieldDef.Name, StringComparison.OrdinalIgnoreCase) == 0 ||
string.Compare(tableDef.ModelName + x.FieldName, fieldDef.Name, StringComparison.OrdinalIgnoreCase) == 0);
if (matchingField != null)
{
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
sbSelect.AppendFormat("{0} as {1}",
DialectProvider.GetQuotedColumnName(tableDef, matchingField),
SqlColumn(fieldDef.Name));
break;
}
}
}
}
var columns = sbSelect.Length > 0 ? sbSelect.ToString() : "*";
SelectExpression = "SELECT " + (selectDistinct ? "DISTINCT " : "") + columns;
return(ToSelectStatement());
}
protected override object VisitSqlMethodCall(MethodCallExpression m)
{
List <object> args = VisitInSqlExpressionList(m.Arguments);
object quotedColName = args[0];
args.RemoveAt(0);
string statement;
switch (m.Method.Name)
{
case nameof(Sql.In):
statement = ConvertInExpressionToSql(m, quotedColName);
break;
case nameof(Sql.Desc):
statement = $"{quotedColName} DESC";
break;
case nameof(Sql.As):
statement = $"{quotedColName} AS {DialectProvider.GetQuotedColumnName(RemoveQuoteFromAlias(args[0].ToString()))}";
break;
case nameof(Sql.Cast):
statement = DialectProvider.SqlCast(quotedColName, args[0].ToString());
break;
case nameof(Sql.Sum):
case nameof(Sql.Count):
case nameof(Sql.Min):
case nameof(Sql.Max):
case nameof(Sql.Avg):
statement = $"{m.Method.Name}({quotedColName}{(args.Count == 1 ? $",{args[0]}" : "")})";
break;
case nameof(Sql.CountDistinct):
statement = $"COUNT(DISTINCT {quotedColName})";
break;
case nameof(Sql.AllFields):
var argDef = m.Arguments[0].Type.GetModelMetadata();
statement = DialectProvider.GetQuotedTableName(argDef) + ".*";
break;
case nameof(Sql.JoinAlias):
statement = args[0] + "." + quotedColName.ToString().LastRightPart('.');
break;
case nameof(Sql.Custom):
statement = quotedColName.ToString();
break;
case nameof(Sql.IsJson):
statement = $"ISJSON({quotedColName})";
break;
case nameof(Sql.JsonValue):
statement = $"JSON_VALUE({quotedColName}, '{args[0]}')";
break;
case nameof(Sql.JsonQuery):
statement = $"JSON_QUERY({quotedColName}";
if (DialectProvider is SqlServer2017OrmLiteDialectProvider && args.Count > 0)
{
statement += $", '{args[0]}'";
}
statement += ")";
break;
default:
throw new NotSupportedException();
}
return(new PartialSqlString(statement));
}
public string SelectInto <TModel>()
{
if (typeof(TModel) == typeof(T) && !PrefixFieldWithTableName)
{
return(ToSelectStatement());
}
var sbSelect = new StringBuilder();
var selectDef = typeof(TModel).GetModelDefinition();
foreach (var fieldDef in selectDef.FieldDefinitions)
{
var found = false;
foreach (var tableDef in tableDefs)
{
foreach (var tableFieldDef in tableDef.FieldDefinitions)
{
if (tableFieldDef.Name == fieldDef.Name)
{
found = true;
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
sbSelect.AppendFormat("{0}.{1}",
SqlTable(tableDef.ModelName),
tableFieldDef.GetQuotedName(DialectProvider));
break;
}
}
if (found)
{
break;
}
}
if (!found)
{
// Add support for auto mapping `{Table}{Field}` convention
foreach (var tableDef in tableDefs)
{
var matchingField = tableDef.FieldDefinitions
.FirstOrDefault(x =>
string.Compare(tableDef.Name + x.Name, fieldDef.Name, StringComparison.OrdinalIgnoreCase) == 0 ||
string.Compare(tableDef.ModelName + x.FieldName, fieldDef.Name, StringComparison.OrdinalIgnoreCase) == 0);
if (matchingField != null)
{
if (sbSelect.Length > 0)
{
sbSelect.Append(", ");
}
sbSelect.AppendFormat("{0} as {1}",
DialectProvider.GetQuotedColumnName(tableDef, matchingField),
fieldDef.Name);
break;
}
}
}
}
var columns = sbSelect.Length > 0 ? sbSelect.ToString() : "*";
SelectExpression = "SELECT " + columns;
return(ToSelectStatement());
}