Whilst there are always arguments for being able to delete records from a database, I have always preferred to InActivate my records as opposed to deleting them thus maintaining the referential integrity, history etc. With that in mind I thought I would pen a really quick post about how to do this using LINQ and VB.NET hopefully writing as little code as possible.
Consider the following (incredibly simplistic) Countries database table:
|Column Name||Data Type||Details|
|ID||INT||Primary Key, Identity|
|Active||BIT||Not Null, Default 1|
Using Visual Studio 2008 we create a standard ASP.NET 3.5 web project called Example and add a Linq To SQL class as shown below:
Using the Server Explorer we simply drag the Countries table onto the design surface of the Example.dbml file as shown below:
We save that file and modify the Default.aspx file to include a simple Gridview and accompanying LinqDataSource:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<title>JOATIT - Example</title>
<form id="frm" runat="server">
<asp:GridView ID="grvRecords" runat="server" AutoGenerateColumns="False"
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:CheckBoxField DataField="Active" HeaderText="Active"
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True"
<asp:LinqDataSource ID="ldsRecords" runat="server"
ContextTypeName="ExampleDataContext" EnableDelete="True" OrderBy="Name"
If we run this as it is the GridView will bind as shown below, but the Delete link will ACTUALLY delete the records from the database which is not what we want:
Instead what we want to is InActivate them when the Delete event is fired. To do this we simply open the Default.aspx.vb code-behind file and add the following custom event as shown below:
Protected Sub ldsRecords_Deleting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceDeleteEventArgs) Handles ldsRecords.Deleting
Dim _recordToBeDeleted As Country = CType(e.OriginalObject, Country)
Dim _db As New ExampleDataContext
Dim _specificRecord = (From _allRecords In _db.Countries _
Where _allRecords.ID = _recordToBeDeleted.ID _
_specificRecord.Active = False
e.Cancel = True
Now when we click the Delete link, our custom event fire and updates the record in question setting Active = 0, and cancels the Delete event.
More than happy to learn of a better way of doing this other than with a database trigger (which has its own advantages and disadvantages) so please, feedback (positive or negative) is always welcome.