以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层次数据查询的实现方法就介绍到这里了，希望本次的介绍能够对您有所收获！