I am a danish programmer living in Bangkok.
Read more about me @ rasmus.rummel.dk.
Webmodelling Home > ASP.NET > C# Utility Functions > DataTable Order Recursive

DataTable Order Recursive

Getting recursive data is such a common task that it gives good meaning to encapsulate it into reusable utilities. The typical way of getting data recursively ordered is by using either a stored procedure, using multiple queries or do the ordering client-side and while the stored procedure may get the data faster if you need a sub hierarchy of a big data set, in my use it is most often advantegeous to do it client-side as it is database backend indifferent as well as easier to reuse between projects than a stored procedure.

Usage

  • Example
    • Categories are often recursive in nature and indeed I use this function to order the category menu in my Discussions WebControl.
  • Example Code
    • DataTable myTable = GetData(); //your custom function
      Utils.DataTable.OrderRecursive(ref myTable, "ID""PID""SortOrder");
      

The OrderRecursive function :

public static void OrderRecursive(ref DataTable pdtSource, string pIDColumn, string pPIDColumn, string pSortColumn)
{
	pdtSource.Columns.Add(new DataColumn("RecursiveLevel"Type.GetType("System.Int32")));
	DataTable dtResult = pdtSource.Clone();
 
	DataView dv = new DataView(pdtSource);
	dv.RowFilter = pPIDColumn + " IS NULL OR " + pPIDColumn + " = 0";
	dv.Sort = pSortColumn;
 
	foreach (DataRowView drv in dv)
	{
		DataRow drResult = dtResult.NewRow();
		drResult.ItemArray = drv.Row.ItemArray;
		drResult["RecursiveLevel"] = 0;
		dtResult.Rows.Add(drResult);
 
		OrderRecursiveHelper(ref pdtSource, ref dtResult, drv.Row, 1, pIDColumn, pPIDColumn, pSortColumn);
	}
 
	pdtSource.Clear();
	foreach (DataRow drResult in dtResult.Rows)
	{
		pdtSource.Rows.Add(drResult.ItemArray);
	}
 
	pdtSource = dtResult;
}
private static void OrderRecursiveHelper(ref DataTable pdtSource, ref DataTable pdtResult, DataRow pdrCurrent, int pRecursiveLevel, string pIDColumn, string pPIDColumn, string pSortColumn)
{
	int currentID = (int)pdrCurrent["ID"];
	DataView dv = new DataView(pdtSource);
	dv.RowFilter = pPIDColumn + " = " + currentID;
	dv.Sort = pSortColumn;
 
	foreach (DataRowView drv in dv)
	{
		DataRow drResult = pdtResult.NewRow();
		drResult.ItemArray = drv.Row.ItemArray;
		drResult["RecursiveLevel"] = pRecursiveLevel;
		pdtResult.Rows.Add(drResult);
 
		OrderRecursiveHelper(ref pdtSource, ref pdtResult, drv.Row, (pRecursiveLevel + 1), pIDColumn, pPIDColumn, pSortColumn);
	}
}

Comments

You can comment without logging in
 
 B  U  I  S 
Words: Chars: Chars left: 
 Captcha 
 Nickname
Facebook
    
bunjing
--------------
      report  reply  
tae