Hi,

I have a a BO with related records and at some point I would like to delete all records in the BO pretty much like a VFP DELETE ALL or ZAP, how to do this?

I have tried using the BO's GetEnumerable() I saw in thread like this:

But the above code is not deleting all rows in the BO, so I must be missing something here.

Edhy Rijo

I had to do this and with many records in the BO it was slow (ZAP was awesome!!) so I did the delete via a stored procedure on the server (if your using SQL/DB2/Oracle/etc).

MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteStoredProcedure("rentitems_deleteitemsfororder", MicroFour.StrataFrame.Data.DbCommandExecutionType.ExecuteNonQuery, New SqlClient.SqlParameter("@ordnum", Me.BO_Orders.ordernum))

Keith Chisarik

You can do this with SQL Server, DB2, etc.  Just call a truncate on a table.  Create a method on the BO that is called "DeleteAll" or something along those lines, then TRUNCATE the table.  This is akin to the VFP "ZAP" except far more efficient.

Note: This will delete ALL records within the table.  That is the same thing a ZAP used to do.  If you want to delete within a certain crtiera, then just create a command or a sproc (as Keith mentioned) to delete within that group or records.

Great to know about TRUNCATE. Thanks.

Keith Chisarik

Trent, Keith,

Thanks for the recommendations, I am sure will use them.

In this case I am dealing with a few records, less than 12, and since I already have a BO with those 12 records shown on a list, I was looking for an easy way to get rid of those 12 records in a single step. 

I could implement any of your suggestions by creating a method in the BO to delete those records using the Forein key of the parent, but wanted to know if there was a better way to handle this.

Edhy Rijo

You can't change the contents of an enumeration...that is just .NET.  The reason you don't flat out get an exception when using the GetEnumerable is because the GetEnumerable returns unique instances of the BO...so even though you are changing the collection it is somewhat wrapped...but you will never get the records deleted.  But you can delete a lot of different ways, create an array of the PKs to delete and then enumerate that collection, create a shared method to delete for you, etc.  At some point we may add another overload to the BO that allows you to pass a "WhereClause" like the Seek method for a delete.  But for now those are some options.

Be careful with using Truncate.  It is handled differently in Log Operations.   If the table has a FOREIGN KEY constraint you cannot use truncate.  In addition, no Triggers will be fired as it is a non-logged operation.   I generally frown on the use of Truncate in any operational code for those two reasons.

Richard

We never use TRUNCATE in code either...except when the table truly needs to be cleared out and reset, and which time the code should accomodate this.  The context of this post is more in regards to the replacement method of ZAP in FoxPro for a similar method in SQL Server.  This would be the method in this case.

Thanks to all for the posting.

I decided to use the following code in this particular case...

This is very fast and will only do one trip to the database to remove all deleted records.

Edhy Rijo

That is a good solution.  You could also just do a while as well: