复制数据

public void CopyData(string Goyear, string Toyear, string mangdep, ref string strError) { DataTable tepdt = SqlHelper.ExecuteDataset(conn, CommandType.Text, "select Theyear from tb_b_defResInfo " + mangdep + " and Theyear=" + Toyear).Tables[0]; if (tepdt.Rows.Count > 0) { strError = "已有 " + Toyear + " 年数据"; return; } //table[0]:基本信息 table[1]:项目明细 DataSet tepds = SqlHelper.ExecuteDataset(conn, CommandType.Text, "select * from tb_b_defResInfo " + mangdep + " and Theyear=" + Goyear + " ;select * from tb_o_defResInfo where Theyear=" + Goyear); //基本信息 DataTable tepdt_b_defResInfo = new DataTable(); tepdt_b_defResInfo.Columns.Add("Theyear"); tepdt_b_defResInfo.Columns.Add("Dept"); tepdt_b_defResInfo.Columns.Add("Person"); tepdt_b_defResInfo.Columns.Add("Tel"); tepdt_b_defResInfo.Columns.Add("Address"); tepdt_b_defResInfo.Columns.Add("Remark"); tepdt_b_defResInfo.Columns.Add("MangDep"); tepdt_b_defResInfo.Columns.Add("Deptid"); tepdt_b_defResInfo.Columns.Add("XTFW"); //项目明细 DataTable tepdt_o_defResInfo = new DataTable(); tepdt_o_defResInfo.Columns.Add("Serial"); tepdt_o_defResInfo.Columns.Add("Theyear"); tepdt_o_defResInfo.Columns.Add("Deptid"); tepdt_o_defResInfo.Columns.Add("units"); tepdt_o_defResInfo.Columns.Add("quantity"); for (int i = 0; i < tepds.Tables[0].Rows.Count; i++) { DataRow tepdr = tepdt_b_defResInfo.NewRow(); tepdr["Theyear"] = Toyear; tepdr["Dept"] = tepds.Tables[0].Rows[i]["Dept"].ToString(); tepdr["Person"] = tepds.Tables[0].Rows[i]["Person"].ToString(); tepdr["Tel"] = tepds.Tables[0].Rows[i]["Tel"].ToString(); tepdr["Address"] = tepds.Tables[0].Rows[i]["Address"].ToString(); tepdr["Remark"] = tepds.Tables[0].Rows[i]["Remark"].ToString(); tepdr["MangDep"] = tepds.Tables[0].Rows[i]["MangDep"].ToString(); tepdr["Deptid"] = tepds.Tables[0].Rows[i]["Deptid"].ToString(); tepdr["XTFW"] = tepds.Tables[0].Rows[i]["XTFW"].ToString(); tepdt_b_defResInfo.Rows.Add(tepdr); tepds.Tables[1].DefaultView.RowFilter = "Theyear=" + tepds.Tables[0].Rows[i]["Theyear"].ToString() + " and Deptid='" + tepds.Tables[0].Rows[i]["Deptid"].ToString() + "'"; DataView tepdv=tepds.Tables[1].DefaultView; for (int j = 0; j < tepdv.Count; j++) { tepdr = tepdt_o_defResInfo.NewRow(); tepdr["Serial"] = tepdv[j]["Serial"].ToString(); tepdr["Theyear"] = Toyear; tepdr["Deptid"] = tepdv[j]["Deptid"].ToString(); tepdr["units"] = tepdv[j]["units"].ToString(); tepdr["quantity"] = tepdv[j]["quantity"].ToString(); tepdt_o_defResInfo.Rows.Add(tepdr); } } SqlConnection sqlconn = new SqlConnection(conn); sqlconn.Open(); SqlTransaction sqlst = sqlconn.BeginTransaction(); try { for (int i = 0; i < tepdt_b_defResInfo.Rows.Count; i++) { SqlParameter[] bpr = new SqlParameter[9]; bpr[0] = new SqlParameter("@Theyear", tepdt_b_defResInfo.Rows[i]["Theyear"].ToString()); bpr[1] = new SqlParameter("@Dept", tepdt_b_defResInfo.Rows[i]["Dept"].ToString()); bpr[2] = new SqlParameter("@Person", tepdt_b_defResInfo.Rows[i]["Person"].ToString()); bpr[3] = new SqlParameter("@Tel", tepdt_b_defResInfo.Rows[i]["Tel"].ToString()); bpr[4] = new SqlParameter("@Address", tepdt_b_defResInfo.Rows[i]["Address"].ToString()); bpr[5] = new SqlParameter("@Remark", tepdt_b_defResInfo.Rows[i]["Remark"].ToString()); bpr[6] = new SqlParameter("@MangDep", tepdt_b_defResInfo.Rows[i]["MangDep"].ToString()); bpr[7] = new SqlParameter("@Deptid", tepdt_b_defResInfo.Rows[i]["Deptid"].ToString()); bpr[8] = new SqlParameter("@XTFW", tepdt_b_defResInfo.Rows[i]["XTFW"].ToString()); SqlHelper.ExecuteNonQuery(sqlst, CommandType.Text, "INSERT INTO tb_b_defResInfo (Theyear, Dept, Person, Tel, Address,Remark,MangDep,Deptid,XTFW) VALUES (@Theyear, @Dept, @Person, @Tel, @Address,@Remark,@MangDep,@Deptid,@XTFW)", bpr); } for (int i = 0; i < tepdt_o_defResInfo.Rows.Count; i++) { SqlParameter[] opr = new SqlParameter[5]; opr[0] = new SqlParameter("@Serial", tepdt_o_defResInfo.Rows[i]["Serial"].ToString()); opr[1] = new SqlParameter("@Theyear", tepdt_o_defResInfo.Rows[i]["Theyear"].ToString()); opr[2] = new SqlParameter("@Deptid", tepdt_o_defResInfo.Rows[i]["Deptid"].ToString()); opr[3] = new SqlParameter("@units", tepdt_o_defResInfo.Rows[i]["units"].ToString()); opr[4] = new SqlParameter("@quantity", tepdt_o_defResInfo.Rows[i]["quantity"].ToString()); SqlHelper.ExecuteNonQuery(sqlst, CommandType.Text, "INSERT INTO tb_o_defResInfo (Serial, Theyear, Deptid, units, quantity) VALUES (@Serial,@Theyear,@Deptid,@units,@quantity)", opr); } sqlst.Commit(); } catch (Exception ex) { sqlst.Rollback(); strError = "数据保存超时"; Console.Write(ex.ToString()); } finally { sqlconn.Close(); } }