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.
Solution
Consider the following (incredibly simplistic) Countries database table:
| Column Name | Data Type | Details |
|---|---|---|
| ID | INT | Primary Key, Identity |
| Active | BIT | Not Null, Default 1 |
| Name | NVARCHAR(50) | Not Null |
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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>JOATIT - Example</title>
</head>
<body>
<form id="frm" runat="server">
<div>
<asp:GridView ID="grvRecords" runat="server" AutoGenerateColumns="False"
DataKeyNames="ID" DataSourceID="ldsRecords">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False"
ReadOnly="True" SortExpression="ID" />
<asp:CheckBoxField DataField="Active" HeaderText="Active"
SortExpression="Active" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:CommandField HeaderText="Delete" ShowDeleteButton="True"
ShowHeader="True" />
</Columns>
</asp:GridView>
<asp:LinqDataSource ID="ldsRecords" runat="server"
ContextTypeName="ExampleDataContext" EnableDelete="True" OrderBy="Name"
TableName="Countries">
</asp:LinqDataSource>
</div>
</form>
</body>
</html>
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 _
Select _allRecords).Single
_specificRecord.Active = False
_db.SubmitChanges()
e.Cancel = True
End Sub
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.
Summary
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.
JOATIT 