SQL Deleting duplicate records by keeping a single row

Recently i came across a situation to remove duplicate records from a table by keeping a row . i used below listed SQL query pattern to achieve this .

DELETE
FROM Foo1
WHERE Foo1.ID IN

-- List 1 - all rows that have duplicates
(SELECT F.ID
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
   AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1))
AND Foo1.ID NOT IN

-- List 2 - one row from each set of duplicate
(SELECT Min(ID)
FROM Foo1 AS F
WHERE Exists (SELECT Field1, Field2, Count(ID)
FROM Foo1
WHERE Foo1.Field1 = F.Field1
   AND Foo1.Field2 = F.Field2
GROUP BY Foo1.Field1, Foo1.Field2
HAVING Count(Foo1.ID) > 1)
GROUP BY Field1, Field2);

Free .NET Decompiler and Assembly Browser

I was a great fan of .Net reflector (http://www.reflector.net/) , but that application is purchased by Redgate and started charging licence fees  .

Recently i came across a free .Net decompiler -  dotpeek from jetbrains . it is really cool .. it has almost all features offered by .Net reflector. so its time to switch .

http://www.jetbrains.com/decompiler/

SQL Server - Query to get references & constraints of a table

Just substitute 'YOUR TABLE NAME'  to the name of the table you want to see Reference , Constraints and other relationships.

SELECT

    K_Table = FK.TABLE_NAME,

    FK_Column = CU.COLUMN_NAME,

    PK_Table = PK.TABLE_NAME,

    PK_Column = PT.COLUMN_NAME,

    Constraint_Name = C.CONSTRAINT_NAME

FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK

    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK

    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU

    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

INNER JOIN (

            SELECT

                i1.TABLE_NAME,

                i2.COLUMN_NAME

            FROM

                INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2

                ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME

            WHERE

                i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

                AND I1.TABLE_NAME = [YOUR TABLE NAME]

           ) PT

    ON PT.TABLE_NAME = PK.TABLE_NAME

REST Client - Firefox addon

RESTClient, a debugger for RESTful web services.

Today i tried REST Client addon for fire fox browser , its really cool.

About this Add-on

RESTClient supports all HTTP methods RFC2616 (HTTP/1.1) and RFC2518 (WebDAV). You can construct custom HTTP request (custom method with resources URI and HTTP request Body) to directly test requests against a server.

For download this addon - click here

Dan Roth Takes Us on a Deep Dive into Web API

ADO.NET Entity Framework

The Microsoft® ADO.NET Entity Framework is an Object/Relational Mapping (ORM) framework that enables developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework’s ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.

High-level capabilities of the Entity Framework:

Works with a variety of database servers (including Microsoft SQL Server, Oracle, and DB2)

Includes a rich mapping engine that can handle real-world database schemas and works well with stored procedures

Provides integrated Visual Studio tools to visually create entity models and to auto-generate models from an existing database. New databases can be deployed from a model, which can also be hand-edited for full control

Provides a Code First experience to create entity models using code. Code First can map to an existing database or generate a database from the model.

Integrates well into all the .NET application programming models including ASP.NET, Windows Presentation Foundation (WPF), Windows Communication Foundation (WCF), and WCF Data Services (formerly ADO.NET Data Services)

The Entity Framework is built on the existing ADO.NET provider model, with existing providers being updated additively to support the new Entity Framework functionality. Because of this, existing applications built on ADO.NET can be carried forward to the Entity Framework easily with a programming model that is familiar to ADO.NET developers.

Using the Entity Framework to write data-oriented applications provides the following benefits:

• Reduced development time: the framework provides the core data access capabilities so developers can concentrate on application logic.
• Developers can work in terms of a more application-centric object model, including types with inheritance, complex members, and relationships. In .NET Framework 4, the Entity Framework also supports Persistence Ignorance through Plain Old CLR Objects (POCO) entities.
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema by supporting a conceptual model that is independent of the physical/storage model.
• Mappings between the object model and the storage-specific schema can change without changing the application code.
• Language-Integrated Query support (called LINQ to Entities) provides IntelliSense and compile-time syntax validation for writing queries against a conceptual model.

More details please visit - http://msdn.microsoft.com/en-us/data/aa937709

JSON Parsing using JQuery

Takes a well-formed JSON string and returns the resulting JavaScript object.

• version added: 1.4.1 

  usage  -- jQuery.parseJSON( json )

  json  --- The JSON string to parse.

Passing in a malformed JSON string may result in an exception being thrown. For example, the following are all malformed JSON strings:

• {test: 1} (test does not have double quotes around it).
• {'test': 1} ('test' is using single quotes instead of double quotes).

Additionally if you pass in nothing, an empty string, null, or undefined, 'null' will be returned from parseJSON. Where the browser provides a native implementation of JSON.parse, jQuery uses it to parse the string. For details on the JSON format, see http://json.org/.

Example:

Parse a JSON string.

var obj = jQuery.parseJSON('{"name":"John"}');
alert( obj.name === "John" );