How to auto-build custom commands in a DataSet based on non-"DELETE" deletion method?

Question

I am using DataSets for access to Sql Server 200x in a C# project. Our common practice is, in almost all tables, to not delete the record. Instead we have a field which simply holds a bit for whether the record is deleted. I can manually edit each table in the DataSet and make their select command include Where Deleted = 0 and the delete command be an update instead. However, this is tedious.

Is there any way to change the method that VS uses to generate the commands for the tableadaptor to add this functionality for them automatically?

Edit: In effect, this would be some sort of way to customize the GenerateDBDirectMethods functionality.

Answer 1

You could access your data through database views and do your filtering there.

Personally I do not like SQL commmands in DataSet definitions.

EDIT:

There is not a built-in possibility to do this AFAIK. You could make an VS add-in and invoke it on a context menu from Solution Explorer (right click on .xsd) or open DS and invoke add-in from main menu. The add in would then parse xsd and identify select and update text and make corrections. Probably this would be useful only right after generating the DS (drag drop from sql server).

For add.in look at:

• Creating a Visual Studio.NET add-in to write SQL statements
• Writing a Visual Studio 2005 & 2008 Add-In
• Tutorial : Creating Visual Studio Add-Ins

I personally would go the DB way. Make views that do the filtering for you and add delete triggers on those views that would perform an update instead of delete. This way you are removing the burden to do that on application and make this the responsibility of the person that makes a physical DB model. This would be done once per table. On the contrary you probably have multiple points in code that deal with single table (multiple datasets that use the same db table).

Answer 2

I don't think you will be able to auto generate this using something like the SqlCommandBuilder.

Instead what you may be looking to do is utilize the fact that the strongly typed datasets and data adapters in the dataset auto generated code use partial classes and you should therefore be able to add some additional logic to a partial class which will override the default implementation.

For example you should be able to override the InitCommandCollection method on the generated data table adapter which you could then substitute in your Update isDeleted = 1, instead of the delete command.

You will have to write some smarts around this but it should help you. I would recommend having the table adapter check a list to see if the table name is within the list, if it is then you know you are dealing with a deleted column, if not then it's a deletable table record.

Answer 3

I would look at using a tool like CodeSmith and creating a custom template to create your Datasets rather then using the "drag + drop" designer. Creating a template takes longer in the first place, but gives your control and repeatability over what you create.

Otherwise look at one of the better ORM systems and customise it’s mapping to do what you want.