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

Service Desk Express QuickViews Don’t Return Any Data

Recently I came across an unusual experience with Service Desk Express 9.8 where my QuickViews suddenly stopped returning any records. So following typical fault finding principles I considered what I had changed and all I had done was install a web application at the root of the same website containing the SDE virtual directory. The rest of Service Desk Express (and for that matter, everything else on the server ) all worked absolutely fine but the QuickViews simply returned “Retrieving data, please wait…”

I would love to say that I identified the root cause and the resolution but alas a gifted colleague did all that. Suffice to say, the issue was caused by the fact that the Service Desk Express web.config file failed to contain a default section that specifies which version of which compiler the application should use. As my new application did provide this information, Service Desk Express tried to inherit it and, as a result of .NET Framework version issues, promptly fell over.

Solution

So to the solution. If you want to have an ASP.NET web application at the root of the same website where Service Desk Express is installed, open the Service Desk Express web.config file (found in C:Program FilesBMCService Desk ExpressApplication Server by default) in Notepad and add the following section just above the </configuration> tag as shown below:

...
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs" warningLevel="4" type="Microsoft.CSharp.CSharpCodeProvider, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
<providerOption name="CompilerVersion" value="v2.0"/>
<providerOption name="WarnAsError" value="false"/>
</compiler>
</compilers>
</system.codedom>
</configuration>

Summary

Whilst this may be a somewhat isolated situation, hopefully it will save someone pulling their hair out as we did for a morning! As always, comments, positive or negative, are always welcome.

More Traffic Lights

In previous posts (http://www.joatit.com/wordpress/?p=96) and (http://www.joatit.com/wordpress/?p=69) I showed how to colour code the quick views in a traffic light style using the following query:

(CASE WHEN [CLK_STOPTIME:] IS NOT NULL THEN 'On Hold' WHEN GETDATE() > [RECOMMENDEDFIX_DATE:] AND GETDATE() < [DUE_DATE:] THEN 'Warning' WHEN GETDATE() > [DUE_DATE:] THEN 'Critical' ELSE 'Normal' END)

Recently I was talking to one of our users who, when using Service Desk Express 9.8, rather liked the ability to view closed incidents in the quick views. She commented however, that when you viewed closed incidents with the above query everything went red eventually. It would be much better if you could see if the call was inside or outside SLA as defined by Close Date vs. Due Date.

So without further ado; creating a calculated field with the query below will not only achieve the same results as the previous posts but additionally will show if the incident was closed in time (Normal) or not (Critical) when looking at closed incidents:

(CASE WHEN BASE.[CLK_STOPTIME:] IS NOT NULL AND BASE.[STATUS] = 'O' THEN 'On Hold' WHEN GETDATE() > BASE.[RECOMMENDEDFIX_DATE:] AND GETDATE() < BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Warning' WHEN GETDATE() > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'O' THEN 'Critical' WHEN BASE.[CLOSED ON] > BASE.[DUE_DATE:] AND BASE.[STATUS] = 'C' THEN 'Critical' ELSE 'Normal' END)

Hope it helps. As always, please keep the feedback coming. It makes sure that what I am posting is of relevance and interest to the people reading.