欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2023-12-23 20:41:51
...

//层次数据实体 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 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 {
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();

上一篇:

下一篇: