Anastasiosyal.com

Beyond SoundEx - Functions for Fuzzy Searching in MS SQL Server

Anastasios Yalanopoulos — Sun, 11 Jan 2009 00:08:42 GMT

In this post:

SoundEx in Sql Server
SimMetrics
Adding string Metric functions in MS Sql Server
Evaluating metric accuracy and comparing Metrics
Conclusion + code

Quite often we come across a requirement where we may need to perform some sort of fuzzy string grouping or data correlation. For example, we may want to correlate the customer records of a database by identifying records that are similar but not necessarily exactly the same (due to spelling mistakes for example). Obviously a simple group by, will not successfully group such data. We will need to employ what is commonly referred to as a distance algorithm or a string metric in order to determine how close 2 string values are.

1. SoundEx

SoundEx is one such algorithm built in to Sql server, but it doesnt really perform well on calculating string value distances since it is a phonetic encoding algorithm. Vowels are ignored and the resulting value consists of a letter followed by 3 digits calculated like so:

Replace consonants with digits as follows (but do not change the first letter):
- b, f, p, v => 1
- c, g, j, k, q, s, x, z => 2
- d, t => 3
- l => 4
- m, n => 5
- r => 6
Collapse adjacent identical digits into a single digit of that value.
Remove all non-digits after the first letter.
Return the starting letter and the first three remaining digits. If needed, append zeroes to make it a letter and three digits.

So let's try SoundEx with a few variations and see how it performs

select Soundex('LLoyds') -- returns L432
select Soundex('Loyds')  -- returns L320

select Soundex('Brighton') -- returns B623
select Soundex('Bristol')  -- returns B623

As you can see, Soundex returned the same code for Brighton and Bristol, while at the same time it returned a different code for Loyds and LLoyds. Such results make you think twice before employing this function in a production environment.

So, is this all that is available to us? Of course not.

2. SimMetrics

The Web Intelligence Group within the University of Sheffield have released a wonderful library over at sourceforge just for this purpose. SimMetrics is the library you would need to use. The developer of this library and credit for its development goes to Sam Chapman

Here are only some of the similarity metrics in SimMetrics that can be briefly described in simple English. For an even more detailed explanation and for the full list of included functions have a look over at http://www.dcs.shef.ac.uk/~sam/stringmetrics.html or on Wikipedia

Hamming distance
Measures the minimum number of substitutions required to change one into the other
Levenshtein distance
Measures the minimum number of operations needed to transform one string into the other, where an operation is an insertion, deletion, or substitution of a single character
Needleman-Wunch distance or Sellers Algorithm
Similar to Levenstein with an added cost adjustment to the cost of a gap of the operation
Smith-Waterman distance
Similar to Levenstein but with two adjustable parameters for cost functions
Gotoh Distance or Smith-Waterman-Gotoh distance
Extension of the Smith Waterman with variable gap costs
Jaro distance metric
This distance metric is designed and best suited for short strings such as person names. The score is normalized such that 0 equates to no similarity and 1 is an exact match. The higher the distance for two strings is, the more similar the strings are
Jaro Winkler
Similar to Jaro but with more favourable ratings for strings that match from the beginning
Cosine similarity
Often used to compare documents in text mining.

There are plenty more in this library, but frankly, the descriptions are pretty baffling and dont fit in simple one-liner explanation. Follow the links above for even more information on this.

What I would like instead to focus on is how we can exploit the power of such a library within Sql Server.

3. Adding string Metric functions in MS Sql Server

In Sql Server 2005 upwards we can leverage the power of CLR to introduce these string metrics into our SQL queries. And I will walk you through the process, step by step.

Download the source code of the SimMetrics library from http://sourceforge.net/projects/simmetrics/
Open and compile the project so you have your SimMetrics.dll in your bin folder.
Create a new C# Class Library project and call it TextFunctions (or if you're feeling lazy, download it here)
Add a reference to the SimMetrics.dll
Add a class and call it SimMetrics
Copy and paste the following Code into your Class file

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using SimMetricsMetricUtilities;

    public class StringMetrics
    {

        private static readonly Levenstein _Levenstein;
        private static readonly NeedlemanWunch _NeedlemanWunch;
        private static readonly SmithWaterman _SmithWaterman;
        private static readonly SmithWatermanGotoh _SmithWatermanGotoh;
        private static readonly SmithWatermanGotohWindowedAffine _SmithWatermanGotohWindowedAffine;
        private static readonly Jaro _Jaro;
        private static readonly JaroWinkler _JaroWinkler;
        private static readonly ChapmanLengthDeviation _ChapmanLengthDeviation;
        private static readonly ChapmanMeanLength _ChapmanMeanLength;
        private static readonly QGramsDistance _QGramsDistance;
        private static readonly BlockDistance _BlockDistance;
        private static readonly CosineSimilarity _CosineSimilarity;
        private static readonly DiceSimilarity _DiceSimilarity;
        private static readonly EuclideanDistance _EuclideanDistance;
        private static readonly JaccardSimilarity _JaccardSimilarity;
        private static readonly MatchingCoefficient _MatchingCoefficient;
        private static readonly MongeElkan _MongeElkan;
        private static readonly OverlapCoefficient _OverlapCoefficient;

        static StringMetrics()
        {
            _Levenstein = new Levenstein();
            _NeedlemanWunch = new NeedlemanWunch();
            _SmithWaterman = new SmithWaterman();
            _SmithWatermanGotoh = new SmithWatermanGotoh();
            _SmithWatermanGotohWindowedAffine = new SmithWatermanGotohWindowedAffine();
            _Jaro = new Jaro();
            _JaroWinkler = new JaroWinkler();
            _ChapmanLengthDeviation = new ChapmanLengthDeviation();
            _ChapmanMeanLength = new ChapmanMeanLength();
            _QGramsDistance = new QGramsDistance();
            _BlockDistance = new BlockDistance();
            _CosineSimilarity = new CosineSimilarity();
            _DiceSimilarity = new DiceSimilarity();
            _EuclideanDistance = new EuclideanDistance();
            _JaccardSimilarity = new JaccardSimilarity();
            _MatchingCoefficient = new MatchingCoefficient();
            _MongeElkan = new MongeElkan();
            _OverlapCoefficient = new OverlapCoefficient();
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble Levenstein(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_Levenstein.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble NeedlemanWunch(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_NeedlemanWunch.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble SmithWaterman(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_SmithWaterman.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble SmithWatermanGotoh(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_SmithWatermanGotoh.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble SmithWatermanGotohWindowedAffine(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_SmithWatermanGotohWindowedAffine.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble Jaro(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_Jaro.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble JaroWinkler(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_JaroWinkler.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble ChapmanLengthDeviation(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_ChapmanLengthDeviation.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble ChapmanMeanLength(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_ChapmanMeanLength.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble QGramsDistance(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_QGramsDistance.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble BlockDistance(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_BlockDistance.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble CosineSimilarity(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_CosineSimilarity.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble DiceSimilarity(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_DiceSimilarity.GetSimilarity(firstWord.Value, secondWord.Value));
        }

        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble EuclideanDistance(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_EuclideanDistance.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble JaccardSimilarity(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_JaccardSimilarity.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble MatchingCoefficient(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_MatchingCoefficient.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble MongeElkan(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_MongeElkan.GetSimilarity(firstWord.Value, secondWord.Value));
        }


        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlDouble OverlapCoefficient(SqlString firstWord, SqlString secondWord)
        {
            if (firstWord.IsNull || secondWord.IsNull)
                return 0;

            return new SqlDouble(_OverlapCoefficient.GetSimilarity(firstWord.Value, secondWord.Value));
        }


    }

Compile your project and copy the path to your dll.
We need to make sure that our target sql server is CLR Enabled so we need to run:

EXEC sp_configure 'clr enabled', 1
RECONFIGURE

Install the library and the functions onto SQL Server

CREATE ASSEMBLY [TextFunctions]
AUTHORIZATION [dbo]
FROM 'c:\SqlAssembly\TextFunctions.dll'
WITH PERMISSION_SET = SAFE

GO

CREATE FUNCTION Levenstein(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.Levenstein
GO

CREATE FUNCTION NeedlemanWunch(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.NeedlemanWunch
GO

CREATE FUNCTION SmithWaterman(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.SmithWaterman
GO

CREATE FUNCTION SmithWatermanGotoh(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.SmithWatermanGotoh
GO

CREATE FUNCTION SmithWatermanGotohWindowedAffine(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.SmithWatermanGotohWindowedAffine
GO

CREATE FUNCTION Jaro(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.Jaro
GO

CREATE FUNCTION JaroWinkler(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.JaroWinkler
GO

CREATE FUNCTION ChapmanLengthDeviation(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.ChapmanLengthDeviation
GO

CREATE FUNCTION ChapmanMeanLength(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.ChapmanMeanLength
GO

CREATE FUNCTION QGramsDistance(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.QGramsDistance
GO

CREATE FUNCTION BlockDistance(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.BlockDistance
GO

CREATE FUNCTION CosineSimilarity(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.CosineSimilarity
GO

CREATE FUNCTION DiceSimilarity(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.DiceSimilarity
GO

CREATE FUNCTION EuclideanDistance(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.EuclideanDistance
GO

CREATE FUNCTION JaccardSimilarity(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.JaccardSimilarity
GO

CREATE FUNCTION MatchingCoefficient(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.MatchingCoefficient
GO

CREATE FUNCTION MongeElkan(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.MongeElkan
GO

CREATE FUNCTION OverlapCoefficient(@firstword NVARCHAR(255),@secondword NVARCHAR(255))
RETURNS float EXTERNAL NAME TextFunctions.StringMetrics.OverlapCoefficient
GO

Now we are ready to use the functions our newly available string metrics functions.

4. Evaluating metric accuracy and comparing Metrics

All string metric functions accept two string arguments and return a float value between 0 and 1. The closer the score reaches 1 means that the metric we have applied is telling us the 2 input strings are a match.

Now that we have plenty of Metrics to choose from, we will want to evaluate them and see which one is best for us. This is where the following function comes in handy:

CREATE FUNCTION dbo.CompareStringMetrics(@firstword [nvarchar](255), @secondword [nvarchar](255))
RETURNS TABLE
AS
RETURN
(
    SELECT dbo.Jaro(@firstword, @secondword) as Score, 'Jaro' as Metric
    UNION SELECT dbo.JaroWinkler(@firstword, @secondword), 'JaroWinkler'
    UNION SELECT dbo.BlockDistance(@firstword, @secondword), 'BlockDistance'
    UNION SELECT dbo.ChapmanLengthDeviation(@firstword, @secondword), 'ChapmanLengthDeviation'
    UNION SELECT dbo.ChapmanMeanLength(@firstword, @secondword), 'ChapmanMeanLength'
    UNION SELECT dbo.CosineSimilarity(@firstword, @secondword), 'CosineSimilarity'
    UNION SELECT dbo.DiceSimilarity(@firstword, @secondword), 'DiceSimilarity'
    UNION SELECT dbo.EuclideanDistance(@firstword, @secondword), 'EuclideanDistance'
    UNION SELECT dbo.JaccardSimilarity(@firstword, @secondword), 'JaccardSimilarity' 
    UNION SELECT dbo.Levenstein(@firstword, @secondword), 'Levenstein'
    UNION SELECT dbo.MatchingCoefficient(@firstword, @secondword), 'MatchingCoefficient'
    UNION SELECT dbo.MongeElkan(@firstword, @secondword), 'MongeElkan'
    UNION SELECT dbo.NeedlemanWunch(@firstword, @secondword), 'NeedlemanWunch'
    UNION SELECT dbo.OverlapCoefficient(@firstword, @secondword), 'OverlapCoefficient'
    UNION SELECT dbo.QGramsDistance(@firstword, @secondword), 'QGramsDistance'
    UNION SELECT dbo.SmithWaterman(@firstword, @secondword), 'SmithWaterman'
    UNION SELECT dbo.SmithWatermanGotoh(@firstword, @secondword), 'SmithWatermanGotoh'
    UNION SELECT dbo.SmithWatermanGotohWindowedAffine(@firstword, @secondword), 'SmithWatermanGotohWindowedAffine'
)

This means that now we can easily evaluate the performance and the accuracy of each metric by running queries like:

Select * from CompareStringMetrics('Loyds', 'LLoyds') where score > 0.6
Select * from CompareStringMetrics('bristol', 'brighton') where score < 0.5

Try out various functions on large database tables, the performance is great!

5. Conclusion

SimMetrics is a great library for fuzzy text comparisons. This article showed you how you can bring its power into Sql Server. There are plenty of functions to choose from, experiment and see what works best for you. You can be creative on what you want to apply your metric on, e.g Instead of applying directly on a field you can choose to make simple searchkeys by lower casing fields, striping common text patterns that you dont need (eg Inc, Ltd etc) and removing numbers. There are many ways to apply these sort of functions, what is more suitable for you is your call, since it depends on the context of your problem. If you find any innovative ways of correlating your data share it with us!

source code: (including sql install and uninstall scripts)
TextFunctions.zip

SQL Server script to auto-create indexes on all Foreign Key Columns

Anastasios Yalanopoulos — Tue, 29 Jul 2008 22:38:52 GMT

If you want a quick way to set some default indexes up in your database the best candidates for your indexes would be the foreign keys. They are usually highly selective and many of our queries tend to have some sort of filter on the foreign keys, this makes them good candidates for an index.

You may be quick to think:

'but sql server has indexes on the foreign keys'

That's not true, since a foreign key is an integrity constraint while an index is for performance. Have a look at an interesting discussion over at sqlblog about this: http://sqlblog.com/blogs/greg_low/archive/2008/07/29/indexing-foreign-keys-should-sql-server-do-that-automatically.aspx

The following SQL Script will create a non clustered index on each of the foreign key columns in our database.

--Create non clustered indexes for all foreign key references in the database
DECLARE @sql nvarchar(max) 
SELECT @sql = IsNull(@sql + ';' + char(13) , '') +  'CREATE NONCLUSTERED INDEX [IX_' + tablename + '_' + columnname +'] ON [dbo].[' + tablename + '] ( [' + columnname + '] ASC)'
FROM 
(
    --Script all foreign key columns that are not already an index      SELECT
        o.name AS tablename, cols.name AS columnName FROM sys.foreign_key_columns fc
        inner join sys.objects o on fc.parent_object_id = o.object_id
        inner join sys.columns cols on cols.object_id = o.object_id and fc.parent_column_id = cols.column_id
    EXCEPT 
        SELECT o.name, cols.name  FROM sys.index_columns icols
            inner join sys.objects o on icols.object_Id = o.object_id
            inner join sys.columns cols on cols.object_id = o.object_id and icols.column_id = cols.column_id
) T
ORDER BY 
    tablename, 
    columnname

--Display the sql that will be executed
Print @sql 

--Run the sql and create all the indexes
exec sp_executesql @sql

To monitor the performance of your existing indexes check out the script sp_indexInfo by Tibor Karaszi.
It is a stored procedure that provides some useful metrics for all the indexes in a given table or in a given database.

4 Features for C# 4.0

Anastasios Yalanopoulos — Sat, 19 Jul 2008 00:26:34 GMT

Plenty of powerful new language features have fairly recently become mainstream with the launch of VS 2008 in Nov 2007 that supports C# 3.0, amongst which:

Implicitly Typed Local Variables
Extension Methods
Lambda Expressions
Object and Collection Initializers
Anonymous Types
Query Expressions
Automatically Implemented Properties

Although most of us are still trying to fully absorb the above features, there has been some speculation going on about what sort of features we would like to see in the next version of C# and Jeremy Miller has posted a question with what he would like to see.

Here are some of the language features I would really like to see in C#:

1. Implementing Interfaces by delegation to fields

I would like to see support in C# 4.0 that would allow us to delegate the implementation of an interface to a field in the implementing class. For example this could look something like the following:

    //faux code
    public class Foo : IList<string>
    {
        private List<string> _Collection implements IList<string>;

        public Foo()
        {
            _Collection = new List<string>();
        }
    }

The field could be delegated to implement one or more interfaces from the encapsulating class by separating them with a comma. This would remove a lot of redundant code in scenarios like the above one, ie. Instead of implementing a lot of pass through functions on the encapsulating class (Foo) the functions are directly mapped to the delegated interface implementation. This sort of functionality would also enhance support for mixins

This is known as the delegation pattern and from wikipedia:

The delegation pattern is a technique where an object outwardly expresses certain behaviour but in reality delegates responsibility for implementing that behaviour to an associated object in an Inversion of Responsibility. The delegation pattern is the fundamental abstraction that underpins composition (also referred to as aggregation), mixins and aspects.

Taking it one step even further, one could override the delegated implementation in a syntax like so:

    public class Foo : IList<string>
    {
        private List<string> _Collection { get; set; } implements IList<string>;

        public Foo()
        {
            _Collection = new List<string>();
        }

        //This would override the delegated implementation 
        // for nice mixin functionality and easy decorator pattern implementation
        public int IList.Add(string value)
        {
            if (!_Collection.Contains(value))
                _Collection.Add(value);
        }
    }

2. Anonymous type return values

I would like to see the anonymous types becoming first class 'citizens' in C#. Anonymous types can only be used in a local scope and cannot be returned from functions. It would be nice if we could return our strongly typed Linq Query Projection from a function eg:

        //faux code
        public var GetProductInfos()
        {
            var productInfos =
                from p in products
                select new { p.ProductName, p.Category, Price = p.UnitPrice };

            return productInfos;
        }

3. Some Duck-typing or Structural Subtyping support

If a class has a property or a method signature that is the same as the method signature on a declared interface, then that class implicitly implements that interface, if it is not already inheriting from it. The class would implicitly implement an interface if and only if it would implement all the method signatures of a given interface. Basically

if it walks like a duck and it quacks like a duck, then I would call it a duck! (James Riley)

So what is the difference with Structural Subtyping? I would argue that structural subtyping is more suitable to the static style of C#, since it is a 'static duck typing', or according to wikipedia:

Duck typing differs from structural typing in that only the part of the structure accessed at run time is checked for compatibility.

Lets see how this could be beneficial through a use case:

The .Net framework has a few controls that implement a ReadOnly property amongst which are the TextBox, DataGrid, NumericUpDown.

Lets define the IReadOnlyRestrictable interface in our source like so:

        public interface IReadOnlyRestricable
        {
            bool ReadOnly { get; set; }
        }

Let's assume that we wanted to Loop through all the controls on a form and anything that satisfies the above interface signature (i.e has a property 'ReadOnly') set its readonly property to true. With ducktyping the qualifying controls would be cast to a valid IReadOnlyRestrictable instance like below, without needing to resort to reflection

        foreach (Control c in f.Controls)
        {
            //would like to have implicit cast to IReadOnlyRestrictable if interface contract is in class we are checking against
            IReadOnlyRestricable editable = c as IReadOnlyRestricable; 
            if (editable != null)
                editable.ReadOnly = true;
        }

The main advantage I see with ducktyping is that you can declare interfaces for libraries that you do not have access to, this can be useful in scenarios where you would like to minimise dependancies, check out Phil Haacks more extensive post on duck typing and why he believes it would benefit C# developers.

Apparantly, the c# foreach operator already uses duck typing according to Krzysztof Cwalina

4. Safe Null Dereferencing Operator

I would really like to see a safe way to dereference a null value in an expression that of form Object.Property.Property.Value.

For example if we had Customer?.FirstName and Customer was null, then the expression would evaluate to null instead of raising a null reference exception.
Some more examples of how this could work:

    //FAUX CODE
    //this would throw a null reference exception as usual if either Customer or Order was null
    int orderNumber = Customer.Order.OrderNumber;

    //this would not compile since it would require a nullable return type
    int orderNumber = Customer.Order?.OrderNumber;

    //this would return null if a Customer was null or if Order was null 
    int? orderNumber = Customer?.Order?.OrderNumber;
    if (orderNumber.HasValue) 
        //... do something with it

    //instead of having to do 
    if ((Customer != null) && (Customer.Order != null))
        int a = Customer.Order.OrderNumber

Wrap up

Here is a recent interview of the C# 4.0 team in the room where all the magic happens

What is your take? Do you agree? What features you would like to see?

SQL CLR without DLL dependency for your production server

Anastasios Yalanopoulos — Thu, 17 Jul 2008 19:32:38 GMT

Have you been shying out of putting extra functions on your SQL Server 2005 using SQL CLR simply because you would like to avoid DLL Hell?!

Well, the good news is that you need not be so worried, since SQL Server does not link to the dll itself, but instead it embeds the assembly in the database. What's even better is that you can deploy your assembly by simply scripting it.

Lets do this with an example, in the previous post there is a sample that shows how to create support for regular expressions using SQL CLR. To deploy this on your production server without having to copy any url's you simply need to open Sql Server Management Studio and go to:
DB > Programmability > Assemblies > AssemblyName > RightClick > ScriptAssembly As > Create To > Clipboard | File | New Window

like so:

In fact this will create the following sql script for you:

CREATE ASSEMBLY [TextFunctions]
AUTHORIZATION [dbo]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C5987F480000000000000000E0000E210B0108000010000000200000000000000E290000002000000040000000004000002000000010000004000000000000000400000000000000008000000010000000000000030040050000100000100000000010000010000000000000100000000000000000000000C02800004B000000004000002803000000000000000000000000000000000000006000000C000000442800001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000014090000002000000010000000100000000000000000000000000000200000602E7273726300000028030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C00000000600000001000000030000000000000000000000000000040000042000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000F0280000000000004800000002000500DC200000680700000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000CA0F00281100000A2D090F01281100000A2C067E1200000A2A0F00281300000A0F01281300000A17281400000A281500000A2A000330040042000000000000000F00281100000A2D120F01281100000A2D090F02281100000A2C067E1600000A2A0F00281300000A0F01281300000A0F02281300000A17281700000A731800000A2A1E02281900000A2A000042534A4201000100000000000C00000076322E302E35303732370000000005006C00000074020000237E0000E00200005403000023537472696E6773000000003406000008000000235553003C0600001000000023475549440000004C0600001C01000023426C6F620000000000000002000001471500000900000000FA0133001600000100000016000000020000000300000005000000190000000F000000010000000300000000000A0001000000000006003F0038000A00670052000A00720052000600AC009A000600C9009A0006000101E20006000F01E200060023019A0006003C019A00060057019A00060072019A0006008B019A000600A4019A000600C3019A000600E0019A0006000A02F70143001E02000006004D022D0206006D022D020A00B40299020E001103F2020E001703F2020000000001000000000001000100010010001C00000005000100010050200000000096007C000A0001008420000000009600870013000300D22000000000861894001E00060000000100C90200000200CF0200000100C90200000200CF02000003003803210094002200290094002200310094002200390094002700410094002200490094002200510094002200590094002200610094002200690094002200710094002200790094002200810094002C00910094003200990094001E00A10094001E001900D7025C001100E20260001900E8026400A9002403680011002C037000190044037600A90049037A00190094002200090094001E002000830037002E004300D8002E000B0083002E001B0090002E002300BA002E003300BA002E003B00C0002E005B00BA002E004B00BA002E005300BA002E006B00EB002E007B00FD002E006300D8002E007300F4004000830037000480000001000000000000000000000000008B02000002000000000000000000000001002F00000000000200000000000000000000000100460000000000020000000000000000000000010038000000000000000000003C4D6F64756C653E005465787446756E6374696F6E732E646C6C00526567756C617245787072657373696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670052656745784D617463680052656745785265706C616365002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7946696C6556657273696F6E41747472696275746500417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F705365727669636573004775696441747472696275746500436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C697479417474726962757465005465787446756E6374696F6E73004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500696E707574007061747465726E006765745F49734E756C6C0046616C7365006765745F56616C75650053797374656D2E546578742E526567756C617245787072657373696F6E730052656765780052656765784F7074696F6E730049734D61746368006F705F496D706C69636974007265706C6163656D656E74004E756C6C005265706C616365000000000003200000000000D51DC224975A24449F15285FBFA1D5530008B77A5C561934E0890800021109110D110D0A0003110D110D110D110D03200001042001010E04200101020520010111450420010108240100020054020F497344657465726D696E6973746963015402094973507265636973650103200002030611090320000E070003020E0E11590500011109020306110D0800040E0E0E0E11590C010007312E302E302E3000002901002464313338356464382D376539322D343065362D383234632D646632376639393862656630000005010000000017010012436F7079726967687420C2A920203230303700001201000D5465787446756E6374696F6E7300000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F77730100000000C5987F4800000000020000005F0000006028000060180000525344536A480B33B1CFDD46B12FF5343A6664BF01000000433A5C446576656C6F706D656E745C54657374696E675C434C525C5465787446756E6374696F6E735C6F626A5C52656C656173655C5465787446756E6374696F6E732E7064620000E82800000000000000000000FE280000002000000000000000000000000000000000000000000000F02800000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF2500204000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000000000000000100000000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C020000010030003000300030003000340062003000000044000E000100460069006C0065004400650073006300720069007000740069006F006E00000000005400650078007400460075006E006300740069006F006E0073000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003000000044001200010049006E007400650072006E0061006C004E0061006D00650000005400650078007400460075006E006300740069006F006E0073002E0064006C006C0000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003000370000004C00120001004F0072006900670069006E0061006C00460069006C0065006E0061006D00650000005400650078007400460075006E006300740069006F006E0073002E0064006C006C0000003C000E000100500072006F0064007500630074004E0061006D006500000000005400650078007400460075006E006300740069006F006E0073000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003000000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0030000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000001039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = SAFE

Without having the dll nor the requirement to link to any dll you now have part of your change script for deploying this assembly to your production server.

Of course, the server still needs to be CLR Enabled, if it is not CLR Enabled you will need to enable it before creating the assembly:

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Before we can test our assembly we register our new functions

CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO

Now we lets test our newly available functions:

Select dbo.RegExMatch('333-4444','\d{3}-\d{4}')
--returns 1

Select dbo.RegExReplace('333-4444','(\d+)-(\d+)', '$2-$1')
--reverses two numbers seperated by a dash returns 4444-333

And that's all needed. If you would like to check out the source code for creating the original dll and compiling it, have a look here

Regular Expressions In MS SQL Server using CLR

Anastasios Yalanopoulos — Sat, 05 Jul 2008 14:31:39 GMT

In this post I'll show just how easy it is to make your SQL Server 2005 database support Regular Expressions through what is known as SQL CLR

Just Fire up Visual Studio and create a new library project (I called it TextFunctions)

Add a new Class and Call it Regular Expressions and simply paste in the following Code:

using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;


    public class RegularExpressions
    {
        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, IsPrecise=true)]
        public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
        {
            if (input.IsNull || pattern.IsNull) //nulls dont qualify for a match
                return SqlBoolean.False;
            
            //Use the static IsMatch method. This is more performant than creating a 
            // new instance of Regex as the static method also caches the last expressions we used. 
            return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
        }

        [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
        public static SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
        {
            if (input.IsNull || pattern.IsNull || replacement.IsNull)
                return SqlString.Null;

            return new SqlString(Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnoreCase));
        }
    }

In this example we have two functions: one to Match a Regular expression pattern and one to perform a Replace based on a regular expression pattern.

To enable our SQL Server database to make use of the above functions we need to follow the steps below:

Compile the library for Release
CLR Enable Sql Server with:

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Reference the assembly and register the functions:

CREATE ASSEMBLY TextFunctions FROM 'c:\Development\Testing\CLR\TextFunctions\bin\Release\TextFunctions.dll'
GO
CREATE Function RegExMatch(@Input NVARCHAR(512),@Pattern NVARCHAR(127))
RETURNS BIT
EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO
CREATE Function RegExReplace(@Input NVARCHAR(512),@Pattern NVARCHAR(127), @Replacement NVARCHAR(512))
RETURNS NVARCHAR(512)
EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO

Now we're ready to put our new functions to the test:

select dbo.RegExMatch('me@mymail.com','^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
--Verifies an email address

SELECT dbo.RegExMatch('12354', '\d')
--Verifies number Returns 1

SELECT dbo.RegExReplace('Poem Title (Author)', '.*?\((.*?)\).*', '$1')
-- Returns the match within the parenthesis, returns: Author (Useful for splitting one column in two)

SELECT EmailAddress, dbo.RegExMatch(EmailAddress,'^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$') 
FROM Users 
ORDER BY 2 
--Checks your users table for Invalid Email addresses, 
--at the top of the results all users with invalid email addresses

Regular expressions can come in really handy especially in data cleansing/transformation operations in your database.

Here are some further pointers to boost creativity with regular expressions:

Email Validation with Regular Expressions
Regular Expressions Cheat Sheet
RegExLib - Library of Regular Expressions for .Net

Disable Text Wrapping in Excel Export of Reporting Services

Anastasios Yalanopoulos — Tue, 01 Jul 2008 19:44:36 GMT

Well, i guess that's a pretty elaborate title, but it's what this post is about. How you text-wrapping when exporting to Excel via SQL Server Reporting services 2005 on the web be disabled.

It is unfortunate that there is no configuration option in when authoring a report to control whether the text can wrap or not.

Why disable text wrapping?

In the above screenshot we can see that when a given cell is constrained in size (i.e fixed height).
With text wrap on: the cell will not show the complete text unless it is wide enough.
With text wrap off: the complete text will display by overflowing onto the adjacent empty cells. So long as the next cells are empty.

But why would I want to do that when I could simply merge cells B + C + D together? It all comes down to usability and user expectations. If the end user expects to be able to sort the data in the columns then merged cells is just a feature that will be an obstacle in his path. In the screenshot below I try to sort by column B, but 1 of the rows contains merged cells so Excel naturally prompts the user.

If you do find yourself with a reporting services report that you have spent hours creating in your report designer and you are handed the above niche requirement, there are the following options for you friend:

Fix it manually through automation
Buy a third party product that has more control over the export such as http://officewriter.softartisans.com/OfficeWriter-250.aspx

If a third party product is not an option for you, you may find the following helpful.

Disable Text Wrap using Microsoft.Office.Interop.Excel

Office.Interop requires that Excel is installed on the machine that will render the report. Here is the function required:

        private static void DisableTextWrap(string fileName)
        {

            Application excel = new Application();
            try
            {
                Workbook workbook = excel.Workbooks.Open(
                        fileName,
                        Type.Missing, false, Type.Missing, Type.Missing,
                        Type.Missing, true, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                try
                {
                    Worksheet wsheet = workbook.Sheets[1] as Worksheet;
                    try
                    {
                        Range excelRange = wsheet.UsedRange;
                        try
                        {
                            excelRange.WrapText = false;
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(excelRange);
                        }
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(wsheet);
                    }
                    workbook.Save();

                }
                finally
                {
                    workbook.Close(false, fileName, null);
                    Marshal.ReleaseComObject(workbook);
                }
            }
            finally
            {
                excel.Quit();
                Marshal.ReleaseComObject(excel);
            }
        }

The above function will open an excel file and disable text-wrap in the used area of only the first spreadsheet

Enable a Web Server to run Excel through COM automation

In order to enable a web server to run Excel through COM automation you can follow this very helpful and detailed advice:

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

One point to note after following the above instructions
After running DCOMCNFG
In the Security Tab
Under Activation
Click customise. I would recommend that the excel is launched (activated) by a user that has already used excel once on the server you are trying to perform this. So, first create a user, then go to Excel right click and use 'Run As..' option and select the newly created user. The reason for doing this is that when a user runs excel for the first time, excel creates a profile for them and does some custom installation and also pops up a dialog box which is something we would like to avoid during the COM automation. Doing this will ensure that the installation of the user profile is not trying to take place under the COM automation.

Hope this helps someone!

Quick Tip: Use Hashbytes to create a Hash in TSQL in SQL Server

Anastasios Yalanopoulos — Wed, 23 Apr 2008 19:03:00 GMT

Just a quick tip.
I was unaware until recently that SQL Server 2005 has nicely built in support for hashing and it is called hashbytes

HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1

Parameters are the algorithm you wish to hash to and the input string to hash.
Here is a sample along with the return values commented in the next line:

Select HashBytes('MD2', 'Hello world!') 
--0x63503D3117AD33F941D20F57144ECE64

Select HashBytes('MD4', 'Hello world!') 
--0x0D7A9DB5A3BED4AE5738EE6D1909649C

Select HashBytes('MD5', 'Hello world!') 
--0x86FB269D190D2C85F6E0468CECA42A20

Select HashBytes('SHA', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802

Select HashBytes('SHA1', 'Hello world!') 
--0xD3486AE9136E7856BC42212385EA797094475802

MSDN: http://msdn2.microsoft.com/en-us/library/ms174415.aspx

Expose your objects over HTTP with minimal coding

Anastasios Yalanopoulos — Mon, 24 Mar 2008 05:44:16 GMT

Quite often we create systems that make use of web services so as to expose our Data, or Business methods for client applications to consume them. In a classic 3 tier scenario it may be either the data layer exposed through the web service as a data service, or the business layer exposed through the web service as a business service. I would say that both are perfectly valid for exposure through a web service depending on our needs and requirements. But since web services already exist, why make a custom method and use this method instead of web services? The following paragraph contains some points I dont like when coding standard microsoft web services.

Points I don't like when using Microsoft Web Services.

What I do not like when coding a web service when using the standard .Net framework approach is the feeling of redundant coding I get. Surely you've felt the same at some point, especially when all we are doing is simply coding pass through functions to my business or data layer.

Further to that, I end up having to maintain a mess of web service references in my client application. I need to add one reference to each asmx page created. Further to this, when creating web service references, the Microsoft .Net framework will create a custom Proxy to access this web service. This custom proxy will also re-define any complex types (eg typed datasets or custom classes) returned through the web service. What i dont like about this, is that even though two seperate asmx files may expose the same type, each web service reference added will redifine the same type on its own copy of the proxy. This leaves the client application with multiple definitions of what essentially is the same data type.

What would I like to see instead?

I would like to be able to switch between 'web service' mode and 'non web service' mode easily, with just a simple switch in the client application.
I do not want to create redundant code in webmethods just so that I can expose my data or business objects over HTTP. The code is already in those layers, I just want the ability to consume them over an HTTP connection.
i would just like to add a 'magic' attribute to my business/data classes that would mean 'This object can be invoked over HTTP.
I would like to be able to consume my business or data objects seamlessly, with no requirements to add tons of web references on my client application.

For these reasons, i thought it would be worthwhile to find a method of dynamically exposing my data or business objects over an HTTP channel. I'd like to share a solution which addresses the above issues, but as in most cases there are tradeoffs.

Whats the tradeoff though? The tradeoff is that this is not webservices and because of that, this custom HTTP invocation protocol is not interoperable across other non .Net platforms.

If interoperability is a requirement in your application then this post does not offer any solutions to the above points. If however interoperability with other platforms is not a requirement, then you may find this helpful. Furthermore this solution requires that your Base Business or Data objects inherit from ContextBoundObject, but this requirement could be overcome in a different implementation.

Method Interception and how this solution works.

This solution is based on the following bullet points:

Create an object and intercept all method calls to that object
Determine if we are in a 'web service mode'
if we are not in 'web service mode', then just call the underlying dataobject method and return its value.
If we are in 'web service mode' then marshal the call accross to a custom HTTP Handler, this handler will create the dataobject via reflection execute the method and return the value (if any) and output parameters.

The key point of this architecture is the ability to intercept method calls to the object, as soon as we intercept method calls we can decide whether we route the request to the underlying object, or route the request over HTTP to our Handler. This handler is generic and does not require any code modifications. It does however require a reference to our dll that contains our DAL classes.

Thomas Danecker has an excellent post about how to intercept methods with a custom proxy.
http://tdanecker.blogspot.com/2007/09/interception-with-proxies.html

I like that solution because it uses only classes from the .Net framework. What I dont like about it, is the requirement it emposes on the proxyable types to derive from the ContextBoundObject type.

Oren has a nice short post where he compares 7 different options when it comes to method interception, with pros and cons of each:
http://www.ayende.com/Blog/archive/2007/07/02/7-Approaches-for-AOP-in-.Net.aspx

This solution uses interception with native Microsoft .Net classes as per Thomas's example.

Ok, now some code!

Our sample application consists of a DAL, a BLL a UI and a Test Project with some Test Cases. Our project also includes the Dynamic Service itself. So, let's see some of this in action and how it would all fit together. For the purpose of this test, our UI is a console application. This is what it would typically look like.

Listing #1:

using System;
using System.Text;
using DynamicService.Library;
using DynamicService.DAL;

namespace DynamicService.UI
{
    class Program
    {
        static void Main(string[] args)
        {

            using (WebServiceSettingsScope settingScope = 
                    new WebServiceSettingsScope(
"http://localhost:4040/Dataservice.ashx?db=TestDb", "", "", 3000))
            {
                TestDal dal = new TestDal();
                int i = dal.ReturnInputParameter(10);

                Console.WriteLine(i);
            }

            Console.ReadKey();
        }
    }
}

And this is what our TestDal class would look like (listing #2):

using System;
using System.Text;
using System.Data;
using DynamicService.Library;

namespace DynamicService.DAL
{
    [HttpRemotable]
    public class TestDal : ContextBoundObject
    {

        public TestDal()
        {
            //This is how the dal knows it's connection string for example...
            //the code below could be added in a base dal class that would allow the derived data objects 
            //to know about the database they are talking to. (useful for multi database scenarios)
            DbConnectionStringSettings connectionStringSettings = 
                ConnectionSettingsScope.ActiveConnectionSettings as DbConnectionStringSettings;
            string connectionString;
            if (connectionStringSettings != null)
                connectionString = connectionStringSettings.ConnectionString;
        }

        public int ReturnInputParameter(int param)
        {
            return param;
        }
    }
}

Well, this should get us going! So, the solution proposes that objects that can be invoked remotely over HTTP are decorated with an HttpRemotable attribute. They must also derive from ContextBoundObject so that their methods can be intercepted by the appropriate proxy and routed accordingly, either to the object directly, or to our generic handler.

Listing #1 shows that the remotable object, in this case our TestDal, should be instantiated within a ConnectionSettingsScope. There are two types of connectionSettingsScope, two classes that derive from this class. There is a DbConnectionSettingsScope and a WebServiceSettingsScope.

Depending on the scope that the TestDal object is being created it will be wrapped with either a ServiceProxy or an InterceptorProxy. The serviceproxy will route the requests to the service defined in the WebServiceSettingsScope. If created within a DbConnectionSettingsScope the InterceptorProxy will simply forward the requests on to the underlying object within the same application domain.

How does this work behind the scenes? The decision of which Proxy is created is made within the HttpRemotableAttribute.

using System;
using System.Text;
using System.Runtime.Remoting.Proxies;

namespace DynamicService.Library
{
    public class HttpRemotableAttribute : ProxyAttribute
    {
        public override MarshalByRefObject CreateInstance(Type serverType)
        {
            if (!ConnectionSettingsScope.Exists)
                throw new Exception("Error creating serviced object: "  + serverType.FullName + "\r\n\r\n You must define the ServiceScope to create a ServicedObject\r\ne.g. using(ServiceScope svcScope = new svcScope(...)) \r\n{ //object instantiation } \r\n\r\n");

            RealProxy proxy = null;

            //if we are using a remote invocation then create a service proxy 
            if (ConnectionSettingsScope.UseRemoteInvocation)
            {
                WebServiceSettings webServiceSettings = ConnectionSettingsScope.ActiveConnectionSettings as WebServiceSettings;
                if (webServiceSettings == null)
                    throw new Exception("Unexpected error: the ConnectionScope.ActiveConnectionSettings is null.");

                proxy = new ServiceProxy(serverType, webServiceSettings);
            }
            else
            {
                //just create a dumb proxy that will forward on the requests locally
                proxy = new InterceptorProxy(serverType);
            }

            MarshalByRefObject transparentProxy = (MarshalByRefObject)proxy.GetTransparentProxy();
            return transparentProxy;
        }
    }
}

HttpRemotable is a special attribute, inheriting from ProxyAttribute. What makes it so special is the CreateInstance method which is called when we try to create an instance of an object that is derived from ContextBoundObject and that is decorated with a ProxyAttribute derived class such as HttpRemotable. It determines the type of proxy to be created depending on the Active ConnectionSettings on our ConnectionSettings stack.

What is this ConnectionSettingsScope that I keep refereing to?

using System;
using System.Text;
using System.Threading;
using System.Collections.Generic; 
using DynamicService.Library;

namespace DynamicService.Library
{

    public class ConnectionSettingsScope : IDisposable
    {

        [ThreadStatic] //Each thread will have its own copy of a ServiceScope stack
        private static Stack<ConnectionSettings> _ConnectionSettings;

        protected static Stack<ConnectionSettings> ConnectionSettingsStack
        {
            get 
            {
                //this is thread safe, ConnectionSettings is a ThreadStatic variable.
                if (_ConnectionSettings == null)
                    _ConnectionSettings = new Stack<ConnectionSettings>();

                return _ConnectionSettings;
            }
        }

        public ConnectionSettingsScope(ConnectionSettings ConnectionSettings)
        {
            ConnectionSettingsStack.Push(ConnectionSettings);
        }

        static ConnectionSettingsScope()
        {

        }

        public ConnectionSettingsScope()
        {

        }


        public static bool UseRemoteInvocation
        {
            get { return ActiveConnectionSettings is WebServiceSettings; }
        }
        


        public static bool Exists
        {
            get { return (ConnectionSettingsStack!= null) && (ConnectionSettingsStack.Count > 0); }
        }

        public static ConnectionSettings ActiveConnectionSettings
        {
            get { return GetCurrentThreadConnectionSettings(); }
        }

        private static ConnectionSettings GetCurrentThreadConnectionSettings()
        {
            if (ConnectionSettingsStack.Count > 0) 
                return ConnectionSettingsStack.Peek();
            else
                return null;
        }

        // Dispose() calls Dispose(true)
        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        // The bulk of the clean-up code is implemented in Dispose(bool)
        protected virtual void Dispose(bool disposing)
        {
            if (disposing)
            {
                // free managed resources
                ConnectionSettingsStack.Pop(); // Remove the latest server url from the threadstatic stack
            }
        }
    }
}

Maybe I need to explain myself a little on the above code snippet. ConnectionSettingsScope contains a ThreadStatic stack of ConnectionSettings. Each time we create a new ConnectionSettingsScope object it pushes the latest ConnectionSettings class on to its ThreadStatic stack (ThreadStatic means that the stack is static to the thread level, each thread has its own ConnectionSettings stack). Each time we create an HttpRemotable object, the Settings used on it, will be based on the latest ConnectionSettingsScope.

DataService.ashx explained

So what happens on the other side of the fence. What happens when I we are creating our HttpRemotable object within a WebServiceSettingsScope?
All method calls are intercepted by the ServiceProxy and a ServiceRequest is sent over to our ServiceHandler. The service request contains all sorts of information of what we are trying to do, it contains information such as the fully qualified name of the underlying real type that has been proxied, the method we want to execute, and all the parameters passed to the method.

This information is used by the handler to instantiate the object via reflection and call the appropriate method. When method execution completes, the handler constructs a ServiceResponse to send back to the client. This response contains the return value along with the values of any output parameters.

The actual connection string to the database is stored on the server side. To allow for a multidatabase scenario, the handler is called in the format of DataService.ashx?db=ConnectionStringKeyName

ConnectionStringKeyName is the key in the web.config of the connectionstring to the database we want to use.

All objects are created within a DbConnectionSettingsScope region on the service handler. They will be pointing to the database in the selected connectionstring entry of the web.config as described above.

The DataService.ashx must contain a reference to the dlls that contain the types to be remotely invoked. In this case a single reference to our Dal will suffice. Furthermore, when publishing this web project we must ensure that the referenced dll is also in place.
I can see some are already thinking that adding references is what i wanted to avoid. True to some extent, what i wanted to avoid was the volume of references added. In classic web services I would have typically created one seperate asmx file per data object that i wanted to expose.

The Service Handler supports:

Methods with void result
Methods with parameters whose values are serialisable
Graceful handling of exceptions and returning them to the client
Methods with output parameters
Overloaded methods with different parameter signatures.
ServiceResponses are gzipped by default to minimise data traffic

The Service Handler does not support:

Methods whose parameter values are not serialisable
Generic methods

Testing and Performance

As mentioned above, the generic service handler uses reflection to reconstruct the underlying object being invoked. In order to assess whether this would mean a serious performance hit or not I created a few tests that benchmark it against equivalent invocations against a microsoft .Net web service.

The performance is pretty much similar, and in some cases the custom ServiceHandler calls outperform the equivalent calls in web services.

The following image contains the various test cases that validate the features supported by the generic Service Handler.

Summing it all up

This is a solution that you may consider, so long that cross-platform interoperability is not a concern to your application. The remotable objects should only have stateless atomic operations invoked upon them just as the case would be in Web Services. If you require to have the object alive on the remote tier then Remoting is probably for you. This solution allows you to easily switch between a web service mode and a non webservice mode by literally changing 1 line of code in your application. This solution minimises redundant coding in a web service tier and allows you to expose your objects over an HTTP channel by simply decorating them with one attribute and deriving from ContextBoundObject. I hope this helps yeou shave off some development time from your projects!

Suggestions, complaints, errors? I will be very happy to hear your opinion and feedback on this. There is a visual studio 2008 solution file that can be downloaded from here to get you started. Happy Coding!

Sample solution for this post: DynamicService.zip

C# 3.0 Anonymous type support is incomplete

Anastasios Yalanopoulos — Tue, 27 Nov 2007 02:54:11 GMT

For those who have been playing with Linq and getting up to speed with the latest C# 3.0 features, you are most likely aware of the new language feature of Anonymous Types.

An anonymous type allows us to create and an instance of a an object whose type has not been defined in our code but whose properties are inferred from the object initializer.

The following example illustrates this by creating an instance of a variable of an unknown type that has 3 properties

var product = new
{
    Name = "Acme Rocket",
    Description = "The quick way to the moon",
    Stock = 10
};

The variable type of product is not defined in our code. It is inferred through type inference and is of a compiler generated anonymous type that has 3 properties whose types again are inferred through type inference.

Anonymous types are a very powerful feature since they allow for creating LINQ projections without the need to define a class containing the projected fields of our query.

So in the following adapted sample taken from microsoft:

    string[] words = { "aPPLE", "BlUeBeRrY", "cHeRry" };
    var upperLowerWords =
        (from w in words
        select new { Upper = w.ToUpper(), Lower = w.ToLower() }).ToList();

    foreach (var ul in upperLowerWords)
    {
        Console.WriteLine("Uppercase: {0}, Lowercase: {1}", ul.Upper, ul.Lower);
    }

upperLowerWords is an instance of an IEnumerable<anonymousType> and is also intellisensed as such:

This is all very good. We can create and use anonymous types in our code.

So what is the problem and why am I saying that anonymous type support is incomplete?

The problem lays in the fact that type inference only works on local variables. Which means that the anonymous type in our example (and anonymous types in general) is only useful as a strongly bound object in the scope of the procedure in which it has been created. Anonymous types cannot be returned as strongly typed objects in function return values. They can only be returned as objects as the following code shows


object ReturnAnonymous()
{
    return new { Name = "Acme Rocket", Description = "Quick way to the moon", Stock = 10};
}

There is an interesting yet dirty workaround for returning anonymous types and getting strongly typed access to a return value of type anonymousType from the calling code.

However, the suggested approach is to define a solid class in your code and use that as the return type instead of returning an anonymous type. In a LINQ Datalayer scenario this means that for each function that returns a projection of Data we would have to define and maintain empty classes that are merely there just to map to the projected object...

Further reading about this issue

Msdn forum post and proposed resolutions:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2434363&SiteID=1

Rick also talks about this issue in one of his posts (http://www.west-wind.com/WebLog/posts/33570.aspx)

I really enjoy the new features of C# 3.0 but not being able to return anonymous types across functions cripples the potential of this language feature. This appears to be due to limitations in type inference which only works locally, and also seems to be a bit flaky if you ask me, from what I've been reading anyway:

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=295134

http://blogs.msdn.com/ericlippert/archive/2007/11/05/c-3-0-return-type-inference-does-not-work-on-member-groups.aspx

Conclusion
Anonymous types even only accessible through local type inference are a really powerful language construct that have added value to LINQ queries (projections) . But I sure do look forward to seeing the C# compiler team unleashing the full potential of type inference and anonymous types by allowing us to return anonymous types as return values in our functions.

Yield return and Iterators use case: looping through the days between a date span

Anastasios Yalanopoulos — Thu, 15 Nov 2007 23:54:28 GMT

I recently found myself in the situation where I had to loop through all the days from a StartDate to an EndDate.

Sure easy, all you need to do is something like this:

DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now.AddDays(3);

DateTime curDate = startDate;
while (curDate <= endDate)
{
    //Do Something with curDate ...

    curDate = curDate.AddDays(1);
}

Does the job, but it is not very intuitive in my opinion. It is not that easy to read.
Wouldn't it be so much nicer if we could do something like this:

DayIterator dayIterator = new DayIterator(startDate, endDate);
foreach (DateTime dt in dayIterator)
{
     //Do Something with dt... 
}

I think the second version really shows the intent of what we are trying to do and is much more easy to follow and maintain.

So how do we go about implementing the DayIterator then? As you will see for yourself it really is simple code that makes use of the yield return statement for Iterators.

Here is the implementation of the DayIterator:

    public class DayIterator : IEnumerable<DateTime>
    {

        private DateTime _StartDate;
        private DateTime _EndDate;

        public DayIterator(DateTime startDate, DateTime endDate)
        {
            _StartDate = startDate;
            _EndDate = endDate;
        }

        public IEnumerator<DateTime> GetEnumerator()
        {

            DateTime currentDate = _StartDate;
            while (currentDate <= _EndDate) // Note that our Iterator is inclusive of endDate behaving like 'between'
            {
                yield return currentDate; // <-- This is the key line
                currentDate = currentDate.AddDays(1);
            }
        }

        #region IEnumerable Members

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {

            throw Exception("Not Implemented");

        }

        #endregion
    }

Thats all it takes! Now we have our custom DayIterator class. Now when we do foreach (DateTime dt in dayIterator) what foreach does is it calls GetEnumerator to start the loop. The yield return statement within GetEnumerator returns an instance of a DateTime class so the outer foreach loop starts looping using this as the first element. Each time our code reaches the foreach statement it calls GetEnumerator again resuming from the last yield return statement. When our GetEnumerator function finishes then the outer foreach loop stops looping. So in this example the GetEnumerator will finish when the condition
while (currentDate <= _EndDate) is not true anymore and therefore the foreach loop will terminate.

We can also use yield return break within the GetEnumerator implementation if we want to terminate the foreach looping.

So I guess some of you may be wondering What's with the two GetEnumerator functions instead of just one? Well In this example we want to loop through a collection of DateTime objects for this reason we inherited the DayIterator class from IEnumerable<DateTime>. This requires us to implement both the IEnumerable<DateTime> GetEnumerator() function and the IEnumerbale GetEnumerator (the latter returns only objects and we need not implement)

Ok, this is all good but does it really work?! Well, lets put it to the test with the following little sample code:


DateTime startDate = DateTime.Now;
DateTime endDate = DateTime.Now.AddDays(5);

Console.WriteLine("StartDate: {0:dd MMM yyyy}", startDate);
Console.WriteLine("EndDate: {0: dd MMM yyyy}", endDate);

DayIterator dayIterator = new DayIterator(startDate, endDate);
foreach (DateTime dt in dayIterator)
{
    Console.WriteLine("In foreach: {0:dd MMM yyyy} ", dt);
}

This is what we get:

Now the DayIterator Class can be easily expanded to include functions such as IsCurrentDayInSameMonthAsPrevious and the resulting code again looks even more elegant in a scenario where while we are looping through the days, we want to do something on when the month changes from one day to the next for example. It is quite straightforward to implement but as an excercise I will leave it up to you to implement :)

So if you find yourself looping in a for or a while loop, take another careful look at your code, you may be able to use an Iterator and express your code intent in a more clear and concise fashion. Of course this does not mean that we take it to the other extreme abandoning all loops for Iterators!

Question:
Can I implement the IEnumerbale GetEnumerator with the same logic as IEnumerable<DateTime> GetEnumerator() but without copying down the same code in two places, how?

Further Reading:
http://www.yoda.arachsys.com/csharp/csharp2/iterators.html
http://codebetter.com/blogs/david.hayden/archive/2006/10/05/C_2300_-2.0-Iterators-and-Yield-Keyword-_2D00_-Custom-Collection-Enumerators.aspx