public async Task <int> UpdateAsync <T, TKey>(T model, Expression <Func <T, TKey> > onlyFields)
{
var ev = DialectProvider.ExpressionVisitor <T>();
ev.Update(onlyFields);
return(await UpdateAsync(model, ev));
}
public override string ToQuotedString(Type fieldType, object value)
{
var isEnumAsInt = fieldType.HasAttribute <EnumAsIntAttribute>();
if (isEnumAsInt)
{
return(this.ConvertNumber(Enum.GetUnderlyingType(fieldType), value).ToString());
}
var isEnumFlags = fieldType.IsEnumFlags() ||
(!fieldType.IsEnum && fieldType.IsNumericType()); //i.e. is real int && not Enum
if (!isEnumFlags && long.TryParse(value.ToString(), out var enumValue))
{
value = Enum.ToObject(fieldType, enumValue);
}
var enumString = DialectProvider.StringSerializer.SerializeToString(value);
if (enumString == null || enumString == "null")
{
enumString = value.ToString();
}
return(!isEnumFlags
? DialectProvider.GetQuotedValue(enumString.Trim('"'))
: enumString);
}
public override string ToQuotedString(Type fieldType, object value)
{
var dateTime = (DateTime)value;
if (DateStyle == DateTimeKind.Unspecified)
{
dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Local);
}
else if (DateStyle == DateTimeKind.Local && dateTime.Kind == DateTimeKind.Unspecified)
{
dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc).ToLocalTime();
}
else if (DateStyle == DateTimeKind.Utc)
{
dateTime = dateTime.Kind == DateTimeKind.Local
? DateTime.SpecifyKind(dateTime, DateTimeKind.Local).ToUniversalTime()
: DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
return(DialectProvider.GetQuotedValue(dateTime.ToString("yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), typeof(string)));
}
var dateStr = DateTimeSerializer.ToLocalXsdDateTimeString(dateTime);
dateStr = dateStr.Replace("T", " ");
const int tzPos = 6; //"-00:00".Length;
var timeZoneMod = dateStr.Substring(dateStr.Length - tzPos, 1);
if (timeZoneMod == "+" || timeZoneMod == "-")
{
dateStr = dateStr.Substring(0, dateStr.Length - tzPos);
}
return(DialectProvider.GetQuotedValue(dateStr, typeof(string)));
}
/// <summary>
/// Execute a Delete statement using the specified where clause
/// </summary>
/// <param name="where">the where statement action</param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task <int> DeleteAllAsync <T>(Action <TypedWhereStatement <T> > where)
{
var s = new TypedDeleteStatement <T>(DialectProvider);
where (s);
return(await this.ExecuteScalarAsync <int>(DialectProvider.ToDeleteStatement(s.Statement)));
}
protected virtual SqlExpression <T> InternalJoin(string joinType, Expression joinExpr, ModelDefinition sourceDef, ModelDefinition targetDef, TableOptions options = null)
{
PrefixFieldWithTableName = true;
Reset();
var joinFormat = options?.JoinFormat;
if (options?.Alias != null) //Set joinAlias
{
options.ParamName = joinExpr is LambdaExpression l && l.Parameters.Count == 2
? l.Parameters[1].Name
: null;
if (options.ParamName != null)
{
joinFormat = null;
options.ModelDef = targetDef;
joinAlias = options;
}
}
if (!tableDefs.Contains(sourceDef))
{
tableDefs.Add(sourceDef);
}
if (!tableDefs.Contains(targetDef))
{
tableDefs.Add(targetDef);
}
var isCrossJoin = "CROSS JOIN" == joinType;
var sqlExpr = joinExpr != null
? InternalCreateSqlFromExpression(joinExpr, isCrossJoin)
: InternalCreateSqlFromDefinitions(sourceDef, targetDef, isCrossJoin);
var joinDef = tableDefs.Contains(targetDef) && !tableDefs.Contains(sourceDef)
? sourceDef
: targetDef;
FromExpression += joinFormat != null
? $" {joinType} {joinFormat(DialectProvider, joinDef, sqlExpr)}"
: joinAlias != null
? $" {joinType} {SqlTable(joinDef)} AS {DialectProvider.GetQuotedName(joinAlias.Alias)} {sqlExpr}"
: $" {joinType} {SqlTable(joinDef)} {sqlExpr}";
if (joinAlias != null) //Unset joinAlias
{
joinAlias = null;
if (options != null)
{
options.ParamName = null;
options.ModelDef = null;
}
}
return(this);
}
public Task <IEnumerable <T> > SelectAsync <T>(Expression <Func <T, bool> > predicate)
{
var expression = DialectProvider.SqlExpression <T>();
string sql = expression.Where(predicate).SelectClause();
return(Connection.QueryAsync <T>(sql, expression.Params));
}
public Task <TKey> ScalarAsync <T, TKey>(Expression <Func <T, object> > field, Expression <Func <T, bool> > predicate)
{
var expression = DialectProvider.SqlExpression <T>();
string sql = expression.Where(predicate).Select(field).SelectClause();
return(Connection.ExecuteScalarAsync <TKey>(sql, expression.Params));
}
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);
}
/// <summary>An OrmConnection method that gets a scalar.</summary>
/// <typeparam name="T"> Generic type parameter.</typeparam>
/// <typeparam name="TKey">Type of the key.</typeparam>
/// <param name="field"> The field.</param>
/// <param name="predicate">The predicate.</param>
/// <returns>The scalar.</returns>
public TKey GetScalar <T, TKey>(Expression <Func <T, TKey> > field, Expression <Func <T, bool> > predicate)
{
//int maxAgeUnder50 = db.Scalar<Person, int>(x => Sql.Max(x.Age), x => x.Age < 50);
var ev = DialectProvider.ExpressionVisitor <T>();
return(this.ExecuteScalar <TKey>(DialectProvider.ToSelectStatement(ev.Where(predicate).Select(field), CommandFlags.None)));
}
public void Does_add_multiple_column_unique_constraint_with_custom_name()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <UniqueTest3>();
var createSql = DialectProvider.ToCreateTableStatement(typeof(UniqueTest3));
Assert.That(createSql.ToUpper(), Does.Contain("CONSTRAINT UC_CUSTOM UNIQUE"));
db.Insert(new UniqueTest3 {
Field4 = "A", Field5 = "A", Field6 = "A"
});
db.Insert(new UniqueTest3 {
Field4 = "A", Field5 = "A", Field6 = "B"
});
try
{
db.Insert(new UniqueTest3 {
Field4 = "A", Field5 = "A", Field6 = "B"
});
Assert.Fail("Should throw");
}
catch (Exception ex)
{
ex.Message.Print();
Assert.That(ex.Message.ToLower().Contains("unique") || ex.Message.ToLower().Contains("duplicate"));
}
}
}
public void Can_select_NotesDto_with_pretty_sql()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <Note>();
var id = db.Insert(new Note
{
SchemaUri = "tcm:0-0-0",
NoteText = "Hello world 5",
LastUpdated = new DateTime(2013, 1, 5),
UpdatedBy = "RC"
}, selectIdentity: true);
var sql = @"
SELECT
Id, {0}, {1}
FROM {2}
WHERE {0}={3}
".Fmt("SchemaUri".SqlColumn(DialectProvider), "NoteText".SqlColumn(DialectProvider), "Note".SqlTable(DialectProvider),
DialectProvider.GetParam("schemaUri"));
var notes = db.Select <NoteDto>(sql, new { schemaUri = "tcm:0-0-0" });
Assert.That(notes[0].Id, Is.EqualTo(id));
Assert.That(notes[0].NoteText, Is.EqualTo("Hello world 5"));
}
}
/// <summary>An OrmConnection method that first or default.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="predicate">The predicate.</param>
/// <returns>A T.</returns>
public async Task <T> FirstOrDefaultAsync <T>(Expression <Func <T, bool> > predicate)
{
var ev = DialectProvider.ExpressionVisitor <T>();
var r = await SelectAsync(ev.Where(predicate).Limit(1));
return(r.FirstOrDefault());
}
/// <summary>An OrmConnection method that inserts an only.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="obj"> The object.</param>
/// <param name="onlyFields">The only fields.</param>
public async Task <int> InsertOnlyAsync <T>(T obj, SqlExpressionVisitor <T> onlyFields) where T : new()
{
var ev = DialectProvider.ExpressionVisitor <T>();
var sql = DialectProvider.ToInsertRowStatement(new[] { obj }, ev.InsertFields);
return(await this.ExecuteAsync(sql));
}
/// <summary>An OrmConnection method that inserts an only.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="obj"> The object.</param>
/// <param name="onlyFields">The only fields.</param>
public async Task <int> InsertOnlyAsync <T>(T obj, Func <SqlExpressionVisitor <T>, SqlExpressionVisitor <T> > onlyFields)
where T : new()
{
var ev = DialectProvider.ExpressionVisitor <T>();
return(await InsertOnlyAsync(obj, onlyFields(ev)));
}
/// <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));
}
/// <summary>
/// Update only some fields of an object
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="TKey"></typeparam>
/// <param name="model">Object to update</param>
/// <param name="onlyFields">Specify the fields to update. ie : x=> x.SomeProperty1 or x=> new{ x.SomeProperty1, x.SomeProperty2}</param>
/// <returns></returns>
public int Update <T, TKey>(T model, Expression <Func <T, TKey> > onlyFields)
{
var ev = DialectProvider.ExpressionVisitor <T>();
ev.Update(onlyFields);
return(Update(model, ev));
}
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));
}
/// <summary>An OrmConnection method that inserts all.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="objs"> The objects.</param>
public void Insert <T>(IEnumerable <T> objs)
{
foreach (var t in objs)
{
this.ExecuteScalar(DialectProvider.ToInsertRowStatement(t));
}
}
public Task <T> SingleAsync <T>(Expression <Func <T, bool> > predicate)
{
var expression = DialectProvider.SqlExpression <T>();
string sql = expression.Where(predicate).Take(1).SelectClause();
return(Connection.QueryFirstOrDefaultAsync <T>(sql, expression.Params));
}
/// <summary>An OrmConnection method that selects.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="expression">The ev.</param>
/// <param name="buffered"></param>
/// <returns>A List<T></returns>
public IEnumerable <T> Select <T>(Func <SqlExpressionVisitor <T>, SqlExpressionVisitor <T> > expression,
CommandFlags flags = CommandFlags.Buffered)
{
var ev = DialectProvider.ExpressionVisitor <T>();
return(this.Query <T>(DialectProvider.ToSelectStatement(expression(ev), flags)));
}
public Task <long> CountAsync <T>(Expression <Func <T, bool> > predicate)
{
var expression = DialectProvider.SqlExpression <T>();
string sql = expression.Where(predicate).CountClause();
return(Connection.ExecuteScalarAsync <long>(sql, expression.Params));
}
/// <summary>An OrmConnection method that inserts an only.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="obj"> The object.</param>
/// <param name="onlyFields">The only fields.</param>
public void InsertOnly <T>(T obj, SqlExpressionVisitor <T> onlyFields) where T : new()
{
var ev = DialectProvider.ExpressionVisitor <T>();
var sql = DialectProvider.ToInsertRowStatement(new[] { obj }, ev.InsertFields);
this.Execute(sql);
}
public override string ToQuotedString(Type fieldType, object value)
{
if (fieldType.HasAttribute <EnumAsIntAttribute>())
{
return(this.ConvertNumber(fieldType.GetEnumUnderlyingType(), value).ToString());
}
if (value is int && !fieldType.IsEnumFlags())
{
value = fieldType.GetEnumName(value);
}
if (fieldType.IsEnum)
{
var enumValue = DialectProvider.StringSerializer.SerializeToString(value);
// Oracle stores empty strings in varchar columns as null so match that behavior here
if (enumValue == null)
{
return(null);
}
enumValue = DialectProvider.GetQuotedValue(enumValue.Trim('"'));
return(enumValue == "''"
? "null"
: enumValue);
}
return(base.ToQuotedString(fieldType, value));
}
protected virtual StatementPart VisitColumnMemberAccess(MemberExpression memberExpression)
{
if (memberExpression.Member.DeclaringType == typeof(string) &&
memberExpression.Member.Name == "Length")
{
return(new StatementPart(
DialectProvider.GetStringFunction("length",
_statement,
null,
null)));
}
if (memberExpression.Member.DeclaringType == typeof(DateTime))
{
switch (memberExpression.Member.Name)
{
case "Year":
case "Month":
case "Day":
case "Hour":
case "Minute":
case "Second":
return(new StatementPart(
DialectProvider.GetDatePartFunction(memberExpression.Member.Name, _statement)));
default:
throw new NotSupportedException();
}
}
return(null);
}
/// <summary>
/// Quoted Value in SQL Statement
/// </summary>
/// <param name="fieldType">Type of the field.</param>
/// <param name="value">The value.</param>
/// <returns>System.String.</returns>
public override string ToQuotedString(Type fieldType, object value)
{
var enumKind = GetEnumKind(fieldType);
if (enumKind == EnumKind.Int)
{
return(this.ConvertNumber(Enum.GetUnderlyingType(fieldType), value).ToString());
}
if (enumKind == EnumKind.Char)
{
return(DialectProvider.GetQuotedValue(ToCharValue(value).ToString()));
}
var isEnumFlags = fieldType.IsEnumFlags() ||
!fieldType.IsEnum && fieldType.IsNumericType(); //i.e. is real int && not Enum
if (!isEnumFlags && long.TryParse(value.ToString(), out var enumValue))
{
value = Enum.ToObject(fieldType, enumValue);
}
var enumString = enumKind == EnumKind.EnumMember
? value.ToString()
: DialectProvider.StringSerializer.SerializeToString(value);
if (enumString == null || enumString == "null")
{
enumString = value.ToString();
}
return(!isEnumFlags
? DialectProvider.GetQuotedValue(enumString.Trim('"'))
: enumString);
}
/// <summary>An OrmConnection method that selects.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="predicate">The predicate.</param>
/// <param name="flags"></param>
/// <returns>A List<T></returns>
public async Task <IEnumerable <T> > SelectAsync <T>(Expression <Func <T, bool> > predicate, CommandFlags flags = CommandFlags.Buffered)
{
TypedSelectStatement <T> select = new TypedSelectStatement <T>(DialectProvider);
select.Where(predicate);
return(await this.QueryAsync <T>(DialectProvider.ToSelectStatement(select.Statement, flags)));
}
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)));
}
/// <summary>An OrmConnection method that selects.</summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="expression">The expression.</param>
/// <param name="flags"></param>
/// <returns>A List<T></returns>
public async Task <IEnumerable <T> > SelectAsync <T>(Action <TypedSelectStatement <T> > expression, CommandFlags flags = CommandFlags.Buffered)
{
TypedSelectStatement <T> select = new TypedSelectStatement <T>(DialectProvider);
expression(select);
return(await this.QueryAsync <T>(DialectProvider.ToSelectStatement(select.Statement, flags)));
}
public void Can_use_limit()
{
using (var db = OpenDbConnection())
{
db.DropAndCreateTable <Sqltest>();
5.Times(i => db.Insert(new Sqltest {
Value = i + 1
}));
var sqlLimit = DialectProvider.SqlLimit(rows: 1);
var results = db.SqlList <double>($"SELECT Value from sqltest ORDER BY Id {sqlLimit}").Sum();
Assert.That(results, Is.EqualTo(1));
sqlLimit = DialectProvider.SqlLimit(rows: 3);
results = db.SqlList <double>($"SELECT Value from sqltest ORDER BY Id {sqlLimit}").Sum();
Assert.That(results, Is.EqualTo(6));
sqlLimit = DialectProvider.SqlLimit(offset: 1);
results = db.SqlList <double>($"SELECT Value from sqltest ORDER BY Id {sqlLimit}").Sum();
Assert.That(results, Is.EqualTo(14));
sqlLimit = DialectProvider.SqlLimit(offset: 4);
results = db.SqlList <double>($"SELECT Value from sqltest ORDER BY Id {sqlLimit}").Sum();
Assert.That(results, Is.EqualTo(5));
sqlLimit = DialectProvider.SqlLimit(offset: 1, rows: 1);
results = db.SqlList <double>($"SELECT Value from sqltest ORDER BY Id {sqlLimit}").Sum();
Assert.That(results, Is.EqualTo(2));
sqlLimit = DialectProvider.SqlLimit(offset: 2, rows: 2);
results = db.SqlList <double>($"SELECT Value from sqltest ORDER BY Id {sqlLimit}").Sum();
Assert.That(results, Is.EqualTo(7));
}
}
/// <summary>An OrmConnection method that gets a scalar.</summary>
/// <typeparam name="T"> Generic type parameter.</typeparam>
/// <typeparam name="TKey">Type of the key.</typeparam>
/// <param name="field"> The field.</param>
/// <returns>The scalar.</returns>
public async Task <TKey> GetScalarAsync <T, TKey>(Expression <Func <T, TKey> > field)
{
//int maxAgeUnder50 = db.Scalar<Person, int>(x => Sql.Max(x.Age));
var ev = DialectProvider.ExpressionVisitor <T>();
return(await this.ExecuteScalarAsync <TKey>(DialectProvider.ToSelectStatement(ev.Select(field), CommandFlags.None)));
}
