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

138次閱讀

共計 6242 個字符，預計需要花費 16 分鐘才能閱讀完成。

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 中怎么實現層次數據查詢問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注丸趣 TV 行業資訊頻道了解更多相關知識。

正文完