public override object VisitDeleteStatement([NotNull] MiniSQLParser.DeleteStatementContext context)
{
Expression exp = (Expression)Visit(context.whereClause());
string tableName = context.tableRef().GetText();
DeleteStatement obj = new DeleteStatement();
obj.TableName = tableName;
obj.Condition = exp;
return(obj);
}
public void Basic_Delete_Statement()
{
// Exercise
DeleteStatement statement = ParserFactory.Execute <DeleteStatement>("delete from dbo.table").First();
// Verify outcome
Assert.IsNotNull(statement);
Assert.AreEqual("dbo.table", statement.From.First().Name);
Assert.AreEqual(null, statement.TableName);
}
private void DeleteObject(object obj, IList <ModificationStatement> statements)
{
if (mappings.TryGetValue(obj.GetType(), out var mapping))
{
string alias = null;
var statement = new DeleteStatement(mapping.Table, alias);
SetupUpdateDeleteStatement(statement, obj, mapping, alias);
statements.Add(statement);
}
}
protected int DeleteRows(string table_name)
{
DataTable myTable = this.Tables[table_name];
DataRowCollection rows = myTable.Rows;
int rowsCount = rows.Count;
int rowsAffected = 0;
if (rowsCount == 0)
{
return(0);
}
DeleteStatement cmd = new DeleteStatement(myTable.ExtendedProperties["Storage"].ToString());
cmd.ConnectionStringName = _connectionStringName;
List <SqlParameter> prmlist = new List <SqlParameter>();
int colsCount = myTable.Columns.Count;
int keys = 0;
string identcol = String.Empty;
for (int c = 0; c < colsCount; c++)
{
DataColumn col = myTable.Columns[c];
if (col.ExtendedProperties["PrimaryKey"] != null)
{
SqlParameter prm = new SqlParameter(col.ColumnName, col.DataType);
prmlist.Add(prm);
cmd.Where(Condition.IsEqual(col.ExtendedProperties["Storage"].ToString(), prm));
keys++;
}
}
List <DataRow> toDelete = new List <DataRow>();
foreach (DataRow row in myTable.Rows)
{
if (row.RowState == DataRowState.Deleted)
{
for (int p = 0; p < keys; p++)
{
SqlParameter prm = prmlist[p];
prm.Value = row[prm.Name, DataRowVersion.Original];
}
cmd.Execute();
toDelete.Add(row);
rowsAffected++;
}
}
toDelete.ForEach(delegate(DataRow row) { row.AcceptChanges(); });
return(rowsAffected);
}
public int Delete(DeleteStatement <TEntity> deleteQuery)
{
Check.NotNull(deleteQuery, nameof(deleteQuery));
(string query, IEnumerable <object> parameters) = _queryTranslator.TranslateQuery(deleteQuery);
return(_queryProcessor.ExecuteCommand(
query: query,
parameters: parameters
));
}
public override void Visit(DeleteStatement node)
{
var whereClauseVisitor = new WhereClauseVisitor();
node.Accept(whereClauseVisitor);
if (whereClauseVisitor.whereClausefound)
{
errorCallback(RULE_NAME, "Delete " + RULE_TEXT, node.StartLine, GetColumnNumber(node));
}
}
public void Visit(DeleteStatement statement)
{
Write("DELETE ");
WriteTop(statement.TopExpression);
using (Indent(8))
statement.Target.Accept(this);
Write(statement.FromList);
WriteWhereClause(statement.WhereClause);
}
/// <summary>
/// Renders DELETE statement.
/// </summary>
/// <param name="delete">DELETE statement to render.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="output">StringBuilder to which SQL is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
public void Render(DeleteStatement delete, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
output.Append("DELETE FROM ");
delete.FromTable.RenderTableName(dbms, output);
if (delete.Where != null && !delete.Where.IsEmpty)
{
output.Append(" WHERE ");
delete.Where.Render(dbms, output, parameters);
}
}
public void ConstructorShouldAssignPredicate()
{
// Arrange
Expression <Func <Exception, bool> > predicate = ex => ex.Message == "test";
// Act
var deleteStatement = new DeleteStatement <Exception>(predicate);
// Assert
Assert.Same(predicate, deleteStatement.Predicate);
}
public void ConstructorShouldAcceptNullPredicate()
{
// Arrage
Expression <Func <Exception, bool> > predicate = null;
// Act an Assert
var statement = new DeleteStatement <Exception>(predicate);
// Assert
Assert.Null(statement.Predicate);
}
public override void ExplicitVisit(DeleteStatement node)
{
if (node.DeleteSpecification != null && node.DeleteSpecification.OutputClause != null)
{
SelectElementVisitor vstor = new SelectElementVisitor(resolveColumnType, columnTypeResolver, node);
node.DeleteSpecification.OutputClause.Accept(vstor);
if (vstor.HasOutput)
{
resultHasOutputResultSet.Add(vstor.ResultOutput);
}
}
}
private List <IPlanStep> GetWhereClauseSteps(DeleteStatement statement)
{
var result = new List <IPlanStep>();
if (statement.HasWhereClause)
{
var whereGenerator = new WhereClausePlanGenerator(_process, _level);
result.AddRange(whereGenerator.GetPlanSteps(statement.WhereClause));
}
return(result);
}
/// <summary>
/// Deletes records using boolean expression
/// <summary>
public virtual void Delete <T>(Expression <BooleanExpression <T> > expression)
{
var whereClause = new WhereClause <T>(new FromClause <T>(), expression);
var model = new DeleteStatement <T>(whereClause).Model;
// todo: hack, refactor
var sql = "DELETE FROM T0 USING " + model.FromSql.Replace("FROM", "") + " " + model.WhereSql;
using (var command = _transaction.Connection.CreateCommand()){
command.CommandText = sql;
command.ExecuteScalar();
}
}
public void ExecuteTest_Into_No_Table()
{
string sql = string.Format("DELETE FROM ");
IList <IStatement> statements = ParserFactory.Execute(sql);
Assert.AreEqual(1, statements.Count);
IStatement actualStatement = statements[0];
DeleteStatement actual = actualStatement as DeleteStatement;
Assert.IsNotNull(actual);
Assert.AreEqual(0, actual.Tables.Count);
}
public static int Delete <T>(this IQueryable <T> queryable)
where T : class
{
// Resolve service
var serviceFactory = new ServiceFactory <T>(queryable);
ICommandService <T> commandService = serviceFactory.CreateCommandService();
// Execute Query
var query = new DeleteStatement <T>(IQueryableHelper.GetQueryPredicate(queryable));
return(commandService.Delete(query));
}
public static int ExecuteDeleteBuild(this Database database, Table table, Action <DeleteStatement> action, DbTransaction dbTransaction = null, int commandTimeout = CommandTimeout)
{
var builder = database.CreateSQLBuilder();
var delete = new DeleteStatement(table);
action(delete);
builder.BuildDeleteStatement(delete);
return(database.ExecuteNonQuery(builder.ToSQL(), dbTransaction, commandTimeout));
}
/*
* BNF:
* <delete statement> ::=
* delete <table identifier>
* [<where clause>]
*/
protected Statement DeleteStatement(Lexer lexer)
{
DeleteStatement statement = new DeleteStatement();
lexer.NextToken();
statement.DeleteClause = new DeleteClause();
statement.DeleteClause.TableExpression = new TableExpression(QualifiedIdentifier(lexer));
if (lexer.PeekTokenSymbol(1) == Keywords.Where)
{
statement.WhereClause = WhereClause(lexer);
}
return(statement);
}
public override void Visit(DeleteStatement deleteStatement)
{
var tableRef = deleteStatement.DeleteSpecification.Target.ToTableReferenceModel();
if (tableRef == null)
{
return;
}
var result = CreateNewResult(TSqlTokenType.Delete);
ExtractNamedTableReference(tableRef, result);
StoreResult(result);
}
protected void AddValueDeleteStatement(IValue value, ValueInfo valueInfo)
{
var builder = new ComplexCommandBuilder();
var idParameter = value.Id.ToParameter();
builder.AddParameter(idParameter);
var whereClause = string.Format("{0}.{1} = {2}", valueInfo.Name, valueInfo.Identifer.Name, idParameter.Name);
var statement = new DeleteStatement(valueInfo.Name, whereClause);
builder.AddStatement(statement);
Add(builder);
}
public virtual CommandDefinition ToDeleteStatement(DeleteStatement statement)
{
var query = new StringBuilder("DELETE FROM ");
query.Append(statement.TableName);
if (statement.WhereExpression.Length > 0)
{
query.Append(" WHERE ");
query.Append(statement.WhereExpression);
}
return(new CommandDefinition(query.ToString(), statement.Parameters));
}
protected override DeleteStatement <TestEntity> CreateStatement(IStatementExecutor statementExecutor,
IEntityMapper entityMapper,
IWritablePropertyMatcher writablePropertyMatcher,
ISelectClauseBuilder selectClauseBuilder,
IFromClauseBuilder fromClauseBuilder,
IWhereClauseBuilder whereClauseBuilder,
ISqlConnectionProvider connectionProvider)
{
var statement = new DeleteStatement <TestEntity>(statementExecutor, entityMapper, whereClauseBuilder);
statement.UseConnectionProvider(connectionProvider);
return(statement);
}
/// <summary>
/// Generates and executes DELETE statement for each
/// entity in the entities collection
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
public virtual void Delete <T>(IEnumerable <T> entities)
{
var model = new DeleteStatement <T>().Model;
using (var command = _transaction.Connection.CreateCommand())
{
foreach (var entity in entities)
{
command.CommandText = model.ApplyParameters(entity);
command.ExecuteScalar();
}
}
}
private TSqlStatement BuildDelete(InsertSpecification originalInsert)
{
var delete = new DeleteStatement();
delete.WithCtesAndXmlNamespaces = new WithCtesAndXmlNamespaces();
var cte = new CommonTableExpression();
cte.ExpressionName = new Identifier()
{
Value = "to_delete"
};
cte.QueryExpression = BuildNewRowSource(originalInsert);
delete.WithCtesAndXmlNamespaces.CommonTableExpressions.Add(cte);
delete.DeleteSpecification = new DeleteSpecification();
var tableName = new SchemaObjectName();
tableName.Identifiers.Add(new Identifier()
{
Value = "to_delete"
});
delete.DeleteSpecification.Target = new NamedTableReference()
{
SchemaObject = tableName
};
var outputInto = delete.DeleteSpecification.OutputIntoClause = new OutputIntoClause();
var deletedTable = new MultiPartIdentifier();
deletedTable.Identifiers.Add(new Identifier()
{
Value = "deleted"
});
outputInto.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = deletedTable
});
outputInto.IntoTable = originalInsert.Target;
foreach (var col in originalInsert.Columns)
{
outputInto.IntoTableColumns.Add(col);
}
return(delete);
}
protected override Task <int> DoDeleteAsync(string executableSql, Dict executableParams)
{
DeleteStatement executableStatement = new DeleteStatement(this.database, executableSql);
var memoryTable = executableStatement.StatementFromRefs[0].table as MemoryTable;
string evaluatedWhereClause = MemoryDatabase.EvaluateWhereClause(executableStatement.whereClause, executableParams, executableStatement.StatementFromRefs);
var dataRows = memoryTable.DataTable.Select(evaluatedWhereClause);
foreach (var dataRow in dataRows)
{
dataRow.Delete();
}
this.changedTables.Add(memoryTable);
return(Task.FromResult(dataRows.Length));
}
public void Delete_Statement_With_Top_N_Clause_With_Missing_Value()
{
// Exercise
try
{
DeleteStatement statement = ParserFactory.Execute <DeleteStatement>(
@"delete top () t from dbo.table as t join dbo.other o on o.id = a.id where field <> 2"
).First();
Assert.Fail();
}
catch (SyntaxException ex)
{
Assert.AreEqual("expected alpha, numeric, or variable, found )", ex.Message);
}
}
protected void AddEntityDeleteStatement(IChild child, EntityInfo childInfo)
{
var builder = new ComplexCommandBuilder();
var idParameter = child.Id.ToParameter();
builder.AddParameter(idParameter);
var whereClause = string.Format("{0}.{1} = {2}", childInfo.Name, childInfo.Identifier.Name, idParameter.Name);
var statement = new DeleteStatement(childInfo.Name, whereClause);
builder.AddStatement(statement);
Add(builder);
AddDeleteStatements(child, childInfo);
}
public override void Visit(DeleteStatement node)
{
var deleteSpecification = node.DeleteSpecification;
if (deleteSpecification == null)
{
return;
}
var fromIds = ProcessFromClause(deleteSpecification.FromClause, SqlAction.Select);
ProcessWhereClause(deleteSpecification.WhereClause, SqlAction.Select);
ProcessTarget(deleteSpecification.Target, fromIds, DeleteTables);
}
public virtual void Delete(ICondition filter)
{
if (filter == null)
{
return;
}
var delete = new DeleteStatement(_tableName)
{
ConnectionStringName = _connectionStringName
};
delete.Where(filter);
delete.Execute();
}
public static EngineResult Evaluate(StatementWithCtesAndXmlNamespaces statement, Scope scope)
{
if (statement.WithCtesAndXmlNamespaces?.CommonTableExpressions != null)
{
scope = Evaluate(statement.WithCtesAndXmlNamespaces.CommonTableExpressions, scope);
}
return(statement switch
{
SelectStatement select => Evaluate(select, scope),
InsertStatement insert => Evaluate(insert.InsertSpecification, scope),
UpdateStatement update => Evaluate(update.UpdateSpecification, scope),
DeleteStatement delete => Evaluate(delete.DeleteSpecification, scope),
MergeStatement merge => Evaluate(merge.MergeSpecification, scope),
_ => throw FeatureNotSupportedException.Subtype(statement)
});
public Statements(string tableName, Connection connection)
{
try
{
DeleteStatement = new DeleteStatement(tableName, connection);
InsertStatement = new InsertStatement(tableName, connection);
SelectStatement = new SelectStatement(tableName, connection);
}
catch
{
DeleteStatement?.Dispose();
InsertStatement?.Dispose();
SelectStatement?.Dispose();
throw;
}
}
public static async NonQueryTask ExecuteDeleteBuildAsync(this Database database, Table table, Action <DeleteStatement> action, DbConnection dbConnection = null, DbTransaction dbTransaction = null, int commandTimeout = UseDefaultCommandTimeout)
{
return(await database.ExecuteNonQueryAsync(
sql : database.BuildSql(builder =>
{
var delete = new DeleteStatement(table);
action(delete);
builder.BuildDeleteStatement(delete);
}, out var parameters),
parameters : parameters,
dbConnection : dbConnection,
dbTransaction : dbTransaction,
commandTimeout : commandTimeout));
}
private TSqlStatement BuildDelete(InsertSpecification originalInsert)
{
var delete = new DeleteStatement();
delete.WithCtesAndXmlNamespaces = new WithCtesAndXmlNamespaces();
var cte = new CommonTableExpression();
cte.ExpressionName = new Identifier()
{
Value = "to_delete"
};
cte.QueryExpression = BuildNewRowSource(originalInsert);
delete.WithCtesAndXmlNamespaces.CommonTableExpressions.Add(cte);
delete.DeleteSpecification = new DeleteSpecification();
var tableName = new SchemaObjectName();
tableName.Identifiers.Add( new Identifier()
{
Value = "to_delete"
});
delete.DeleteSpecification.Target = new NamedTableReference() {SchemaObject = tableName };
var outputInto = delete.DeleteSpecification.OutputIntoClause = new OutputIntoClause();
var deletedTable = new MultiPartIdentifier();
deletedTable.Identifiers.Add(new Identifier()
{
Value = "deleted"
});
outputInto.SelectColumns.Add(new SelectStarExpression()
{
Qualifier = deletedTable
});
outputInto.IntoTable = originalInsert.Target;
foreach (var col in originalInsert.Columns)
{
outputInto.IntoTableColumns.Add(col);
}
return delete;
}
private string buildChunkedDelete(DeleteSpecification delete)
{
var counter = new DeclareVariableStatement();
var counterVariable = new DeclareVariableElement();
counterVariable.DataType = new SqlDataTypeReference() {SqlDataTypeOption = SqlDataTypeOption.Int};
counterVariable.VariableName = new Identifier() {Value = "@rowcount"};
counterVariable.Value = new IntegerLiteral() {Value = "10000"};
counter.Declarations.Add(counterVariable);
delete.TopRowFilter = new TopRowFilter();
delete.TopRowFilter.Expression = new ParenthesisExpression() {Expression = new IntegerLiteral() {Value = "10000"} };
var setCounter = new SetVariableStatement();
setCounter.Variable = new VariableReference() {Name = "@rowcount"};
setCounter.Expression = new GlobalVariableExpression() {Name = "@@rowcount"};
setCounter.AssignmentKind = AssignmentKind.Equals;
var deleteStatement = new DeleteStatement();
deleteStatement.DeleteSpecification = delete;
var beginEnd = new BeginEndBlockStatement();
beginEnd.StatementList = new StatementList();
beginEnd.StatementList.Statements.Add(deleteStatement);
beginEnd.StatementList.Statements.Add(setCounter);
var whilePredicate = new BooleanComparisonExpression();
whilePredicate.ComparisonType = BooleanComparisonType.GreaterThan;
whilePredicate.FirstExpression = new VariableReference() {Name = "@rowcount"};
whilePredicate.SecondExpression = new IntegerLiteral() {Value = "0"};
var whileStatement = new WhileStatement();
whileStatement.Predicate = whilePredicate;
whileStatement.Statement = beginEnd;
var text = ScriptDom.GenerateTSql(counter) + "\r\n" + ScriptDom.GenerateTSql(whileStatement);
return text;
}
public override void ExplicitVisit(DeleteStatement fragment)
{
_fragments.Add(fragment);
}
DeleteStatement ParseDeleteStatement()
{
DeleteStatement ss = new DeleteStatement();
ReadNextToken(); // skip 'delete'
SkipExpected("FROM");
ss.TableName = fCurrentToken.Value;
if (fCurrentToken == null)
return ss;
if (fCurrentToken.Equals("WHERE"))
{
ReadNextToken(); // skip 'where'
ss.Where = ParseExpression();
}
skipEmptyStatements();
return ss;
}
public DeleteTopFromTable(DeleteStatement statement, Expression top, Table table)
: base(statement)
{
this.Top = top;
this.Table = table;
}
// DeleteStatement
protected internal virtual bool Walk(DeleteStatement node) { return true; }
public override void Visit(DeleteStatement node) { this.action(node); }
public override void ExplicitVisit(DeleteStatement node)
{
CommandStatistics.DeletesCount++;
base.ExplicitVisit(node);
}
protected internal virtual void PostWalk(DeleteStatement node) { }
public override void ExplicitVisit(DeleteStatement node)
{
DeleteStatements.Add(node);
base.ExplicitVisit(node);
}
