a·gen·tic a·gil·i·ty

Where am I?

TL;DR; Describes a .NET application that tracks users’ locations via IP, updates Active Directory, and uses LINQ for database queries, aiding internal office presence visibility.

Published on
3 minute read
Image
https://nkdagility.com/resources/LIBAeHbeyk9
Subscribe

I decided that I should have a go at this LINQ thing, so as .NET 3.5 will be RTM at the end of the month I am using LINQ and .NET 3.5 in my new Locator project.

The Locator project is just a small application that will run on a users computer and tell a central server where they are. In Aggreko we have the problem that people are moving about a lot (everyone has a laptop) and when you are looking for someone you need to be able to find them.

Where am I?

The application sits and poles the IP address of the local computer, and when it get an internal IP (i.e. not localhost) it will send that IP to a Windows Communication Foundation service for identification. If it is an IP that represents an internal company IP it sends back the detected location to the client and stores it in a database. The client then updates the physicalDeliveryOfficeName property in Active Directory and notifies the user of their location.

Where am I?

The information updated into Active Directory will then be visible on SharePoint and in any presence display.

Where am I? Where am I?

As you can see the SharePoint listing is considerably out of date. This is because of the Schedule associated with its import of profiles. it does not seam as if it can be done any more regularly than once a day Where am I?

The LINQ comes to the forefront when I am updating the database from the service. LINQ made this incredibly easy, but produced some strange looking SQL.

 1 ' Get single value. This will execute the SQL
 2            Dim wai_Uip = (From val_uip In DataStore.WhereAmI_UserIPs _
 3                          Join val_ipm In DataStore.WhereAmI_IPMasks _
 4                            On val_uip.CurrentIP.Substring(0, val_uip.CurrentIP.LastIndexOf(".")) _
 5                            Equals val_ipm.Data.Substring(0, val_ipm.Data.LastIndexOf(".")) _
 6                          Join val_off In DataStore.WhereAmI_Offices _
 7                            On val_ipm.OfficeID _
 8                            Equals val_off.OfficeID _
 9                          Where val_uip.UserID = user _
10                          Select val_off.Country, val_off.Name, val_ipm.Data).First

Which produces this crazy SQL:

 1SELECT [t2].[Country], [t2].[Name], [t1].[Data]
 2FROM [dbo].[WhereAmI_UserIP] AS [t0]
 3INNER JOIN [dbo].[WhereAmI_IPMask] AS [t1] ON SUBSTRING([t0].[CurrentIP], @p0 + 1,
 4    (CASE
 5        WHEN (CONVERT(Int,DATALENGTH(@p1) / 2)) = 0 THEN (CONVERT(Int,DATALENGTH([t0].[CurrentIP]) / 2)) - 1
 6        WHEN CHARINDEX(@p1, [t0].[CurrentIP]) = 0 THEN -1
 7        ELSE 1 + ((CONVERT(Int,DATALENGTH([t0].[CurrentIP]) / 2)) - ((CONVERT(Int,DATALENGTH(@p1) / 2)) + CHARINDEX(REVERSE(@p1), REVERSE([t0].[CurrentIP]))))
 8     END)) = SUBSTRING([t1].[Data], @p2 + 1,
 9    (CASE
10        WHEN (CONVERT(Int,DATALENGTH(@p3) / 2)) = 0 THEN (CONVERT(Int,DATALENGTH([t1].[Data]) / 2)) - 1
11        WHEN CHARINDEX(@p3, [t1].[Data]) = 0 THEN -1
12        ELSE 1 + ((CONVERT(Int,DATALENGTH([t1].[Data]) / 2)) - ((CONVERT(Int,DATALENGTH(@p3) / 2)) + CHARINDEX(REVERSE(@p3), REVERSE([t1].[Data]))))
13     END))
14INNER JOIN [dbo].[WhereAmI_Office] AS [t2] ON [t1].[OfficeID] = [t2].[OfficeID]
15WHERE [t0].[UserID] = @p4',N'@p0 int,@p1 nvarchar(1),@p2 int,@p3 nvarchar(1),@p4 nvarchar(20)

Now that is nuts Where am I?

Technorati Tags: .NET   WCF

Subscribe

Connect with Martin Hinshelwood

If you've made it this far, it's worth connecting with our principal consultant and coach, Martin Hinshelwood, for a 30-minute 'ask me anything' call.

Our Happy Clients​

We partner with businesses across diverse industries, including finance, insurance, healthcare, pharmaceuticals, technology, engineering, transportation, hospitality, entertainment, legal, government, and military sectors.​

NIT A/S

CR2

YearUp.org Logo

YearUp.org

Philips Logo

Philips

Higher Education Statistics Agency Logo

Higher Education Statistics Agency

Brandes Investment Partners L.P. Logo

Brandes Investment Partners L.P.

Graham & Brown Logo

Graham & Brown

Ericson Logo

Ericson

Lockheed Martin Logo

Lockheed Martin

Capita Secure Information Solutions Ltd Logo

Capita Secure Information Solutions Ltd

Akaditi Logo

Akaditi

Xceptor - Process and Data Automation Logo

Xceptor - Process and Data Automation

Workday Logo

Workday

Bistech Logo

Bistech

Illumina Logo

Illumina

MacDonald Humfrey (Automation) Ltd. Logo

MacDonald Humfrey (Automation) Ltd.

Boxit Document Solutions Logo

Boxit Document Solutions

ProgramUtvikling Logo

ProgramUtvikling

Nottingham County Council Logo

Nottingham County Council

Royal Air Force Logo

Royal Air Force

Ghana Police Service Logo

Ghana Police Service

Department of Work and Pensions (UK) Logo

Department of Work and Pensions (UK)

New Hampshire Supreme Court Logo

New Hampshire Supreme Court

Washington Department of Enterprise Services Logo

Washington Department of Enterprise Services

Jack Links Logo

Jack Links

CR2

Graham & Brown Logo

Graham & Brown

MacDonald Humfrey (Automation) Ltd. Logo

MacDonald Humfrey (Automation) Ltd.

Lean SA Logo

Lean SA

Slicedbread Logo

Slicedbread