方法1： public static bool AdjustLeave(OaLeaveInfo oaLeaveInfo, DataTable dtDetail,decimal dl_OlderYiXiuNianJia,string str_userID, string str_plantID, decimal dlDours){ArrayList al = new ArrayList();//備份原始記錄string strSql = "insert into oa_leave_origin(origin_sid, user_id, dept_sid, pst_sid, date_from," +"date_to, lc_sid, Reason, com_hour, al_day, rfs_sid, rf_sid, curr_node_no, curr_approver," +"leave_total_days,leave_total_real_days,leave_total_hours," +"create_person, create_date, update_person, update_date,adjust_person,adjust_date)" +"select sid, user_id, dept_sid, pst_sid, date_from," +"date_to, lc_sid, Reason, com_hour, al_day, rfs_sid, rf_sid, curr_node_no, curr_approver," +"leave_total_days,leave_total_real_days,leave_total_hours," +"create_person, create_date, update_person,update_date,'" + BllPubSafe.ConvertString(oaLeaveInfo.UpdatePerson) + "' adjust_person," +"getdate() as adjust_date from oa_leave (nolock) where sid='" + oaLeaveInfo.Sid + "'";al.Add(strSql);strSql = "insert into oa_leave_detail_origin(p_sid,origin_sid,origin_p_sid,no,leave_category,tick,leave_days," +"date_from,date_to,hours) " +"select (select SCOPE_IDENTITY()),sid,p_sid,no,leave_category,tick,leave_days,date_from,date_to,hours " +"from oa_leave_detail " +"where p_sid = '" + oaLeaveInfo.Sid + "'";al.Add(strSql);//原始表StringBuilder sbSql = new StringBuilder();sbSql.Append("update oa_leave set date_from = '" + oaLeaveInfo.DateFrom + "',");sbSql.Append("date_to = '" + oaLeaveInfo.DateTo + "',");sbSql.Append("reason = '" + oaLeaveInfo.Reason + "',");//sbSql.Append("lc_sid = '" + BllPubSafe.ConvertString(oaLeaveInfo.LcSid) + "',");//sbSql.Append("com_hour = '" + oaLeaveInfo.ComHour + "',");//sbSql.Append("al_day = '" + oaLeaveInfo.AlDay + "'");sbSql.Append("leave_total_days = '" + oaLeaveInfo.leaveTotalDays + "',");sbSql.Append("leave_total_real_days = '" + oaLeaveInfo.leaveTotalrealDays + "',");sbSql.Append("leave_total_hours = '" + oaLeaveInfo.leaveTotalHours + "'");sbSql.Append(" where sid = '" + oaLeaveInfo.Sid + "'");al.Add(sbSql.ToString());//從表if (dtDetail.Rows.Count > 0){sbSql = new StringBuilder();sbSql.Append("delete from oa_leave_detail where p_sid='" + oaLeaveInfo.Sid + "'").Append("delete from oa_compensative_detail where leave_sid='" + oaLeaveInfo.Sid + "'");al.Add(sbSql.ToString());}DbHelperSQL.ExecuteSqlTran(al);DataTable dtTemp = BLL.HR.BLL_HRCommon.getcommtable("select * from fn_oa_leave_left_timeAddModifyTep('" + str_plantID + "','" + BllPubSafe.ConvertString(str_userID) + "')");using (SqlConnection conn = new SqlConnection(DbHelperSQL.ConnectionString)){conn.Open();SqlCommand cmd = new SqlCommand();cmd.Connection = conn;SqlTransaction tx = conn.BeginTransaction();cmd.Transaction = tx;try{//從表for (int i = 0; i < dtDetail.Rows.Count; i++){sbSql = new StringBuilder();if (dl_OlderYiXiuNianJia.ToString().Trim().Length > 0){//dlhourdayif (dtDetail.Rows[i]["no"].ToString() == "C"){sbSql.Append(" update KQ_EmpYearVac set Vac_Been=(Vac_Been+" +Convert.ToDecimal(dtDetail.Rows[i]["leave_days"].ToString()) +" -" + dl_OlderYiXiuNianJia+ ") where Plant_Id='" + str_plantID + "' and Vac_Year='" + DateTime.Now.Year +"' and Employee_Id='" + str_userID + "' ");cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery();}else if (i == 0){sbSql.Append(" update KQ_EmpYearVac set Vac_Been=(Vac_Been+" +0 +" -" + dl_OlderYiXiuNianJia+ ") where Plant_Id='" + str_plantID + "' and Vac_Year='" + DateTime.Now.Year +"' and Employee_Id='" + str_userID + "' ");cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery();}}if (dtDetail.Rows[i]["no"].ToString() == "D"){sbSql.Append(" insert oa_leave_detail(no,leave_category,tick,leave_days,date_from,date_to,p_sid,hours)values(");sbSql.Append("'" + dtDetail.Rows[i]["no"].ToString() + "',");sbSql.Append("'" + dtDetail.Rows[i]["leave_category"].ToString() + "',");sbSql.Append("'" + Convert.ToInt32(dtDetail.Rows[i]["tick"]) + "',");sbSql.Append("'" + Convert.ToDecimal(dtDetail.Rows[i]["leave_days"].ToString()) + "',");sbSql.Append("'" + dtDetail.Rows[i]["date_from"].ToString() + "',");sbSql.Append("'" + dtDetail.Rows[i]["date_to"].ToString() + "',");sbSql.Append("'" + oaLeaveInfo.Sid + "',");sbSql.Append("'" + Convert.ToDecimal(dtDetail.Rows[i]["hours"].ToString()) + "')");//主表cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery();//獲取IDcmd.CommandText = "select SCOPE_IDENTITY()";string strMID = cmd.ExecuteScalar().ToString();decimal dlTemp = 0;decimal dlTemp1 = 0;dlTemp1 = Convert.ToDecimal(dtDetail.Rows[i]["leave_days"].ToString()) * dlDours +Convert.ToDecimal(dtDetail.Rows[i]["hours"].ToString());for (int j = 0; j < dtTemp.Rows.Count; j++){sbSql = new StringBuilder();if (BLL.HR.BLL_HRCommon.returndecimal(dtTemp.Rows[j]["lefttime"].ToString()) - dlTemp1 > 0){sbSql.Append("insert oa_compensative_detail(overtime_sid,cps_sid,cps_hours,leave_sid)values('" +dtTemp.Rows[j]["sid"].ToString() + "','" + strMID + "','" +dlTemp1 + "','" + oaLeaveInfo.Sid + "')");cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery();break;}else{dlTemp = BLL.HR.BLL_HRCommon.returndecimal(dtTemp.Rows[j]["lefttime"].ToString());if (dlTemp1 - dlTemp > 0){sbSql.Append("insert oa_compensative_detail(overtime_sid,cps_sid,cps_hours,leave_sid)values('" +dtTemp.Rows[j]["sid"].ToString() + "','" + strMID + "','" +dlTemp + "','" + oaLeaveInfo.Sid + "')");dlTemp1 = dlTemp1 - dlTemp;cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery(); }else{sbSql.Append("insert oa_compensative_detail(overtime_sid,cps_sid,cps_hours,leave_sid)values('" +dtTemp.Rows[j]["sid"].ToString() + "','" + strMID + "','" +dlTemp1 + "','" + oaLeaveInfo.Sid + "')");cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery();break;}}}}else{sbSql = new StringBuilder();sbSql.Append(" insert oa_leave_detail(no,leave_category,tick,leave_days,date_from,date_to,p_sid,hours)values(");sbSql.Append("'" + dtDetail.Rows[i]["no"].ToString() + "',");sbSql.Append("'" + dtDetail.Rows[i]["leave_category"].ToString() + "',");sbSql.Append("'" + Convert.ToInt32(dtDetail.Rows[i]["tick"]) + "',");sbSql.Append("'" + Convert.ToDecimal(dtDetail.Rows[i]["leave_days"].ToString()) + "',");sbSql.Append("'" + dtDetail.Rows[i]["date_from"].ToString() + "',");sbSql.Append("'" + dtDetail.Rows[i]["date_to"].ToString() + "',");sbSql.Append("'" + oaLeaveInfo.Sid + "',");sbSql.Append("'" + Convert.ToDecimal(dtDetail.Rows[i]["hours"].ToString()) + "')");cmd.CommandText = sbSql.ToString();cmd.ExecuteNonQuery();}}tx.Commit();}catch (System.Data.SqlClient.SqlException E){tx.Rollback();throw new Exception(E.Message);}}return true;}方法2：可通過 create trigger trUser_insert on tbUser for insert asselect @@identitygo 獲取最后一次出現的標識方法3： 同時也可以 set nocount on; insert into tbUser (a,b) values(“a”,”b”); select @@identity; 獲取ID

?

轉載于:https://www.cnblogs.com/anbylau2130/archive/2013/05/01/3053657.html

總結

以上是生活随笔為你收集整理的主表关联字表，主表自增长，同时插入的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。