Search

Nov 28, 2008

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)

Nov 25, 2008

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.

Nov 24, 2008

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.

Nov 20, 2008

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.