/// <summary> /// <see cref="SqlInsert"/> nesnesinin içerdiği sorguyu bağlantı üzerinde çalıştırır ve /// istenirse eklenen kaydın IDENTITY alanının değerini döndürür.</summary> /// <remarks> /// <p>Bu bir extension metodu olduğundan direk query.Execute(connection, true) şeklinde de /// çalıştırılabilir.</p></remarks> /// <param name="query"> /// Sorguyu içeren <see cref="SqlInsert"/> nesnesi.</param> /// <param name="connection"> /// Sorgunun çalıştırılacağı bağlantı. Gerekirse otomatik olarak açılır.</param> /// <returns> /// Identity value of inserted record.</returns> public static Int64?ExecuteAndGetID(this SqlInsert query, IDbConnection connection) { string queryText = query.ToString(); var dialect = connection.GetDialect(); if (dialect.UseReturningIdentity || dialect.UseReturningIntoVar) { string identityColumn = query.IdentityColumn(); if (identityColumn == null) { throw new ArgumentNullException("query.IdentityColumn"); } queryText += " RETURNING " + SqlSyntax.AutoBracket(identityColumn); if (dialect.UseReturningIntoVar) { queryText = "DECLARE\r\n INSERTED__VALUE NUMBER(18);\r\nBEGIN\r\n" + queryText + " INTO INSERTED__VALUE; DBMS_OUTPUT.PUT_LINE(INSERTED__VALUE);\r\nEND;"; } using (var command = NewCommand(connection, queryText, query.Params)) { var param = command.CreateParameter(); param.Direction = dialect.UseReturningIntoVar ? ParameterDirection.ReturnValue : ParameterDirection.Output; param.ParameterName = dialect.UseReturningIntoVar ? "INSERTED__VALUE" : identityColumn; param.DbType = DbType.Int64; command.Parameters.Add(param); ExecuteNonQuery(command); return(Convert.ToInt64(param.Value)); } } if (dialect.UseScopeIdentity) { var scopeIdentityExpression = dialect.ScopeIdentityExpression; queryText += ";\nSELECT " + scopeIdentityExpression + " AS IDCOLUMNVALUE"; using (IDataReader reader = ExecuteReader(connection, queryText, query.Params)) { if (reader.Read() && !reader.IsDBNull(0)) { return(Convert.ToInt64(reader.GetValue(0))); } return(null); } } throw new NotImplementedException(); }
public string OriginExpression(string propertyName, OriginAttribute origin, DialectExpressionSelector expressionSelector, string aliasPrefix, List <Attribute> extraJoins) { if (aliasPrefix.Length >= 1000) { throw new DivideByZeroException("Infinite origin recursion detected!"); } var org = GetOriginProperty(propertyName); var originProperty = org.Item1; if (aliasPrefix.Length == 0) { aliasPrefix = origin.Join; } else { aliasPrefix = aliasPrefix + "_" + origin.Join; } var columnAttr = originProperty.GetCustomAttribute <ColumnAttribute>(); if (columnAttr != null) { return(aliasPrefix + "." + SqlSyntax.AutoBracket(columnAttr.Name)); } else { var originDictionary = GetPropertyDictionary(org.Item2); var expressionAttr = originProperty.GetCustomAttributes <ExpressionAttribute>(); if (expressionAttr.Any()) { var expression = expressionSelector.GetBestMatch(expressionAttr, x => x.Dialect); return(originDictionary.PrefixAliases(expression.Value, aliasPrefix, expressionSelector, extraJoins)); } else { var originOrigin = originProperty.GetCustomAttribute <OriginAttribute>(); if (originOrigin != null) { originDictionary.PrefixAliases(originOrigin.Join + ".Dummy", aliasPrefix, expressionSelector, extraJoins); return(originDictionary.OriginExpression(originProperty.Name, originOrigin, expressionSelector, aliasPrefix, extraJoins)); } else { return(aliasPrefix + "." + SqlSyntax.AutoBracket(originProperty.Name)); } } } }
protected Field(ICollection <Field> fields, FieldType type, string name, LocalText caption, int size, FieldFlags flags) { this.name = name; expression = "T0." + SqlSyntax.AutoBracket(name); this.size = size; this.flags = flags; this.type = type; index = -1; minSelectLevel = SelectLevel.Auto; naturalOrder = 0; this.caption = caption; if (fields != null) { fields.Add(this); } }
/// <summary> /// Adds a field of a given table alias to the SELECT statement. /// </summary> /// <param name="query">The query.</param> /// <param name="alias">A table alias that will be prepended to the field name with "." between</param> /// <param name="field">A field that only its field name will be used. It won't be set as a target.</param> /// <param name="columnName">A column name</param> /// <returns> /// The query itself. /// </returns> /// <exception cref="ArgumentNullException"> /// alias /// or /// field /// or /// columnName /// </exception> /// <remarks> /// Field is not set as a target, unlike field only overload, only field name is used. /// </remarks> public static SqlQuery Select(this SqlQuery query, IAlias alias, IField field, string columnName) { if (alias == null) { throw new ArgumentNullException("alias"); } if (field == null) { throw new ArgumentNullException("field"); } if (columnName == null) { throw new ArgumentNullException("columnName"); } return(query.Select(alias.NameDot + SqlSyntax.AutoBracket(field.Name), columnName)); }
/// <summary> /// Formats an INSERT query.</summary> /// <param name="tableName"> /// Tablename (required).</param> /// <param name="nameValuePairs"> /// Field names and values. Must be passed in the order of <c>[field1, value1, field2, /// value2, ...., fieldN, valueN]</c>. It must have even number of elements.</param> /// <returns> /// Formatted query.</returns> public static string Format(string tableName, List <string> nameValuePairs) { if (tableName == null || tableName.Length == 0) { throw new ArgumentNullException(tableName); } if (nameValuePairs == null) { throw new ArgumentNullException("nameValuePairs"); } if (nameValuePairs.Count % 2 != 0) { throw new ArgumentOutOfRangeException("nameValuePairs"); } StringBuilder sb = new StringBuilder("INSERT INTO ", 64 + nameValuePairs.Count * 16); sb.Append(SqlSyntax.AutoBracketValid(tableName)); sb.Append(" ("); for (int i = 0; i < nameValuePairs.Count; i += 2) { if (i > 0) { sb.Append(", "); } sb.Append(SqlSyntax.AutoBracket(nameValuePairs[i])); } sb.Append(") VALUES ("); for (int i = 1; i < nameValuePairs.Count; i += 2) { if (i > 1) { sb.Append(", "); } sb.Append(nameValuePairs[i]); } sb.Append(')'); return(sb.ToString()); }
/// <summary> /// Formats an SQL UPDATE statement.</summary> /// <param name="tableName"> /// Tablename (required).</param> /// <param name="nameValuePairs"> /// Field name and values. Should have structure of <c>[field1, value1, field2, value2, ...., fieldN, valueN]</c>. /// This array is required and must have even number of elements.</param> /// <param name="where"> /// WHERE clause (can be null).</param> /// <returns> /// Formatted UPDATE query.</returns> public static string Format(string tableName, string where, List <string> nameValuePairs) { if (tableName == null || tableName.Length == 0) { throw new ArgumentNullException(tableName); } if (nameValuePairs == null) { throw new ArgumentNullException("nameValuePairs"); } if (nameValuePairs.Count % 2 != 0) { throw new ArgumentOutOfRangeException("nameValuePairs"); } StringBuilder sb = new StringBuilder("UPDATE ", 64 + where.Length + nameValuePairs.Count * 16); sb.Append(SqlSyntax.AutoBracketValid(tableName)); sb.Append(" SET "); for (int i = 0; i < nameValuePairs.Count - 1; i += 2) { if (i > 0) { sb.Append(", "); } sb.Append(SqlSyntax.AutoBracket(nameValuePairs[i])); sb.Append(" = "); sb.Append(nameValuePairs[i + 1]); } if (where != null && where.Length > 0) { sb.Append(" WHERE "); sb.Append(where); } return(sb.ToString()); }
internal string PrefixAliases(string expression, string alias, DialectExpressionSelector expressionSelector, List <Attribute> extraJoins) { if (string.IsNullOrWhiteSpace(expression)) { return(expression); } Check.NotNullOrWhiteSpace(alias, "alias"); var aliasPrefix = alias + "_"; var mappedJoins = new Dictionary <string, ISqlJoin>(); Func <string, string> mapAlias = null; Func <string, string> mapExpression = x => { if (x == null) { return(null); } return(JoinAliasLocator.ReplaceAliases(x, mapAlias)); }; mapAlias = x => { if (x == "t0" || x == "T0") { return(alias); } ISqlJoin sqlJoin; if (mappedJoins.TryGetValue(x, out sqlJoin)) { return(sqlJoin.Alias); } Tuple <string, ForeignKeyAttribute, ISqlJoin> propJoin; if (joinPropertyByAlias.TryGetValue(x, out propJoin)) { var propertyInfo = propertyByName[propJoin.Item1]; string leftExpression; var newAlias = aliasPrefix + x; var columnAttr = propertyInfo.GetCustomAttribute <ColumnAttribute>(); if (columnAttr != null) { leftExpression = alias + "." + SqlSyntax.AutoBracket(columnAttr.Name); } else { var expressionAttr = propertyInfo.GetCustomAttribute <ExpressionAttribute>(); if (expressionAttr != null) { leftExpression = mapExpression(expressionAttr.Value); } else { var origin = propertyInfo.GetCustomAttribute <OriginAttribute>(); if (origin != null) { leftExpression = OriginExpression(propertyInfo.Name, origin, expressionSelector, alias, extraJoins); } else { leftExpression = alias + "." + SqlSyntax.AutoBracket(propertyInfo.Name); } } } ISqlJoin srcJoin = propJoin.Item3; var criteriax = leftExpression + " = " + newAlias + "." + SqlSyntax.AutoBracket(propJoin.Item2.Field); if (srcJoin is LeftJoinAttribute) { srcJoin = new LeftJoinAttribute(newAlias, propJoin.Item2.Table, criteriax); } else if (srcJoin is InnerJoinAttribute) { srcJoin = new InnerJoinAttribute(newAlias, propJoin.Item2.Table, criteriax); } else { throw new ArgumentOutOfRangeException("joinType"); } srcJoin.RowType = propJoin.Item2.RowType ?? propJoin.Item3.RowType; mappedJoins[x] = srcJoin; extraJoins.Add((Attribute)srcJoin); return(newAlias); } if (rowJoinByAlias.TryGetValue(x, out sqlJoin)) { var mappedCriteria = mapExpression(sqlJoin.OnCriteria); var newAlias = aliasPrefix + x; var rowType = sqlJoin.RowType; var lja = sqlJoin as LeftJoinAttribute; if (lja != null) { sqlJoin = new LeftJoinAttribute(lja.Alias, lja.ToTable, mappedCriteria); } else { var ija = sqlJoin as InnerJoinAttribute; if (ija != null) { sqlJoin = new InnerJoinAttribute(ija.Alias, ija.ToTable, mappedCriteria); } else { var oaa = sqlJoin as OuterApplyAttribute; if (oaa != null) { sqlJoin = new OuterApplyAttribute(ija.Alias, mappedCriteria); } } } sqlJoin.RowType = rowType; mappedJoins[x] = sqlJoin; extraJoins.Add((Attribute)sqlJoin); return(newAlias); } return(x); }; return(mapExpression(expression)); }