C # Implementation of unlimited Classification

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
CATEGORY Design
Public class CategoryInfo
{
Private int ciID; // category ID
Private string ciName; // category name
Private int ciParent; // The parent category ID of the category
Private string ciDescription; // Category Description
Private int ciLayer; // category level
// Constructor
Public CategoryInfo (){}
Public CategoryInfo (int cID, string cName, int cParent, string cDescription, int cLayer)
{
This. ciID = cID;
This. ciName = cName;
This. ciParent = cParent;
This. ciDescription = cDescription;
This. ciLayer = cLayer;
}
// Attributes
Public int CategoryID
{
Get {return ciID ;}
Set {ciID = value ;}
}
Public string CategoryName
{
Get {return ciName ;}
Set {ciName = value ;}
}
Public int CategoryParent
{
Get {return ciParent ;}
Set {ciParent = value ;}
}
Public string CategoryDescription
{
Get {return ciDescription ;}
Set {ciDescription = value ;}
}
Public int CategoryLayer
{
Get {return ciLayer ;}
Set {ciLayer = value ;}
}
}
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"],
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 call the above function directly. The upper-Layer Program also needs 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.