以C#为前端，SQL Server 2000层次数据查询是如何实现的呢？本文我们主要就介绍这部分内容，接下来就让我们一起来了解一下这一过程吧。
//层次数据实体
publicclassComLevelDataEntity { publicComLevelDataEntity() { searchLevel=0; startWithRoot=true; dataObjectAlias="t"; } privateintsearchLevel; ///<summary>///查询层次 ///</summary>publicintSearchLevel { get{returnsearchLevel;} set{searchLevel=value;} } privatestringdataObjectAlias; ///<summary>///别名 ///</summary>publicstringDataObjectAlias { get{returndataObjectAlias;} set{dataObjectAlias=value;} } privatestringprotasis; ///<summary>///条件从句 ///</summary>publicstringProtasis { get { returnstring.IsNullOrEmpty(protasis)?string.Empty:string.Concat("and",protasis); } set{protasis=value;} } privateboolstartWithRoot; ///<summary>///整树搜索 ///</summary>publicboolStartWithRoot { get{returnstartWithRoot;} set{startWithRoot=value;} } privatestringdataObjectName; ///<summary>///数据对象名称 ///</summary>publicstringDataObjectName { get{returndataObjectName;} set{dataObjectName=value;} } privateintcascadeLevel; ///<summary>///层级 ///</summary>publicintCascadeLevel { get{returncascadeLevel;} set{cascadeLevel=value;} } privatestringdisplayFieldName; ///<summary>///显示字段名称 ///</summary>publicstringDisplayFieldName { get{returndisplayFieldName;} set{displayFieldName=value;} } privatestringkeyFieldName; ///<summary>///键值字段名称 ///</summary>publicstringKeyFieldName { get{returnkeyFieldName;} set{keyFieldName=value;} } privatestringdisplayFieldValue; ///<summary>///显示字段值 ///</summary>publicstringDisplayFieldValue { get{returndisplayFieldValue;} set{displayFieldValue=value;} } privatestringkeyFieldValue; ///<summary>///键值字段值 ///</summary>publicstringKeyFieldValue { get{returnkeyFieldValue;} set{keyFieldValue=value;} } privatestringlevelFieldName; ///<summary>///层次字段名称 ///</summary>publicstringLevelFieldName { get{returnlevelFieldName;} set{levelFieldName=value;} } privatestringlevelFieldValue; ///<summary>///层次字段值 ///</summary>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] GO SETQUOTED_IDENTIFIERON GO SETANSI_NULLSON GO CREATEPROCEDURE[dbo].[sp_gettreedata] (@table_namenvarchar(50),@idnvarchar(50),@namenvarchar(50),@parent_idnvarchar(50),@startIdnvarchar(50),@maxlevelint) AS declare@v_idnvarchar(50) declare@v_levelint declare@sqlnvarchar(500) declare@v_maxlevelint begin createtable#temp(idnvarchar(50),namenvarchar(50),parent_idnvarchar(50)) createtable#t1(idnvarchar(50),namenvarchar(50),parent_idnvarchar(50),levelint) deletefromcom_temp set@sql='insertinto#tempselect'+@id+','+@name+','+@parent_id+'from'+@table_name execsp_executesql@sql set@v_level=1set@v_id=@startId set@v_maxlevel=@maxlevel insert#t1selecta.id,a.name,a.parent_id,@v_levelfrom#tempawherea.id=@v_id while@@rowcount>0 begin set@v_level=@v_level+1 insert#t1selecta.id,a.name,a.parent_id,@v_level from#tempawherea.parent_idin (selectidfrom#t1wherelevel=@v_level-1) end insertintoCom_TEMPselecta.level,a.id,a.name,a.parent_id,b.nameparent_name from#t1aleftouterjoin#tempb ona.parent_id=b.id wherea.level<=@maxlevel orderbya.level select*fromCom_TEMPorderbydbo.f_getidpath(id) end GO SETQUOTED_IDENTIFIEROFF GO SETANSI_NULLSON GO
//sqlserver2000函数
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[f_getidpath]')andxtypein(N'FN',N'IF',N'TF')) dropfunction[dbo].[f_getidpath] GO SETQUOTED_IDENTIFIEROFF GO SETANSI_NULLSON GO CREATEfunctionf_getidpath(@idchar(36)) returnsvarchar(8000) as begin declare@revarchar(8000),@pidchar(36) set@re=@id select@pid=parent_idfromcom_tempwhereid=@id while@@rowcount>0 select@re=@pid+'.'+@re,@pid=parent_idfromcom_tempwhereid=@pid return(@re) end GO SETQUOTED_IDENTIFIEROFF GO SETANSI_NULLSON GO
//数据访问层方法
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; } List<ComLevelDataEntity>results=newList<ComLevelDataEntity>(); 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();
关于以C#为前端，SQL Server 2000层次数据查询的实现方法就介绍到这里了，希望本次的介绍能够对您有所收获！
【编辑推荐】
- 适合初学者的MySQL学习笔记之管理员常用操作总结适合初学者的MySQL学习笔记之SELECT语句使用详解MySQL数据库存储引擎之MyISAM和InnoDB的区别对比适合初学者的MySQL学习笔记之ORDER BY子句使用详解适合初学者的MySQL学习笔记之MySQL常用命令操作技巧