Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.