INSERT INTO jobs (job_desc,min_lvl,max_lvl) VALUES ('Accountant',12,125)------------------>执行插入语句 SELECT @@IDENTITY AS 'Identity'--------------->查询刚插入的记录的标识
参考: SqlConnection sqlconn = new SqlConnection(conn); string stext="INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125);SELECT @a=@@IDENTITY"; SqlCommand sqlcmd=new SqlCommand(sText,sqlconn);SqlParameter ptrParam= sqlcmd.Parameters.Add("@a",SqlDbType.Int); ptrParam.Direction =ParameterDirection.Output;int id=ptrParam.Value
VALUES ('Accountant',12,125)------------------>执行插入语句
SELECT @@IDENTITY AS 'Identity'--------------->查询刚插入的记录的标识
SqlConnection sqlconn = new SqlConnection(conn);
string stext="INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125);SELECT @a=@@IDENTITY";
SqlCommand sqlcmd=new SqlCommand(sText,sqlconn);SqlParameter ptrParam= sqlcmd.Parameters.Add("@a",SqlDbType.Int);
ptrParam.Direction =ParameterDirection.Output;int id=ptrParam.Value
myConnection.BeginTransaction( IsolationLevel.ReadCommitted,
"NewUser" );
strSql = "insert into Login (UserName,Password) VALUES ('" +
UserName.Text.Trim() + "','" +
Password.Text.Trim() + "') select @ID = @@IDENTITY";
myCommand =
new SqlCommand( strSql, myConnection, myTransaction );
myCommand.Parameters.Add( "@ID", SqlDbType.Int );
myCommand.Parameters["@ID"].Direction =
ParameterDirection.Output;
myCommand.ExecuteNonQuery(); int nID = Convert.ToInt32( myCommand.Parameters["@ID"].Value );
除了输入和输出参数之外,存储过程还可以具有返回值。以下示例阐释 ADO.NET 如何发送和接收输入参数、输出参数和返回值,其中采用了这样一种常见方案:将新记录插入其中主键列是自动编号字段的表。该示例使用输出参数来返回自动编号字段的 @@Identity,而 DataAdapter 则将其绑定到 DataTable 的列,使 DataSet 反映所生成的主键值。
该示例使用以下存储过程将新目录插入 Northwind Categories 表(该表将 CategoryName 列中的值当作输入参数),从 @@Identity 中以输出参数的形式返回自动编号字段 CategoryID 的值,并提供所影响行数的返回值。
CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = @@Identity
RETURN @@ROWCOUNTSqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;" +
"Initial Catalog=northwind");SqlDataAdapter catDA = new SqlDataAdapter("SELECT CategoryID, CategoryName FROM Categories", nwindConn);catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
catDA.InsertCommand.CommandType = CommandType.StoredProcedure;SqlParameter myParm = catDA.InsertCommand.Parameters.Add("@RowCount", SqlDbType.Int);
myParm.Direction = ParameterDirection.ReturnValue;catDA.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NChar, 15, "CategoryName");myParm = catDA.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "CategoryID");
myParm.Direction = ParameterDirection.Output;DataSet catDS = new DataSet();
catDA.Fill(catDS, "Categories");DataRow newRow = catDS.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
catDS.Tables["Categories"].Rows.Add(newRow);catDA.Update(catDS, "Categories");Int32 rowCount = (Int32)catDA.InsertCommand.Parameters["@RowCount"].Value;
@myID int output
插入语句执行完以后set @myID=@@identity
然后插入新的数据行
每次返回这个最大ID就可以了
不需要用事务,也可以返回最后插入的记录的自增长列的值。如果需要在一条SQL语句中执行并返回,可以
"insert table(a,b) values('a','b');select @@IDENTITY"
真的假的?ID不用自动增长?虽然这是个争议性的问题,但使用自动增长才是正途吧?