使用C#根据父级查询对应的子级
前端通过选中树形下拉框进行查询,id可能为父级也可能为子级
public static string GetChildFieldIds(int id)
{
IFieldBL fbl = new FieldBL();
List<IntellManuSchool_Field> fieldAll = fbl.GetAllField();
string result = id + ",";
List<IntellManuSchool_Field> second = fieldAll.FindAll(s => s.ParentId == id && s.IsShow == 1).OrderBy(x => x.Orders).ToList();
if (second != null && second.Count > 0)
{
foreach (IntellManuSchool_Field item in second)
{
result += item.FieldID + ",";
List<IntellManuSchool_Field> three = fieldAll.FindAll(s => s.ParentId == item.FieldID && s.IsShow == 1).OrderBy(x => x.Orders).ToList();
if (three != null && three.Count > 0)
{
foreach (IntellManuSchool_Field entity in three)
{
result += entity.FieldID + ",";
}
}
}
}
return result.TrimEnd(',');
}
最终的到的结果类似于"1,2,3,4,…"以逗号隔开的字符串id
通过SQL server通过对应的子级查询对应的数据
USE [base]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[IntellManuSchool_CourseStatisticsByPage]
(
@Title varchar(20),
@Field VARCHAR(100),--前端传来以逗号隔开的字符串id
@page_size int,
@current_page int,
@total int output
)
as
begin
DECLARE @theStr varchar(2000)
set @theStr='1=1 '
if @Title<>''
set @theStr=@theStr+' and Title like ''%'+@Title+'%'''
if @Field<>''
begin
DECLARE @theField varchar(1000)
set @theField = ''
declare @idx int
declare @slice varchar(10)
select @idx = 1
while @idx!= 0
begin
set @idx =charindex(',',@Field)
if @idx!=0
set @slice =left(@Field,@idx - 1)
else
set @slice = @Field
if(len(@slice)>0)
begin
if @theField = ''
set @theField = 'charindex('',''+'''+@slice+'''+'','','',''+Field+'','') > 0'
else
set @theField = @theField + ' or charindex('',''+'''+@slice+'''+'','','',''+Field+'','') > 0'
end
set @Field =right(@Field,len(@Field)- @idx)
if len(@Field)= 0 break
end
set @theStr=@theStr+' and ('+ @theField +')'
end
exec C_up_custompage1 PackegId,'AddTime','*','IntellManuSchool_CourseStatisticsV',@theStr,@page_size,@current_page,@total output
print @theStr
end
GO