Dec 31, 2013

C# Switch statement alternative

You can use a Dictionary to create a mapping of Condition => Action.


 

This technique is not a general alternative to switch statement.
Suppose you have a workflow system that is required to perform validation of a data item. Different validation routines are executed based upon some state of the data being processed. Using this technique means
a) You do not have a switch statement with over 100 case statements - very difficult to read.
b) You do not have to include conditional logic in the case statement for each validation routine - very difficult to maintain.
c) You can offload the validation routines and conditional logic into a database where the customer can then update when a validation routine executes as per their requirements - very flexible.

Dec 27, 2013

Calling .NET function from a SQL query

In this example we will write function for matching regular expression

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

namespace SqlRegularExpression
{
    public partial class RegularExpression
    {
        [SqlFunction]
        public static bool Like(string text, string pattern)
        {
            Match match = Regex.Match(text, pattern);
            return (match.Value != String.Empty);
        }
    }
}


In the SQl Server side, we will need to enable the CLR(Common Language Runtime) of .NET:

SELECT * FROM sys.configurations
where name = 'user connections'
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
CREATE ASSEMBLY SqlRegularExpression from 'C:\anyFolder\SqlRegularExpression.dll' WITH PERMISSION_SET = SAFE --- please check folder name
Go

In order to be able to use a method from any DLL, we should create a function or store procedure by using an external name:

CREATE FUNCTION RegExpLike(@Text nvarchar(max), @Pattern nvarchar(255))
RETURNS BIT AS
EXTERNAL NAME SqlRegularExpression.[SqlRegularExpression.RegularExpression].[Like]
go


Here it goes an example for a query:

select dbo.RegExpLike('Roy Jimenez', '^[a-zA-Z''.\s]{1,40}$');
go

You can use check constraints in a table to validate well constructed data in the database.
For example:

create table Users (
    login varchar(15) not null check ( dbo.RegExpLike(login, '^[A-Za-z0-9_]*$') = 1 ),
    name varchar(100) not null
)
go

Convert a list to CSV string

Code to convert list of string to CSV
public static string ToCsv(this IEnumerable source)
{
  if (source == null)
    throw new ArgumentNullException("source");
  return string.Join(",", source.Select(s => s.ToString()).ToArray());
}