以C#為前端，SQL Server 2000層次數據查詢是如何實現的呢？本文我們主要就介紹這部分內容，接下來就讓我們一起來了解一下這一過程吧。
//層次數據實體
public class ComLevelDataEntity
{
public ComLevelDataEntity()
{
searchLevel = 0;
startWithRoot = true;
dataObjectAlias = "t";
}
private int searchLevel;
/// <summary>
/// 查詢層次
/// </summary>
public int SearchLevel
{
get { return searchLevel; }
set { searchLevel = value; }
}
private string dataObjectAlias;
/// <summary>
/// 別名
/// </summary>
public string DataObjectAlias
{
get { return dataObjectAlias; }
set { dataObjectAlias = value; }
}
private string protasis;
/// <summary>
/// 條件從句
/// </summary>
public string Protasis
{
get
{
return string.IsNullOrEmpty(protasis) ? string.Empty : string.Concat(" and ", protasis);
}
set { protasis = value; }
}
private bool startWithRoot;
/// <summary>
/// 整樹搜索
/// </summary>
public bool StartWithRoot
{
get { return startWithRoot; }
set { startWithRoot = value; }
}
private string dataObjectName;
/// <summary>
/// 數據對象名稱
/// </summary>
public string DataObjectName
{
get { return dataObjectName; }
set { dataObjectName = value; }
}
private int cascadeLevel;
/// <summary>
/// 層級
/// </summary>
public int CascadeLevel
{
get { return cascadeLevel; }
set { cascadeLevel = value; }
}
private string displayFieldName;
/// <summary>
/// 顯示字段名稱
/// </summary>
public string DisplayFieldName
{
get { return displayFieldName; }
set { displayFieldName = value; }
}
private string keyFieldName;
/// <summary>
/// 鍵值字段名稱
/// </summary>
public string KeyFieldName
{
get { return keyFieldName; }
set { keyFieldName = value; }
}
private string displayFieldValue;
/// <summary>
/// 顯示字段值
/// </summary>
public string DisplayFieldValue
{
get { return displayFieldValue; }
set { displayFieldValue = value; }
}
private string keyFieldValue;
/// <summary>
/// 鍵值字段值
/// </summary>
public string KeyFieldValue
{
get { return keyFieldValue; }
set { keyFieldValue = value; }
}
private string levelFieldName;
/// <summary>
/// 層次字段名稱
/// </summary>
public string LevelFieldName
{
get { return levelFieldName; }
set { levelFieldName = value; }
}
private string levelFieldValue;
/// <summary>
/// 層次字段值
/// </summary>
public string LevelFieldValue
{
get { return levelFieldValue; }
set { levelFieldValue = value; }
}
}
//sqlserver2000存儲過程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_gettreedata]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_gettreedata]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_gettreedata]
(@table_name nvarchar(50), @id nvarchar(50), @name nvarchar(50), @parent_id nvarchar(50), @startId nvarchar(50), @maxlevel int)
AS
declare @v_id nvarchar(50)
declare @v_level int
declare @sql nvarchar(500)
declare @v_maxlevel int
begin
create table #temp (id nvarchar(50),name nvarchar(50),parent_id nvarchar(50))
create table #t1 (id nvarchar(50),name nvarchar(50),parent_id nvarchar(50),level int)
delete from com_temp
set @sql='insert into #temp select '+@id+','+@name+','+@parent_id +' from '+@table_name
exec sp_executesql @sql
set @v_level=1
set @v_id=@startId
set @v_maxlevel = @maxlevel
insert #t1 select a.id,a.name,a.parent_id,@v_level from #temp a where a.id=@v_id
while @@rowcount>0
begin
set @v_level=@v_level+1
insert #t1 select a.id,a.name,a.parent_id,@v_level
from #temp a where a.parent_id in
(select id from #t1 where level=@v_level-1)
end
insert into Com_TEMP select a.level,a.id,a.name,a.parent_id,b.name parent_name
from #t1 a left outer join #temp b
on a.parent_id = b.id
where a.level <= @maxlevel
order by a.level
select * from Com_TEMP order by dbo.f_getidpath(id)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//sqlserver2000函數
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getidpath]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getidpath]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE function f_getidpath(@id char(36))
returns varchar(8000)
as
begin
declare @re varchar(8000),@pid char(36)
set @re=@id
select @pid=parent_id from com_temp where id=@id
while @@rowcount> 0
select @re=@pid + '.'+ @re,@pid=parent_id from com_temp where id=@pid
return(@re)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//數據訪問層方法
public ComLevelDataEntity[] PrepareHierarchyData(ComLevelDataEntity entity)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[6];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@table_name";
parameters[0].Size = 50;
parameters[0].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[1] = new SqlParameter();
parameters[1].ParameterName = "@id";
parameters[1].Size = 50;
parameters[1].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[2] = new SqlParameter();
parameters[2].ParameterName = "@name";
parameters[2].Size = 50;
parameters[2].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[3] = new SqlParameter();
parameters[3].ParameterName = "@parent_id";
parameters[3].Size = 50;
parameters[3].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[4] = new SqlParameter();
parameters[4].ParameterName = "@startId";
parameters[4].Size = 50;
parameters[4].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[5] = new SqlParameter();
parameters[5].ParameterName = "@maxlevel";
parameters[5].SqlDbType = System.Data.SqlDbType.Int;
string spName = "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 = new List<ComLevelDataEntity>();
IDbConnection connection = IDALProvider.IDAL.PopConnection();
IDataReader sqlReader = IDALProvider.IDAL.ExecuteReader(connection,spName, parameters);
while (sqlReader.Read())
{
ComLevelDataEntity result = new ComLevelDataEntity();
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);
return results.ToArray();
}
//前端調用代碼
ComLevelDataEntity entity = new ComLevelDataEntity();
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層次數據查詢的實現方法就介紹到這里了，希望本次的介紹能夠對您有所收獲！
//層次數據實體
public class ComLevelDataEntity
{
public ComLevelDataEntity()
{
searchLevel = 0;
startWithRoot = true;
dataObjectAlias = "t";
}
private int searchLevel;
/// <summary>
/// 查詢層次
/// </summary>
public int SearchLevel
{
get { return searchLevel; }
set { searchLevel = value; }
}
private string dataObjectAlias;
/// <summary>
/// 別名
/// </summary>
public string DataObjectAlias
{
get { return dataObjectAlias; }
set { dataObjectAlias = value; }
}
private string protasis;
/// <summary>
/// 條件從句
/// </summary>
public string Protasis
{
get
{
return string.IsNullOrEmpty(protasis) ? string.Empty : string.Concat(" and ", protasis);
}
set { protasis = value; }
}
private bool startWithRoot;
/// <summary>
/// 整樹搜索
/// </summary>
public bool StartWithRoot
{
get { return startWithRoot; }
set { startWithRoot = value; }
}
private string dataObjectName;
/// <summary>
/// 數據對象名稱
/// </summary>
public string DataObjectName
{
get { return dataObjectName; }
set { dataObjectName = value; }
}
private int cascadeLevel;
/// <summary>
/// 層級
/// </summary>
public int CascadeLevel
{
get { return cascadeLevel; }
set { cascadeLevel = value; }
}
private string displayFieldName;
/// <summary>
/// 顯示字段名稱
/// </summary>
public string DisplayFieldName
{
get { return displayFieldName; }
set { displayFieldName = value; }
}
private string keyFieldName;
/// <summary>
/// 鍵值字段名稱
/// </summary>
public string KeyFieldName
{
get { return keyFieldName; }
set { keyFieldName = value; }
}
private string displayFieldValue;
/// <summary>
/// 顯示字段值
/// </summary>
public string DisplayFieldValue
{
get { return displayFieldValue; }
set { displayFieldValue = value; }
}
private string keyFieldValue;
/// <summary>
/// 鍵值字段值
/// </summary>
public string KeyFieldValue
{
get { return keyFieldValue; }
set { keyFieldValue = value; }
}
private string levelFieldName;
/// <summary>
/// 層次字段名稱
/// </summary>
public string LevelFieldName
{
get { return levelFieldName; }
set { levelFieldName = value; }
}
private string levelFieldValue;
/// <summary>
/// 層次字段值
/// </summary>
public string LevelFieldValue
{
get { return levelFieldValue; }
set { levelFieldValue = value; }
}
}
//sqlserver2000存儲過程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_gettreedata]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_gettreedata]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[sp_gettreedata]
(@table_name nvarchar(50), @id nvarchar(50), @name nvarchar(50), @parent_id nvarchar(50), @startId nvarchar(50), @maxlevel int)
AS
declare @v_id nvarchar(50)
declare @v_level int
declare @sql nvarchar(500)
declare @v_maxlevel int
begin
create table #temp (id nvarchar(50),name nvarchar(50),parent_id nvarchar(50))
create table #t1 (id nvarchar(50),name nvarchar(50),parent_id nvarchar(50),level int)
delete from com_temp
set @sql='insert into #temp select '+@id+','+@name+','+@parent_id +' from '+@table_name
exec sp_executesql @sql
set @v_level=1
set @v_id=@startId
set @v_maxlevel = @maxlevel
insert #t1 select a.id,a.name,a.parent_id,@v_level from #temp a where a.id=@v_id
while @@rowcount>0
begin
set @v_level=@v_level+1
insert #t1 select a.id,a.name,a.parent_id,@v_level
from #temp a where a.parent_id in
(select id from #t1 where level=@v_level-1)
end
insert into Com_TEMP select a.level,a.id,a.name,a.parent_id,b.name parent_name
from #t1 a left outer join #temp b
on a.parent_id = b.id
where a.level <= @maxlevel
order by a.level
select * from Com_TEMP order by dbo.f_getidpath(id)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//sqlserver2000函數
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getidpath]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_getidpath]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE function f_getidpath(@id char(36))
returns varchar(8000)
as
begin
declare @re varchar(8000),@pid char(36)
set @re=@id
select @pid=parent_id from com_temp where id=@id
while @@rowcount> 0
select @re=@pid + '.'+ @re,@pid=parent_id from com_temp where id=@pid
return(@re)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
//數據訪問層方法
public ComLevelDataEntity[] PrepareHierarchyData(ComLevelDataEntity entity)
{
System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[6];
parameters[0] = new SqlParameter();
parameters[0].ParameterName = "@table_name";
parameters[0].Size = 50;
parameters[0].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[1] = new SqlParameter();
parameters[1].ParameterName = "@id";
parameters[1].Size = 50;
parameters[1].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[2] = new SqlParameter();
parameters[2].ParameterName = "@name";
parameters[2].Size = 50;
parameters[2].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[3] = new SqlParameter();
parameters[3].ParameterName = "@parent_id";
parameters[3].Size = 50;
parameters[3].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[4] = new SqlParameter();
parameters[4].ParameterName = "@startId";
parameters[4].Size = 50;
parameters[4].SqlDbType = System.Data.SqlDbType.VarChar;
parameters[5] = new SqlParameter();
parameters[5].ParameterName = "@maxlevel";
parameters[5].SqlDbType = System.Data.SqlDbType.Int;
string spName = "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 = new List<ComLevelDataEntity>();
IDbConnection connection = IDALProvider.IDAL.PopConnection();
IDataReader sqlReader = IDALProvider.IDAL.ExecuteReader(connection,spName, parameters);
while (sqlReader.Read())
{
ComLevelDataEntity result = new ComLevelDataEntity();
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);
return results.ToArray();
}
//前端調用代碼
ComLevelDataEntity entity = new ComLevelDataEntity();
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層次數據查詢的實現方法就介紹到這里了，希望本次的介紹能夠對您有所收獲！
快消品貨源網提供超市貨源信息,超市采購進貨渠道。超市進貨網提供成都食品批發,日用百貨批發信息、微信淘寶網店超市采購信息和超市加盟信息.打造國內超市采購商與批發市場供應廠商搭建網上批發市場平臺,是全國批發市場行業中電子商務權威性網站。
