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

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(); } } }}