To enable deleting, click the quick tag (the little ">" icon on the corner of the widget on the aspx page) on the LinqDataSource and choose Enable Delete. Then click the quick tag of the DetailsView and choose Enable Deleting. A new row will be added to the DetailsView containing a link for deleting.
That is all well and good, and for a very simple database schema, that will be sufficient for deletes to work correctly. The LinqDataSource will use the primary key of the record to delete the record from the database: behind the scenes it generates SQL that is something like "DELETE FROM recordtable WHERE id=@id". This will fail, however, if the record has child records in another table, because the foreign key constraint on the other table will prevent the original record from being deleted. For better or worse, Linq swallows the error and fails silently.
The LinqDataSource does not have a straightforward way to customize the DELETE command, although other data sources such as SqlDataSource do have that. I wanted to use LinqDataSource, and after trying many solutions, I found one that worked: write a simple stored procedure to do the delete, and call that.
- Create a stored procedure in the database for deleting the record. As an example, it might look like this:
CREATE PROCEDURE [dbo].[DELETE_RECORD]
DELETE FROM RECORDCHILDTABLE WHERE RECORDID = @RECORD_ID
DELETE FROM RECORDTABLE WHERE ID = @RECORD_ID
You can see that I simply delete the child records first, then delete the master record. My schema is still pretty simple, having only this one relationship to worry about. A more complicated schema would require a more complicated stored procedure.
- Open the .dbml data context file, and find the stored procedure in the Server Explorer pane. Drag the stored procedure from the Server Explorer to the data context pane (the main part of the window displaying the tables in your .dbml file) and it appears in that pane, in a split pane. The stored procedure is now part of the data context.
- In the properties for the main record table (not the child record table), change the Delete property from Use Runtime to use the new stored procedure.
- In the properties pane, click on the "..." button next to the value for the Delete property, and a Configure Behavior window will open.
- Select your object class and choose "Delete" from the Behavior dropdown menu.
- Select the Customize radio button, select the stored procedure from the dropdown menu, and choose the primary key of the record as a method parameter.
- The LINQ TO SQL data context now knows to use the stored procedure when trying to delete that record. The LinqDataSource in the .aspx page will follow this behavior, and should now safely and successfully delete the records.
I have a personal bias against stored procedures because I prefer to implement data layers as part of the application, not as part of the database. Thus, this is a less optimal solution than to teach LINQ itself how to delete the record. Nevertheless, it's pretty good, and it works.
Post a Comment