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