|
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class UserDefinedFunctionsInt
{
/// <summary>
/// Converts an hierarchical table (id-parent relations) to a flat table (id-child relations) where
/// all subrecords are included.
/// usage example:
/// SELECT * FROM TreeToTable ('Organization', 'Id', 'ParentId', null)
/// </summary>
/// <remarks>
/// (c) 2006 Arjan Pot - Evident Interactive
/// </remarks>
/// <param name="tableName">Name of the table.</param>
/// <param name="idColumnName">Name of the id column.</param>
/// <param name="parentIdColumnName">Name of the parent id column.</param>
/// <param name="rootId">The root id.</param>
/// <returns></returns>
[SqlFunction(DataAccess = DataAccessKind.Read, FillRowMethodName = "TreeToTableIntFillRow", TableDefinition = "Id int, ChildId int, Depth int, ChildDepth int")]
public static IEnumerable TreeToTableInt(string tableName, string idColumnName, string parentIdColumnName, SqlInt32 rootId)
{
// The maximum depth that of child records that will be returned. This can be a parameter.
int maxDepth = int.MaxValue;
// Additional where to clause to limit records that need to be processed. This can be a parameter.
string whereClause = null;
// Read source table into dictionary and find the root
Node root = null;
SortedDictionary<SqlInt32, Node> source = new SortedDictionary<SqlInt32, Node>();
using (SqlConnection cn = new SqlConnection("Context Connection=true"))
{
string sql = string.Format("SELECT {0}, {1} FROM {2} ", idColumnName, parentIdColumnName, tableName);
if (whereClause != null && whereClause.Length > 0)
{
sql += string.Format(" WHERE ({0})", whereClause);
}
SqlCommand cmd = new SqlCommand(sql, cn);
cmd.CommandType = CommandType.Text;
cn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
SqlInt32 id = rdr.GetSqlInt32(0);
SqlInt32 parentId = rdr.GetSqlInt32(1);
Node node = new Node(id, parentId);
source.Add(id, node);
// If the requested root is null, the node with parent-id null is used
if (id == rootId || rootId.IsNull && parentId.IsNull)
{
root = node;
}
}
rdr.Close();
}
cn.Close();
}
// Check if there is a root
if (root == null)
{
return null;
}
// Create hierarchy
foreach (Node node in source.Values)
{
Node parent;
if (source.TryGetValue(node.ParentId, out parent))
{
parent.Add(node);
}
}
// Calculate the child-depth
root.CalculateDepth(0);
// Flatten
RowList result = new RowList();
root.Flatten(result, root.Id, root.Depth, maxDepth);
foreach (Node node in root.SubNodeList())
{
node.Flatten(result, node.Id, node.Depth, maxDepth);
}
// Return result
return result;
}
/// <summary>
/// This method is called when SQL Server needs to translate and understand what
/// the enumeration returned. Here we translate the content of the enumeration
/// to the columns in the output table.
/// </summary>
/// <param name="obj">An object containing one row from the enumeration.</param>
/// <param name="id">The id.</param>
/// <param name="childId">The child id.</param>
/// <param name="depth">The depth.</param>
/// <param name="childDepth">The child depth.</param>
public static void TreeToTableIntFillRow(Object obj, out SqlInt32 id, out SqlInt32 childId, out int depth, out int childDepth)
{
Row row = (Row)obj;
id = row.Id;
childId = row.ChildId;
depth = row.Depth;
childDepth = row.ChildDepth;
}
public class Row
{
public SqlInt32 Id;
public SqlInt32 ChildId;
public int Depth = -1;
public int ChildDepth = -1;
/// <summary>
/// Initializes a new instance of the Row class.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="childId">The child id.</param>
/// <param name="depth">The depth.</param>
/// <param name="childDepth">The child depth.</param>
public Row(SqlInt32 id, SqlInt32 childId, int depth, int childDepth)
{
this.Id = id;
this.ChildId = childId;
this.Depth = depth;
this.ChildDepth = childDepth;
}
}
public class RowList : List<Row>
{
}
public class Node
{
public SqlInt32 Id;
public SqlInt32 ParentId;
public NodeList NodeList;
public int Depth = -1;
public int ChildDepth = -1;
/// <summary>
/// Initializes a new instance of the <see cref="T:Org"/> class.
/// </summary>
/// <param name="id">The id.</param>
/// <param name="parentId">The parent id.</param>
public Node(SqlInt32 id, SqlInt32 parentId)
{
this.Id = id;
this.ParentId = parentId;
}
/// <summary>
/// Adds the specified node.
/// </summary>
/// <param name="node">The node.</param>
public void Add(Node node)
{
// Add
if (NodeList == null)
{
NodeList = new NodeList();
}
NodeList.Add(node);
}
/// <summary>
/// Get a list of subnodes.
/// </summary>
/// <returns>All nodes below this node.</returns>
public NodeList SubNodeList()
{
NodeList result = new NodeList();
if (this.NodeList != null)
{
result.AddRange(this.NodeList);
foreach (Node node in this.NodeList)
{
if (node.NodeList != null)
{
result.AddRange(node.SubNodeList());
}
}
}
return result;
}
/// <summary>
/// Flattens the specified list.
/// </summary>
/// <param name="result">The result.</param>
/// <param name="id">The id.</param>
/// <param name="depth">The depth.</param>
/// <param name="maxDepth">The maximum depth.</param>
public void Flatten(RowList result, SqlInt32 id, int depth, int maxDepth)
{
if (depth > maxDepth)
{
return;
}
Row row = new Row(id, this.Id, depth, this.Depth);
result.Add(row);
foreach (Node node in this.SubNodeList())
{
Row row2 = new Row(id, node.Id, depth, node.Depth);
result.Add(row2);
}
}
/// <summary>
/// Calculates the depth.
/// </summary>
/// <param name="startDepth">The start depth.</param>
public void CalculateDepth(int startDepth)
{
this.Depth = startDepth;
if (this.NodeList != null)
{
foreach (Node node in this.NodeList)
{
node.CalculateDepth(startDepth + 1);
}
}
}
/// <summary>
/// Returns a <see cref="T:System.String"></see> that represents the current <see cref="T:System.Object"></see>.
/// </summary>
/// <returns>
/// A <see cref="T:System.String"></see> that represents the current <see cref="T:System.Object"></see>.
/// </returns>
public new string ToString()
{
return string.Format("Id={0} ParentId={1} Depth={2}", this.Id, this.ParentId, this.Depth);
}
}
public class NodeList : List<Node>
{
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.