方法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)
{
//dlhourday
if (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();
//获取ID
cmd.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 as
select @@identity
go
获取最后一次出现的标识
方法3：
同时也可以
set nocount on;
insert into tbUser (a,b) values(“a”,”b”);
select @@identity;
获取ID
