/// <summary> /// Deletes an Entity from the database /// </summary> /// <remarks>This method utilizes the <see cref="PreparedNonQuery"/> to speed things along.</remarks> /// <param name="obj">The <see cref="TEntity"/> object to remove from the dataset</param> /// <returns>true if an entity was removed from the dataset; false otherwise.</returns> public bool Remove(TEntity obj) { // Generate the SQL if (DeleteQuery == null) { // Start the query using a query builder var builder = new DeleteQueryBuilder(Context).From(EntityTable.TableName); // build the where statement, using primary keys only foreach (string keyName in EntityTable.PrimaryKeys) { PropertyInfo info = EntityTable.Columns[keyName].Property; builder.Where(keyName, Comparison.Equals, new SqlLiteral($"@{keyName}")); } DeleteQuery = new PreparedNonQuery(builder.BuildCommand()); } // Execute the SQL Command lock (DeleteQuery) { DeleteQuery.SetParameters(obj, EntityTable); return(DeleteQuery.Execute() > 0); } }
public static void Delete <T>(string connectionString, T entity) where T : new() { var(condition, wheres) = KeyAttribute.GetKeysQueryWhere(entity); var deleteQueryBuilder = new DeleteQueryBuilder() .WithWhere(condition, wheres.ToArray()); var query = deleteQueryBuilder.Build <T>(); DataBaseDataReader.ExecuteNonQuery(connectionString, query); }
public void GenerateSimpleQueryTest() { // Arrange const string queryExpected = "DELETE FROM `books`"; var deleteQueryBuilder = new DeleteQueryBuilder(); // Act var query = deleteQueryBuilder.Build <Book>(); // Assert Assert.AreEqual(queryExpected, query); }
public void GenerateQuerySimpleWhereTest() { // Arrange const string queryExpected = "DELETE FROM `books` WHERE `Id` = 1"; var deleteQueryBuilder = new DeleteQueryBuilder() .WithWhere(new WhereQueryEquals(GetColumnNameWithQuotes <Book>(nameof(Book.Id)), 1)); // Act var query = deleteQueryBuilder.Build <Book>(); // Assert Assert.AreEqual(queryExpected, query); }
private string GetDeleteQuery(string tableName, string whichColumnForWhereCondition, string value) { if (string.IsNullOrEmpty(value)) { return(string.Empty); } var queryBuilder = new DeleteQueryBuilder(); queryBuilder.SelectFromTable(tableName); queryBuilder.AddWhere(whichColumnForWhereCondition, Comparison.Equals, value, 1); var query = queryBuilder.BuildQuery(); return(query); }
public void GenerateQueryWith2WhereConditionsTest() { // Arrange const string queryExpected = "DELETE FROM `books` WHERE `Id` = 1 OR `Id` = 2"; var deleteQueryBuilder = new DeleteQueryBuilder() .WithWhere( new WhereQueryEquals(GetColumnNameWithQuotes <Book>(nameof(Book.Id)), 1), (WhereQuerySyntaxEnum.Or, new WhereQueryEquals(GetColumnNameWithQuotes <Book>(nameof(Book.Id)), 2))); // Act var query = deleteQueryBuilder.Build <Book>(); // Assert Assert.AreEqual(queryExpected, query); }
public void GenerateQueryWithJoinTest() { // Arrange const string queryExpected = "DELETE FROM `books` JOIN `publishers` ON `books`.`PublisherId` = `publishers`.`Id` WHERE `publishers`.`Name` LIKE '%Scholastic%'"; var deleteQueryBuilder = new DeleteQueryBuilder() .WithJoin( JoinEnum.Join, GetTableNameWithQuotes <Book>(), GetTableNameWithQuotes <Publisher>(), (GetColumnNameWithQuotes <Book>(nameof(Book.PublisherId)), GetColumnNameWithQuotes <Publisher>(nameof(Publisher.Id)))) .WithWhere <Publisher>(new WhereQueryLike(GetColumnNameWithQuotes <Publisher>(nameof(Publisher.Name)), "%Scholastic%")); // Act var query = deleteQueryBuilder.Build <Book>(); // Assert Assert.AreEqual(queryExpected, query); }
public static bool deleteValue(int id) { DeleteQueryBuilder query = new DeleteQueryBuilder(); query.Table = _table; query.AddWhere("id", Comparison.Equals, id); string cmd = query.BuildQuery(); bool result = Utils.Mysql.execute(cmd); if (result) { UpdateQueryBuilder update = new UpdateQueryBuilder(); update.Table = "users"; update.addSet("faculty", "0"); update.AddWhere("faculty", Comparison.Equals, id); cmd = update.BuildQuery(); Utils.Mysql.execute(cmd); } return(result); }
public bool AddRelations(Guid tenantId, string relationEntityName, string parentEntityName, Guid parentId, string childEntityName, List <Guid> childIds) { IMetadataManager _metadaManager = new VPC.Framework.Business.MetadataManager.Contracts.MetadataManager(); var tableName = _metadaManager.GetTableNameByEntityname(relationEntityName); var primaryKey = _metadaManager.GetPrimaryKeyByEntityname(relationEntityName); var fields = _metadaManager.GetColumnNameByEntityName(relationEntityName, null); if (fields.Any()) { var parentTableName = _metadaManager.GetTableNameByEntityname(parentEntityName); var parentField = fields.FirstOrDefault(t => t.ReferenceTableName.Equals(parentTableName)); var childTableName = _metadaManager.GetTableNameByEntityname(childEntityName); var childField = fields.FirstOrDefault(t => t.ReferenceTableName.Equals(childTableName)); //delete and create.... var queryBuilder = new DeleteQueryBuilder(); queryBuilder.SelectFromTable(tableName); queryBuilder.AddWhere(parentField.ColumnName, Comparison.Equals, parentId.ToString(), 1); var deleteQuery = queryBuilder.BuildQuery(); //---------------------------------------------------------------------------------------- IRelationQueryAdmin deleteAdmin = new RelationQueryAdmin(); var res = deleteAdmin.DeleteResult(tenantId, relationEntityName, deleteQuery); //need to change this logic.... foreach (var childId in childIds) { var insertQueryBuilder = new InsertQueryBuilder(); var insertColumns = GetNecessaryColumns(tenantId, parentId, fields, parentField); insertColumns.Add(childField.ColumnName, childId.ToString()); insertQueryBuilder.InsertIntoTable(tableName, insertColumns, false); var insertQuery = insertQueryBuilder.BuildQuery(); IRelationQueryAdmin saveAdmin = new RelationQueryAdmin(); saveAdmin.SaveResult(tenantId, relationEntityName, insertQuery); } } return(true); }
private string BuildDelete(IDictionary <string, object> parameters, DeleteQueryBuilder query) { if (query == null) { return(null); } if (query.Tables.Count == 0) { return(null); } var builder = new StringBuilder(); builder.Append("DELETE FROM ") .Append(BuildTables(parameters, query.Tables)); var where = SqlRender.BuildCondition(query.WhereConditions, parameters); if (!string.IsNullOrWhiteSpace(where)) { builder.Append(Environment.NewLine).Append($"WHERE ({where})"); } return(builder.ToString()); }
public static DeleteQueryBuilder From(this DeleteQueryBuilder @this, string tableName) { @this.Tables.Clear(); @this.Tables.Add(new Table(tableName)); return(@this); }