需求:删除某一条ProductType记录,同时将所有相关的Product记录也删除掉
ProductType:Product -> 1:N relationship
如:删除家具类型,并且把相关的产品全部删除。
产品类型:家具
产品类型为家具的三个产品:
Codes:
/// <summary>
/// 测试级联删除操作:先删除主表数据,再删除子表数据
/// 删除某一条产品类型,并且将所有相关的产品都删除
/// </summary>
public static int TestCascadeOperation(ProductType productType) {
int lines1 = 0;
int lines2 = 0;
int typeId = productType.TypeId;
SqlConnection connect = null;
IDbTransaction transaction = null;
string sqlProductTypeDeleted = @"DELETE FROM ProductType WHERE TypeId=@TypeId";
string sqlProductDeleted = @"DELETE FROM Product WHERE TypeId=@TypeId";
try
{
connect = new SqlConnection(ConnectStr);
connect.Open();//打开连接
transaction = connect.BeginTransaction();//开启事务控制
lines1 = connect.Execute(sqlProductTypeDeleted, new { TypeId = typeId},transaction,null,null);
lines2 = connect.Execute(sqlProductDeleted, new { TypeId = typeId },transaction,null,null);
transaction.Commit();//提交事务
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
transaction.Rollback();//回滚事务
}
finally {
if (connect!=null) {
connect.Close();//关闭连接
}
if (transaction!=null) {
transaction.Dispose();//释放资源
}
}
return lines1 + lines2;
}
测试代码:
ProductType pt = new ProductType() {TypeId=16,TypeName= "家具" };
int lines=TestCascadeOperation(pt);
Product记录已被删除:
相关的Product数据已被删除: