从数据库某表转换并导入数据到另一表
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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();
}
}
}
}
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();
}
}
}
}