Posts

Showing posts from April, 2009

CLR custom aggregates for SQL Server

Having the tables "product" and "product_stock" I want to display for each product the comma-separated list of sizes and this list is ordered by a specific order.

CREATE TABLE product(
product_id int,
code varchar(50) )

CREATE TABLE product_stock(
product_stock_id int,
size_order int ,
size varchar(20) ,
product_id int )

-- desired output
Product_id Sizes In Stock
35 UK7, UK8, UK9, UK11, UK10.5, UK12, UK10
36 L, M, S, XL, XXL, XXXL


In SQL this can be achieved with cursors, but everyone suggest not to use them. After some digging I found out about custom aggregates and they saved the day.

In the MSDN article is an example which concatenates the strings and this I'm writing does the same, only it adds the ordering feature.

The problem occurs with the ordering because SQL doesn't allow to use the "ORDER BY" clause in subqueries or if the "GROUP BY" exists, then the only columns allowed are the ones in grouping or in th…

Tracking codes in DEBUG-RELEASE mode

While you work on a website which includes some JavaScript for counters and statistics is better not to allow or to "hide" them during the development time.

One solution is to comment the source code and every time before the page is published, to uncomment the relevant lines. This leads to errors sooner or later, we use to forget things (uncomment things).

A better option is to use preprocessor directives #if, #endif with the DEBUG compile option.
A protected(at least) boolean field must be added in the code behind.


public partial class home : System.Web.UI.Page
{

#if (DEBUG)
protected static readonly bool Debug = true;
#else
protected static readonly bool Debug = false;
#endif
}



Now the field Debug can be used in the web form. The following google analytics code will exist only when the source code is compiled in Release mode.

<%if (!Debug) { %>
<script type="text/javascript">
var gaJsHost = (("https:" == document.…

Levenshtein distance - use on web site search

Levenshtein distance is the minimum number of character deletion (D), insertion (I) or substitution(S) operations to transform a string to another.

I used this algorithm to give alternatives to an user when he gets no results after he submitted search by a specific keyword. Often happens when the keyword is misspelled or even there are no matches.

If an e-shop sells very expensive bracelets and has one called "Diamond Tennis Bracelet", it would be nice when the customer searched for "tenis" and he found nothing to give him a close suggestion like "tennis" and no random ones.

Here follows the Levenshtein algorithm implementation in C# in a O(mn) space

public static int distanceMN(string source, string destination, bool ignoreCase, int threshold) {
if (ignoreCase)
{
source = source.ToLower();
destination = destination.ToLower();
}
if (source == destination)
return 0;