public void ExecuteDeleteTest()
{
int? id = null;
ISqlObject source = new SqlServerSource(TABLE_NAME);
DbConnection connection = new SqlConnection(Properties.Settings.Default.SqlConnectionString);
try {
ISqlSelectStatement identityStatement = new SqlServerSelectStatement();
identityStatement.SelectClause.AddExpressions(new SqlFunction("IDENT_CURRENT").AddArgument(new SqlServerExpressionFactory().ConstantFactory.Create(TABLE_NAME)));
ISqlObject nameField = new SqlServerField(source, "Name", null);
ISqlExpression name = SqlStringExpression.FromParameter(new SqlParameter("@name", "Insert Test"));
ISqlInsertStatement insertStatement = new SqlInsertStatement();
insertStatement.FieldValueClause.SetSource(source).AddField(nameField, name);
id = SqlHelper.ExecuteInsert(connection, insertStatement, identityStatement);
} finally {
if (id.HasValue)
{
ISqlDeleteStatement deleteStatement = new SqlDeleteStatement();
deleteStatement.FromClause.Source = source;
deleteStatement.WhereClause.Condition = new SqlServerField(source, "ID", null).Equal(SqlStringExpression.FromParameter(new SqlParameter("@ID", id.Value)));
SqlHelper.ExecuteDelete(connection, deleteStatement);
}
}
}
protected override void BuildDeleteClause(SqlDeleteStatement deleteStatement)
{
var selectQuery = deleteStatement.SelectQuery;
AppendIndent();
StringBuilder.Append("DELETE");
BuildSkipFirst(selectQuery);
StringBuilder.Append(" FROM ");
ISqlTableSource table;
ISqlTableSource source;
if (deleteStatement.Table != null)
{
table = source = deleteStatement.Table;
}
else
{
table = selectQuery.From.Tables[0];
source = selectQuery.From.Tables[0].Source;
}
var alias = GetTableAlias(table);
BuildPhysicalTable(source, alias);
StringBuilder.AppendLine();
}
private RawData GetRowsForDelete(Dictionary <string, Table> tables, SqlDeleteStatement deleteStatement)
{
var rawData = new RawData(_Command);
var specification = deleteStatement.DeleteSpecification;
if (specification.FromClause != null)
{
rawData.AddTablesFromClause(specification.FromClause, tables);
}
else
{
rawData.AddTable(specification.Target, tables);
}
if (specification.WhereClause != null)
{
rawData.ExecuteWhereClause(specification.WhereClause);
}
if (specification.TopSpecification != null)
{
var rowCount = int.Parse(specification.TopSpecification.Value.Sql);
rawData.RawRowList = rawData.RawRowList.Take(rowCount).ToList( );
}
return(rawData);
}
static Query <int> CreateQuery(
IDataContext dataContext,
string?tableName, string?serverName, string?databaseName, string?schemaName,
Type type)
{
var sqlTable = new SqlTable(dataContext.MappingSchema, type);
if (tableName != null)
{
sqlTable.PhysicalName = tableName;
}
if (serverName != null)
{
sqlTable.Server = serverName;
}
if (databaseName != null)
{
sqlTable.Database = databaseName;
}
if (schemaName != null)
{
sqlTable.Schema = schemaName;
}
var deleteStatement = new SqlDeleteStatement();
deleteStatement.SelectQuery.From.Table(sqlTable);
var ei = new Query <int>(dataContext, null)
{
Queries = { new QueryInfo {
Statement = deleteStatement,
} }
};
var keys = sqlTable.GetKeys(true).Cast <SqlField>().ToList();
if (keys.Count == 0)
{
throw new LinqException($"Table '{sqlTable.Name}' does not have primary key.");
}
foreach (var field in keys)
{
var param = GetParameter(type, dataContext, field);
ei.Queries[0].Parameters.Add(param);
deleteStatement.SelectQuery.Where.Field(field).Equal.Expr(param.SqlParameter);
if (field.CanBeNull)
{
deleteStatement.IsParameterDependent = true;
}
}
SetNonQueryQuery(ei);
return(ei);
}
public void SqlStringTest_NoWhere()
{
ISqlObject source = new SqlServerSource("Table");
ISqlDeleteStatement statement = new SqlDeleteStatement();
statement.FromClause.SetSource(source);
Assert.AreEqual <string>(string.Format("{0} {1}", statement.Keyword, statement.FromClause.SqlString), statement.SqlString);
}
protected override void BuildDeleteClause(SqlDeleteStatement deleteStatement)
{
var table = deleteStatement.Table != null ?
(deleteStatement.SelectQuery.From.FindTableSource(deleteStatement.Table) ?? deleteStatement.Table) :
deleteStatement.SelectQuery.From.Tables[0];
AppendIndent().Append("DELETE ");
Convert(StringBuilder, GetTableAlias(table) !, ConvertType.NameToQueryTableAlias);
StringBuilder.AppendLine();
}
public void Execute(Dictionary <string, Table> tables, SqlDeleteStatement deleteStatement)
{
var rawData = GetRowsForDelete(tables, deleteStatement);
foreach (var rows in rawData.RawRowList)
{
var rawRow = rows[0];
rawRow.Table.Rows.Remove(rawRow.Row);
}
}
protected override void BuildDeleteQuery(SqlDeleteStatement deleteStatement)
{
if (deleteStatement.With?.Clauses.Count > 0)
{
BuildDeleteQuery2(deleteStatement);
}
else
{
base.BuildDeleteQuery(deleteStatement);
}
}
public void SqlStringTest()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field = new SqlServerField(source, "ID", null);
ISqlDeleteStatement statement = new SqlDeleteStatement();
statement.FromClause.SetSource(source);
statement.WhereClause.Condition = field.GreaterThan(new SqlStringExpression("13"));
Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FromClause.SqlString, statement.WhereClause.SqlString), statement.SqlString);
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var deleteType = methodCall.Method.Name switch
{
nameof(LinqExtensions.DeleteWithOutput) => DeleteContext.DeleteType.DeleteOutput,
nameof(LinqExtensions.DeleteWithOutputInto) => DeleteContext.DeleteType.DeleteOutputInto,
_ => DeleteContext.DeleteType.Delete,
};
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0]));
if (methodCall.Arguments.Count == 2 && deleteType == DeleteContext.DeleteType.Delete)
{
sequence = builder.BuildWhere(buildInfo.Parent, sequence, (LambdaExpression)methodCall.Arguments[1].Unwrap(), false);
}
var deleteStatement = new SqlDeleteStatement(sequence.SelectQuery);
sequence.Statement = deleteStatement;
// Check association.
//
if (sequence is SelectContext ctx && ctx.IsScalar)
{
var res = ctx.IsExpression(null, 0, RequestFor.Association);
if (res.Result)
{
var isTableResult = res.Context !.IsExpression(null, 0, RequestFor.Table);
if (!isTableResult.Result)
{
throw new LinqException("Can not retrieve Table context from association.");
}
var atc = (TableBuilder.TableContext)isTableResult.Context !;
deleteStatement.Table = atc.SqlTable;
}
else
{
res = ctx.IsExpression(null, 0, RequestFor.Table);
if (res.Result && res.Context is TableBuilder.TableContext context)
{
var tc = context;
if (deleteStatement.SelectQuery.From.Tables.Count == 0 || deleteStatement.SelectQuery.From.Tables[0].Source != tc.SelectQuery)
{
deleteStatement.Table = tc.SqlTable;
}
}
}
}
public void SqlStringTest_Parametric()
{
ISqlObject source = new SqlServerSource("Table");
ISqlObject field = new SqlServerField(source, "ID", null);
ISqlExpression value = SqlStringExpression.FromParameter(new SqlParameter("@p", 13));
ISqlDeleteStatement statement = new SqlDeleteStatement();
statement.FromClause.SetSource(source);
statement.WhereClause.Condition = field.GreaterThan(value);
Assert.AreEqual <string>(string.Format("{0} {1} {2}", statement.Keyword, statement.FromClause.SqlString, statement.WhereClause.SqlString), statement.SqlString);
ReadOnlyCollection <IDataParameter> parameters = statement.Parameters;
Assert.AreEqual <int>(1, parameters.Count);
Assert.IsTrue(parameters.Contains(value.Parameters[0]));
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0]));
if (methodCall.Arguments.Count == 2)
{
sequence = builder.BuildWhere(buildInfo.Parent, sequence, (LambdaExpression)methodCall.Arguments[1].Unwrap(), false);
}
var deleteStatement = new SqlDeleteStatement(sequence.SelectQuery);
sequence.Statement = deleteStatement;
// Check association.
//
if (sequence is SelectContext ctx && ctx.IsScalar)
{
var res = ctx.IsExpression(null, 0, RequestFor.Association);
if (res.Result && res.Context is TableBuilder.AssociatedTableContext)
{
var atc = (TableBuilder.AssociatedTableContext)res.Context;
deleteStatement.Table = atc.SqlTable;
}
else
{
res = ctx.IsExpression(null, 0, RequestFor.Table);
if (res.Result && res.Context is TableBuilder.TableContext)
{
var tc = (TableBuilder.TableContext)res.Context;
if (deleteStatement.SelectQuery.From.Tables.Count == 0 || deleteStatement.SelectQuery.From.Tables[0].Source != tc.SelectQuery)
{
deleteStatement.Table = tc.SqlTable;
}
}
}
}
return(new DeleteContext(buildInfo.Parent, sequence));
}
protected virtual void BuildOutputSubclause(SqlDeleteStatement deleteStatement)
{
var output = deleteStatement.GetOutputClause();
BuildOutputSubclause(output);
}
protected override void BuildDeleteQuery(SqlDeleteStatement deleteStatement) {
if (deleteStatement.With != null) {
throw new NotSupportedException("iSeries doesn't support Cte in Delete statement");
}
base.BuildDeleteQuery(deleteStatement);
}
public override void Visit(SqlDeleteStatement codeObject)
{
}
protected override void BuildOutputSubclause(SqlDeleteStatement deleteStatement)
{
// OUTPUT clause is only supported by the MS SQL Server starts with 2005 version.
}
protected override IBuildContext BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
{
var deleteType = methodCall.Method.Name switch
{
nameof(LinqExtensions.DeleteWithOutput) => DeleteContext.DeleteType.DeleteOutput,
nameof(LinqExtensions.DeleteWithOutputInto) => DeleteContext.DeleteType.DeleteOutputInto,
_ => DeleteContext.DeleteType.Delete,
};
var sequence = builder.BuildSequence(new BuildInfo(buildInfo, methodCall.Arguments[0]));
if (methodCall.Arguments.Count == 2 && deleteType == DeleteContext.DeleteType.Delete)
{
sequence = builder.BuildWhere(buildInfo.Parent, sequence, (LambdaExpression)methodCall.Arguments[1].Unwrap(), false);
}
var deleteStatement = new SqlDeleteStatement(sequence.SelectQuery);
sequence.Statement = deleteStatement;
// Check association.
//
if (sequence is SelectContext ctx && ctx.IsScalar)
{
var res = ctx.IsExpression(null, 0, RequestFor.Association);
if (res.Result)
{
var isTableResult = res.Context !.IsExpression(null, 0, RequestFor.Table);
if (!isTableResult.Result)
{
throw new LinqException("Can not retrieve Table context from association.");
}
var atc = (TableBuilder.TableContext)isTableResult.Context !;
deleteStatement.Table = atc.SqlTable;
}
else
{
res = ctx.IsExpression(null, 0, RequestFor.Table);
if (res.Result && res.Context is TableBuilder.TableContext context)
{
var tc = context;
if (deleteStatement.SelectQuery.From.Tables.Count == 0 || deleteStatement.SelectQuery.From.Tables[0].Source != tc.SelectQuery)
{
deleteStatement.Table = tc.SqlTable;
}
}
}
}
var indexedParameters
= methodCall.Method.GetParameters().Select((p, i) => Tuple.Create(p, i)).ToDictionary(t => t.Item1.Name, t => t.Item2);
Expression GetArgumentByName(string name)
{
return(methodCall.Arguments[indexedParameters[name]]);
}
LambdaExpression GetOutputExpression(Type outputType)
{
if (!indexedParameters.TryGetValue("outputExpression", out var index))
{
var param = Expression.Parameter(outputType);
return(Expression.Lambda(param, param));
}
return((LambdaExpression)methodCall.Arguments[index].Unwrap());
}
IBuildContext? outputContext = null;
LambdaExpression?outputExpression = null;
if (deleteType != DeleteContext.DeleteType.Delete)
{
outputExpression = GetOutputExpression(methodCall.Method.GetGenericArguments().Last());
deleteStatement.Output = new SqlOutputClause();
var deletedTable = SqlTable.Deleted(methodCall.Method.GetGenericArguments()[0]);
outputContext = new TableBuilder.TableContext(builder, new SelectQuery(), deletedTable);
deleteStatement.Output.DeletedTable = deletedTable;
if (deleteType == DeleteContext.DeleteType.DeleteOutputInto)
{
var outputTable = GetArgumentByName("outputTable");
var destination = builder.BuildSequence(new BuildInfo(buildInfo, outputTable, new SelectQuery()));
UpdateBuilder.BuildSetter(
builder,
buildInfo,
outputExpression,
destination,
deleteStatement.Output.OutputItems,
outputContext);
deleteStatement.Output.OutputTable = ((TableBuilder.TableContext)destination).SqlTable;
}
}
if (deleteType == DeleteContext.DeleteType.DeleteOutput)
{
return(new DeleteWithOutputContext(buildInfo.Parent, sequence, outputContext !, outputExpression !));
}
return(new DeleteContext(buildInfo.Parent, sequence));
}
public virtual void Visiting(SqlDeleteStatement deleteStatement)
{
}
