How to realize hierarchical data query in SQLServer

How to implement hierarchical data query in SQLServer? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

What is the implementation method of SQLServer2000 hierarchical data query?

/ / hierarchical data entities

PublicclassComLevelDataEntity {publicComLevelDataEntity () {searchLevel=0;startWithRoot=true;dataObjectAlias= "t";} privateintsearchLevel;/ query hierarchy / / publicintSearchLevel {get {returnsearchLevel;} set {searchLevel=value;}} privatestringdataObjectAlias;/ alias / / publicstringDataObjectAlias {get {returndataObjectAlias;} set {dataObjectAlias=value;}} privatestringprotasis;/ conditional clause / / publicstringProtasis {get {returnstring.IsNullOrEmpty (protasis) string.Empty:string.Concat ("and", protasis);} set {protasis=value;}} privateboolstartWithRoot / whole tree search / publicboolStartWithRoot {get {returnstartWithRoot;} set {startWithRoot=value;}} privatestringdataObjectName;/ data object name / / publicstringDataObjectName {get {returndataObjectName;} set {dataObjectName=value;}} privateintcascadeLevel;/ level / / publicintCascadeLevel {get {returncascadeLevel;} set {cascadeLevel=value;} privatestringdisplayFieldName;/ display field name / / publicstringDisplayFieldName {get {returndisplayFieldName;} set {displayFieldName=value;} privatestringkeyFieldName / key field name / / publicstringKeyFieldName {get {returnkeyFieldName;} set {keyFieldName=value;}} privatestringdisplayFieldValue;/ display field value / / publicstringDisplayFieldValue {get {returndisplayFieldValue;} set {displayFieldValue=value;}} privatestringkeyFieldValue;/ key field value / publicstringKeyFieldValue {get {returnkeyFieldValue;} set {keyFieldValue=value;} privatestringlevelFieldName;/ hierarchical field name / / publicstringLevelFieldName {get {returnlevelFieldName;} set {levelFieldName=value;} privatestringlevelFieldValue / hierarchical field value / publicstringLevelFieldValue {get {returnlevelFieldValue;} set {levelFieldValue=value;}}

/ / sqlserver2000 stored procedure

Ifexists (select*fromdbo.sysobjectswhereid=object_id (N' [dbo]. [sp_gettreedata]') andOBJECTPROPERTY (id,N'IsProcedure') = 1) dropkeeper [dbo]. [sp _ gettreedata] GOSETQUOTED_IDENTIFIERONGOSETANSI_ NullSONGOCREPROCEDURE [dbo]. [sp _ gettreedata] (@ table_namenvarchar (50), @ idnvarchar (50), @ namenvarchar (50), @ parent_idnvarchar (50), @ startIdnvarchar (50), @ maxlevelint) ASdeclare@v_idnvarchar (50) declare@v_levelintdeclare@sqlnvarchar (500) declare@v_maxlevelintbegincreatetable#temp (idnvarchar (50) Namenvarchar (50), parent_idnvarchar (50) createtable#t1 (idnvarchar (50), namenvarchar (50), parent_idnvarchar (50), levelint) deletefromcom_tempset@sql='insertinto#tempselect'+@id+','+@name+','+@parent_id+'from'+@table_nameexecsp_executesql@sqlset@v_level=1set@v_id=@startIdset@v_maxlevel=@maxlevelinsert#t1selecta.id,a.name,a.parent_id @ v_levelfrom#tempawherea.id=@v_idwhile@@rowcount > 0 beginset.parentroomidin (selectidfrom#t1wherelevel=@v_level-1) endinsertintoCom_TEMPselecta.level,a.id,a.name,a.parent_id,b.nameparent_namefrom#t1aleftouterjoin#tempbona.parent_id=b.idwherea.level0select@re=@pid+'.'+@re,@pid=parent_idfromcom_tempwhereid=@pidreturn (@ re) endGOSETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGO

/ / data access layer method

PublicComLevelDataEntity [] PrepareHierarchyData (ComLevelDataEntityentity) {System.Data.SqlClient.SqlParameter [] parameters=newSystem.Data.SqlClient.SqlParameter [6]; parameters [0] = newSqlParameter (); parameters [0] .ParameterName = "@ table_name"; parameters [0] .SqlDbType = System.Data.SqlDbType.VarChar;parameters [1] = newSqlParameter (); parameters [1] .ParameterName = "@ id"; parameters [1] .SqlDbType = 50th parameters [1] .SqlDbType = System.Data.SqlDbType.VarChar;parameters [2] = newSqlParameter (); parameters [2] .ParameterName = "@ name" Parameters [2] .SqlDbType = System.Data.SqlDbType.VarChar;parameters [3] = newSqlParameter (); parameters [3] .ParameterName = "@ parent_id"; parameters [3] .SqlDbType = System.Data.SqlDbType.VarChar;parameters [4] = newSqlParameter (); parameters [4] .ParameterName = "@ startId"; parameters [4] .size = 50 startId parameters [4] .SqlDbType = System.Data.SqlDbType.VarChar;parameters [5] = newSqlParameter (); parameters [5] .ParameterName = "@ maxlevel"; SqlDbType = System.Data.SqlDbType.Int [5] StringspName= "sp_gettreedata"; if (entity.SearchLevel