Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 treeview - Was told this is the place to post
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 01/05/2007 :  15:14:23  Show Profile  Reply with Quote
I just wanted to share my version of a treeview with the people who have helped me.

It only requires returning the recordID and a parent recordID field.

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Text;
using System.Web.UI.HtmlControls;
using Com.Commerce.DomainUsers;

public partial class rd_addOrEdit_grouplist : System.Web.UI.Page
String userName = "";
String msg = "";
String ListOwnerRoles = "";
protected void Page_Load(object sender, EventArgs e)
Master.Page.Title = "Kansas Resources Directory - Add or Edit Group List";

if ((msg != null))
Session["errmsg"] = msg;
DomainUserInfo userInfo = new DomainUserInfo();
userName = userInfo.GetLongLogInUserName();
if (Session["currRolesString"] != null)
ListOwnerRoles = Session["currRolesString"].ToString();
if (!IsPostBack)
void PopulateNodes()
DataTable groupList = GetTreeViewData();
DataView parent = GetParents(groupList);
foreach (DataRowView row in parent)
TreeNode parentNode = new TreeNode();
parentNode.Text = row["ListTitle"].ToString();
//IF owned by user, can be edited
if (row["ListOwner"].ToString() == userName)
parentNode.NavigateUrl = "rd_edit_existing_list.aspx?GroupListID=" + row["ListID"].ToString();
parentNode.Value = row["ListID"].ToString();
Addchild(groupList, parentNode);
DataTable GetTreeViewData()
string connString1 = ConfigurationManager.ConnectionStrings["CommerceCustomersConnectionString"].ConnectionString;
SqlConnection conn = new SqlConnection(connString1);
SqlDataAdapter dalist = new SqlDataAdapter();
SqlCommand sqlcmd = new SqlCommand("treeViewDefaultDir", conn);
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@ListOwner", userName);
sqlcmd.Parameters.AddWithValue("@Roles", ListOwnerRoles);
dalist.SelectCommand = sqlcmd;
//Get all the records which belong to user or
//which are shared by the owner and the user has that role

// find shared lists who's parent is not present in the selected
// records and set the parent to 0
DataSet groupDataSet = new DataSet();
dalist.Fill(groupDataSet, "groups");
foreach (DataRow outerLoop in groupDataSet.Tables["groups"].Rows)
String flag = "0";

foreach (DataRow innerLoop in groupDataSet.Tables["groups"].Rows)
if (outerLoop[2].ToString() == innerLoop[0].ToString()) // innerLoop[2] parentID outerLoop[0] listID
flag = "1"; // if there is a parent in the list, exit
if (flag == "0")
outerLoop[2] = 0;
DataTable dtbl = groupDataSet.Tables["groups"];
//DataTable dtbl = new DataTable();
return dtbl;
DataView GetParents(DataTable dupParentID)
DataView view = new DataView(dupParentID);
view.RowFilter = "ListParent=0";
return view;
void Addchild(DataTable groupList, TreeNode node)
DataView child = Getchild(groupList, node.Value);
foreach (DataRowView row in child)
TreeNode childNode = new TreeNode();
childNode.Text = row["ListTitle"].ToString();
if (row["ListOwner"].ToString() == userName)
childNode.NavigateUrl = "rd_edit_existing_list.aspx?GroupListID=" + row["ListID"].ToString();
childNode.Value = row["ListID"].ToString();
//See if there are children of this child
Addchild(groupList, childNode);
DataView Getchild(DataTable groupList, string ID)
DataView view = new DataView(groupList);
view.RowFilter = "ListParent=" + ID;
return view;
protected void GetAuth()
AuthUser oiGet = new AuthUser();
msg = oiGet.xMsg;

The SQL:

ALTER PROCEDURE dbo.treeViewDefaultDir

@ListOwner varchar(200),
@Roles varchar(2000)


SELECT Distinct ListID,

FROM GroupList

SELECT ListReferencedID
FROM GroupListShares
charindex('\',SecurityGroupReferenceID) +1,
(','+@Roles+',')) > 0)
OR ListOwner = @ListOwner

ORDER BY ListTitle


The SecurityGroupReferenceID contains a "\" in the middle of it which SQL Server doesn't like. It will actually double the amount of "\" you have in the field in the return set.

Thanks to all
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000