Use C # To obtain an infinite multi-level classification

Multi-level classification is required for small projects. Because the stored procedure itself is not very familiar with and does not want to use Treeview, the recursive logic is implemented in C #, and the multi-level classification acquisition method is completed in combination with the data database. Adding a category node should be simple. This article is omitted.

Database Table: categoryinfo

Field name Type

Ciid int // record serial number, auto Increment

Ciname nvarchar (20) // category name

Ciparent int // parent category No.

Cilayer int // hierarchy

Cidescription nvarchar (200) // description of the category

Obtain the sub-category Stored Procedure

Create procedure [DBO]. [category_getchild]
@ Cname nvarchar (20)
As
Begin
Declare @ tmpid int
Select @ tmpid = ciid from categoryinfo
Where rtrim (ciname) = rtrim (@ cname)
If (@ tmpid is not null)
Select * From categoryinfo
Where ciparent = @ tmpid
Order by cilayer
End

Function for obtaining subcategories

Public ilist <categoryinfo> getchildcategories (ilist <categoryinfo> cinfos, string cname)
{
Sqlconnection con = new sqlconnection (connectionstring );
Sqlcommand cmd = new sqlcommand ("category_getchild", con );
Cmd. commandtype = commandtype. storedprocedure;
Cmd. Parameters. Add (New sqlparameter (param_cname, sqldbtype. nvarchar, 20 ));
Cmd. Parameters [param_cname]. value = cname;

Ilist <string> tmpnames = new list <string> (); // the child obtained by temporary storage
Try
{
Con. open ();
Sqldatareader reader = cmd. executereader ();
If (reader. hasrows)
{
While (reader. Read ())
{

Categoryinfo Cinfo = new categoryinfo (
(INT) Reader ["ciid"],
Reader ["ciname"]. tostring (),
(INT) Reader ["ciparent"],
(INT) Reader ["cinum"],
Reader ["cidescription"]. tostring (),
(INT) Reader ["cilayer"]
);
String tmpname = reader ["ciname"]. tostring ();
Cinfos. Add (Cinfo); // Add the obtained category to cinfos
Tmpnames. Add (tmpname); // Add the obtained sub-category name to tmpnames
}
}
}
Catch
{
Throw new applicationexception ("An error occurred while obtaining the category! ");
}
Finally
{
Con. Close ();
}
Foreach (string C in tmpnames)
{
Cinfos = getchildcategories (cinfos, c); // recursive operation. Continue to obtain sub-category
}

Return cinfos;
}

Note: In this function, if tmpnames is replaced by ilist <categoryinfo>, that is, if one of the elements added by tmpnames is the same as cinfos, cinfos removes one item at the same time. Because categoryinfo is a class, it is a reference type, not a value type. Therefore, tmpnames adopts the string type to avoid this problem.

It is a little difficult to directly call the above function. The upper layerProgramYou also need to create an ilist <categoryinfo> object, so you can add a function to call the above function. In this way, the upper-layer program only needs to provide the classification name and whether or not it contains the two parameters of the classification.

// Obtain the subcategory. The Boolean parameter indicates whether the subcategory is included.
Public ilist <categoryinfo> getcategories (string cname, bool isincludeself)
{
Ilist <categoryinfo> cinfos = new list <categoryinfo> ();
Cinfos = getchildcategories (cinfos, cname );
If (isw.deself = true)
{
Cinfos. insert (0, getbyname (cname); // obtain the category by name. This is very simple.
}
Return cinfos;
}

Note: When this method is used, the web server must have multiple round-trips between database servers when obtaining sub-categories, reducing the performance. Recursive logic is implemented using stored procedures, and direct return to the subcategory list should provide better performance, especially when the web server and database server are not on the same server, they will be more affected by the network.