SQLServer中怎么實現層次數據查詢

SQLServer中怎么實現層次數據查詢，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

　　SQLServer2000層次數據查詢的實現方法是什么

　　//層次數據實體

　　publicclassComLevelDataEntity{publicComLevelDataEntity(){searchLevel=0;startWithRoot=true;dataObjectAlias="t";}privateintsearchLevel;//////查詢層次///publicintSearchLevel{get{returnsearchLevel;}set{searchLevel=value;}}privatestringdataObjectAlias;//////別名///publicstringDataObjectAlias{get{returndataObjectAlias;}set{dataObjectAlias=value;}}privatestringprotasis;//////條件從句///publicstringProtasis{get{returnstring.IsNullOrEmpty(protasis)string.Empty:string.Concat("and",protasis);}set{protasis=value;}}privateboolstartWithRoot;//////整樹搜索///publicboolStartWithRoot{get{returnstartWithRoot;}set{startWithRoot=value;}}privatestringdataObjectName;//////數據對象名稱///publicstringDataObjectName{get{returndataObjectName;}set{dataObjectName=value;}}privateintcascadeLevel;//////層級///publicintCascadeLevel{get{returncascadeLevel;}set{cascadeLevel=value;}}privatestringdisplayFieldName;//////顯示字段名稱///publicstringDisplayFieldName{get{returndisplayFieldName;}set{displayFieldName=value;}}privatestringkeyFieldName;//////鍵值字段名稱///publicstringKeyFieldName{get{returnkeyFieldName;}set{keyFieldName=value;}}privatestringdisplayFieldValue;//////顯示字段值///publicstringDisplayFieldValue{get{returndisplayFieldValue;}set{displayFieldValue=value;}}privatestringkeyFieldValue;//////鍵值字段值///publicstringKeyFieldValue{get{returnkeyFieldValue;}set{keyFieldValue=value;}}privatestringlevelFieldName;//////層次字段名稱///publicstringLevelFieldName{get{returnlevelFieldName;}set{levelFieldName=value;}}privatestringlevelFieldValue;//////層次字段值///publicstringLevelFieldValue{get{returnlevelFieldValue;}set{levelFieldValue=value;}}}

　　//sqlserver2000存儲過程

　　ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[sp_gettreedata]')andOBJECTPROPERTY(id,N'IsProcedure')=1)dropprocedure[dbo].[sp_gettreedata]GOSETQUOTED_IDENTIFIERONGOSETANSI_NULLSONGOCREATEPROCEDURE[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>0beginset@v_level=@v_level+1insert#t1selecta.id,a.name,a.parent_id,@v_levelfrom#tempawherea.parent_idin(selectidfrom#t1wherelevel=@v_level-1)endinsertintoCom_TEMPselecta.level,a.id,a.name,a.parent_id,b.nameparent_namefrom#t1aleftouterjoin#tempbona.parent_id=b.idwherea.level<=@maxlevelorderbya.levelselect*fromcom_temporderbydbo.f_getidpath(id)endgosetquoted_identifieroffgosetansi_nullsongo fromdbo.sysobjectswhereid="object_id(N'[dbo].[f_getidpath]')andxtypein(N'FN',N'IF',N'TF'))dropfunction[dbo].[f_getidpath]GOSETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGOCREATEfunctionf_getidpath(@idchar(36))returnsvarchar(8000)asbegindeclare@revarchar(8000),@pidchar(36)set@re=@idselect@pid=parent_idfromcom_tempwhereid=@idwhile@@rowcount">0select@re=@pid+'.'+@re,@pid=parent_idfromcom_tempwhereid=@pidreturn(@re)endGOSETQUOTED_IDENTIFIEROFFGOSETANSI_NULLSONGO

　　//數據訪問層方法

　　publicComLevelDataEntity[]PrepareHierarchyData(ComLevelDataEntityentity){System.Data.SqlClient.SqlParameter[]parameters=newSystem.Data.SqlClient.SqlParameter[6];parameters[0]=newSqlParameter();parameters[0].ParameterName="@table_name";parameters[0].Size=50;parameters[0].SqlDbType=System.Data.SqlDbType.VarChar;parameters[1]=newSqlParameter();parameters[1].ParameterName="@id";parameters[1].Size=50;parameters[1].SqlDbType=System.Data.SqlDbType.VarChar;parameters[2]=newSqlParameter();parameters[2].ParameterName="@name";parameters[2].Size=50;parameters[2].SqlDbType=System.Data.SqlDbType.VarChar;parameters[3]=newSqlParameter();parameters[3].ParameterName="@parent_id";parameters[3].Size=50;parameters[3].SqlDbType=System.Data.SqlDbType.VarChar;parameters[4]=newSqlParameter();parameters[4].ParameterName="@startId";parameters[4].Size=50;parameters[4].SqlDbType=System.Data.SqlDbType.VarChar;parameters[5]=newSqlParameter();parameters[5].ParameterName="@maxlevel";parameters[5].SqlDbType=System.Data.SqlDbType.Int;stringspName="sp_gettreedata";if(entity.SearchLevel<=0){parameters[0].Value=entity.DataObjectName;parameters[1].Value=entity.KeyFieldName;parameters[2].Value=entity.DisplayFieldName;parameters[3].Value=entity.LevelFieldName;parameters[4].Value=entity.KeyFieldValue;parameters[5].Value=100;}else{parameters[0].Value=entity.DataObjectName;parameters[1].Value=entity.KeyFieldName;parameters[2].Value=entity.DisplayFieldName;parameters[3].Value=entity.LevelFieldName;parameters[4].Value=entity.KeyFieldValue;parameters[5].Value=entity.SearchLevel;}Listresults=newList();IDbConnectionconnection=IDALProvider.IDAL.PopConnection();IDataReadersqlReader=IDALProvider.IDAL.ExecuteReader(connection,spName,parameters);while(sqlReader.Read()){ComLevelDataEntityresult=newComLevelDataEntity();if(!sqlReader.IsDBNull(0))result.CascadeLevel=(int)sqlReader.GetInt32(0);if(!sqlReader.IsDBNull(1))result.KeyFieldValue=sqlReader.GetString(1);if(!sqlReader.IsDBNull(2))result.DisplayFieldValue=sqlReader.GetString(2);if(!sqlReader.IsDBNull(3))result.LevelFieldValue=sqlReader.GetString(3);result.DataObjectName=entity.DataObjectName;result.DisplayFieldName=entity.DisplayFieldName;result.LevelFieldName=entity.LevelFieldName;result.KeyFieldName=entity.KeyFieldName;results.Add(result);}sqlReader.Close();IDALProvider.IDAL.PushConnection(connection);returnresults.ToArray();} 　　//前端調用代碼 　　ComLevelDataEntityentity=newComLevelDataEntity();entity.DataObjectName="COM_DEPART";entity.DataObjectAlias="t";entity.StartWithRoot=true;entity.KeyFieldName="id";entity.LevelFieldName="parent_id";entity.DisplayFieldName="name";entity.KeyFieldValue=SystemLogic.CurrentUser.DATA_ORGANISE_ID;ComLevelDataEntity[]results=IDALProvider.IDAL.PrepareHierarchyData(entity);this.tvTree.DataSource=results;this.tvTree.DataBind();  

關于SQLServer中怎么實現層次數據查詢問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。