使用C#和SQL server根据父级查询该子级（包括父级）下所有对应的数据

使用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
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

最终的到的结果类似于"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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53