public void delete(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, string id) { using(var logger = this.CreateCommandExecutionLogger()) { Transaction transaction = (Transaction)_transaction; lock(transaction) { using(DbCommand command = transaction.sqlconnection.CreateCommand()) { command.Transaction = transaction.sqltransaction; command.CommandType = System.Data.CommandType.Text; command.CommandText = logger.commandText = "DELETE FROM " + table.compile(traits) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id"); command.AddParameter("id", id); command.ExecuteNonQuery(); } } } }
public static PageOuter createFromUrl(penartur.FLocal.Common.URL.AbstractUrl url, long perPage, Dictionary<char, Func<string, long>> customAction) { if(url.remainder.Contains("/")) throw new WrongUrlException(); string[] requestParts = url.remainder.Split(new[] { '-' }, StringSplitOptions.RemoveEmptyEntries); bool reversed = (requestParts.Length > 1) && (requestParts[1].ToLower() == "reversed"); if(requestParts.Length > 0) { if(requestParts[0].ToLower() == "all") { return new PageOuter(perPage, reversed); } else if(Char.IsDigit(requestParts[0][0])) { return new PageOuter(long.Parse(requestParts[0]), perPage, perPage, reversed); } else { return new PageOuter(customAction[requestParts[0][0]](requestParts[0].Substring(1)), perPage, perPage, reversed); } } else { return new PageOuter(0, perPage, perPage, reversed); } }
public long GetCountByConditions(ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, params JoinSpec[] joins) { using(var logger = this.CreateCommandExecutionLogger()) { using(DbConnection connection = this.createConnection()) { using(DbCommand command = connection.CreateCommand()) { 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; string queryJoins = ""; { if(joins.Length > 0) { throw new NotImplementedException(); } } command.CommandText = logger.commandText = "SELECT COUNT(*) " + "FROM " + table.compile(this.traits) + " " + queryJoins + " " + queryConditions; foreach(KeyValuePair<string, string> kvp in paramsHolder.data) { command.AddParameter(kvp.Key, kvp.Value); } object rawCount = command.ExecuteScalar(); long count = (long)rawCount; return count; } } } }
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 void update(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, string id, Dictionary<string, string> data) { using(var logger = this.CreateCommandExecutionLogger()) { Transaction transaction = (Transaction)_transaction; lock(transaction) { using(DbCommand command = transaction.sqlconnection.CreateCommand()) { List<string> updates = new List<string>(); ParamsHolder paramsholder = new ParamsHolder(); foreach(KeyValuePair<string, string> kvp in data) { updates.Add(this.traits.escapeIdentifier(kvp.Key) + " = " + this.traits.markParam(paramsholder.Add(kvp.Value))); } command.Transaction = transaction.sqltransaction; command.CommandType = System.Data.CommandType.Text; command.CommandText = logger.commandText = "UPDATE " + table.compile(traits) + " set " + String.Join(", ", updates.ToArray()) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id"); command.AddParameter("id", id); foreach(KeyValuePair<string, string> kvp in paramsholder.data) { command.AddParameter(kvp.Key, kvp.Value); } // throw new CriticalException(command.CommandText + "; parameters: " + string.Join(", ", (from DbParameter parameter in command.Parameters select parameter.ParameterName + "='" + parameter.Value.ToString() + "'").ToArray())); command.ExecuteNonQuery(); } } } }
public void lockTable(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table) { using(var logger = this.CreateCommandExecutionLogger()) { Transaction transaction = (Transaction)_transaction; lock(transaction) { using(DbCommand command = transaction.sqlconnection.CreateCommand()) { command.Transaction = transaction.sqltransaction; command.CommandType = System.Data.CommandType.Text; command.CommandText = logger.commandText = "LOCK TABLE " + table.compile(this.traits); command.ExecuteNonQuery(); } } } }
public List<string> LoadIdsByConditions(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, penartur.Web.Core.DB.conditions.AbstractCondition conditions, Diapasone diapasone, JoinSpec[] joins, SortSpec[] sorts, bool allowHugeLists) { Transaction transaction = (Transaction)_transaction; lock(transaction) { using(DbCommand command = transaction.sqlconnection.CreateCommand()) { command.Transaction = transaction.sqltransaction; return this._LoadIdsByConditions(command, table, conditions, diapasone, joins, sorts, table.getIdSpec(), allowHugeLists); } } }
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 List<Dictionary<string, string>> LoadByIds(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, List<string> ids) { Transaction transaction = (Transaction)_transaction; lock(transaction) { using(DbCommand command = transaction.sqlconnection.CreateCommand()) { command.Transaction = transaction.sqltransaction; return this._LoadByIds(command, table, ids, true); } } }
public string insert(penartur.Web.Core.DB.Transaction _transaction, ITableSpec table, Dictionary<string, string> data) { using(var logger = this.CreateCommandExecutionLogger()) { Transaction transaction = (Transaction)_transaction; lock(transaction) { using(DbCommand command = transaction.sqlconnection.CreateCommand()) { List<string> updates = new List<string>(); List<string> updatesPlaceholders = new List<string>(); ParamsHolder paramsholder = new ParamsHolder(); foreach(KeyValuePair<string, string> kvp in data) { updates.Add(this.traits.escapeIdentifier(kvp.Key)); updatesPlaceholders.Add(this.traits.markParam(paramsholder.Add(kvp.Value))); } command.Transaction = transaction.sqltransaction; command.CommandType = System.Data.CommandType.Text; command.CommandText = logger.commandText = "INSERT INTO " + table.compile(this.traits) + " (" + String.Join(", ", updates.ToArray()) + ") VALUES (" + String.Join(", ", updatesPlaceholders.ToArray()) + ")"; foreach(KeyValuePair<string, string> kvp in paramsholder.data) { command.AddParameter(kvp.Key, kvp.Value); } command.ExecuteNonQuery(); if(data.ContainsKey(table.idName)) return data[table.idName]; return this.traits.LastInsertId(command, table).ToString(); } } } }
public static PageOuter createFromUrl(penartur.FLocal.Common.URL.AbstractUrl url, long perPage) { return createFromUrl(url, perPage, new Dictionary<char, Func<string, long>>()); }