sqlserver2000中的层次数据查询实现(c#前端)

//层次数据实体

 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),name nvarchar(50),parent_id nvarchar(50),level int)
    delete from   com_temp
    set @sql='insert into #temp select '+@id+','+@name+','+@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,a.name,a.parent_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,a.name,a.parent_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();