查询刚刚插入表的自增id

[aiyin434]

两个表同时插入第二个表需要用第一个表的id

#region 添加请假、审批信息事务处理
public static int AddlLeavaShenPi(Accounts_Leave lve, Accounts_ShenPi sp)
{
SqlConnection con = new SqlConnection(SqlHelper.connString);//获取数据库连接
con.Open();//打开连接
SqlTransaction sqltra = con.BeginTransaction();//开始事务
SqlCommand cmd = new SqlCommand();//实例化
cmd.Connection = con;//获取数据连接
cmd.Transaction = sqltra;//，在执行SQL时，
try
{
#region sql准备
string sql = string.Format(@"INSERT INTO [dbo].[Accounts_Leaves]
([apName] ,[Instead] ,[LName] ,[Time] ,[BeginTime] ,[EndTime] ,[Reson] ,[Department] ,[State] ,[Type])
VALUES
('{0}' ,'{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}' ,'{8}' ,'{9}' )"
, lve.apName, lve.Instead, lve.LName, lve.Time, lve.BeginTime, lve.EndTime, lve.Reson, lve.Department, lve.State, lve.Type);
#endregion
#region sqlhelper
SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sql, null);
#endregion
#region 取得增加的请假信息的lId
string sql1 = string.Format(@"select lId from dbo.Accounts_Leaves order by Time desc where apName = '{0}'", lve.apName);
lve.lId = Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.connString, CommandType.Text, sql1, null));
//string sql1 = string.Format(@"INSERT INTO userinfo (Time, apName) VALUES (lve.Time, lve.apName) SELECT @@IDENTITY AS lve.lId", lve.Time, lve.apName);
//SqlHelper.ExecuteReader(SqlHelper.connString, CommandType.Text, sql1, null);
#endregion
#region sql准备
sp.spLId = lve.lId;
string sql2 = string.Format(@"INSERT INTO [DB_Leave].[dbo].[Accounts_ShenPi]
([spPerson] ,[xiangshui] ,[Opinion] ,[spPass] ,[spLId])
VALUES
( '{0}' , '{1}' , '{2}' , '{3}' , '{4}')"
, sp.spPerson, sp.xiangshui, sp.Opinion, sp.spPass, sp.spLId);
#endregion
#region sqlhelper
int n = SqlHelper.ExecuteNonQuery(SqlHelper.connString, CommandType.Text, sql2, null);
#endregion
sqltra.Commit();
return n;
}
catch (Exception ex)
{
sqltra.Rollback();
return 0;
}
}

#endregion