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.