private string InternalCreateSqlFromDefinitions(ModelDefinition sourceDef, ModelDefinition targetDef, bool isCrossJoin)
{
var parentDef = sourceDef;
var childDef = targetDef;
var refField = parentDef.GetRefFieldDefIfExists(childDef);
if (refField == null)
{
parentDef = targetDef;
childDef = sourceDef;
refField = parentDef.GetRefFieldDefIfExists(childDef);
}
if (refField == null)
{
if (!isCrossJoin)
{
throw new ArgumentException("Could not infer relationship between {0} and {1}".Fmt(sourceDef.ModelName, targetDef.ModelName));
}
return(string.Empty);
}
return("{0}\n({1}.{2} = {3}.{4})".Fmt(
isCrossJoin ? "WHERE" : "ON",
DialectProvider.GetQuotedTableName(parentDef),
SqlColumn(parentDef.PrimaryKey.FieldName),
DialectProvider.GetQuotedTableName(childDef),
SqlColumn(refField.FieldName)));
}
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);
}
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 async Task Can_updated_with_ExecuteSql_and_db_params_Async()
{
using var db = await OpenDbConnectionAsync();
db.DropAndCreateTable <PocoUpdateAsync>();
db.Insert(new PocoUpdateAsync {
Id = 1, Name = "A"
});
db.Insert(new PocoUpdateAsync {
Id = 2, Name = "B"
});
var result = await db.ExecuteSqlAsync($"UPDATE {DialectProvider.GetQuotedTableName("PocoUpdateAsync")} SET name = @name WHERE id = @id", new { id = 2, name = "UPDATED" });
Assert.That(result, Is.EqualTo(1));
var row = await db.SingleByIdAsync <PocoUpdateAsync>(2);
Assert.That(row.Name, Is.EqualTo("UPDATED"));
}
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 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 override string ToDeleteRowStatement()
{
return(base.tableDefs.Count > 1
? $"DELETE {DialectProvider.GetQuotedTableName(modelDef)} {FromExpression} {WhereExpression}"
: base.ToDeleteRowStatement());
}
private SqlExpression <T> InternalJoin(string joinType,
Expression joinExpr, ModelDefinition sourceDef, ModelDefinition targetDef)
{
PrefixFieldWithTableName = true;
var fromExpr = FromExpression;
var sbJoin = new StringBuilder();
string sqlExpr;
//Changes how Sql Expressions are generated.
useFieldName = true;
sep = " ";
if (joinExpr != null)
{
sqlExpr = Visit(joinExpr).ToString();
}
else
{
var parentDef = sourceDef;
var childDef = targetDef;
var refField = OrmLiteReadExtensions.GetRefFieldDefIfExists(parentDef, childDef);
if (refField == null)
{
parentDef = targetDef;
childDef = sourceDef;
refField = OrmLiteReadExtensions.GetRefFieldDefIfExists(parentDef, childDef);
}
if (refField == null)
{
throw new ArgumentException("Could not infer relationship between {0} and {1}"
.Fmt(sourceDef.ModelName, targetDef.ModelName));
}
sqlExpr = "\n({0}.{1} = {2}.{3})".Fmt(
DialectProvider.GetQuotedTableName(parentDef),
SqlColumn(parentDef.PrimaryKey.FieldName),
DialectProvider.GetQuotedTableName(childDef),
SqlColumn(refField.FieldName));
}
var joinDef = tableDefs.Contains(targetDef) && !tableDefs.Contains(sourceDef)
? sourceDef
: targetDef;
sbJoin.Append(" {0} {1} ".Fmt(joinType, SqlTable(joinDef)));
sbJoin.Append(" ON ");
sbJoin.Append(sqlExpr);
FromExpression = fromExpr + sbJoin;
if (!tableDefs.Contains(sourceDef))
{
tableDefs.Add(sourceDef);
}
if (!tableDefs.Contains(targetDef))
{
tableDefs.Add(targetDef);
}
return(this);
}