Wednesday, April 30, 2014

Creating an OData Endpoint in ASP.NET Web API 2 tutorial

Recently I wanted to migrate a Web service that returns an array of database records to the most recent WCF service and return a List<T>.

Lightswitch can be used to generate Odata services almost without any coding.

But recently I have come to this set of articles, that explain step by step how to create a full Odata service with CRUD functionality in VS 2013: Creating an OData Endpoint in ASP.NET Web API 2 By Mike Wasson (feb 2014).

Thursday, April 10, 2014

Delete duplicate rows using Common Table Expression

Here is another way to delete duplicate rows by means of acommon table expression(CTE). The CTE will lookup the business keys that have duplicate values. then the delete statement will join the table to the CTE by the business key field.

WITH a (CustomerId, OrderId, numrecs)
    AS (
        select CustomerId, OrderId, COUNT(*) as numrecs
        from dbo.ImportOrders
        group by CustomerId, OrderId having COUNT(*) > 1
        )
DELETE dbo.ImportOrders
FROM dbo.ImportOrders AS b
INNER JOIN a on a.CustomerId= b.CustomerId AND a.OrderId = b.OrderId
GO