SQL Server 2000层次数据查询的实现方法

发布时间：2011-08-17 09:34 来源：未知

以C#为前端，SQL Server 2000层次数据查询是如何实现的呢？本文我们主要就介绍这部分内容，接下来就让我们一起来了解一下这一过程吧。

//层次数据实体

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(); 

关于以C#为前端，SQL Server 2000层次数据查询的实现方法就介绍到这里了，希望本次的介绍能够对您有所收获！