Friday, 22 February 2013

Split string Function in SQL Server



Create FUNCTION [dbo].[Split]
(
       @String varchar(MAX),
       @Delimiter char(1)
)      
RETURNS @TempTable TABLE (Items varchar(MAX))      
AS      
BEGIN      
    DECLARE @Indx int
    DECLARE @Slice varchar(MAX)          
   
    IF len(@String)<1 or @String is null  RETURN     
    
    SET @Indx = 1 
   
    WHILE @Indx != 0
    BEGIN      
        SET @Indx = charindex(@Delimiter, @String)
       
        IF @Indx != 0
            SET @Slice = left(@String, @Indx - 1)      
        ELSE      
            SET @Slice = @String      
         
        IF(len(@Slice)>0) 
            INSERT INTO @TempTable(Items) VALUES (@Slice)      
 
        SET @String = right(@String,len(@String) - @Indx)      
        IF len(@String) = 0 BREAK      
    END
    RETURN      
END

Ex:
Select * from Split('A,B,C,D', ',')

Output:
Items
A
B
C
D

Thursday, 7 February 2013

Dynamic number of parameters, params in C#



The params keyword lets you specify a method parameter that takes a variable number of arguments.
You can send a comma-separated list of arguments of the type specified in the parameter declaration, or an array of arguments of the specified type. You also can send no arguments.
No additional parameters are permitted after the params keyword in a method declaration, and only one params keyword is permitted in a method declaration.
The following example demonstrates various ways in which arguments can be sent to a params parameter.
public class MyClass
{
    public static void UseParams(params int[] list)
    {
        for (int i=0; i<list.Length; i++)
        {
            Console.Write(list[i] + " ");
        }
        Console.WriteLine();
    }

    public static void UseParams2(params object[] list)
    {
        for (int i=0; i<list.Length; i++)
        {
            Console.Write(list[i] + " ");
        }
        Console.WriteLine();
    }

    static void Main()
    {
        // You can send a comma-separated list of arguments of the  
        // specified type.
        UseParams(1, 2, 3, 4);
        UseParams2(1, 'a', "test");

        // A params parameter accepts zero or more arguments.
        // The following calling statement displays only a blank line.
        UseParams2();

        // An array argument can be passed, as long as the array 
        // type matches the parameter type of the method being called. 
        int[] myIntArray = { 5, 6, 7, 8, 9 };
        UseParams(myIntArray);

        object[] myObjArray = { 2, 'b', "test", "again" };
        UseParams2(myObjArray);

        // The following call causes a compiler error because the object 
        // array cannot be converted into an integer array. 
        //UseParams(myObjArray); 

        // The following call does not cause an error, but the entire  
        // integer array becomes the first element of the params array.
        UseParams2(myIntArray);
    }
}

/*
Output:
    1 2 3 4
    1 a test

    5 6 7 8 9
    2 b test again
    System.Int32[]
*/

Visual C# Breaking Changes in Visual Studio 2012

Lambda expressions
The use of a foreach iteration variable in a nested lambda expression no longer produces unexpected results. The following example uses the variable word in a lambda expression.
static void Main()
{
    var methods = new List<Action>();
    foreach (var word in new string[] { "hello", "world" })
    {
        methods.Add(() => Console.Write(word + " "));
    }

    methods[0]();
    methods[1]();
}

// Output in Visual Studio 2012:
// hello world

// Output in Visual Studio 2010:
// world world


LINQ expressions
The use of a foreach iteration variable in a LINQ expression no longer produces unexpected results. The following example uses the variable number in a LINQ query.
static void Main()
{
var lines = new List<IEnumerable<string>>();
      int[] numbers = { 1, 2, 3 };
      char[] letters = { 'a', 'b', 'c' };

      foreach (var number in numbers)
      {
            var line = from letter in letters
                  select number.ToString() + letter;

lines.Add(line);
}

      foreach (var line in lines)
      {
foreach (var entry in line)
                    Console.Write(entry + " ");
            Console.WriteLine();
      }
}
// Output in Visual Studio 2012:
// 1a 1b 1c
// 2a 2b 2c
// 3a 3b 3c

// Output in Visual Studio 2010:
// 3a 3b 3c
// 3a 3b 3c
// 3a 3b 3c


Named Arguments
Side effects from named and positional arguments that are combined in a method call are now produced from left to right in the calling statement's argument list. In the following example, TestMethod is called by using a combination of named and positional arguments in different orders.
static void Main(string[] args)
{
      Console.ReadKey();

       var lines = new List<IEnumerable<string>>();
       int[] numbers = { 1, 2, 3 };
       char[] letters = { 'a', 'b', 'c' };

       foreach (var number in numbers)
       {
            var line = from letter in letters
                  select number.ToString() + letter;

             lines.Add(line);
       }

       foreach (var line in lines)
       {
            foreach (var entry in line)
                    Console.Write(entry + " ");

             Console.WriteLine();
       }
}

// Output in Visual Studio 2012:
// A B C A C B

// Output in Visual Studio 2010:
// B C A C B A
 

Overload Resolution
When more than one resolution candidate is found, overload resolution prefers the most specific type match for named arguments. Parameters for which arguments aren’t required or provided in the call are considered only when the type matches in overload candidates are equally good.
In the following example, string is a better type than object for p2. Therefore, the version of ExampleMethod in which parameter p2 is defined as a string should be chosen, even though it has a third params parameter.
class Program
{
static void Main(string[] args)
      {
            ExampleMethod(p2: "");
      }

      public static void ExampleMethod(string p1 = null, object p2 = null)
      {
            Console.WriteLine("ExampleMethod: p2 is object");
      }

      public static void ExampleMethod(string p2 = null, object p1 = null,
         params int[] p3)
      {
            Console.WriteLine("ExampleMethod: p2 is string");
      }
}

// Output in Visual Studio 2012:
// ExampleMethod: p2 is string

// Output in Visual Studio 2010:
// ExampleMethod: p2 is object
 

In the following example, the call to CandidateMethod that sends a Func<dynamic> argument has two resolution candidates. The corresponding parameter in one of the candidates is Func<object>, and the corresponding parameter in the other is Func<string>.
The overload candidate that has a Func<object> parameter should be chosen because object and dynamic are considered to be equivalent. Therefore, an identity conversion exists not only between dynamic and object but also between constructed types Func<dynamic> and Func<object>.
class Program
{
public static void CandidateMethod(Func<object> fun)
      {
Console.WriteLine("Method that has a Func<object> parameter.");
      }

      public static void CandidateMethod(Func<string> fun)
      {
Console.WriteLine("Method that has a Func<string> parameter.");
      }

      static void Main(string[] args)
      {
dynamic dyn = 15;
CandidateMethod(() => { return dyn; });
}
}

// Output in Visual Studio 2012:
// Method that has a Func<object> parameter.

// Output in Visual Studio 2010 if Microsoft.CSharp is referenced:
// Method that has a Func<string> parameter.

// Output in Visual Studio 2010 if Microsoft.CSharp isn’t referenced (for instance, in a Unit Test Project):
// Method that has a Func< object > parameter.