VS2005中获取新增记录的ID方法总结

1.使用触发器方式

CREATE TRIGGER intrig
ON sales
FOR update AS
declare @before_id (your title_id’type and length),
@after_id (your title_id’type and length)
SELECT @before_id =title_id FROM inserted
SELECT @after_id =title_id FROM deleted
SELECT title_id FROM sales where title_id=@before_id and title_id=@after_id

2.使用ID自增方式

\n

返回最大ID就是新增的ID

3.使用SQL命令语句方式

\n

addAnnouncementCmd.CommandText = “insert into SYS_Announcement (Title) values(@Title) ;”;
addAnnouncementCmd.CommandText += “SELECT @ID=@@Identity;”;
addAnnouncementCmd.CommandType = CommandType.Text;
addAnnouncementCmd.Parameters.Add(“@Title”, SqlDbType.NVarChar, 50);

\n

SqlParameter oID = new SqlParameter(“@ID”, SqlDbType.Int);
oID.Direction = ParameterDirection.Output;
addAnnouncementCmd.Parameters.Add(oID);

\n

try
{
addAnnouncementCmd.Connection.Open();
addAnnouncementCmd.ExecuteNonQuery();
int addID = Convert.ToInt32(addAnnouncementCmd.Parameters["@ID"].Value.ToString());//addID新增的ID
}
finally
{
command.Connection.Close();
}

4. 使用 ado.net 里面获取返回值的方法

——————————————–
假设有存储过程如下:
———————————————

CREATE proc sp_uptmp @tmpName varchar(50),@srcPos varchar(255)
as

Begin TRAN
insert into t_template values(@tmpName,@srcPos)
COMMIT

return isnull(@@identity,0)
GO


————————————————————
在 ado.net 里面获取返回值的方法为(c#):
————————————————————
SqlConnection dbconn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand(“sp_uptmp”,dbconn);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter tmpName = cmd.Parameters.Add(“@tmpName”,SqlDbType.VarChar);
SqlParameter srcPos = _cmd.Parameters.Add(“@srcPos”,SqlDbType.VarChar);
SqlParameter rtnval = cmd.Parameters.Add(“rval”,SqlDbType.Int);

tmpName.Direction = ParameterDirection.Input;
srcPos.Direction = ParameterDirection.Input;
rtnval.Direction = ParameterDirection.ReturnValue;

tmpName.Value = “”;
srcPos.Value = “”;
dbconn.Open();
cmd.ExecuteNonQuery();
dbconn.Close();

tmpid = (int)rtnval.Value; //此处即为返回值

5.利用执行查询时，并返回查询所返回的结果集中第一行的第一列方法

\n

只要把ID设为第一列,采用以下方式查询执行就可获得ID

\n

int iKey=(int)cmd.ExecuteScalar();