数据库结构:
表内的数据:
自定义函数: 递归查出 树下所有节点 ,参数是 父id
create function sss(@id as int)returns @t table(id int not null,name int not null,pid int null)asbegindeclare @lay as int;insert into @t select * from tree where pid =@id;select @lay = min(id) from tree where pid =@id; --第一次 @lay=5while @lay is not nullbegininsert into @t select * from sss(@lay);select @lay=min(id) from treewhere id>@lay and pid=@idendreturn;endgo
.net代码:
string cons = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();using (SqlConnection con=new SqlConnection(cons)){if (con.State==ConnectionState.Closed){con.Open();}string sql = "select * from sss(@id)";SqlCommand cmd = new SqlCommand(sql,con);mandType = CommandType.Text;cmd.Parameters.Add(new SqlParameter("@id", DbType.Int32)).Value = 4;cmd.Parameters.Add("@re",DbType.String);cmd.Parameters["@re"].Direction = ParameterDirection.ReturnValue;SqlDataReader dr = cmd.ExecuteReader();while (dr.Read()){int i = 0;Response.Write(dr[0].ToString() + "\t\t\t" +dr[1].ToString() +"\t\t\t"+ dr[2].ToString() + "</br>");i++;}con.Close();
}
实现的效果如下:
