Share my version of treeview - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Share my version of treeview

Author

Topic

durban

7 Posts

Posted - 2007-01-05 : 14:29:15

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";

GetAuth();
if ((msg != null))
{
Session["errmsg"] = msg;
Response.Redirect("Default.aspx");
}
else
{
DomainUserInfo userInfo = new DomainUserInfo();
userName = userInfo.GetLongLogInUserName();
}
if (Session["currRolesString"] != null)
{
ListOwnerRoles = Session["currRolesString"].ToString();
}
{
if (!IsPostBack)
PopulateNodes();
}
}
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();
TreeView1.Nodes.Add(parentNode);
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
break;
}
}
if (flag == "0")
{
outerLoop[2] = 0;
}
}
DataTable dtbl = groupDataSet.Tables["groups"];
//DataTable dtbl = new DataTable();
//dalist.Fill(dtbl);
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();
node.ChildNodes.Add(childNode);
//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)

AS

SELECT Distinct ListID,
ListTitle,
ListParent,
ListOwner

FROM GroupList

WHERE (ListID IN
(
SELECT ListReferencedID
FROM GroupListShares
WHERE (PATINDEX(('%,'
+
(substring(SecurityGroupReferenceID,
1,
(charindex('\',SecurityGroupReferenceID)-1))
+
SUBSTRING(SecurityGroupReferenceID,
charindex('\',SecurityGroupReferenceID) +1,
len(SecurityGroupReferenceID)))
+
',%'),
(','+@Roles+',')) > 0)
OR ListOwner = @ListOwner
)
)

ORDER BY ListTitle

RETURN

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

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-05 : 14:49:18

Thanks - you should post it to the Script Library forum, that's where complete scripts are kept for general use.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources