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();
    }
}