Saturday, April 23, 2011

Automatic Properties, Object Initializers and Collection Initializers


Introduction
In c# 3.0 and later versions many new features are available but some people are not aware of them. There are some new features explained below In C# 3.0 and later.

Auto-implemented properties:

Auto-implemented properties make property-declaration more concise when no additional logic is required in the property accessors. They also enable client code to create objects. When you declare a property as shown in the following example, the compiler creates a private, anonymous backing field that can only be accessed through the property's get and set accessors.

Let’s see the old writing method.

  public class Person
  {
    private string _firstName;
    private string _lastName;
    private int _age;

    public string FirstName
    {
      get
      { return _firstName; }
      set
      { _firstName = value; }
    }

    public string LastName
    {
      get
      { return _lastName; }
      set
      { _lastName = value; }
    }

    public int Age
    {
      get
      { return _age; }
      set
      { _age = value; }
    }
  }
    
 Now we can see the enhanced implementation.

  public class Person
  {
    public string FirstName
    {
      get;
      set;
    }

    public string LastName
    {
      get;
      set;
    }

    public int Age
    {
      get;
      set;
    }
  }

Note: This way is possible only when you don’t want to write any logical implementation in the getter/setter part. If you want to write implementation then you must go through the old method.

Object & Collection Initializers:

Object/Collection initializers, let you assign values to any accessible fields or properties of an object at creation time without having to explicitly invoke a constructor. 

Considering above example now we initialize of an object with the old method.

  Person person1 = new Person();

  person1.FirstName = "Munavvar";
  person1.LastName = "Husein";
  person1.Age = 25;

Now in place of this we can write it like,

  Person person1 = new Person
  {
    FirstName = "Munavvar",
    LastName = "Husein",
    Age = 25
  };

In the case of Collections same thing would be done as under. Considering above examle to make a list with old method.

  List<Person> lstPerson = new List<Person>();

  Person persons = new Person();
  persons.FirstName = "Munavvar";
  persons.LastName = "Husein";
  persons.Age = 25;
  lstPerson.Add(persons);

  persons = new Person();
  persons.FirstName = "Rikin";
  persons.LastName = "Patel";
  persons.Age = 26;

  lstPerson.Add(persons);

Now in place of this we can write it like,

  List<Person> lstPerson = new List<Person>
  {
    new Person
    {
      FirstName = "Munavvar",
      LastName = "Husein",
      Age = 25
    },
    new Person
    {
      FirstName = "Rikin",
      LastName = "Patel",
      Age = 26
      }
    };

Conclusion:
  As per enhanced feature we can just reduse the burdon of typing extra statements with no loss of performance.

Friday, April 15, 2011

Generating Comma separated list through T-SQL

Introduction
Many times while working on LOB applications, we often land into a situation where we want to generate a delimiter separated string from the record set. Here, I will walk through the different ways which I know in which we can achieve the same. For simplicity sake, the delimiter I have chosen here is a comma (‘,’)

Implementation
Let’s first create some sample data.
    
DECLARE @Cities AS TABLE
(
  [CityID] [int] IDENTITY(1,1) NOT NULL,
  [CityName] [varchar](100) NULL,
 PRIMARY KEY ([CityID])
)

INSERT INTO @Cities
SELECT 'Surat' UNION ALL
SELECT 'Mumbai' UNION ALL
SELECT 'Sawai Madhopur' UNION ALL
SELECT 'Ahmedabad'

We want to create a list of all the valid @Lookup values (Example: Surat,Mumbai,Sawai Madhopur,Ahmedabad).

To get this one would normally use either a Cursor or WHILE loop to read through the table above. Instead there are many easier ways to do the same.

First let's list the traditional CURSOR approach.

--------------- CURSOR approach ---------------

DECLARE @CityNameList VARCHAR(1000)
DECLARE @CityNames VARCHAR(25)

SET @CityNameList = ''

DECLARE clookup CURSOR
      FOR SELECT DISTINCT CityName
      FROM @Cities

OPEN clookup

FETCH NEXT FROM clookup INTO @CityNames
WHILE @@FETCH_STATUS = 0
BEGIN
      SET @CityNameList = ISNULL(@CityNameList,'') + @CityNames + ','
      FETCH NEXT FROM clookup INTO @CityNames
END
CLOSE clookup
DEALLOCATE clookup

SET @CityNameList = SUBSTRING(@CityNameList, 1, LEN(@CityNameList)-1) -- drop the trailing delimiter
SELECT @CityNameList
-------------------------------------------------------------------

Now we can see other approaches which are better than the above approach.

----------------------Other Approaches----------------------
1.      This way is very known and simplest which is used by most of the programmers.

DECLARE @CitieNames AS VARCHAR(MAX)
DECLARE @CitieIDs AS VARCHAR(MAX)

SELECT
  @CitieIDs = COALESCE(@CitieIDs + ',', '') + CONVERT(VARCHAR, C.CityID),
  @CitieNames = COALESCE(@CitieNames + ',', '') + C.CityName
FROM
  @Cities C
         
SELECT @CitieIDs CityIDs, @CitieNames Cities

--Output:




2.      Now I want to make only comma separated ids.

SELECT
  REPLACE
    (
      (
        SELECT
          C.CityID as 'data()'
        FROM
          @Cities C
        FOR XML PATH('')
      ), ' ', ','
 ) CityIDs

--Output





3.      Now I want to make only comma separated names.

SELECT
  STUFF(
        (
          SELECT
            ',' + C.CityName
          FROM
            @Cities C
          FOR XML PATH('')
   ), 1,1,''
  ) CityNames

--Output:





After experiencing the different approaches, I would like you to concentrate on the below points -
1.      Usage of REPLACE in Step 2 and STUFF in Step 3.
Performance wise Replace function gives the better performance. However, it cannot be used always. Suppose, I will make the comma separated names with the use of replace function it will not give the proper output like,
 --Output:







See the mark value in output, It will replace “Sawai Madhopur” to “Sawai,Madhopur”.


2.      Main Limitation of FOR XML PATH is it cannot be used to make the delimiter separated values for more than 1 column. Follow Step 1 instead.

Conclusion
Unfortunately, you can't make it generic (tables and columns are different for each task), but it can be implemented as template.