Database Structure:
Data in the table:
Custom function: recursively identifies all nodes under the tree. The parameter is the parent id.
Create function sss (@ id as int) returns @ t table (id int not null, name int not null, pid int null) as begin declare @ lay as int; insert into @ t select * from tree where pid = @ id; select @ lay = min (id) from tree where pid = @ id; -- First @ lay = 5 while @ lay is not null begin insert into @ t select * from sss (@ lay); select @ lay = min (id) from tree where id> @ lay and pid = @ id end return; end go
. Net code:
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); cmd.CommandType = 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();
}
The implementation result is as follows: