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

Where am I?

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

Related Blog

No related videos found.

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.​

Emerson Process Management Logo

Emerson Process Management

SuperControl Logo

SuperControl

MacDonald Humfrey (Automation) Ltd. Logo

MacDonald Humfrey (Automation) Ltd.

Flowmaster (a Mentor Graphics Company) Logo

Flowmaster (a Mentor Graphics Company)

Workday Logo

Workday

Bistech Logo

Bistech

Higher Education Statistics Agency Logo

Higher Education Statistics Agency

Lean SA Logo

Lean SA

Graham & Brown Logo

Graham & Brown

Lockheed Martin Logo

Lockheed Martin

Teleplan Logo

Teleplan

Big Data for Humans Logo

Big Data for Humans

Slicedbread Logo

Slicedbread

Xceptor - Process and Data Automation Logo

Xceptor - Process and Data Automation

ALS Life Sciences Logo

ALS Life Sciences

Boxit Document Solutions Logo

Boxit Document Solutions

YearUp.org Logo

YearUp.org

Illumina Logo

Illumina

Washington Department of Enterprise Services Logo

Washington Department of Enterprise Services

Washington Department of Transport Logo

Washington Department of Transport

Department of Work and Pensions (UK) Logo

Department of Work and Pensions (UK)

Ghana Police Service Logo

Ghana Police Service

Royal Air Force Logo

Royal Air Force

Nottingham County Council Logo

Nottingham County Council

Alignment Healthcare Logo

Alignment Healthcare

YearUp.org Logo

YearUp.org

Bistech Logo

Bistech

Kongsberg Maritime Logo

Kongsberg Maritime

Hubtel Ghana Logo

Hubtel Ghana

MacDonald Humfrey (Automation) Ltd. Logo

MacDonald Humfrey (Automation) Ltd.