public static string LoadIdByField(this IDBConnection connection, ColumnSpec column, string value) { List<string> ids = connection.LoadIdsByConditions( column.table, new ComparisonCondition( column, ComparisonType.EQUAL, value ), Diapasone.unlimited ); if(ids.Count > 1) { throw new CriticalException("not unique"); } else if(ids.Count == 1) { return ids[0]; } else { throw new NotFoundInDBException(column, value); } }
private List<string> _LoadIdsByConditions(DbCommand command, ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists) { using(var logger = this.CreateCommandExecutionLogger()) { command.CommandType = System.Data.CommandType.Text; var conditionsCompiled = ConditionCompiler.Compile(conditions, this.traits); string queryConditions = ""; if(conditionsCompiled.Key != "") queryConditions = "WHERE " + conditionsCompiled.Key; ParamsHolder paramsHolder = conditionsCompiled.Value; StringBuilder queryJoins = new StringBuilder(); { foreach(var join in joins) { queryJoins.Append(" JOIN "); queryJoins.Append(join.additionalTableRaw.compile(this.traits)); queryJoins.Append(" "); queryJoins.Append(join.additionalTable.compile(this.traits)); queryJoins.Append(" ON "); queryJoins.Append(join.mainColumn.compile(this.traits)); queryJoins.Append(" = "); queryJoins.Append(join.additionalTable.getIdSpec().compile(this.traits)); } } string querySorts = ""; if(sorts.Length > 0) { List<string> sortParts = new List<string>(); foreach(SortSpec sortSpec in sorts) { if(sortSpec.ascending) { sortParts.Add(sortSpec.column.compile(this.traits) + " ASC"); } else { sortParts.Add(sortSpec.column.compile(this.traits) + " DESC"); } } querySorts = "ORDER BY " + string.Join(", ", sortParts.ToArray()); } string queryMain = "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions; foreach(KeyValuePair<string, string> kvp in paramsHolder.data) { command.AddParameter(kvp.Key, kvp.Value); } if(!diapasone.total.HasValue) { command.CommandText = logger.commandText = "SELECT COUNT(*) " + queryMain; object rawCount; //try { rawCount = command.ExecuteScalar(); //} catch(Npgsql.NpgsqlException e) { //throw new FLocalException("Error while trying to execute " + command.CommandText + ": " + e.Message); //} long count = (long)rawCount; if(count < 1) { diapasone.total = 0; } else { diapasone.total = count; } } if(diapasone.total.Value < 1) { return new List<string>(); } else { if(diapasone.total.Value > 1000 && diapasone.count < 0 && !allowHugeLists) { throw new CriticalException("huge list"); } string queryLimits = ""; if(diapasone.count >= 0) { queryLimits = "LIMIT " + diapasone.count + " OFFSET " + diapasone.start; } command.CommandText = logger.commandText = "SELECT " + idSpec.compile(this.traits) + " " + queryMain + " " + querySorts + " " + queryLimits; List<string> result = new List<string>(); using(DbDataReader reader = command.ExecuteReader()) { while(reader.Read()) { result.Add(reader.GetValue(0).ToString()); } } return result; } } }
public List<string> LoadIdsByConditions(ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, ColumnSpec idSpec, bool allowHugeLists) { using(DbConnection connection = this.createConnection()) { using(DbCommand command = connection.CreateCommand()) { return this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, idSpec, allowHugeLists); } } }
public SortSpec(ColumnSpec column, bool ascending) { this.column = column; this.ascending = ascending; }
public static ColumnOrValue createColumn(ColumnSpec column) { return new ColumnOrValue{_isColumn = true, _column = column}; }
public JoinSpec(ColumnSpec mainColumn, ITableSpec additionalTable, string alias) { this.mainColumn = mainColumn; this.additionalTableRaw = additionalTable; this.additionalTable = new TableSpec(additionalTable, alias); }
public NotFoundInDBException(ColumnSpec columnSpec, string value) : base("Object " + columnSpec.table.name + "[" + columnSpec.name + "=" + value + "] not found in db") { }
public static List<string> LoadIdsByConditions(this IDBConnection connection, ITableSpec table, AbstractCondition conditions, Diapasone diapasone, ColumnSpec idSpec, params SortSpec[] sorts) { return connection.LoadIdsByConditions(table, conditions, diapasone, new JoinSpec[0], sorts, idSpec, false); }