LINQ – InActivating Records As Opposed to Deleting Them

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:

linq-inactivate-01

Using the Server Explorer we simply drag the Countries table onto the design surface of the Example.dbml file as shown below:

linq-inactivate-02

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:

linq-inactivate-03

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.

Advertisements

ASP.NET Duration UserControl

Recently, I had need for a “duration control” that would allow users to view, edit and update a duration in hours, minutes and seconds. The result of the user selection needed to be stored as an integer in a database (as seconds) and then converted back to hour, minutes and seconds for display. In read only mode the duration needed to be displayed as a single label whilst in edit mode I wanted a reasonably foolproof interface for my users.

I decided to create a ASP.NET UserControl that consisted of, in edit mode, a textbox for the hours, and two dropdownlists for the minutes and seconds, whilst in read only mode, a label as shown below:

Read-Only Mode (used on a ASPX page):

Duration Control - View

Duration Control - View

Edit Mode (used on a ASPX page):

Duration Control - Edit

Duration Control - Edit

The code for this usercontrol is shown below:

HTML

<%@ Control Language="VB" AutoEventWireup="false" CodeFile="ctrlDuration.ascx.vb" Inherits="UserControls_ctrlDuration" %>
<asp:Panel ID="pnlDisplay" runat="server">
<asp:Label ID="lblDuration" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="pnlEdit" runat="server">
Hours:
<asp:TextBox ID="txtHours" runat="server" Text="00"></asp:TextBox>
Minutes:
<asp:DropDownList ID="ddlMinutes" runat="server">
<asp:ListItem Selected="True">0</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem>31</asp:ListItem>
<asp:ListItem>32</asp:ListItem>
<asp:ListItem>33</asp:ListItem>
<asp:ListItem>34</asp:ListItem>
<asp:ListItem>35</asp:ListItem>
<asp:ListItem>36</asp:ListItem>
<asp:ListItem>37</asp:ListItem>
<asp:ListItem>38</asp:ListItem>
<asp:ListItem>39</asp:ListItem>
<asp:ListItem>40</asp:ListItem>
<asp:ListItem>41</asp:ListItem>
<asp:ListItem>42</asp:ListItem>
<asp:ListItem>43</asp:ListItem>
<asp:ListItem>44</asp:ListItem>
<asp:ListItem>45</asp:ListItem>
<asp:ListItem>46</asp:ListItem>
<asp:ListItem>47</asp:ListItem>
<asp:ListItem>48</asp:ListItem>
<asp:ListItem>49</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>51</asp:ListItem>
<asp:ListItem>52</asp:ListItem>
<asp:ListItem>53</asp:ListItem>
<asp:ListItem>54</asp:ListItem>
<asp:ListItem>55</asp:ListItem>
<asp:ListItem>56</asp:ListItem>
<asp:ListItem>57</asp:ListItem>
<asp:ListItem>58</asp:ListItem>
<asp:ListItem>59</asp:ListItem>
</asp:DropDownList>
Seconds:
<asp:DropDownList ID="ddlSeconds" runat="server">
<asp:ListItem Selected="True">0</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>11</asp:ListItem>
<asp:ListItem>12</asp:ListItem>
<asp:ListItem>13</asp:ListItem>
<asp:ListItem>14</asp:ListItem>
<asp:ListItem>15</asp:ListItem>
<asp:ListItem>16</asp:ListItem>
<asp:ListItem>17</asp:ListItem>
<asp:ListItem>18</asp:ListItem>
<asp:ListItem>19</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>21</asp:ListItem>
<asp:ListItem>22</asp:ListItem>
<asp:ListItem>23</asp:ListItem>
<asp:ListItem>24</asp:ListItem>
<asp:ListItem>25</asp:ListItem>
<asp:ListItem>26</asp:ListItem>
<asp:ListItem>27</asp:ListItem>
<asp:ListItem>28</asp:ListItem>
<asp:ListItem>29</asp:ListItem>
<asp:ListItem>30</asp:ListItem>
<asp:ListItem>31</asp:ListItem>
<asp:ListItem>32</asp:ListItem>
<asp:ListItem>33</asp:ListItem>
<asp:ListItem>34</asp:ListItem>
<asp:ListItem>35</asp:ListItem>
<asp:ListItem>36</asp:ListItem>
<asp:ListItem>37</asp:ListItem>
<asp:ListItem>38</asp:ListItem>
<asp:ListItem>39</asp:ListItem>
<asp:ListItem>40</asp:ListItem>
<asp:ListItem>41</asp:ListItem>
<asp:ListItem>42</asp:ListItem>
<asp:ListItem>43</asp:ListItem>
<asp:ListItem>44</asp:ListItem>
<asp:ListItem>45</asp:ListItem>
<asp:ListItem>46</asp:ListItem>
<asp:ListItem>47</asp:ListItem>
<asp:ListItem>48</asp:ListItem>
<asp:ListItem>49</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>51</asp:ListItem>
<asp:ListItem>52</asp:ListItem>
<asp:ListItem>53</asp:ListItem>
<asp:ListItem>54</asp:ListItem>
<asp:ListItem>55</asp:ListItem>
<asp:ListItem>56</asp:ListItem>
<asp:ListItem>57</asp:ListItem>
<asp:ListItem>58</asp:ListItem>
<asp:ListItem>59</asp:ListItem>
</asp:DropDownList>
<asp:CompareValidator ID="cvlDuration" runat="server"
ErrorMessage="Hours must be a whole number" ControlToValidate="txtHours"
Operator="GreaterThanEqual" Type="Integer" ValueToCompare="0">*</asp:CompareValidator>
</asp:Panel>

Code-Behind

Partial Class UserControls_ctrlDuration
Inherits System.Web.UI.UserControl
Public WriteOnly Property DisplayOnly() As Boolean
Set(ByVal value As Boolean)
If value Then
pnlDisplay.Visible = True
pnlEdit.Visible = False
Else
pnlDisplay.Visible = False
pnlEdit.Visible = True
End If
End Set
End Property
Public Property Duration() As Integer
Get
Return CInt(txtHours.Text * 3600) + CInt(ddlMinutes.SelectedValue * 60) + CInt(ddlSeconds.SelectedValue)
End Get
Set(ByVal value As Integer)
txtHours.Text = (value 3600)
ddlMinutes.SelectedValue = ((value 60) Mod 60)
ddlSeconds.SelectedValue = (value Mod 60)
lblDuration.Text = (value 3600) & " hours " & ((value 60) Mod 60) & " minutes " & (value Mod 60) & " seconds"
End Set
End Property
End Class

Then in the ASPX page that makes use of this usercontrol we need to register it:

<%@ Register src="ctrlDuration.ascx" tagname="ctrlDuration" tagprefix="uc1" %>

Finally on the page we can use it inside a formview control for example as shown below (omitting all the other datasource controls etc.):

In the ItemTemplate section we would use it in read only mode:

<uc1:ctrlDuration ID="ctrlResponse" runat="server" Duration='<%# Bind("iResponseDuration") %>' DisplayOnly="True" />

Whilst in the EditItemTemplate and InsertItemTemplate we would use it in edit mode:

<uc1:ctrlDuration ID="ctrlResponse" runat="server" Duration='<%# Bind("iResponseDuration") %>' DisplayOnly="False" />

Hope it may be of use. As always comments (positive and negative) always welcome.