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