Convert and import data from a database table to another table __ database

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 S ource=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 number, parent number, name from Project Master Table WHERE (parent number like ' 01% ') Order by parent number"; static string Scommand = "Select Id,no,parentno,name from Temp-ID"; This is your the data adapter that understands SQL Databases:static SqlDataAdapter da = new SqlDataAdapter (Scommand, CN); This is your table to hold the result set:static the DataTable datatable = New DataTable (); static Dataitemcollection DIC = new Dataitemcollection (); Insert Food database 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 ())//If the number is all numbers and decimal point {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 ())//If the number is all numbers and decimal point {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 ())//If the number is all numbers and decimal point {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 = = "") {continue;} else {di. Parentno = Findparentno (di. PARENTNO); (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 to "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 (); The 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 ()); CII No = dr[1]. ToString (); CII Parentno = dr[2]. ToString (); CII Name = dr[3]. ToString (); Cmd.commandtext = "INSERT standardclass (standardclassid,parent, name) VALUES (" + Di.id + "," + Int.) Parse (DI. Parentno) + ", '" + common.preventingsqlinjectionattacks (DI. Name) + "')"; Cmd. ExecuteNonQuery (); (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 ();}} }}