SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

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

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
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000