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))); }