|
//层次数据实体
?public class ComLevelDataEntity
??? {
??????? public ComLevelDataEntity()
??????? {
??????????? searchLevel = 0;
??????????? startWithRoot = true;
??????????? dataObjectAlias = "t";
??????? }
??????? private int searchLevel;
??????? /// <summary>
??????? /// 查询层次
??????? /// </summary>
??????? public int SearchLevel
??????? {
??????????? get { return searchLevel; }
??????????? set { searchLevel = value; }
??????? }
??????? private string dataObjectAlias;
??????? /// <summary>
??????? /// 别名
??????? /// </summary>
??????? public string DataObjectAlias
??????? {
??????????? get { return dataObjectAlias; }
??????????? set { dataObjectAlias = value; }
??????? }
??????? private string protasis;
??????? /// <summary>
??????? /// 条件从句
??????? /// </summary>
??????? public string Protasis
??????? {
??????????? get
??????????? {
??????????????? return string.IsNullOrEmpty(protasis) ? string.Empty : string.Concat(" and ",protasis);
??????????? }
??????????? set { protasis = value; }
??????? }
??????? private bool startWithRoot;
??????? /// <summary>
??????? /// 整树搜索
??????? /// </summary>
??????? public bool StartWithRoot
??????? {
??????????? get { return startWithRoot; }
??????????? set { startWithRoot = value; }
??????? }
??????? private string dataObjectName;
??????? /// <summary>
??????? /// 数据对象名称
??????? /// </summary>
??????? public string DataObjectName
??????? {
??????????? get { return dataObjectName; }
??????????? set { dataObjectName = value; }
??????? }
??????? private int cascadeLevel;
??????? /// <summary>
??????? /// 层级
??????? /// </summary>
??????? public int CascadeLevel
??????? {
??????????? get { return cascadeLevel; }
??????????? set { cascadeLevel = value; }
??????? }
??????? private string displayFieldName;
??????? /// <summary>
??????? /// 显示字段名称
??????? /// </summary>
??????? public string DisplayFieldName
??????? {
??????????? get { return displayFieldName; }
??????????? set { displayFieldName = value; }
??????? }
??????? private string keyFieldName;
??????? /// <summary>
??????? /// 键值字段名称
??????? /// </summary>
??????? public string KeyFieldName
??????? {
??????????? get { return keyFieldName; }
??????????? set { keyFieldName = value; }
??????? }
??????? private string displayFieldValue;
??????? /// <summary>
??????? /// 显示字段值
??????? /// </summary>
??????? public string DisplayFieldValue
??????? {
??????????? get { return displayFieldValue; }
??????????? set { displayFieldValue = value; }
??????? }
??????? private string keyFieldValue;
??????? /// <summary>
??????? /// 键值字段值
??????? /// </summary>
??????? public string KeyFieldValue
??????? {
??????????? get { return keyFieldValue; }
??????????? set { keyFieldValue = value; }
??????? }
??????? private string levelFieldName;
??????? /// <summary>
??????? /// 层次字段名称
??????? /// </summary>
??????? public string LevelFieldName
??????? {
??????????? get { return levelFieldName; }
??????????? set { levelFieldName = value; }
??????? }
??????? private string levelFieldValue;
??????? /// <summary>
??????? /// 层次字段值
??????? /// </summary>
??????? public string LevelFieldValue
??????? {
??????????? get { return levelFieldValue; }
??????????? set { levelFieldValue = value; }
??????? }
??? }
//sqlserver2000存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_gettreedata]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)
drop procedure [dbo].[sp_gettreedata]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE????? PROCEDURE [dbo].[sp_gettreedata]
(@table_name nvarchar(50),@id nvarchar(50),@name nvarchar(50),@parent_id nvarchar(50),@startId? nvarchar(50),@maxlevel int)
AS
??? declare @v_id nvarchar(50)
??? declare @v_level int
??? declare @sql nvarchar(500)
??? declare @v_maxlevel int
begin
??? create table #temp (id nvarchar(50),name nvarchar(50),parent_id nvarchar(50))
??? create table #t1 (id nvarchar(50),parent_id nvarchar(50),level int)
??? delete from?? com_temp
??? set @sql='insert into #temp select '+@id+','+@parent_id +' from '+@table_name
??? exec sp_executesql @sql
??? set @v_level=1
??? set @v_id=@startId
??? set @v_maxlevel = @maxlevel
??? insert #t1 select a.id,a.name,a.parent_id,@v_level from #temp a where a.id=@v_id
??? while @@rowcount>0
??? begin
??????? set @v_level=@v_level+1
??????? insert #t1 select a.id,@v_level
??????? from #temp a where a.parent_id in
??????? (select id from #t1 where level=@v_level-1)
??? end
??? insert into Com_TEMP select a.level,a.id,b.name parent_name
??? from? #t1 a? left outer join #temp b
??? on a.parent_id = b.id
??? where a.level <= @maxlevel
??? order by a.level
??? select * from Com_TEMP order by dbo.f_getidpath(id)
end
?
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//sqlserver2000函数
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getidpath]') and xtype in (N'FN',N'IF',N'TF'))
drop function [dbo].[f_getidpath]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
?
CREATE????? function?? f_getidpath(@id?? char(36))
returns?? varchar(8000)
as
begin
declare?? @re?? varchar(8000),@pid?? char(36)
set?? @re=@id
select?? @pid=parent_id?? from?? com_temp?? where?? id=@id
while?? @@rowcount> 0
select?? @re=@pid + '.'+ @re,@pid=parent_id?? from?? com_temp? where?? id=@pid
return(@re)
end
?
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
?
//数据访问层方法
public ComLevelDataEntity[] PrepareHierarchyData(ComLevelDataEntity entity)
??????? {
??????????? System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[6];
??????????? parameters[0] = new SqlParameter();
??????????? parameters[0].ParameterName = "@table_name";
??????????? parameters[0].Size = 50;
??????????? parameters[0].SqlDbType = System.Data.SqlDbType.VarChar;
??????????? parameters[1] = new SqlParameter();
??????????? parameters[1].ParameterName = "@id";
??????????? parameters[1].Size = 50;
??????????? parameters[1].SqlDbType = System.Data.SqlDbType.VarChar;
??????????? parameters[2] = new SqlParameter();
??????????? parameters[2].ParameterName = "@name";
??????????? parameters[2].Size = 50;
??????????? parameters[2].SqlDbType = System.Data.SqlDbType.VarChar;
??????????? parameters[3] = new SqlParameter();
??????????? parameters[3].ParameterName = "@parent_id";
??????????? parameters[3].Size = 50;
??????????? parameters[3].SqlDbType = System.Data.SqlDbType.VarChar;
??????????? parameters[4] = new SqlParameter();
??????????? parameters[4].ParameterName = "@startId";
??????????? parameters[4].Size = 50;
??????????? parameters[4].SqlDbType = System.Data.SqlDbType.VarChar;
??????????? parameters[5] = new SqlParameter();
??????????? parameters[5].ParameterName = "@maxlevel";
??????????? parameters[5].SqlDbType = System.Data.SqlDbType.Int;
??????????? string spName = "sp_gettreedata";
??????????? if (entity.SearchLevel <= 0)
??????????? {
??????????????? parameters[0].Value = entity.DataObjectName;
??????????????? parameters[1].Value = entity.KeyFieldName;
??????????????? parameters[2].Value = entity.DisplayFieldName;
??????????????? parameters[3].Value = entity.LevelFieldName;
??????????????? parameters[4].Value = entity.KeyFieldValue;
??????????????? parameters[5].Value = 100;
??????????? }
??????????? else
??????????? {
??????????????? parameters[0].Value = entity.DataObjectName;
??????????????? parameters[1].Value = entity.KeyFieldName;
??????????????? parameters[2].Value = entity.DisplayFieldName;
??????????????? parameters[3].Value = entity.LevelFieldName;
??????????????? parameters[4].Value = entity.KeyFieldValue;
??????????????? parameters[5].Value = entity.SearchLevel;
??????????? }
??????????? List<ComLevelDataEntity> results = new List<ComLevelDataEntity>();
??????????? IDbConnection connection = IDALProvider.IDAL.PopConnection();
??????????? IDataReader sqlReader = IDALProvider.IDAL.ExecuteReader(connection,spName,parameters);
??????????? while (sqlReader.Read())
??????????? {
??????????????? ComLevelDataEntity result = new ComLevelDataEntity();
??????????????? if (!sqlReader.IsDBNull(0))
??????????????????? result.CascadeLevel = (int)sqlReader.GetInt32(0);
??????????????? if (!sqlReader.IsDBNull(1))
??????????????????? result.KeyFieldValue = sqlReader.GetString(1);
??????????????? if (!sqlReader.IsDBNull(2))
??????????????????? result.DisplayFieldValue = sqlReader.GetString(2);
??????????????? if (!sqlReader.IsDBNull(3))
??????????????????? result.LevelFieldValue = sqlReader.GetString(3);
??????????????? result.DataObjectName = entity.DataObjectName;
??????????????? result.DisplayFieldName = entity.DisplayFieldName;
??????????????? result.LevelFieldName = entity.LevelFieldName;
??????????????? result.KeyFieldName = entity.KeyFieldName;
??????????????? results.Add(result);
??????????? }
??????????? sqlReader.Close();
??????????? IDALProvider.IDAL.PushConnection(connection);
??????????? return results.ToArray();
??????? }
//前端调用代码
ComLevelDataEntity entity = new ComLevelDataEntity(); entity.DataObjectName = "COM_DEPART"; entity.DataObjectAlias = "t"; entity.StartWithRoot = true; entity.KeyFieldName = "id"; entity.LevelFieldName = "parent_id"; entity.DisplayFieldName = "name"; entity.KeyFieldValue = SystemLogic.CurrentUser.DATA_ORGANISE_ID; ComLevelDataEntity[] results = IDALProvider.IDAL.PrepareHierarchyData(entity); this.tvTree.DataSource = results; this.tvTree.DataBind();
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
