private void RepackDeleteSQL(DeleteBuilder delete)
{
var table = delete.Table;
var where = delete.Where;
HandlerInFilterWhere(where);
//添加有效期验证
FilterGroup validFilter = new FilterGroup(Conjunction.And,
new LessThanEqualToFilter(table.Column(FapDbConstants.FAPCOLUMN_FIELD_EnableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new GreaterThanEqualToFilter(table.Column(FapDbConstants.FAPCOLUMN_FIELD_DisableDate), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_CurrentDate)),
new EqualToFilter(table.Column(FapDbConstants.FAPCOLUMN_FIELD_Dr), new ParameterLiteral(FapDbConstants.FAPCOLUMN_PARAM_Dr)));
delete.AddWhere(validFilter);
}
/// <summary>
/// 执行批量删除
/// </summary>
/// <param name="domains">需要删除的domain</param>
/// <param name="whereSql">where sql 子句</param>
/// <param name="colFilter">where 列</param>
/// <returns></returns>
public virtual int BatchDelete(List <TDomain> domains, string whereSql, params Expression <Func <TDomain, object> >[] colFilter)
{
if (domains.Count == 0)
{
return(0);
}
BatchOracleHelper oracleHelper = new BatchOracleHelper(QuerySession);
Table table = GetTable(this.DefaultColumnMapperStrategy);
DeleteBuilder <TDomain> builder = new DeleteBuilder <TDomain>(table, QuerySession.DatabaseInfo.SqlDialect);
var sql = builder.CreateSql() + "where " + whereSql;
var columnRowData = GetData(domains, colFilter);
return(oracleHelper.BatchInsertOrUpdate(sql, columnRowData));
}
public void Query_DeleteItemById()
{
var query = DeleteBuilder.Make()
.Table("Table")
.Where("Id = @id")
.Parameter("id", 1)
.Query();
var ethalon = @"DELETE [Table]
WHERE Id = @id
";
Assert.AreEqual(ethalon, query.Text);
Assert.AreEqual(1, query.Parameters.Count());
}
private void visitDelete(DeleteBuilder item)
{
writer.Write("DELETE ");
if (options.VerboseDeleteStatement)
{
writer.Write("FROM ");
}
forSourceContext(SourceReferenceType.Declaration).visitAliasedSource(item.Table);
if (item.WhereFilterGroup.HasFilters)
{
writer.Write(" WHERE ");
IFilter filterGroup = item.WhereFilterGroup;
filterGroup.Accept(forSubCommand().forValueContext(ValueReferenceType.Reference));
}
}
public void ShouldBeBuildable()
{
//Given
var expected =
@"DELETE FROM flarktown
WHERE foo_bar = @toadstool_1";
//When
var actual = new DeleteBuilder("flarktown")
.Where("foo_bar").EqualTo("goodbye")
.Build();
//Then
Assert.NotNull(actual);
Assert.Equal(expected, actual);
}
public int BatchDelete(List <TDomain> domains, string where, params Expression <Func <TDomain, object> >[] cols)
{
if (domains.Count == 0)
{
return(0);;
}
BatchMySqlHelper mySqlHelper = new BatchMySqlHelper(QuerySession);
string[] usedProperties = cols.Select(expression => expression.PropertyName()).ToArray();
Table table = GetTable(this.DefaultColumnMapperStrategy);
DeleteBuilder <TDomain> deleteBuilder = new DeleteBuilder <TDomain>(table, QuerySession.DatabaseInfo.SqlDialect);
var sql = deleteBuilder.GetSql() + "where " + where;
var columnRowData = GetData(domains, cols);
return(mySqlHelper.BatchInsertOrUpdate(sql, columnRowData));
}
public void ShouldBeBuildable2()
{
//Given
var expected =
@"DELETE FROM flarktown
WHERE foo_bar = @normal_1
AND bar_bat > @normal_2";
//When
var actual = new DeleteBuilder("flarktown")
.Where("foo_bar").EqualTo("goodbye")
.And("bar_bat").GreaterThan(0)
.Build();
//Then
Assert.NotNull(actual);
Assert.Equal(expected, actual);
}
public void Create_Without_Primary_Key_Throws_Exception()
{
var schema = new SchemaBuilder()
.Define <NoPrimaryKey>()
.Build();
var entity = new NoPrimaryKey();
var threw = false;
try
{
var deleteQuery = DeleteBuilder <NoPrimaryKey> .Create(schema, entity);
}
catch
{
threw = true;
}
Assert.IsTrue(threw);
}
protected override void VisitDelete(DeleteBuilder item)
{
GuardDeleteBuilder(item);
item.Table.Source.Accept(this);
int whereCount = 0;
foreach (IVisitableBuilder where in item.Where)
{
where.Accept(this);
whereCount++;
}
if (whereCount != 1)
{
throw new ArgumentException("The delete statement should have a single filter in the where clause, which should specify the entity id of the record to be deleted.");
}
if (EqualToFilter == null)
{
throw new NotSupportedException("The delete statement has an unsupported filter in it's where clause. The where clause should contain a single 'equal to' filter that specifies the entity id of the particular record to delete.");
}
if (IdFilterColumn == null)
{
throw new NotSupportedException("The delete statement has an unsupported filter in it's where clause. The'equal to' filter should specify the entity id column on one side.");
}
var idAttName = GetColumnLogicalAttributeName(IdFilterColumn);
var expectedIdAttributeName = string.Format("{0}id", EntityName.ToLower());
if (idAttName != expectedIdAttributeName)
{
throw new NotSupportedException("The delete statement has an unsupported filter in it's where clause. The'equal to' filter should specify the id column of the entity on one side.");
}
EntityReference entRef = new EntityReference();
entRef.LogicalName = EntityName;
entRef.Id = DynamicsTypeProvider.GetUniqueIdentifier(IdFilterValue);
CurrentRequest.Target = entRef;
}
public void Create_With_Primary_Key_Returns_Query()
{
var schema = new SchemaBuilder()
.Define <HasPrimaryKey>()
.Build();
var entity = new HasPrimaryKey {
Id = 1
};
var deleteQuery = DeleteBuilder <HasPrimaryKey> .Create(schema, entity).BuildQuery() as DeleteExpression;
Assert.IsNotNull(deleteQuery);
Assert.AreEqual("HasPrimaryKey", deleteQuery.Table.TableName);
var whereCondition = deleteQuery.WhereConditions as ComparisonExpression;
Assert.AreEqual(ComparisonOperator.AreEqual, whereCondition.Operator);
var field = whereCondition.Left as ColumnExpression;
var value = whereCondition.Right as ValueExpression;
Assert.AreEqual("Id", field.ColumnName);
Assert.AreEqual(entity.Id, value.Value);
}
public void ExcuteDeleteCommand <B>(B aEntity, string con) where B : EntityBase, new()
{
SqlConnection Connection = new SqlConnection(con);
SqlCommand Command = new SqlCommand();
Command.Connection = Connection;
var l_QueryBuilder = new DeleteBuilder <B>(aEntity);
QueryDirector <B> l_Construct = new QueryDirector <B>(l_QueryBuilder);
var l_Context = l_Construct.GetDataContext();
Command.CommandText = l_Context.mSQL;
foreach (var l_SQLParameters in l_Context.mSQLParameters)
{
Command.Parameters.Add(l_SQLParameters);
}
try
{
Connection.Open();
Command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
Connection.Close();
}
}
public IEntityDeleteQueryBuilder <T> Delete(IEntityReference <T> entityReference) => DeleteBuilder <T> .Create(_schema, _entityModel, entityReference);
public IEntityDeleteQueryBuilder <T> Delete(T entity) => DeleteBuilder <T> .Create(_schema, _entityModel, entity);
/// <summary>
/// Updates row changes to database. Adds new rows, updates existing rows with changes and deletes rows that are marked to be deleted.
/// If row has any auto id columns defined those values are loaded from database after an insert.
/// When updating rows there are two ways that the row is identified. The first is using the concurrency setting on the table or the global setting 'Settings.UseConcurrenyChecking'. (Note the table overrides the global setting).
/// When this is ture the row is found by comparing all column values. If the row can't be found (i.e. Another process has altered the row) then a concurreny exception is thrown.
/// If Settings.UseConcurrenyChecking == false then the row is found by looking up primary key values. If the row doesn't have a primary key defined an exception is thrown.
///
/// </summary>
/// <param name="transaction"/><param name="discardRowAfterUpdate">If true the row is not updated internally when transaction is committed / rolled back.</param>
public void Update(Transaction transaction, bool discardRowAfterUpdate)
{
if (transaction == null)
{
throw new CooqPreconditionException("transaction cannot be null");
}
if (this.mRowState == Record.RowStateEnum.DeletedAndCommitted)
{
throw new CooqPreconditionException("Row doesn't exist in database. You cannot call update on row that doesn't exist in the database.");
}
if (this.mRowState == Record.RowStateEnum.DeletePerformedNotYetCommitted)
{
throw new CooqPreconditionException("Row has already been deleted from the database and is waiting for transaction to be committed. You cannot update a deleted row more than once within a transaction.");
}
if (this.mRowState == Record.RowStateEnum.AddPerformedNotYetCommitted)
{
throw new CooqPreconditionException("Cannot call Update(...) more than once on a row within the same trasaction");
}
if (!this.mIsInit)
{
this.Init();
}
if (this.mUpdateTransaction == null)
{
this.mUpdateTransaction = transaction;
if (!discardRowAfterUpdate)
{
transaction.CommitEvent += new Transaction.CommitPerformed(this.Transaction_CommitEvent);
transaction.RollbackEvent += new Transaction.RollbackPerformed(this.Transaction_RollbackEvent);
}
}
else if (this.mUpdateTransaction != transaction)
{
throw new CooqDataAccessException("row.Update() has been called more than once on this row with a different transaction instance than before.");
}
if (this.table.Columns.Count == 0)
{
throw new CooqDataAccessException("Table has no fields defined. Cannot update row.");
}
if (this.mRowState == Record.RowStateEnum.AddPending)
{
InsertBuilder insertBuilder = new InsertBuilder(this.table);
List <ColumnBase> list = new List <ColumnBase>();
int index1 = 0;
while (index1 < this.table.Columns.Count)
{
ColumnBase column = this.table.Columns[index1];
if (column.IsAutoId)
{
list.Add(column);
}
else
{
object value = this.mCurrentData[index1];
if (value == Record.NOT_SET)
{
value = (object)null;
}
insertBuilder.SetInternal(column, value);
this.mPersistedData[index1] = value;
}
checked { ++index1; }
}
if (list.Count == 0)
{
insertBuilder.Execute(transaction);
}
else
{
IResult result = insertBuilder.Returning(list.ToArray()).Execute(transaction);
if (list.Count > 1)
{
throw new CooqDataAccessException("Only one auto id field is supported");
}
int index2 = 0;
while (index2 < list.Count)
{
ColumnBase column = list[index2];
int index3 = this.table.Columns.IndexOf(column);
this.mCurrentData[index3] = result.GetRow(column.Table, 0).GetValue(column);
this.mPersistedData[index3] = this.mCurrentData[index3];
checked { ++index2; }
}
}
this.mRowState = Record.RowStateEnum.AddPerformedNotYetCommitted;
}
else if (this.mRowState == Record.RowStateEnum.DeletePending)
{
DeleteBuilder deleteBuilder = new DeleteBuilder(this.table);
Condition condition = null;
bool? concurrenyChecking = this.table.UseConcurrenyChecking;
int num;
if (!concurrenyChecking.HasValue)
{
num = Settings.UseConcurrenyChecking ? 1 : 0;
}
else
{
concurrenyChecking = this.table.UseConcurrenyChecking;
num = concurrenyChecking.Value ? 1 : 0;
}
bool flag1 = num != 0;
bool flag2 = false;
int index = 0;
while (index < this.table.Columns.Count)
{
ColumnBase pLeft = this.table.Columns[index];
if (flag1 || pLeft.IsPrimaryKey)
{
flag2 = true;
object pRight = this.mPersistedData[index];
if (pRight == Record.NOT_SET)
{
pRight = this.rowData[index];
}
if (condition == null)
{
condition = pRight != Record.NOT_SET ? (Condition) new ColumnCondition(pLeft, Operator.EQUALS, pRight) : (Condition) new IsNullCondition((ISelectable)pLeft);
}
else
{
condition &= pRight != Record.NOT_SET ? (Condition) new ColumnCondition(pLeft, Operator.EQUALS, pRight) : (Condition) new IsNullCondition((ISelectable)pLeft);
}
}
checked { ++index; }
}
if (!flag2)
{
throw new CooqDataAccessException("There are no primary keys set on row and use concurrency checking is turned off. Unable to delete.");
}
deleteBuilder.Where(condition);
if (deleteBuilder.Execute(transaction).RowsEffected != 1)
{
throw new CooqDataAccessException("Row not updated. Possible data concurrency issue.");
}
this.mRowState = Record.RowStateEnum.DeletePerformedNotYetCommitted;
}
else
{
bool flag1 = false;
int index1 = 0;
while (index1 < this.rowData.Length)
{
if (this.rowData[index1] is Record.NotSet)
{
throw new CooqDataAccessException("Not all columns were loaded in row. Unable to update.");
}
if ((this.mPersistedData[index1] != null || this.mCurrentData[index1] != null) && (this.mPersistedData[index1] == null && this.mCurrentData[index1] != null || this.mPersistedData[index1] != null && this.mCurrentData[index1] == null || !this.mPersistedData[index1].Equals(this.mCurrentData[index1])))
{
flag1 = true;
break;
}
checked { ++index1; }
}
if (flag1)
{
UpdateBuilder updateBuilder = new UpdateBuilder(this.table);
Condition condition = null;
bool? concurrenyChecking = this.table.UseConcurrenyChecking;
int num;
if (!concurrenyChecking.HasValue)
{
num = Settings.UseConcurrenyChecking ? 1 : 0;
}
else
{
concurrenyChecking = this.table.UseConcurrenyChecking;
num = concurrenyChecking.Value ? 1 : 0;
}
bool flag2 = num != 0;
bool flag3 = false;
int index2 = 0;
while (index2 < this.table.Columns.Count)
{
ColumnBase ColumnBase = this.table.Columns[index2];
if (!ColumnBase.IsAutoId && this.mPersistedData[index2] != this.mCurrentData[index2])
{
updateBuilder.SetInternal(ColumnBase, this.mCurrentData[index2]);
}
if (flag2 || ColumnBase.IsPrimaryKey)
{
flag3 = true;
object pRight = this.mPersistedData[index2];
if (pRight == Record.NOT_SET)
{
pRight = this.rowData[index2];
}
if (condition == null)
{
condition = pRight != null ? new ColumnCondition(ColumnBase, Operator.EQUALS, pRight) : (Condition) new IsNullCondition((ISelectable)ColumnBase);
}
else
{
condition &= pRight != null ? new ColumnCondition(ColumnBase, Operator.EQUALS, pRight) : (Condition) new IsNullCondition((ISelectable)ColumnBase);
}
}
this.mPersistedData[index2] = this.mCurrentData[index2];
checked { ++index2; }
}
if (!flag3)
{
throw new CooqDataAccessException("There are no primary keys set on row and use concurrency checking is turned off. Unable to update.");
}
updateBuilder.Where(condition);
if (updateBuilder.Execute(transaction).RowsEffected != 1)
{
throw new CooqDataAccessException("Row not updated. Possible data concurrency issue.");
}
}
}
}
/// <summary>
/// Generates the text for a Delete builder.
/// </summary>
/// <param name="item">The Delete builder to generate the text for.</param>
protected internal override void VisitDelete(DeleteBuilder item)
{
forCommandType(CommandType.Delete).visitDelete(item);
}
/// <summary> /// 返回 是否有数据受操作影响 /// </summary> public abstract bool Delete <T>(DbSession session, DeleteBuilder deleteBuilder) where T : IPDMTBase, new();
public static DeleteBuilder GetDeleteBuilder(ConnectionConfig currentConnectionConfig)
{
DeleteBuilder result = CreateInstance <DeleteBuilder>(GetClassName(currentConnectionConfig.DbType.ToString(), "DeleteBuilder"));
return(result);
}
internal static string GetDeleteQuery(DatabaseBase database, DeleteBuilder deleteBuilder, Parameters parameters)
{
var builder = GetBuilder(database.DatabaseType);
return(builder.GetDeleteQuery(database, deleteBuilder, parameters));
}
public static void Main(string[] args)
{
var select = new SelectBuilder();
var insert = new InsertBuilder();
var update = new UpdateBuilder();
var delete = new DeleteBuilder();
int?age = null;
int?teste = null;
var selectBuilder =
select.Top(10)
.Distinct()
.Column("ID")
.Column("Name")
.Column("Age")
.From("Table")
.Where("ID").Eq(1).If(() => true)
.And.Where("Age").Gt(age).If(age)
.And.Where("Age").In(1, 2, 3, 4, 5)
.Or.Where("Age").NotIn(1, 2, 5, 6)
.Or.Where("Age").Between(1, 3).If(true)
.Or.Where("Name").Like("%Angelo%")
.And.Where("CreatedDate").Eq(teste)
.Build();
Console.WriteLine(selectBuilder.SQLCommand);
var insertBuilder =
insert.Into("table", "schema")
.Value("ID", 1)
.Value("Age", 20)
.Value("Name", "Angelo")
.Build();
Console.WriteLine(insertBuilder.SQLCommand);
var updateBuilder =
update.Table("Table", "schema")
.Set("ID", 1)
.Set("Age", 1)
.Set("Name", "José")
.Where("ID").Eq(1).If(() => true)
.And.Where("Age").Gt(age).If(age)
.And.Where("Age").In(1, 2, 3, 4, 5)
.Or.Where("Age").NotIn(1, 2, 5, 6)
.Or.Where("Age").Between(1, 3).If(true)
.Or.Where("Name").Like("%Angelo%")
.And.Where("CreatedDate").Eq(teste)
.Build();
Console.WriteLine(updateBuilder.SQLCommand);
var deleteBuilder =
delete.Table("Table")
.Where("ID").Eq(1).If(() => true)
.And.Where("Age").Gt(age).If(age)
.And.Where("Age").In(1, 2, 3, 4, 5)
.Or.Where("Age").NotIn(1, 2, 5, 6)
.Or.Where("Age").Between(1, 3).If(true)
.Or.Where("Name").Like("%Angelo%")
.And.Where("CreatedDate").Eq(teste)
.Build();
Console.WriteLine(deleteBuilder.SQLCommand);
Console.ReadKey();
}