ASP.NET无限级分类的非递归实现(存储过程版）

weixin_34191734

于 2019-06-24 16:37:23 发布

阅读量272

点赞数 1

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhangchenliang/archive/2008/01/10/1034135.html

本文介绍了ASP.NET无限级分类的非递归实现（存储过程版）。首先给出表结构，接着详细阐述了新建、删除、编辑分类的存储过程，以及显示分类的SELECT语句。通过这些存储过程，可实现理论上的无限级分类操作，且读取仅需一条SELECT语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Code
1

通常情况下，我们更多的操作是读取目录，所以，在下面的实现中，读取我们只需要一Select语句就够了,不使用递归，理论上无限级~！
2

===================================================
3

表结构：
4

表名：Tb_Column
5

表结构（所有字段非空）：
6

Column_ID int 主键（注：非标识）
7

Column_Name nvarchar(50)分类名称
8

Parent_ID int 父分类ID（默认值0）
9

Column_Path nvarchar(1000) 分类路径
10

Column_Depth int分类深度(默认值0）
11

Column_Order int排序(默认值0)
12

Column_Intro nvarchar(1000)分类说明
13

================================================
15

存储过程一：新建分类
16

CREATE PROCEDURE sp_Column_Insert
18

(
19

@Parent_ID int,
20

@Column_Name nvarchar(50),
21

@Column_Intro nvarchar(1000)
22

)
23

AS
24

Declare @Err As int
25

Set @Err=0
26

Begin Tran
28

--通过现有记录获取栏目ID
29

Declare @Column_ID As int
30

Declare @Column_Depth As int
31

Select @Column_ID = Max(Column_ID) From Tb_Column
32

IF @Column_ID Is Not Null
33

Set @Column_ID = @Column_ID+1
34

Else
35

Set @Column_ID = 1
36

--判断是否是顶级栏目，设置其Column_Path和Column_Order
38

Declare @Column_Path As nvarchar(1000)
39

Declare @Column_Order As int
40

IF @Parent_ID = 0
41

Begin
42

Set @Column_Path =Ltrim(Str(@Column_ID))
43

Select @Column_Order = Max(Column_Order) From Tb_Column
44

IF @Column_Order Is Not Null
45

Set @Column_Order = @Column_Order + 1
46

Else --如果没有查询到记录，说明这是第一条记录
47

Set @Column_Order = 1
48

--深度
50

Set @Column_Depth = 1
51

End
52

Else
53

Begin
54

--获取父节点的路径和深度
55

Select @Column_Path = Column_Path ,@Column_Depth = Column_Depth From Tb_Column Where
56

Column_ID=@Parent_ID
58

IF @Column_Path Is Null
59

Begin
60

Set @Err = 1
61

Goto theEnd
62

End
63

--获取同父节点下的最大序号
65

Select @Column_Order = Max(Column_Order) From Tb_PicColumn Where Column_Path like
66

''+@Column_Path+'|%' Or Column_ID = @Parent_ID
68

IF @Column_Order Is Not Null --如果序号存在，那么将该序号后的所有序号都加1
69

Begin
70

--更新当前要插入节点后所有节点的序号
71

Update Tb_Column Set Column_Order = Column_Order +1 Where Column_Order
72

>@Column_Order
74

--同父节点下的最大序号加上1，构成自己的序号
75

Set @Column_Order = @Column_Order + 1
76

End
77

Else
78

Begin
79

Set @Err=1
80

Goto theEnd
81

End
82

--父节点的路径加上自己的ID号，构成自己的路径
84

Set @Column_Path = @Column_Path + '|' + Ltrim(Str(@Column_ID))
85

--深度
87

Set @Column_Depth = @Column_Depth+1
88

End
90

Insert Into Tb_Column(Column_ID,Column_Name,Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Intro)
92

Values(@Column_ID,@Column_Name,@Parent_ID,@Column_Path,@Column_Depth,@Column_Order,@Column_Intro)
94

IF @@Error<>0
96

Begin
97

Set @Err=1
98

Goto theEnd
99

End
100

101

--更新当前记录之后的记录的ORDER
102

--Update Tb_Column Set Column_Order = Column_Order+1 Where Column_Order > @Column_Order
103

104

theEnd:
105

IF @Err=0
106

Begin
107

Commit Tran
108

Return @Column_ID
109

End
110

Else
111

Begin
112

Rollback Tran
113

Return 0
114

End
115

GO
116

117

===================================================
118

存储过程二：删除分类
119

CREATE PROCEDURE sp_Column_Delete
120

(
121

@Column_ID int
122

)
123

AS
124

Declare @Err As int
125

Set @Err = 0
126

Begin Tran
127

--首先查询该节点下是否有子节点
128

Select Column_ID From Tb_Column Where Parent_ID = @Column_ID
129

IF @@RowCount<>0
130

Begin
131

Set @Err = 1
132

Goto theEnd
133

End
134

135

--获取该节点的Column_Order,为了删除后整理其他记录的顺序
136

Declare @Column_Order As int
137

Select @Column_Order = Column_Order From Tb_Column Where Column_ID = @Column_ID
138

IF @Column_Order Is NUll
139

Begin
140

Set @Err =2
141

Goto theEnd
142

End
143

144

--更新其他记录的Column_Order
145

Update Tb_Column Set Column_Order = Column_Order -1 Where Column_Order >@Column_Order
146

IF @@Error<>0
147

Begin
148

Set @Err =3
149

Goto theEnd
150

End
151

152

--删除操作
153

Delete From Tb_Column Where Column_ID=@Column_ID
154

IF @@Error<>0
155

Begin
156

Set @Err =4
157

Goto theEnd
158

End
159

160

--更新其他记录的Column_ID
161

--Update Tb_Column Set Column_ID= Column_ID - 1 Where Column_ID >@Column_ID
162

--IF @@Error<>0
163

-- Begin
164

-- Set @Err =5
165

-- Goto theEnd
166

-- End
167

168

theEnd:
169

IF @Err = 0
170

Begin
171

Commit Tran
172

Return 0 --删除成功
173

End
174

Else
175

Begin
176

IF @Err=1
177

Begin
178

Rollback Tran
179

Return 1 --有子节点
180

End
181

Else
182

Begin
183

Rollback Tran
184

Return 2--未知错误
185

End
186

End
187

GO
188

=============================================
189

存储过程三：编辑分类
190

CREATE PROCEDURE sp_Column_Update
191

(
192

@Column_ID int,
193

@Parent_ID int,
194

@Column_Name nvarchar(50),
195

@Column_Intro nvarchar(1000)
196

)
197

AS
198

Declare @Err As int
199

Set @Err=0
200

201

Begin Tran
202

203

--获取修改前的：Parent_ID,Column_Depth,Column_Order
204

Declare @oParent_ID As int
205

Declare @oColumn_Depth As int
206

Declare @oColumn_Order As int
207

Declare @oColumn_Path As nvarchar(1000)
208

209

Select @oParent_ID = Parent_ID, @oColumn_Depth = Column_Depth,@oColumn_Order = Column_Order, @oColumn_Path = Column_Path From Tb_Column Where Column_ID = @Column_ID
210

IF @oParent_ID Is Null
211

Begin
212

Set @Err = 1
213

Goto theEnd
214

End
215

216

--如果父ID没有改变，则直接修改栏目名和栏目简介
217

IF @oParent_ID = @Parent_ID
218

Begin
219

Update Tb_Column Set Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
220

IF @@Error <> 0
221

Set @Err = 2
222

Goto theEnd
223

End
224

225

226

Declare @nColumn_Path As nvarchar(1000)
227

Declare @nColumn_Depth As int
228

Declare @nColumn_Order As int
229

230

--获取当前节点作为父节点所包含的节点数[包括自身] 注：如果返回 “1” 说明是单节点
231

Declare @theCount As int
232

Select @theCount = Count(Column_ID) From Tb_Column Where Column_ID=@Column_ID Or Column_Path like ''+@oColumn_Path+'|%'
233

IF @theCount Is Null
234

Begin
235

Set @Err = 3
236

Goto theEnd
237

End
238

239

IF @Parent_ID=0 --如果是设置为顶级节点，将节点设置为最后一个顶级节点
240

Begin
241

--Print '设置为顶级栏目'
242

Set @nColumn_Path = Ltrim(Str(@Column_ID))
243

Set @nColumn_Depth =1
244

245

Select @nColumn_Order = Max(Column_Order) From Tb_Column
246

IF @nColumn_Order Is NULL
247

Begin
248

Set @Err = 4
249

Goto theEnd
250

End
251

252

Set @nColumn_Order = @nColumn_Order - @theCount + 1
253

254

--更新三部分 1 节点本身 2 所有子节点 2 本树更改之前的后面记录的顺序
255

--Print '更新本栏目之前位置后面的所有栏目[不包括本栏目下的子栏目]的：Column_Order'
256

Update Tb_Column Set Column_Order = Column_Order-@theCount Where (Column_Order >@oColumn_Order) And (Column_Path Not like ''+@oColumn_Path+'|%')
257

IF @@Error <> 0
258

Begin
259

Set @Err = 7
260

Goto theEnd
261

End
262

263

--Print '更新本栏目的：Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro'
264

Print 'Order : '+Ltrim(Str(@nColumn_Order))
265

Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth,Column_Order = @nColumn_Order, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
266

IF @@Error <> 0
267

Begin
268

Set @Err = 5
269

Goto theEnd
270

End
271

272

--Print '更新本栏目下的所有子栏目的：Column_Path,Column_Depth,Column_Order'
273

Update Tb_Column Set Column_Path = Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+( @nColumn_Order-@oColumn_Order) Where Column_Path like ''+@oColumn_Path+'|%'
274

IF @@Error <> 0
275

Begin
276

Set @Err = 6
277

Goto theEnd
278

End
279

280

281

End
282

Else
283

Begin
284

--获取未来父节点的相关信息,并设置本节点的相关值
285

Select @nColumn_Depth = Column_Depth,@nColumn_Path = Column_Path From Tb_Column Where Column_ID = @Parent_ID
286

IF @nColumn_Depth Is NULL Or @nColumn_Path Is Null
287

Begin
288

Set @Err = 8
289

Goto theEnd
290

End
291

Set @nColumn_Depth = @nColumn_Depth +1
292

Select @nColumn_Order =Max(Column_Order) From Tb_Column Where Column_ID = @Parent_ID Or Column_Path like ''+@nColumn_Path+'|%'
293

IF @nColumn_Order Is NULL
294

Begin
295

Set @Err = 9
296

Goto theEnd
297

End
298

299

Set @nColumn_Path = @nColumn_Path +'|'+ Ltrim(Str(@Column_ID))
300

301

IF @nColumn_Order = @oColumn_Order+1 --如果新的父节点是原来位置上端最近一个兄弟,则所有节点的顺序都不改变
302

Begin
303

Update Tb_Column Set Parent_ID=@Parent_ID,Column_Path = @nColumn_Path,Column_Depth = @nColumn_Depth, Column_Name = @Column_Name,Column_Intro = @Column_Intro Where Column_ID = @Column_ID
304

IF @@Error <> 0
305

Begin
306

Set @Err = 10
307

Goto theEnd
308

End
309

End
310

311

Set @nColumn_Order = @nColumn_Order + 1
312

313

--更新三部分 1 本树更改之前的后面（或前面）记录的顺序 1 节点本身 3 所有子节点
314

--分为向上移或象下移
315

--Print '更新本栏目之前位置后面的所有栏目[或者本栏目之后位置] [不包括本栏目下的子栏目]的：Column_Order'
316

IF @nColumn_Order < @oColumn_Order
317

Begin
318

Update Tb_Column Set Column_Order = Column_Order+@theCount Where Column_Order<@oColumn_Order And Column_Order >=@nColumn_Order And (Column_Path Not like ''+@oColumn_Path+'|%') And Column_ID<>@Column_ID
319

IF @@Error <> 0
320

Begin
321

Set @Err = 12
322

Goto theEnd
323

End
324

End
325

Else
326

Begin
327

Update Tb_Column Set Column_Order = Column_Order-@theCount Where Column_Order >@oColumn_Order And Column_Order<@nColumn_Order And (Column_Path Not like ''+@oColumn_Path+'|%') And Column_ID<>@Column_ID
328

IF @@Error <> 0
329

Begin
330

Set @Err = 13
331

Goto theEnd
332

End
333

End
334

335

--Print '更新本栏目的：Parent_ID,Column_Path,Column_Depth,Column_Order,Column_Name,Column_Intro'
336

Print 'Order : '+Ltrim(Str(@nColumn_Order))
337

IF @nColumn_Order > @oColumn_Order
338

Set @nColumn_Order = @nColumn_Order - @theCount
339

IF @@Error <> 0
341

Begin
342

Set @Err = 10
343

Goto theEnd
344

End
345

346

--Print '更新本栏目下的所有子栏目的：Column_Paht,Column_Depth,Column_Order'
347

Update Tb_Column Set Column_Path = Replace(Column_Path,@oColumn_Path,@nColumn_Path),Column_Depth = Column_Depth + (@nColumn_Depth-@oColumn_Depth),Column_Order = Column_Order+(@nColumn_Order-@oColumn_Order) Where Column_Path like ''+@oColumn_Path+'|%'
348

IF @@Error <> 0
349

Begin
350

Set @Err = 11
351

Goto theEnd
352

End
353

End
354

355

theEnd:
356

IF @Err<>0 --如果有错误则返回错误号
357

Begin
358

Rollback Tran
359

Return @Err
360