从数据库某表转换并导入数据到另一表

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

using System.Collections.Generic;
using System.Text.RegularExpressions;

public partial class IMPORTDB : System.Web.UI.Page
{
    // static string connectionString = "Initial Catalog=kingslims;Data Source=192.168.2.232;User ID=sa;PassWord=;";
    static string connectionString = "";//"Initial Catalog=food;Data Source=192.168.2.232;User ID=sa;PassWord=;";
    static SqlConnection cn = new SqlConnection(connectionString);
    // static string sCommand = "SELECT 编号,父级编号,名称 FROM 项目总表 WHERE (父级编号 LIKE '01%') ORDER BY 父级编号";
    static string sCommand = "SELECT ID,no,parentno,name FROM temp  ORDER BY ID";

    // This is your data adapter that understands SQL databases:
    static SqlDataAdapter da = new SqlDataAdapter(sCommand, cn);

    // This is your table to hold the result set:
    static DataTable dataTable = new DataTable();
    static DataItemCollection DIC = new DataItemCollection();




    //插入FOOD数据库

    static System.Data.SqlClient.SqlConnection sqlConnection1 = new System.Data.SqlClient.SqlConnection("");//"Initial Catalog=Food;Data Source=192.168.2.232;User ID=sa;PassWord=;");

    static System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();


    protected void Page_Load(object sender, EventArgs e)
    {
        Response.Write(Common.PreventingSQLInjectionAttacks("ad'asdf"));
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        try
        {
            cn.Open();

            // Fill the data table with select statement's query results:
            int recordsAffected = da.Fill(dataTable);
            int IDCount = 1;
            Regex r1 = new Regex(@"^[0-9]*(\.[0-9]*)*[0-9]*$");
            Regex r2 = new Regex(@"^[0-9]{2}$");
            Regex r3 = new Regex(@"^[0-9]{2}\.[0-9]{2}$");
            Regex r4 = new Regex(@"^[0-9]{2}\.[0-9]{2}\.[0-9]{2}$");
            cn.Close();

            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();




            if (recordsAffected > 0)
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    // Response.Write(dr[0].ToString()+"  "+dr[1].ToString()+"  "+dr[2].ToString()+"<br />");

                    DataItem DI = new DataItem();
                    if (r1.IsMatch(dr[0].ToString()) && r2.IsMatch(dr[1].ToString()))//如果编号全为数字和小数点
                    {
                        DI.No = dr[0].ToString();
                        DI.ParentNo = dr[1].ToString();
                        DI.Name = dr[2].ToString();
                        DI.ID = IDCount;


                        cmd.CommandText = "INSERT temp (id,no, parentno,name) VALUES (" + DI.ID + ", '" + DI.No.ToString() + "','" + DI.ParentNo + "','" + Common.PreventingSQLInjectionAttacks(DI.Name) + "')";
                        cmd.ExecuteNonQuery();

                        IDCount++;
                    }

                }



                foreach (DataRow dr in dataTable.Rows)
                {
                    // Response.Write(dr[0].ToString()+"  "+dr[1].ToString()+"  "+dr[2].ToString()+"<br />");

                    DataItem DI = new DataItem();
                    if (r1.IsMatch(dr[0].ToString()) && r3.IsMatch(dr[1].ToString()))//如果编号全为数字和小数点
                    {
                        DI.No = dr[0].ToString();
                        DI.ParentNo = dr[1].ToString();
                        DI.Name = dr[2].ToString();
                        DI.ID = IDCount;


                        cmd.CommandText = "INSERT temp (id,no, parentno,name) VALUES (" + DI.ID + ", '" + DI.No.ToString() + "','" + DI.ParentNo + "','" + DI.Name + "')";
                        cmd.ExecuteNonQuery();

                        IDCount++;
                    }

                }

                foreach (DataRow dr in dataTable.Rows)
                {
                    // Response.Write(dr[0].ToString()+"  "+dr[1].ToString()+"  "+dr[2].ToString()+"<br />");

                    DataItem DI = new DataItem();
                    if (r1.IsMatch(dr[0].ToString()) && r4.IsMatch(dr[1].ToString()))//如果编号全为数字和小数点
                    {
                        DI.No = dr[0].ToString();
                        DI.ParentNo = dr[1].ToString();
                        DI.Name = dr[2].ToString();
                        DI.ID = IDCount;


                        cmd.CommandText = "INSERT temp (id,no, parentno,name) VALUES (" + DI.ID + ", '" + DI.No.ToString() + "','" + DI.ParentNo + "','" + DI.Name + "')";
                        cmd.ExecuteNonQuery();

                        IDCount++;
                    }

                }
            }


        }
        catch (SqlException ex)
        {
            string msg = "";
            for (int i = 0; i < ex.Errors.Count; i++)
            {
                msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
            }
            Response.Write(msg);
        }
        finally
        {
            if (cn.State != ConnectionState.Closed)
            {
                cn.Close();
            }
            if (sqlConnection1.State != ConnectionState.Closed)
            {
                sqlConnection1.Close();
            }
        }

    }

    public class DataItem
    {
        public int ID
        {
            get;
            set;
        }
        public string No
        {
            get;
            set;

        }
        public string ParentNo
        {
            get;

            set;
        }
        public string Name
        {
            get;
            set;

        }
    }
    public class DataItemCollection : List<DataItem>
    {

    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        try
        {
            cn.Open();

            // Fill the data table with select statement's query results:
            int recordsAffected = da.Fill(dataTable);
            int IDCount = 1;

            cn.Close();

            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();




            if (recordsAffected > 0)
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    // Response.Write(dr[0].ToString()+"  "+dr[1].ToString()+"  "+dr[2].ToString()+"<br />");

                    DataItem DI = new DataItem();
                    DI.ID = int.Parse(dr[0].ToString());
                    DI.No = dr[1].ToString();
                    DI.ParentNo = dr[2].ToString();
                    DI.Name = dr[3].ToString();



                    //cmd.CommandText = "INSERT temp (id,no, parentno,name) VALUES (" + DI.ID + ", '" + DI.No.ToString() + "','" + DI.ParentNo + "','" + Common.PreventingSQLInjectionAttacks(DI.Name) + "')";
                    //cmd.ExecuteNonQuery();

                    DIC.Add(DI);


                }



            }

            foreach (DataItem di in DIC)
            {
                if (di.ParentNo == "01")
                {
                    continue;
                }
                else
                {
                    di.ParentNo = FindParentNo(di.ParentNo);

                }
            }




        }
        catch (SqlException ex)
        {
            string msg = "";
            for (int i = 0; i < ex.Errors.Count; i++)
            {
                msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
            }
            Response.Write(msg);
        }
        finally
        {
            if (cn.State != ConnectionState.Closed)
            {
                cn.Close();
            }
            if (sqlConnection1.State != ConnectionState.Closed)
            {
                sqlConnection1.Close();
            }
        }
    }
    public string FindParentNo(string orgParentNo)
    {
        foreach (DataItem dii in DIC)
        {
            if (dii.No == orgParentNo)
                return dii.ID.ToString();
        }
        return "not found";
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        try
        {
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();
            foreach (DataItem DI in DIC)
            {
                cmd.CommandText = "INSERT temp (id,no, parentno,name) VALUES (" + DI.ID + ", '" + DI.No.ToString() + "','" + DI.ParentNo + "','" + Common.PreventingSQLInjectionAttacks(DI.Name) + "')";
                cmd.ExecuteNonQuery();
            }
        }
        catch (SqlException ex)
        {
            string msg = "";
            for (int i = 0; i < ex.Errors.Count; i++)
            {
                msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
            }
            Response.Write(msg);
        }
        finally
        {
            if (cn.State != ConnectionState.Closed)
            {
                cn.Close();
            }
            if (sqlConnection1.State != ConnectionState.Closed)
            {
                sqlConnection1.Close();
            }
        }

    }
    protected void Button4_Click(object sender, EventArgs e)
    {
        try
        {
            cn.Open();

            // Fill the data table with select statement's query results:
            int recordsAffected = da.Fill(dataTable);
            int IDCount = 1;

            cn.Close();

            cmd.CommandType = System.Data.CommandType.Text;

            cmd.Connection = sqlConnection1;

            sqlConnection1.Open();




            if (recordsAffected > 0)
            {
                foreach (DataRow dr in dataTable.Rows)
                {
                    // Response.Write(dr[0].ToString()+"  "+dr[1].ToString()+"  "+dr[2].ToString()+"<br />");

                    DataItem DI = new DataItem();
                    DI.ID = int.Parse(dr[0].ToString());
                    DI.No = dr[1].ToString();
                    DI.ParentNo = dr[2].ToString();
                    DI.Name = dr[3].ToString();



                    cmd.CommandText = "INSERT standardclass (Standardclassid,parent, name) VALUES (" + DI.ID + "," + int.Parse(DI.ParentNo) + ",'" + Common.PreventingSQLInjectionAttacks(DI.Name) + "')";
                    cmd.ExecuteNonQuery();




                }



            }





        }
        catch (SqlException ex)
        {
            string msg = "";
            for (int i = 0; i < ex.Errors.Count; i++)
            {
                msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
            }
            Response.Write(msg);
        }
        finally
        {
            if (cn.State != ConnectionState.Closed)
            {
                cn.Close();
            }
            if (sqlConnection1.State != ConnectionState.Closed)
            {
                sqlConnection1.Close();
            }
        }
    }
}