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

//层次数据实体 public class ComLevelDataEntity { public ComLevelDataEntity() { searchLevel = 0; startWithRoot = true; dataObjectAlias = t; } private int searchLevel; /// summary /// 查询层次 /// /summary public int SearchLevel { get { retu

//层次数据实体

public class ComLevelDataEntity
{
public ComLevelDataEntity()
{
searchLevel = 0;
startWithRoot = true;
dataObjectAlias = "t";
}
private int searchLevel;
///

/// 查询层次
///

public int SearchLevel
{
get { return searchLevel; }
set { searchLevel = value; }
}

private string dataObjectAlias;
///

/// 别名
///

public string DataObjectAlias
{
get { return dataObjectAlias; }
set { dataObjectAlias = value; }
}
private string protasis;
///

/// 条件从句
///

public string Protasis
{
get
{
return string.IsNullOrEmpty(protasis) ? string.Empty : string.Concat(" and ", protasis);
}
set { protasis = value; }
}

private bool startWithRoot;
///

/// 整树搜索
///

public bool StartWithRoot
{
get { return startWithRoot; }
set { startWithRoot = value; }
}
private string dataObjectName;
///

/// 数据对象名称
///

public string DataObjectName
{
get { return dataObjectName; }
set { dataObjectName = value; }
}

private int cascadeLevel;
///

/// 层级
///

public int CascadeLevel
{
get { return cascadeLevel; }
set { cascadeLevel = value; }
}

private string displayFieldName;
///

/// 显示字段名称
///

public string DisplayFieldName
{
get { return displayFieldName; }
set { displayFieldName = value; }
}

private string keyFieldName;
///

/// 键值字段名称
///

public string KeyFieldName
{
get { return keyFieldName; }
set { keyFieldName = value; }
}

private string displayFieldValue;
///

/// 显示字段值
///

public string DisplayFieldValue
{
get { return displayFieldValue; }
set { displayFieldValue = value; }
}

private string keyFieldValue;
///

/// 键值字段值
///

public string KeyFieldValue
{
get { return keyFieldValue; }
set { keyFieldValue = value; }
}

private string levelFieldName;
///

/// 层次字段名称
///

public string LevelFieldName
{
get { return levelFieldName; }
set { levelFieldName = value; }
}

private string levelFieldValue;
///

/// 层次字段值
///

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 results = new List();
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();