Recursive removal of a whole tree my own thoughts

First, the structure of tree-type database and some basic knowledge of tree

First, let's look at a simple application tree ... database design is as follows:

Table Name: Testtree

Field:
ID (the primary key is automatically incremented by 1) Username (this is arbitrary.) is just a data field) ParentID (the ID value of the parent node)

ID Username parentid
1 A 0
2 B 1
3 C 2
4 D 1
5 E 2
6 F 5

If you arrange this data by tree, you should have the following status:
A
|____b
| |____c
| |____e
| |____f
|
|____d

If we're going to start with a whole tree from A, we'll have to remove it from a. There are a number of algorithms: A-B "C" E "F" D or "A" B-D "C" E "F algorithm is diverse. But as it is to delete a whole tree (note is deleted) if nothing unexpected happens. The above algorithm can be satisfied. But since it is removed we are going to remove the subtree from the tree. Why, then? Because it was deleted from the scratch node. Unexpected one appears. Header node deleted ... But the child nodes are not deleted, but they are not queried. Generate too much junk data. If deleted from the child node. If in any case. Child node deletion. The parent node is not deleted. Can the parent node requery the query to the delete operation?
That's why it starts at the end of the tail.

Ii. Delete procedure (code)
in traditional thinking, we might immediately think of a recursive delete operation ... Is that what I want to talk about?
Use Vs.net to build a WINFORM project for a C # project, drag two button controls
and a Label control  
and then using system.data.sqlclient;  in a reference to the beginning Because this is the class that you use to manipulate the database?
then we're going to build a few ways to achieve our goals. That's the recursive deletion!
Variable:
static string connstring= "server=192.100.100.135;database=jay;uid=sa;pwd=;"; SQL SERVER Connection string
Method:
Sqlcommandshell (String sql): method to manipulate SQL statements
GetDataSet (String sql): Method obtains a dataset through SQL statements (It is equivalent to after disconnecting the database.) Hide in-memory database table  ? I think so.
Sqldeleteid (string id) specifies the ID data to be deleted
Deletechildid (string id) queries out the ID value of its child through this ID number

The specific implementation process of the method:
<summary>
Get an SQL statement execution
</summary>
<param name= "SQL" >sql statement </param>
<returns> returns the number of data rows affected </returns>
public static int Sqlcommandshell (String sql)
{
SqlConnection conn=new SqlConnection (connstring);
Conn. Open ();
SqlCommand cm=new SqlCommand (sql,conn);
int i=cm. ExecuteNonQuery ();
Conn. Close ();
return i;
}

<summary>
Get a DataSet
</summary>
<param name= "SQL" >sql statement </param>
<returns> returns a dataset</returns> via SQL statement
public static DataSet GetDataSet (String sql)
{
Using (SqlConnection conn=new SqlConnection (connstring))
{
SqlDataAdapter Dp=new SqlDataAdapter (Sql,conn);
DataSet ds=new DataSet ();
Dp. Fill (DS);
return DS;
}
}

The above two methods are the operation of the database according to the general idea the next method is
<summary>
The ID to delete
</summary>
<param name= "id" ></param>
void Sqldeleteid (String id)
{
String sql= "delete [testtree] where id= '" +id+ "";
Sqlcommandshell (SQL);
}

<summary>
The usual delete
</summary>
<param name= "id" > Parent id</param>
void Deletechildid (String id)
{
String Sql= "select * from [testtree] where parentid= '" +id+ "";
DataSet ds=getdataset (SQL);
for (int i=0;i<ds. Tables[0]. rows.count;i++)
{
String I_d=ds. Tables[0]. rows[i]["id"]. ToString ();
Deletechildid (i_d); (Delete a tree with a recursive rule)
Sqldeleteid (i_d); Delete process
}
}

This will enable the operation of a tree ... Double-click a button to add the following code inside
private void Button2_Click (object sender, System.EventArgs e)
{
Deletechildid ("0"); This 0 is the ID of the parent node that you are going to delete
}

This will achieve the above description. That is, the deletion of a tree from the end of the head
However, the efficiency of such implementation is not satisfactory. It's OK to operate the tree. Only a few nodes. Then operate the big tree. For example, when there are tens of thousands of nodes each time the data link. Delete This operation will take a lot of time ~ ~ ~
And this morning I began to wonder if there was a better way to operate it?

Third, some of their own ideas you can talk about it.

Declares a ArrayList al=new ArrayList () next to the same common thread
Database link string ();//Declare an array of record IDs
then add two methods as follows:
  #region Getdeletesql
  ///<summary>
  ///get the SQL statement to be deleted
  ///</summary>
   ///<param name= "id" > the start Id</param> to be deleted;
  ///<returns> returns an SQL statement </returns
  string getdeletesql (string id)
  {
   string str=string. Empty;
   al. Clear ();
   arraylist Aa=getid (ID);
   for (int i=0;i<aa. count;i++)
   {
    str=str+ "id=" +aa[i]. ToString () + "or";
   }
   str=str. Substring (0,str. LENGTH-3);
   string sql= "Delete from [Testtree] where" +STR;
   return SQL;

  }
   #endregion
 
   #region getidlist
  ///<summary>
  ///get the ID list to delete
  ///</summary>
  ///<param name= "id" > Parent id</ Param>
  ///<returns> Returns an array of ID lists to be deleted </returns>
  arraylist getId (string ID
  {
   string sql= "select * from [testtree] where parentid= '" +id+ "";
   dataset ds=getdataset (SQL);
   for (int i=0;i<ds. Tables[0]. rows.count;i++)
   {
    string i_d=ds. Tables[0]. rows[i]["id"]. ToString ();
    getid (i_d);
    al. ADD (i_d);
    
   }
   return al;

}
#endregion
This is done by example Getdeletesql ("0"); method to construct a deleted SQL statement
Don't you have two buttons in front of you? Then double-click the other button code as follows
private void Button1_Click (object sender, System.EventArgs e)
{
This.label1.text=getdeletesql ("0"); Label1. Text is the SQL statement you want to manipulate. To make it easier for me to show it.
Sqlcommandshell (This.label1.Text);
}

This generates an SQL statement and then the database is connected once ... Execute an SQL statement I don't have that much data to test. But I think that in the case of large amount of data efficiency can be much higher ... Please discuss the feasibility of this method ~

All the code is as follows:
/* ************************************************************************
* Design staff: Upshania email:uv51@sina.com
* Design time: 2005.05.31
* Functional Description: Recursive removal of the entire tree
* All rights reserved: Copyright (c), Fujian Upshania
* Version Number: 1.0
***************************************************************************/

Using System;
Using System.Drawing;
Using System.Collections;
Using System.ComponentModel;
Using System.Windows.Forms;
Using System.Data;
Using System.Data.SqlClient;

Namespace Testtree
{
<summary>
Summary description of the Form1.
</summary>
public class Form1:System.Windows.Forms.Form
{
#region Variable Properties, etc.
static string connstring= "server=192.100.100.135;database=jay;uid=sa;pwd=;"; SQL SERVER Connection string
ArrayList al=new ArrayList (); Declare an array of record IDs
Private System.Windows.Forms.Label Label1;
Private System.Windows.Forms.Button button1;
Private System.Windows.Forms.Button button2;
<summary>
The required designer variable.
</summary>
Private System.ComponentModel.Container components = null;

Public Form1 ()
{
//
Required for Windows Forms Designer support
//
InitializeComponent ();

//
TODO: Add any constructor code after the InitializeComponent call
//
}

<summary>
Clean up all resources that are in use.
</summary>
protected override void Dispose (bool disposing)
{
if (disposing)
{
if (Components!= null)
{
Components. Dispose ();
}
}
Base. Dispose (disposing);
}

Code generated #region the Windows forms Designer
<summary>
Designer supports required methods-do not use the Code editor to modify
The contents of this method.
</summary>
private void InitializeComponent ()
{
This.label1 = new System.Windows.Forms.Label ();
This.button1 = new System.Windows.Forms.Button ();
This.button2 = new System.Windows.Forms.Button ();
This. SuspendLayout ();
//
Label1
//
This.label1.Location = new System.Drawing.Point (88, 32);
This.label1.Name = "Label1";
This.label1.Size = new System.Drawing.Size (400, 48);
This.label1.TabIndex = 0;
This.label1.Click + = new System.EventHandler (This.label1_click);
//
Button1
//
This.button1.Location = new System.Drawing.Point (48, 144);
This.button1.Name = "Button1";
This.button1.Size = new System.Drawing.Size (104, 32);
This.button1.TabIndex = 1;
This.button1.Text = "Button1";
This.button1.Click + = new System.EventHandler (This.button1_click);
//
Button2
//
This.button2.Location = new System.Drawing.Point (288, 144);
This.button2.Name = "Button2";
This.button2.Size = new System.Drawing.Size (104, 40);
This.button2.TabIndex = 2;
This.button2.Text = "Button2";
This.button2.Click + = new System.EventHandler (This.button2_click);
//
Form1
//
This. AutoScaleBaseSize = new System.Drawing.Size (6, 14);
This. ClientSize = new System.Drawing.Size (528, 309);
This. Controls.Add (This.button2);
This. Controls.Add (This.button1);
This. Controls.Add (THIS.LABEL1);
This. Name = "Form1";
This. Text = "Form1";
This. ResumeLayout (FALSE);

}
#endregion

<summary>
The main entry point for the application.
</summary>
[STAThread]
static void Main ()
{
Application.Run (New Form1 ());
}
#endregion

#region method

#region method of common use

#region Sqlcommandshell
<summary>
Get an SQL statement execution
</summary>
<param name= "SQL" >sql statement </param>
<returns> returns the number of data rows affected </returns>
public static int Sqlcommandshell (String sql)
{
SqlConnection conn=new SqlConnection (connstring);
Conn. Open ();
SqlCommand cm=new SqlCommand (sql,conn);
int i=cm. ExecuteNonQuery ();
Conn. Close ();
return i;

}
#endregion

#region GetDataSet
<summary>
Get a DataSet
</summary>
<param name= "SQL" >sql statement </param>
<returns> returns a dataset</returns> via SQL statement
public static DataSet GetDataSet (String sql)
{
Using (SqlConnection conn=new SqlConnection (connstring))
{
SqlDataAdapter Dp=new SqlDataAdapter (Sql,conn);
DataSet ds=new DataSet ();
Dp. Fill (DS);
return DS;
}
}
#endregion

#endregion

#region own ideas.

   #region getdeletesql
  ///<summary>
  ///get the SQL statement to be deleted
   ///</summary>
  ///<param name= "id" > Start id</param> to delete;
  /// <returns> returns an SQL statement </returns>
  string getdeletesql (string id)
  {
   string str=string. Empty;
   al. Clear ();
   arraylist Aa=getid (ID);
   for (int i=0;i<aa. count;i++)
   {
    str=str+ "id=" +aa[i]. ToString () + "or";
   }
   str=str. Substring (0,str. LENGTH-3);
   string sql= "Delete from [Testtree] where" +STR;
   return SQL;

}
#endregion

#region Getidlist
<summary>
Get the list of IDs to delete
</summary>
<param name= "id" > Parent id</param>
<returns> returns an array of ID lists to delete </returns>
ArrayList getId (String id)
{
String Sql= "select * from [testtree] where parentid= '" +id+ "";
DataSet ds=getdataset (SQL);
for (int i=0;i<ds. Tables[0]. rows.count;i++)
{
String I_d=ds. Tables[0]. rows[i]["id"]. ToString ();
GetId (i_d);
Al. ADD (i_d);

}
Return al;

}
#endregion

#endregion

   #region Normal   ///<summary>
  ///the ID to delete
  /// </summary>
  ///<param name= "id" ></PARAM>
  void SqlDeleteID (string ID)
  {
   string sql= "delete [testtree] where id= '" +id+ "'";
   sqlcommandshell (SQL);
  }

<summary>
The usual delete
</summary>
<param name= "id" > Parent id</param>
void Deletechildid (String id)
{
String Sql= "select * from [testtree] where parentid= '" +id+ "";
DataSet ds=getdataset (SQL);
for (int i=0;i<ds. Tables[0]. rows.count;i++)
{
String I_d=ds. Tables[0]. rows[i]["id"]. ToString ();
Deletechildid (i_d);
Sqldeleteid (i_d);
}
}
#endregion

#endregion

private void Button1_Click (object sender, System.EventArgs e)
{
This.label1.text=getdeletesql ("0");
Sqlcommandshell (This.label1.Text);
}

private void Label1_click (object sender, System.EventArgs e)
{

}

private void Button2_Click (object sender, System.EventArgs e)
{
Deletechildid ("0");
}
}
}