String Concatenation in SQL Group By clause

I got one problem in Ahmedabad SQLServer UserGroup.

The problem was something like... there are grouped data and requirement was making sum of string collection, means concatenation of string which are in the same group. Here is the question along with solutionSource Table

Field1     Field2
---------- ----------
Group 1    Member 1
Group 1    Member 2
Group 1    Member 3
Group 2    Member 1
Group 2    Member 2
Group 3    Member 1
Group 3    Member 2

Need output as

Field1     Field2
---------- ----------------------------
Group 1    Member 1,Member 2,Member 3
Group 2    Member 1,Member 2
Group 3    Member 1,Member 2

Solution:

SET NOCOUNT ON

--Creating Tables
DECLARE @Fields AS TABLE
(
    Field1 VARCHAR(10),
    Field2 VARCHAR(10)
)

--Inserting some values
INSERT INTO @Fields VALUES ('Group 1','Member 1')
INSERT INTO @Fields VALUES ('Group 1','Member 2')
INSERT INTO @Fields VALUES ('Group 1','Member 3')
INSERT INTO @Fields VALUES ('Group 2','Member 1')
INSERT INTO @Fields VALUES ('Group 2','Member 2')
INSERT INTO @Fields VALUES ('Group 3','Member 1')
INSERT INTO @Fields VALUES ('Group 3','Member 2')

--T-Sql
SELECT Field1, Substring(Field2, 2, LEN(Field2)) AS Field2 FROM
(
    SELECT
        [InnerData].Field1,
        (SELECT  ',' + Field2 FROM @Fields WHERE Field1=[InnerData].Field1 FOR XML PATH('')) AS Field2
        FROM
        (
            SELECT DISTINCT Field1 FROM @Fields
        ) AS [InnerData]
) AS OuterData

Display multiple comma separated value into single column output Part II

Here is the data:

Field1      Field2
----------- --------------------
1           A,B,C
2           A
3           D,G

And we need output as following

Field1               ID
-------------------- ----
1                    A
1                    B
1                    C
2                    A
3                    D
3                    G

Lets create data first.

DECLARE @Fields AS TABLE
(
    Field1 INT,
    Field2 VARCHAR(20)
)

INSERT INTO @Fields VALUES (1,'A,B,C')
INSERT INTO @Fields VALUES (2,'A')
INSERT INTO @Fields VALUES (3,'D,G')

Here is the query for getting expected result.

SET @AnswerXML =
    REPLACE(REPLACE((SELECT
                        Field1, '<Ids><Id value="' + REPLACE(Field2, ',','" /><Id value="') + '" /></Ids>'
                    FROM @Fields
                    FOR XML PATH ('F')
                ), '&lt;', '<'), '&gt;', '>')

SELECT
     Answer.value('Field1[1]', 'BIGINT') as Field1
    ,y.value('@value[1]', 'VARCHAR(1)') AS ID
FROM
    @AnswerXML.nodes('/F') p(Answer)
OUTER APPLY Answer.nodes('Ids/Id') o(y)

Also check Display multiple comma separated value into single column output Part I

Creating comma separated string from Array - LINQ

Its common requirement to create comma separated string from Array. Using extension methods of LINQ we can do it in single line. Lets create Array first from comma separated values, its simple as we just need to split them using Split function.

string strTest = "1,2,4,6";
string[] Nums = strTest.Split(',');

let's create comma separated string from string Array Nums,

Generally people use foreach loop to create the comma separated string,

But here is the single line code using Lambda Expression to create the same thing.

Console.Write(Nums.Aggregate<string>((first, second) => first + "," + second));
//OUTPUT:
//1,2,4,6

Here the Lambda expression which is accepting two argument and returning the string having concatenation along with delimiter.

Querying Selected Items From ListItemCollection Using LINQ

Working with ListItemCollection is general practices and getting single value for that it's like child's play. What if you need the list of items which are selected?

I am sure you probably use foreach loop something like

List<ListItem> selectedList = new List<ListItem>();
foreach(ListItem li in cbList.Items)
{
    if (li.Selected)
        selectedList.Add(li);
}

Or advance user use the functionality of C#2.0 using yield keyword

private IEnumerable<ListItem> GetSelectedItems(ListControl listControl)
{
    foreach (ListItem itm in listControl.Items)
    {
        if (itm.Selected)
            yield return itm;
    } 
}
.
.
.
.
.
.
{
    IEnumerable<ListItem> selectedList = new List<ListItem>();
    selectedList = GetSelectedItems(cbList);
}

And now using LINQ C#3.0

var selectedItems =
    from li in cbList.Items.Cast<ListItem>()
    where li.Selected == true
    select li;

The only difference is the Cast, we need to make ListItemCollection generic so we can use it in LINQ query.

And now lets see using Lambda Expression C#3.0

List<ListItem> selectedItems =
    cbList.Items.Cast<ListItem>()
    .Where(item => item.Selected == true).ToList();

Here also we did cast to get the extension method Where. And in Where condition we write the lambda expression instead of anonymous delegate function.

We can even write extension method on base class ListControl so we can have selected items for all the list control. Lets write one extension method on ListControl

public static List<ListItem> SelectedItems(this ListControl lc)
{
    List<ListItem> selectedItems =
                lc.Items.Cast<ListItem>()
                .Where(item => item.Selected == true).ToList();
    return selectedItems;
}

And get the selected items using our extension method we will just call the SelectedItems method.

CheckBoxList cbList = new CheckBoxList();
cbList.SelectedItems();

GROUP BY GROUPING SETS - KATMAI

IN SQL 2008 KATMAI, there new extension for group by clause. Which is GROUPING SETS. Its provides you to create sets of columns for grouping from you group by column. For example if you have having group on year, month its allow you to group first on year+month then year as single group, which means now you can do multiple grouping in the single query!

Lets see the example. We are having data for Sales. We need sales for each Month as well as each Year. So each year will have again sum of Month. Here is the pictorial representation of last statement.

So our data:



We need this as output:



In SQL 2000/2005 We may need CURSOR or TEMPORARY TABLE or TABLE VARIABLE, and I am sure that will be tedious job. So lets see how we can achieve this functionality in KATMAI using GROUPING SETS. Here is the small and sweet query :)

SELECT 
    S_Year, S_Month, SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year

You can see the new keyword GROUPING SETS which has more then one Group Clause. This indicates that first you do Group on S_Year + S_Month then you Group it on S_Year.

Lets see the output.



You see the NULL in S_Month? That indicates the sum on months for particular Year mean its in the S_Month grouping. And KATMAI provides one function GROUPING which indicates whether a specified column expression in a GROUP BY list is aggregated or not. GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified. Read more on span class="keyword">GROUPING. Lets change query to get proper result.

SELECT 
    CASE 
        WHEN 
            GROUPING(S_Month) = 1 THEN 'Year Total: ' + CAST(S_Year AS VARCHAR(5)) 
        ELSE 
            'Month : ' + CAST(S_Month as VARCHAR(3)) 
        END 
    AS 'Grouping',
    
    SUM(S_Amt) as AmountSum, AVG(S_Amt) AvgAmt
FROM @Sales
GROUP BY GROUPING SETS ( 
    (S_Year, S_Month)
    ,(S_Year)
    )
ORDER BY S_Year, ISNULL(S_Month,15);

And lets see the output.



Download the SQL statements from here.

Check the Origional post

Its very useful while creating Reports.

RadGridNamespace is undefined

I got error RadGridNamespace is undefined while working with RadGrid and Themes all to gather.

Problem was, It was not able to find the scripts and related files form Themes folder. The default path for Script and Theme for Rad controls are fixed and which is under /RadControls. It goes to that directory but its obvious and convention says that if we are using Themes and Skins we put all the scripts, images and skins in App_Themes folder.

So we need to override the default Rad control default path property RadControlsDir in OnInit method

protected override void OnInit(EventArgs e)
{
    rgTest.RadControlsDir = string.Format("~/App_Themes/{0}/RadControls/", this.Page.Theme);
}

Note: If you are having multiple RadGrid on same page, then you have to set property RadControlsDir for all the grids in OnInit method.

Display multiple comma separated value into single column output

Here is the data:

Field1      Field2
----------- --------------------
1           A,B,C
2           A
3           D,G

And we need output as following

output
------
A
B
C
A
D
G

Lets create data first.

DECLARE @Fields AS TABLE
(
    Field1 INT,
    Field2 VARCHAR(20)
)

INSERT INTO @Fields VALUES (1,'A,B,C')
INSERT INTO @Fields VALUES (2,'A')
INSERT INTO @Fields VALUES (3,'D,G')

Here is the query for getting expected result.

DECLARE @FieldXml AS XML

DECLARE @v VARCHAR(MAX)

--Creates single row for Field2
SELECT @v = (SELECT ',' + Field2 FROM @Fields FOR XML PATH(''))
--Remove the first comma 
SELECT @v = SUBSTRING(@v, 2, LEN(@v))

--Add the XML tag
SELECT @FieldXml = '<F value="' + REPLACE(@v , ',', '" /><F value="') + '" />'

--List single attribute value
SELECT x.value('@value', 'VARCHAR(1)') AS [output]
FROM @FieldXml.nodes('/F') p(x)

New ASP.NET Charting Control

Scott Guthrie made announcement about the .NET chart control.

Look at his post. It has links for downloading Controls, VS support download, samples, documents etc.

How to sort Hashtable

I was having one requirement for sorting Hashtable. In general you cannot choose the sort order of the items added to the Hashtable. To sort Hashtable best practice is to use SortedList. You can either replace Hashtable with SortedList or you convert Hashtable into SortedList and provided your sort expression. Let's see with example. I have one class name ContactActivity which has contact and activity associated with it. I override ToString method to see the the value of added object.

class ContactActivity
{
    public long ActivityId { get; set; }
    public long ContactId { get; set; }

    public override string ToString()
    {
        return string.Format("Contact Id : {0} => Activity Id : {1}", ContactId, ActivityId);
    }
}

Now we will going to create Hashtable and add few objects of type ContactActivity

Hashtable hs = new Hashtable();

hs.Add("3", new ContactActivity() { ContactId = 3, ActivityId = 1 });
hs.Add("4", new ContactActivity() { ContactId = 4, ActivityId = 4 });
hs.Add("6", new ContactActivity() { ContactId = 6, ActivityId = 5 });
hs.Add("1", new ContactActivity() { ContactId = 1, ActivityId = 1 });

One of the constructor of SortedList; except IDictionary and IComparer; Which initializes a new instance of the SortedList class that contains elements copied from the specified dictionary, has the same initial capacity as the number of elements copied, and is sorted according to the specified IComparer interface. We will use that constructor to build SortedList from Hashtable, and also will provide the logic of sorting key by implementing IComparer interface.

Here is the class which implements IComparer interface

public class MySort : IComparer
{
    bool IsAscendingOrder = true;
    #region IComparer Members

    public int Compare(object x, object y)
    {
        if (IsAscendingOrder)
            return (int.Parse(x.ToString()) - int.Parse(y.ToString()));
        else
            return (int.Parse(y.ToString()) - int.Parse(x.ToString()));
    }
    public MySort(bool blnIsAscendingOrder)
    {
        IsAscendingOrder = blnIsAscendingOrder;
    }

    #endregion
}

I added one variable to set the order of key also added constructor which used to set sort order. As in my case key will be integer so I use int.Parse else you can use simply string comparison. Lets create one common method to prints the Hashtable or SortedList, as both implements IDictionary interface we can use that to create common method.

private static void Show(IDictionary id)
{
    foreach (string strKey in id.Keys)
    {
        Console.WriteLine(id[strKey].ToString());
    }
}

So lets put all together.

Hashtable hs = new Hashtable();

hs.Add("3", new ContactActivity() { ContactId = 3, ActivityId = 1 });
hs.Add("4", new ContactActivity() { ContactId = 4, ActivityId = 4 });
hs.Add("6", new ContactActivity() { ContactId = 6, ActivityId = 5 });
hs.Add("1", new ContactActivity() { ContactId = 1, ActivityId = 1 });

Console.WriteLine("Hashtable values");
Show(hs);
Console.WriteLine("Ascending Order");
SortedList sl = new SortedList(hs, new MySort(true));
Show(sl);
Console.WriteLine("Descending Order");
Show(sl);
sl = new SortedList(hs, new MySort(false));

Let's see the output.

Getting value form pervious page into new window

I saw lots of question posted on http://forums.asp.net/ about how to get the value from pervious page into newly opened window. Its very simple, you have few ways to pass variables to newly opened window. First and foremost is passing variable into query string.

You can get all full form collection; not only this you can also read the value of global variable of parent page in to newly opened window. Let's see how.

I created one variable into parent page. And somewhere I call window.open() to open new page into new window.

<script language="javascript" type="text/javascript">
    var gblPageTitle;
    gblPageTitle = 'Welcome to ';
</script>

In parent page I just have to write following line to get that variable.

<script type="text/javascript">
    document.title = 'Questions on '+ window.opener.gblPageTitle;
</script>

And now lets get the full document of parent page or specifically form collection.

<script type="text/javascript">
    var previousForm = window.opener.document.forms;
</script>

Have fun with window.opener :)

C# ?? operator

I just found one beautiful operator of C#, which is ?? Its more compact then ternary operator [?:]. Ternary operator is short form of if-else and ?? is also kind of if else, but its work with null value an one assumption. lets see how it works. Its always case when we have to check for null condition, like query string check or Session value check, here how we do to check null for query string.

string strUserId = null;

//Using if-else
if (Request["uid"] == null)
    strUserId = string.Empty;
else
    strUserId = Request["uid"];

//Using ternary operator
strUserId = Request["uid"] == null ? string.Empty : Request["uid"];

now lets use ?? operator

//Using ?? operator
strUserId = Request["uid"] ?? string.Empty;

Isn't it handy? What is does is, it check only for null, it it found left side null then return the value form right hand else it returns left hand site value itself. In our case if there is no uid in query string then will return empty string or else will return the value of uid from query string.

Finding nth maximum number in SQL Server 2005

This is the frequent requirement for the developer to find the nth max number from the table. It will easy to get it if you are using SQL Server 2005, as its allows us to make the top query variable.

Please read the Flexibility using TOP clause in SQL Server 2005 for more details.

Lets say, we are having student and their marks, and we want the nth max mark. First will see the table structure and will add few data into it.

SET NOCOUNT ON
DECLARE @Marks TABLE
(
    StudName VARCHAR(100),
    Mark INT
)

INSERT INTO @Marks VALUES('AAAAA', 55)
INSERT INTO @Marks VALUES('BBBBB', 65)
INSERT INTO @Marks VALUES('CCCCC', 59)
INSERT INTO @Marks VALUES('DDDDD', 52)
INSERT INTO @Marks VALUES('FFFFF', 65)
INSERT INTO @Marks VALUES('EEEEE', 95)

SELECT Mark FROM @Marks ORDER BY Mark DESC

Mark
-----------
95
65
65
59
55
52

Now we write the query which allow us to find nth max mark form this list.

DECLARE @Top INT
SET @Top = 2

SELECT MIN(Mark) AS 'Top' FROM(
SELECT DISTINCT TOP (@Top) Mark FROM @Marks ORDER BY Mark DESC) A

Top
-----------
65

@Top is variable; which gives you the ability to fetch Nth max.

Matching a delimited string against another delimited string

I found this is the require thing in our application, as some one in Group asked for help on this as well as one of the member of asp.net forums asked same thing but in different context.

What generally we need is, in our database one field is having multiple value separated with comma. Lets say I am sailor of Property, consider Home as property and features; a bunch of features will create feature group; so home will contains one of the feature group. So here is the feature group table.

SET NOCOUNT ON
DECLARE @FeatureGroup TABLE
(
    PropertyID INT,
    Features VARCHAR(MAX)
)

INSERT INTO @FeatureGroup VALUES(1, 'Gym')
INSERT INTO @FeatureGroup VALUES(2, 'Gym, Swimming Pool')
INSERT INTO @FeatureGroup VALUES(3, 'Swimming Pool, Terrace')
INSERT INTO @FeatureGroup VALUES(4, 'Swimming Pool, Terrace, Gym')
INSERT INTO @FeatureGroup VALUES(5, 'Swimming Pool, Gym, Parking')
INSERT INTO @FeatureGroup VALUES(6, 'Swimming Pool, Terrace, Basement')
INSERT INTO @FeatureGroup VALUES(7, 'Swimming Pool, Gym, Terrace, Basement, Parking')
SELECT * FROM @FeatureGroup 

Gives following output
---------------------------------------------------
1    Gym
2    Gym, Swimming Pool
3    Swimming Pool, Terrace
4    Swimming Pool, Terrace, Gym
5    Swimming Pool, Gym, Parking
6    Swimming Pool, Terrace, Basement
7    Swimming Pool, Gym, Terrace, Basement, Parking

Lets say we have to search for "Gym, Parking", we should list those property which contains either Gym or Parking. There are two way to achieve this, one is using SPLIT function [while is the user define function] and another is XML.

Using SPLIT Function:

Its user define function which splits the comma separated value to Table variable. In this case we first split our filter using Split function which will return the rows representation of our filter; mean each filter will be in separate row. Lets see the definition of Split function.

CREATE function [dbo].[Split](@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results Table (Item1 nvarchar(100))
As    
Begin    
    DECLARE @Index int    
    DECLARE @Slice nvarchar(100)    
     
    SET @Index = 1    
    
    IF @String Is NULL Return    
    
    WHILE @Index != 0    
    BEGIN    
        SELECT @Index = CharIndex(@Delimiter, @String)    
        If @Index != 0    
            SELECT @Slice = LEFT(@String, @Index - 1)    
        else    
            SELECT @Slice = @String    
    
        INSERT INTO @Results VALUES(RTRIM(LTRIM(@Slice)))
      
        SELECT @String = RIGHT(@String, LEN(@String) - @Index)
        IF LEN(@String) = 0 BREAK    
     END    
    RETURN    
END

Here is the use of Split.

SELECT * FROM [master].[dbo].[Split] ('Gym, Parking', ',')

Item1
--------
Gym
Parking

Now lets write query which uses Split function.

DECLARE @Filter VARCHAR(100)
set @Filter = 'Gym, Parking'

select PropertyId, Features from @FeatureGroup FG WHERE -1 IN (
     SELECT CASE WHEN (PATINDEX('%' + Item1 + '%', FG.Features)) > 0 THEN -1 ELSE 0 END
     FROM [master].[dbo].[Split] (@Filter, ','))

Here is the output:
-------------------------------------------------------
1    Gym
2    Gym, Swimming Pool
4    Swimming Pool, Terrace, Gym
5    Swimming Pool, Gym, Parking
7    Swimming Pool, Gym, Terrace, Basement, Parking

And now lets do it with XML.

DECLARE @Filter VARCHAR(100)
set @Filter = 'Gym, Parking'

DECLARE @xmlFilter XML
SELECT @xmlFilter = CAST('<i>' + REPLACE(@Filter, ',', '</i><i>') + '</i>' AS XML)

SELECT DISTINCT    
    PropertyID, Features
FROM @FeatureGroup FG
CROSS JOIN (
    SELECT 
        x.i.value('.', 'VARCHAR(10)') AS filter
    FROM @XmlFilter.nodes('//i') x(i)
) b
WHERE PATINDEX('%' + b.filter + '%', features) > 0

OUTPUT:
-------------------------------------------------------
1    Gym
2    Gym, Swimming Pool
5    Swimming Pool, Gym, Parking
7    Swimming Pool, Gym, Terrace, Basement, Parking
4    Swimming Pool, Terrace, Gym

You can find the details of how XML works in here, you can read the TSQL Labs 13 - Matching a delimited string against another delimited string created by Jacob Sebastian

Changing the value of web.config file runtime

There is pretty small and simple code to change the value of key defined in web.config file.

// Get the reference of the configuration file at the root.
Configuration objConfig = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
// Change the value of one key
Response.Write(string.Format("Old value : {0}<br/>", objConfig.AppSettings.Settings["MyKey"].Value));
objConfig.AppSettings.Settings["MyKey"].Value = "changed web config";
// Save the changes.
objConfig.Save();
Response.Write(string.Format("New value : {0}", objConfig.AppSettings.Settings["MyKey"].Value));

Transfer rows to column in C#

I was having requirement to change result of SQL in such a way that rows become columns, and columns become rows.

PersonName                Year2000               Year2001               Year2002               Year2003
------------------------- ---------------------- ---------------------- -------------------    ---------------
A                         1230                   4521                   5435                   5410
B                         1330                   4231                   5435                   1200
C                         3230                   1623                   5435                   3652
D                         4230                   2321                   5435                   1859

And need output as following

SalesYear       A                      B                      C                      D
--------------- ---------------------- ---------------------- ---------------------- ----------------------
Year2000        1230                   1330                   3230                   4230
Year2001        4521                   4231                   1623                   2321
Year2001        5435                   5435                   5435                   5435
Year2001        5410                   1200                   3652                   1859

Mr Jacob wrote post on this which is uses UNPIVOT operator which is in SQL Server 2005, you can find it here.

Here is the contributed code form Abidali Suthar [one of the developer in my team], which does the same thing but in C#.

I added extension method to DataTable, by calling SwapTable method on any DataTable instance; you can get the rows in columns and columns in rows. Lets look at extension method first.

public static class DatatTableExtension
{
    /// <summary>
    /// Extension method which transform row to column and column to row
    /// </summary>
    /// <param name="dt">Object on which we have to do operation</param>
    /// <returns>Transformed DataTable object</returns>
    public static DataTable SwapTable(this DataTable dt)
    {
        if (!(dt.Columns.Count > 0 && dt.Rows.Count > 0))
            return dt;

        DataTable dtNew = new DataTable();
        dtNew.Columns.Add(dt.Columns[0].ColumnName);

        // Creating columns for new DataTable. 
        // Adding column to new data table having name as first row of old data table
        for (int i = 0; i <= dt.Rows.Count - 1; i++)
            dtNew.Columns.Add(dt.Rows[i][0].ToString());

        DataRow row;
        // Swaping the values
        for (int k = 1; k < dt.Columns.Count; k++)
        {
            row = dtNew.NewRow();
            row[0] = dt.Columns[k].ToString();
            for (int j = 1; j <= dt.Rows.Count; j++)
                row[j] = dt.Rows[j - 1][k];

            dtNew.Rows.Add(row);
        }

        return dtNew;
    }
}

As we created extension method, its now easy to call it by creating object of DataTable. Lets see with example.

protected void Page_Load(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
    {
        con.Open();
        string strSelect = "SELECT_STATEMENT";

        SqlCommand cmd = new SqlCommand(strSelect, con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);
        con.Close();

        // Bind original datatable
        dgBefore.DataSource = dt;
        dgBefore.DataBind();

        // Bind swapped datatable, calling extension method on datatable5
        dgAfter.DataSource = dt.SwapTable();
        dgAfter.DataBind();
    }
}

Error: Validation of viewstate MAC failed

This is common error generally working with UpdatePanel.

On your screen you see message "Validation of viewstate MAC failed. If this application is hosted by a Web Farm or cluster, ensure that configuration specifies the same validationKey and validation algorithm. AutoGenerate cannot be used in a cluster."

You can fix it in two ways, setting in web.config or in page it self.

In web.config:

<pages enableEventValidation="false" viewStateEncryptionMode ="Never" />

Or in page you write following line in @Page directive.

<%@ Page EnableEventValidation="false" ViewStateEncryptionMode="never" ...

Silverlight 2 Release Candidate Now Available

Hello All,

Silverlight 2 is now available, please read this post of Scott Guthre

with operator in JavaScript

Hello Friends,

I found one good thing in JavaScript, there is with operator in JavaScript, which you can say same as with operator in VB.NET. Its always helpful to define short name of big variable or replacement of such big variable; which lead us to make code more readable and maintain too.

In general when we are dealing with XML datasrouce in JavaScript; there always have long long variable name, of the value is so deep inside.

xmlDoc.childNodes[1].childNodes[0].childNodes[3].childNodes[0].text
xmlDoc.childNodes[1].childNodes[0].childNodes[4].childNodes[0].text

We have to write more lines of code to get all the details. Here we can use with, as we know part of path to reach final value is common which is xmlDoc.childNodes[1].childNodes[0], so we can put it into with keyword.

with(xmlDoc.childNodes[i].childNodes[0])
{
    name = childNodes[1].childNodes[0].text;
    add1 = childNodes[2].childNodes[0].text;
    add2 = childNodes[3].childNodes[0].text;
    email = childNodes[4].childNodes[0].text;
    .
    .
    .
}

How to get Property list only of Derived class?

Hi All,

Recently I seen one requirement on ASP.Net Forum, some one posted question about How to get property list of Derived class.

Generally if we want to get all the property of class we use GetProperties() method. Which gives you the PropertyInfo[]. Lets see with example. We have following class Hierarchy.

class A
{
    public int Id { get; set; }

    public virtual void PrintMe()
    {
        Console.WriteLine("Id : " + Id);
    }

}

class B : A
{
    public string Name { get; set; }
    public string Address { get; set; }
    public override void PrintMe()
    {
        base.PrintMe();
        Console.WriteLine("Name : " + Name);
    }
}

In A there is only one property and in B we have 2 more property, our goal to get only two property declared in class B. Lets check with GetProperties().

PropertyInfo[] properties = typeof(B).GetProperties(); 

Console.WriteLine(properties.Count()); //will print 3

You can see its printing 3, that means it consider properties which are in parent and child too. There is one overload of GetProperties() which has BindingFlags, but with that also we are not achieving our goal.

So here is the solution using LINQ.

var prop =
    from
        p in typeof(B).GetProperties()
    where p.DeclaringType == typeof(B)
    select p;

Console.WriteLine(prop.Count()); //Will print 2

We are filtering on DeclaringType; which should be typeof(B). You can see now its returning 2, that what we needed.

Windows live writer for server 2003

Hello All,

This post is related to people who are creating blog using Window Live Writer.

It took me long to get the Windows Live Writer for Server 2003. Here is the link to download the Windows Live Writer for Server 2003

User Group Meeting on 20-Sep-2008

Hi All,

This is to announce that there is next meeting of Ahmedabad_SQLServer_UserGroup on Saturday, September 20, 2008.

I would like you to join this and meeting inperson and gain lots of knowledge shared by MVPs, there always a QA session at the end of each meeting followed by ICE-CREAM.

The topic of this meeting will be CTE and Pinal Dave [MVP] will be the speaker of this meeting.

To Register: Click Here.
To View Meeting Details : Click Here.
Not member of this group?: Click here

In previous meeting we explore the SQL Server Transaction Isolation Levels, which was presented by Jacob Sebastian [MVP, MSDN moderator, Owner of groups, and lots more], that was wonderful session along with live demos. If you miss that event then you can go here

Visual Studio IDE Support for SQL Server

Hello,

As we all know Visual Studio is now supporting Database project, which allows you to write query, procedure etc... from IDE only.

Recently I found the error while connecting SQL2008 from VS2008.

This server version is not supported. Only servers up to Microsoft SQL Server 2005 are supported.

I found after doing some googling; that there is no patch available which allos me to connect SQL2008 to VS2008!!!.

I found few comments which says that it will come soon.

Somasegar's WebLog
Euan Garden's BLOG

However there is Sservice Pack for Visual Studio 2008 Team Foundation Server which has ability to Support for SQL Server 2008, you can download that form Microsoft Download Center

Let me add that there is Service Pack for Visual Studio 2005 Support for SQL Server 2008, which you can download it from Microsoft Download Center

Reducing page load times with UpdatePanels and timers

Hi all,

Some time we have requirement like, on single page we are having 5-6 controls, which at the same tiem only single is visible and by navigation we can view all other controls.

This is very specific to Ajax Tab Implementation. Where we are having 4-5 tabs, in which we are having server controls, so on Page load all the controls get loaded and page become heavy also slow.

There is one technique to optimize this. As we all know we have to show single tab once page is get loaded, put all the controls into Asp:Panel and except one which we have to show on default; make other to visible false. The enable of all visible panel will be done using timer control. Time control will make enable the Asp:Panel one by one. As all the Asp:Panel will be in UpdatePanel will page will not get refreshed.

I found the same code in one of the post of Glavs Blog.

He provides the code along with video. Have a look at them and make optimize your page. I used this and found very good, even we can now use this on same page.

Why doesn't C# support multiple inheritance?

Hi all,

I was always searching for the answer of "Why doesn't C# support multiple inheritance?".

After some googling I found the answer from MSDN.

Please have a look at the link.

Finding occurrences of character in string [LINQ]

Hello All,

This is normal requirement when you need to find the occurrence of single character with in the string. Here is the example which uses LINQ to achive this.

string strString = "AA BRA KA DABRA";

var grp = from c in strString.ToCharArray() 
        group c by c into m
        select new { Key = m.Key, Count = m.Count() };

foreach (var item in grp)
{
    Console.WriteLine(
        string.Format("Character:{0} Appears {1} times", 
        item.Key.ToString(), item.Count));
}

Extended URL rewriting

I was having one requirement for my .net application. In which I want to parse the following url. Handling request with out extension.

http://mysite.com/imran/
http://mysite.com/armaan/

For these url we can't user ASP.NET URL Re-Writing, as there is no aspx extension added to this url.

When you found such request and its not exists then IIS will give 404 error, but we need to parse such url and do some validation and give them proper output. In this case lets say I would like to see the profile of user imran, then I make request to http://mysite.com/imran/ which gives me the profile of user imran.

This is the normal requirement, we can do it easily in php and java. But in .net this is bit tricky to get such functionality working.

If we use http handler, then also we are not able to identify the request as it has no extension. As its is not reaching to the application, IIS itself reject this request, no global.ascx or web.config

I found very easy solution for this solution. This requires some setting on virtual directory and code in our system to handle such request.

As I mention eariler when you send request to http://mysite.com/imran/ which not exist; IIS will raise 404 error, what we do is, we trap this error and get the requested page or url and do some work and give the result.

IIS Setting
In IIS we have to change default 404 error handling to aspx page, bye default 404 page is C:\WINDOWS\help\iisHelp\common\404b.htm with Message Type as File. See the image



We need to change this to forward all such request to our application, once we got control in aspx page then we can do anything on such request.

For this we need to change Message Type, set it to URL and the URL will be /MySite/Handle404.aspx. See the image



Code
We have to write few lines of code in Handle404.aspx to handel this request.

protected override void OnInit(EventArgs e)
{

    if (Request.Url.ToString().Contains("404;"))
    {
        string[] urlInfo404 = Request.Url.Query.ToString().Split(';');

        if (urlInfo404.Length > 1)
        {
            string strRequestUrl = urlInfo404[1].Replace(":" + Request.Url.Port + "/", "/");

            if (!strRequestUrl.EndsWith("/"))
            {
                strRequestUrl = strRequestUrl.Insert(strRequestUrl.Length, "/");
                Response.Redirect(strRequestUrl);
            }
            string[] urlInfoFavAddress = strRequestUrl.Split('/');
            string strUser = urlInfoFavAddress[urlInfoFavAddress.Length - 2];

            Server.Transfer(string.Concat("~/Profile.aspx?usr=", strUser));

        }
    }
    base.OnInit(e);
}

Here we are just grabbing the trailing string, in this case its imran or armaan.

Once I get the key its get redirected to Profile page, as its Server.Transfer which does not change the url.

Load method of DataTable

I found very interesting method of DataTable. There is Load method which loads the IDataReader directly to DataTable. Well this is not interestring I know lolz.

The case is like....

My query or procedure generates 4 different result set, and I have 4 different DataTable. As IDataReader has 4 result set, fatching all result set I have to use NextResult method which put my reader to next result set. like this. [Dont know this??? Have a look at this.]

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();

using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from TABLE1;select * from TABLE1;select * from TABLE2;select * from TABLE3";

    con.Open();
    IDataReader idr = cmd.ExecuteReader();

    dt1.Load(idr);
    idr.NextResult(); // Put DataReader to next result set
    dt2.Load(idr);
    idr.NextResult(); // Put DataReader to next result set
    dt3.Load(idr);
    idr.NextResult(); // Put DataReader to next result set. ERROR
    dt4.Load(idr);                
}

But this get fails at NextResult, why?

Because when you call Load method on DataTable, it automatical set pointer to next result set, we dont need to call NextResult here.

This is interesting thing, lets add few more on it. First we remove the NextResult method.

DataTable dt1 = new DataTable();
DataTable dt2 = new DataTable();
DataTable dt3 = new DataTable();
DataTable dt4 = new DataTable();
DataTable dt5 = new DataTable();

using (SqlConnection con = new SqlConnection("CONNECTION_STRING"))
{
    SqlCommand cmd = con.CreateCommand();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "select * from TABLE1;select * from TABLE2;select * from TABLE3;select * from TABLE4";

    con.Open();
    IDataReader idr = cmd.ExecuteReader();

    dt1.Load(idr);
    dt2.Load(idr);
    dt3.Load(idr);
    dt4.Load(idr);
    dt5.Load(idr);
}

Well this is working fine, can you see the dt5? I am trying to load 5th result set, but there is no such result set produced by my Sql statements, its still working!!!!, this is really interesting. Well its not succesfull operation; but its will make empty DataTable.

Make temporary redirection to another page

Hello All,

I got one question is asp.net forums, some time people require to change the location automatically or the best example is like, when you shifted your site to new location and you dont want people [who bookmarked your site] request to old site locaiton and get page not found error!

This case what you can do is, you can add automatic redirection to the new site, just adding the meta tag.

You can create one html file or you can write meta tag in older site's default page, here is the meta tag, you just have to place in head tag

<meta http-equiv="refresh" content="5;url='http://knowledgebaseworld.blogspot.com/'" />

http-equiv is the command, which says refresh, so it have to do refresh, and in the content we have to set new locatoin and the time in second. Here its 5 so after 5 second it will automatically redirected to http://knowledgebaseworld.blogspot.com.

We can put some thing like, the page you are looking for its moved, please wait, we are redirecting to new page, book mark this page bla bla bla.....

This way we stop loosing our valuable users of site.

Creating Rad Menu using LINQ

Hello all,

I created sample code to generate Dynamic RadMenu with use of LINQ. The controls like RadMenu, AspMenu which accept XML as the datasource to generate the output.

I use LINQ to generate XML, you can read here to generate XML using LINQ.

You can find the simple project at Code Project. You can modify the code with your use, its dynamically creates the node also it uses the recursion to generate n level menu item.

Best way to generate XML using LINQ

Hi all,

As we know C# 3.0 with LINQ gives us lots of power and smart coding, here is one more example which uses features of LINQ to generating XML of the abstract type.

I will give you example where you need to display the person details along with the address, here address can be anything like home address, email address, IM address ...

Here are the classes which require to achive our goal.

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public List<Address> Address = new List<Address>();
}
 
class Address
{
    public string AddressValue { get; set; }
    public string Type { get; set; }
}

Person class used to store person data, and Address class which holds the type of address.
Now lets add few details.

Person Person1 = new Person() { FirstName = "Imran", LastName = "Bhadelia" };
Person1.Address.Add(new Address() { Value = "imran.bhadelia[at]gmail.com", Type = "Email" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran[at]gmail.com", Type = "AlternativeEmail" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran[at]gmail.com", Type = "MSNInstanceMessanger" });
Person1.Address.Add(new Address() { Value = "bhadelia.imran", Type = "YahooInstanceMessanger" });
 
Person Person2 = new Person() { FirstName = "Armaan", LastName = "Bhadelia" };
Person2.Address.Add(new Address() { Value = "armaan.bhadelia[at]gmail.com", Type = "Email" });
Person2.Address.Add(new Address() { Value = "Sweet Home, 406 Paradise Palace...", Type = "Residence" });
Person2.Address.Add(new Address() { Value = "bhadelia.armaan[at]gmail.com", Type = "MSNInstanceMessanger" });
Person2.Address.Add(new Address() { Value = "bhadelia.armaan", Type = "YahooInstanceMessanger" });
 
List<Person> PersonList = new List<Person>();
PersonList.Add(Person1);
PersonList.Add(Person2);

Here I used one feature of C#3.0, which is Object Initialization Expressions. See the constructor of both class, Its default and while creating object I am setting the value to the fields of class [Smart coding]. I added this two class into List variable, from where LINQ will generate XML which looks like...

<Persons>
    <Person FirstName="Imran" LastName="Bhadelia">
        <Address Email="imran.bhadelia[at]gmail.com" /> 
        <Address AlternativeEmail="bhadelia.imran[at]gmail.com" /> 
        <Address MSNInstanceMessanger="bhadelia.imran[at]gmail.com" /> 
        <Address YahooInstanceMessanger="bhadelia.imran" /> 
    </Person>
    <Person FirstName="Armaan" LastName="Bhadelia">
        <Address Email="armaan.bhadelia[at]gmail.com" /> 
        <Address Residence="Sweet Home, 406 Paradise Palace..." /> 
        <Address MSNInstanceMessanger="bhadelia.armaan[at]gmail.com" /> 
        <Address YahooInstanceMessanger="bhadelia.armaan" /> 
    </Person>
</Persons>

And now LINQ to generate above XML.

var PersonXml =
    new XElement("Persons",
        from person in PersonList
        select (new XElement("Person",
            new XAttribute("FirstName", person.FirstName),
            new XAttribute("LastName", person.LastName),
 
            from addr in person.Address
            select new XElement("Address", new XAttribute(addr.Type, addr.Value)))));

Console.WriteLine(PersonXml.ToString());

How it works??
Line#2: Create the main element which is Persons
Line#3: Getting single Persons object from PersonList
Line#4: Creating sub element to Persons which is Person
Line#5&6: Creating attributes to show person details.
Line#8: Getting Address of the person from Address collection
Line#9: Creating Address element along with the attribute

Easy? I found its very easy :)

Now if you want to add this information into SQL Server 2005 and higher, you can pass it as string and in your procedure grab the value. There are lots of way to grab value from XML in SQL, you can check this url, it has almost all the operations to XML datatype in SQL.

I created SQL script for specific to this xml.

SELECT 
    Person.value('@FirstName[1]', 'VARCHAR(100)') as FirstName,
    Person.value('@LastName[1]', 'VARCHAR(100)') as LastName,
    Person.value('(Address/@Email)[1]', 'VARCHAR(100)') as Email,
    Person.value('(Address/@AlternativeEmail)[1]', 'VARCHAR(100)') as AlternativeEmail,
    Person.value('(Address/@Residence)[1]', 'VARCHAR(100)') as Residence,
    Person.value('(Address/@MSNInstanceMessanger)[1]', 'VARCHAR(100)') as MSNInstanceMessanger,
    Person.value('(Address/@YahooInstanceMessanger)[1]', 'VARCHAR(100)') as YahooInstanceMessanger
FROM @Persons.nodes('/Persons/Person') p(Person)

Getting child ids in string separate with delimiter along with parent id[SQL SERVER]

Hi Friends,

I come accros one requirement where I need... the child id should be delemited by '|' and along with the parent Id. Lets say I am having parent as company, one company is having more then one code.

So now my requirement is to get the company code in '|' saperated with company id, just like this.

Comp_id     company_code
----------- ------------
1           1|2
2           1|2|3|4
3           1|2

We can achive this by either using a user-defined aggregate function or using loop. I will explore the user-defined aggreagate function and post how to do, but for now lets do it with looping. Here is the code to achive this by using while-loop.

DECLARE @companies Table
(
    Comp_id INT,
    company_code  int
)
 
insert into @companies values(1,1)
insert into @companies values(1,2)
insert into @companies values(2,1)
insert into @companies values(2,2)
insert into @companies values(2,3)
insert into @companies values(2,4)
insert into @companies values(3,1)
insert into @companies values(3,2)
 
DECLARE @TmpTable Table
(
    Id int IDENTITY (0, 1) NOT NULL,
    CompanyId INT
)
 
SET NOCOUNT ON
 
DECLARE @Tmpcompanies Table
(
    Comp_id int,
    company_code  varchar(10)
)

INSERT INTO @TmpTable SELECT DISTINCT comp_id FROM @companies
 
DECLARE @MaxId INT
DECLARE @PipedList VARCHAR (MAX)
Declare @Count INT
Declare @CurrComp INT
 
SELECT @MaxId = MAX(id) FROM @TmpTable
SET @Count = 0
WHILE( @Count <= @MaxId)
BEGIN
    SET @PipedList = NULL
    SELECT @CurrComp = Comp_id, @PipedList = COALESCE(CAST(@PipedList AS VARCHAR(MAX)) + '|', '') 
    + CAST(company_code AS VARCHAR(10)) FROM @companies Cmp, @TmpTable TT
        WHERE CMP.Comp_id = TT.CompanyId AND TT.ID = @Count
    INSERT INTO @Tmpcompanies VALUES(@CurrComp, @PipedList)
    SET @Count = @Count + 1
END
 
SELECT * FROM @Tmpcompanies

How to sort generic collection

Hi all,

We will now sort generic collection. For this we need to create one comparere class which inherits IComparer<T> and implement Compare method, where T is your class object. Compare method is responsible to compare tow object and based on the result sorting will be done.

public class TestSortComparere : IComparer<TestSort>
{
    public TestSortComparere() { }

    #region IComparer<TestSort> Members

    public int Compare(TestSort x, TestSort y)
    {
        return string.Compare(x.a, y.a);
    }

    #endregion
}

Now lets call Sort method.

List<TestSort> MyObjList = new List<TestSort>();
MyObjList.Add(new TestSort("Imran"));
MyObjList.Add(new TestSort("Dipak"));
MyObjList.Add(new TestSort("Sachin"));
MyObjList.Add(new TestSort("Darshan"));
MyObjList.Add(new TestSort("Gaurav"));

MyObjList.Sort(new TestSortComparere());

You can see the sorted order when you iterate MyObjList

foreach (TestSort testSort in MyObjList)
    Console.Write(testSort.strTest + Environment.NewLine);
//OUTPUT
/*
 Darshan
 Dipak
 Gaurav
 Imran
 Sachin
 */

We can change the sort line using Anonymous delegate. Lets see how, its only in single line!!!!.

MyObjList.Sort(delegate(TestSort x, TestSort y) { return string.Compare(x.strTest, y.strTest); });

Difference between internal and protected internal

Hi All,

Generally what we assume that internal means the variable that can only accessed within the assembly [which is the collection of namespace and classes].

But there is slide different meaning when we add protected to an internal member. What is that? The difference is... if you created a member which is protected internal then you can get it outside the assembly, but condition that you derive that class. Its protected so if you derive that class then in child class you can access the protected internal member.

We can check it out with example. Lets have a look at the classes which are in same assembly.

namespace Understand
{
    public class C : UnderstandInternal.Class1
    {
        public C()
        {
            base.intInternalProtected(); // can call anything
            base.intInternal();
        }
    }
}


namespace UnderstandInternal
{
    public class Class1
    {
        internal int intInternal() { return 1; }
        internal protected int intInternalProtected() { return 3; }
    }


    public class Class3
    {
        Class1 c = new Class1();
        public Class3()
        {
            c.intInternalProtected(); // can call anything
            c.intInternal();
        }
    }
}

You can see there is no problem in accessing the variable, its working fine, now we will do same thing in different assembly. Lets look at the following classes.

namespace TestConsole
{
    class InternalTest : Class1
    {
        public InternalTest()
        {
            base.intInternalProtected(); //your can use here
            base.intInternal(); //Error: 'UnderstandInternal.Class1' does not contain a definition for 'intInternal'

        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            Class1 c = new Class1();
            c.intInternalProtected(); //Error: 'UnderstandInternal.Class1.intInternalProtected()' is inaccessible due to its protection level
            c.intInternal(); //Error: 'UnderstandInternal.Class1' does not contain a definition for 'intInternal' and no extension method 'intInternal'
        }
    }
}

Transferring control from ascx to aspx

Hi all,

In many cases we require some functionality in which there is something happening at control level and we need to notify to page level. Mainly when we are creating control which has grid or some short of dataview control and there is need to pass some message to page [in which the control is loaded] from any event.

This article will help you to achive this using a powerful functionality provided by .NET framwork which is delegate. Delegate in C# is similar to a function pointer in C or C++. Using a delegate allows the programmer to encapsulate a reference to a method inside a delegate object. The delegate object can then be passed to code which can call the referenced method, without having to know at compile time which method will be invoked.

Our goal is to display the messages on page [aspx] about which row is currently selected. We will create one event in control [ascx] which will be handeled in page [aspx], for creating event we need to create one delegate first.

public delegate void SelectedIndexChanging(object sender, GridViewSelectEventArgs e);

SelectedIndexChanging is the delegate which has same argument as GridView's SelectedIndexChanging event.

Now will create Event of the type SelectedIndexChanging delegate in ascx

public event SelectedIndexChanging GridViewSelectedIndexChanging;

How to call this event? Well we have to call this event on the same event of Grid in ascx, so we can raise this event and in page [aspx] we can capture this and do the some operation.

protected void gvTest_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
    if (GridViewSelectedIndexChanging != null)
        GridViewSelectedIndexChanging(sender, e);
}

Why null condition check? We have to check whethere there is any handler to this event or not, if there is no handler bound then no need to raise this event, else it will thro Exception :)
We are ready with our user control, now place this on page and bind the hendler to SelectedIndexChanging event, here is the bind code.

childControl.GridViewSelectedIndexChanging += new ChildControl.SelectedIndexChanging(childControl_GridViewSelectedIndexChanging);

And in method I am just displaying the selected item details.

void childControl_GridViewSelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{
    GridViewRow gvr = (sender as GridView).Rows[e.NewSelectedIndex];
    lblGridSelectedFor.Text = string.Format("Id = {0}, Name = {0}",
    (gvr.FindControl("lblIdentity") as Label).Text, (gvr.FindControl("lblName") as Label).Text);
}

As I passed the same sender along with GridViewSelectEventArgs, I can access all the events and gridview here. First I cast sender in GridView to get the GridView which is inside user control, and from NewSelectedIndex, I read some value and displaying using label.

And this way we achive our goal, you can do lots many thing by use of Delegates, you can also read the article on Anonymous Delegate and Delegates and Events in C# / .NET

Parent can hold reference of Child

Hello all,

We all know that A a = new B(); we learn this by heart from the day we start OOPS.

But there are lots many people who just do cramming lolz, I have one good example which helps you to get your mind set what is meaning of A a = new B();

We all know that using virtual function we can achive the golden word "Parent can hold reference of child", but its not just virtual function involved, there are also reusability.

Lets say you have one base class having 10 properties and you derived 2 more classes which individual has more then 5-5 properties.

So what I do? Should I create methods for each child class? What if I have more 2-3 class?? The answer is NO [I knew that you know it :)]

So what to do? How A a = new B(); will help full here?

Lets learn with example.

I am haivng A as parent class and B, C as child class.

class A
{
    public int Id { get; set; }

    public virtual void PrintMe()
    {
        Console.WriteLine("Id : " + Id);
    }

}

class B : A
{
    public string Name { get; set; }

    public override void PrintMe()
    {
        base.PrintMe();
        Console.WriteLine("Name : " + Name);
    }
}

class C : A
{
    public string Company { get; set; }
    public override void PrintMe()
    {
        base.PrintMe();
        Console.WriteLine("Company : " + Company);
    }
}

You can see the virtual method; this is the key thing to see the power of A a = new B();.

Now the method call, here is the code to calling method PrintMe

A a = new B();
a.PrintMe();    //will call B method            
a = new A();
a.PrintMe();    //Will call A method
a = new C();
a.PrintMe();    //Will call C method

There is nothing wrong in it, it can easily understood.

Now our purpose, what is it? Yes we need to set properties and then call PrintMe method.

private static void CallMe(A a)
{
    a.Id = 4;

    if (a is C)
        ((C)a).Company = "My Company";
    else if (a is B)
        ((B)a).Name = "My Name";
    a.PrintMe();
}

See the signature of this method, it says private static void CallMe(A a), the parameter is object of class A; that is PARENT. And inside it the first line is setting the property of parent and then based on the type the rest of properties is getting populated. And at the end calling PrintMe which is virtual so based on reference it will call the method.

Lets call method CallMe.

1. a = new B();
2. CallMe(a); //Will print Id and Name
3. a = new C();
4. CallMe(a); //Will print Id and Company

1st line creating object of B and setting it to A'a object. 2nd linke calling CallMe by passing the parent object. This will first set the property of child and then as its B's object will set the property of Name and call PrintMe, which give call to B's PrintMe method [remember virtual].

So, this is the real world use of A a = new B();

Hope you understand, any question any query please feel free to approch me.