Tag Archives: cool

TSQL: Remove duplicate records. Clean and Simple

It’s a common scenario, your table has several records with identical values and you need to leave only one, deleting the rest. Here is a generic TSQL query (SQL Server 2005 and above) that does just that in a few lines:

WITH DUP_TABLE AS
   (SELECT ROW_NUMBER()
    OVER (PARTITION BY FIELD1, FIELD2 ORDER BY FIELD1, FIELD2) As ROW_NO
    FROM ORIGINAL_TABLE)
DELETE FROM DUP_TABLE WHERE ROW_NO > 1

Here ORIGINAL_TABLE is your table with duplicates. FIELD1 and FIELD2 are columns with duplicates value (feel free to add or remove columns to suit your needs). Internal query assigns a row number to each duplicate record and DELETE statements that uses that CTE deletes all the rows except the one with Row Number = 1

WebDataGrid: Custom drag and drop columns when Colum Moving behavior is enabled

Infragistics Aikido WebDataGrid offers a nice built-in ColumnMoving behavior. When enabled – it allows user to drag columns to change their order:

WebDataGrid with ColumnMoving behavior enabled

But what if you want to keep this behavior and add your own custom column drag-and-drop? For example to create your own column grouping, since WebHierarchicalDataGrid doesn’t handle grouping well.

In this post I will describe a simple technique how to both keep behavior shown above and implement custom column drag-and-drop:

WebDataGrid with custom column drag-and-drop
Continue reading →

Custom grouping in classic Infragistics UltraWebGrid

Infragistics UltraWebGrid offers a nice grouping feature: when the grid is in OutlookGroupBy mode, you can group similar data with very little coding required, you can go from this view:
Before Grouping
to this:
After Grouping
by just dragging columns to designated area.

But what if you want to group by first letter of a name or a year of a date? New WebHierarchicalDataGrid control offers this functionality after 10.2 release of Infragistics NetAdvantage, but if you invested years of work in classic UltraWebGrid – it’s not easy to move to a brand new control cold turkey. There’re other methods that offer custom grouping for UltraWebGrid, but the ones I found were pretty convoluted (like create a hidden column, populate it with data to group by etc.) Here is a simpler approach. Continue reading →

JavaScript IsDate(): Validate date according browser locale

In one of my recent projects I needed to validate whether user’s input is a valid date, and this needed to be done client-side, in browser prior submission to the server. Lazy as I am, I Googled for a ready-to-use code snippet. There’re plenty of versions out there, but most of them offer incomplete solution and none of them take into account browser locale, you know – the language settings:

So I decided to cook something of my own Continue reading →

Excellent Freeware RAR password recovery utility

If you ever need to recover lost password to your RAR file (or, let’s be honest, unrar a file you got from the Internet), look no further than cRARk – beautifully done RAR password recoverer. It’s the only utility capable of using NVIDIA CUDA, which utilizes GPU for password permutations, and even without that it’s extremally fast. And it’s free!

You can download it at http://www.crark.net

Serving image from ASP.NET MSChart to external applications via WebService

I have an existing ASP.NET application that uses Microsoft Charting control for .NET. I created a CCharting class that hold several methods related to getting data for the chart, applying chart appearance etc. Main method of that class is

Public Sub DrawChart(ByVal i_omsChart As Chart, ByVal i_iChartWidth As Integer, ByVal i_iChartHeight As Integer)

As a 1st parameter it accepts actual chart control from the page, 2nd and 3rd are chart width and height. The method then gets the data for the chart, binds chart to that data, applies chart appearance (colors, series, axises) etc. So drawing a chart is a simple as instantiating the class and calling the method:

Dim oCharting As New CCharting
CCharting.DrawChart(xmsChart,500,300)

where xmsChart is a chart control from HTML markup of the page. The result is displayed on the page:

But now I needed to give access to that chart to external applications, that do not have access neither to chart data nor to Microsoft charting control, may run under different OS’s, be Web apps or not. Continue reading →

LINQ: Truly language integrated. Using VB functions inside of LINQ Query

LINQ is truly integrated into VB.NET. This allows not only to use LINQ-specific query language in a standard VB.NET code, but use VB.NET code in a LINQ query. Consider function from the previous post. To make it universal we can pass one more parameter
“Aggregate Type” and based on that parameter return Min, Max, Avg, Sum or Count

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable, i_iAggregateType as Integer) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Choose(i_iAggregateType, Group.Min(Function(row) row(i_sAggregateColumn)), Group.Max(Function(row) row(i_sAggregateColumn)), Group.Sum(Function(row) row(i_sAggregateColumn)), Group.Avg(Function(row) row(i_sAggregateColumn)), Group.Count(Function(row) row(i_sAggregateColumn)))

    return aQuery.toDataTable

End Function

In this example VB.NET function Choose is used inside of a LINQ query’s Select Statement. If i_iAggregateType parameter is equal 1 – Minimum value, will be calculated, 2 – Maximum etc.

Grouping ADO.NET DataTable using LINQ

I’ve described before how to group and aggregate data in ADO.NET data table using standard .NET 2.0 features. But if you happen to use .NET 3.5 or above and Visual Studio 2008 or above – that entire block of code can be replaced with a single LINQ query:

Function GroupBy(ByVal i_sGroupByColumn As String,  ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

     dim aQuery = From row In i_dSourceTable Group By Group1 = row(i_sGroupByColumn) Into Group Select Group1,  Aggr = Group.Count(Function(row) row(i_sAggregateColumn))

    return aQuery.toDataTable

End Function

The query at line 3 is a LINQ to DataSet query, so reference to System.Data.Linq has to be added to your project. The Group.Count aggregate can be replaced with Group.Max, Group.Min, Group.Sum or Group.Avg to perform respectful function.

There is one caveat though. Continue reading →

Google Easter Egg. December 2009

This is December of 2009 and Google has an interesting Easter Egg in its Search page. If you leave the search field blank and click “I am feeling lucky” button Google displays countdown in large numbers:

From the looks of it it’s a countdown to New 2010 Year. It’s coming 🙂

TNX Text Links Ads: How to automatically fill all your ad spots (including pages with 0 PR) and other tricks

TNX Corp allows you to place static ad links on pages of your site, so you can earn some profit. Unfortunately if you leave it to TNX system, automatic rate at which links are sold and paced on your site could be very slow. You can add links to your pages manually via TNX site interface, but it is only allowed if your page Google PageRank is above 0. Also manual placement can be really inconvenient and you cannot place more than 4 ad links.

Fortunately there is a solution. Continue reading →