获取主键或任何其他属性刚刚插入行后 [英] Getting primary key or any other attribute just after inserting a row

问题描述

我有一个主键，两个外键和其他属性表。我想插入刚插入后应该返回主键的方式排，我使用下面的语句来执行查询

I have a table with a primary key, two foreign keys and other attributes. I want to insert a row in a way that just after insertion it should return the PRIMARY KEY, I am using following statement to execute query

int MyId = (int)insert.ExecuteScalar();  

但高于code将返回一个外键，这是插入查询的一部分。我怎样才能在插入后返回主键？其次，有没有什么办法让刚插入后的任何特定属性。结果
我使用asp.net和SQL Server 2008

but above code is returning one of foreign keys, which is part of insert query. How can I get primary key in return after insertion? Second, is there any way to get any specific attribute just after insertion.
I am using asp.net and Sql Server 2008

推荐答案

通过在SQL Server这样的表：

With a table like this in SQL Server:

create table test
(
 id int identity primary key,
 data nvarchar(255)
)

您可以使用 SCOPE_IDENTITY（）：（检查冷落错误等）

You can use SCOPE_IDENTITY(): (error checking left out, etc)

using System;
using System.Data;
using System.Data.SqlClient;

namespace sqltest
{
  class Program
  {
      static void Main(string[] args)
      {
        SqlParameter id = new SqlParameter("id",0);
        //parameter value can be set server side and will be returned to client
        id.Direction=ParameterDirection.Output; 
        string query="Insert into test values ('hello world');select @id=SCOPE_IDENTITY()";
        int lastID=0;
        using (SqlConnection conn = new SqlConnection("put your connection string here"))
        {
            conn.Open();
            using (SqlCommand comm = new SqlCommand(query, conn))
            {
                comm.Parameters.Add(id);
                comm.ExecuteNonQuery();
                //id.Value now holds the last identity value
                lastID = Convert.ToInt32(id.Value);
            }//end using comm
        }//end using conn
       }//end Main
    }
}

不过说实话，如果在所有可能使用的抽象（LINQ2SQL，实体框架，NHibernate的，等等，等等），对于这种事情，因为它使你不必应付这种样板的，我会建议。

But honestly I would recommend if at all possible you use an abstraction (Linq2SQL, Entity Framework, NHibernate, etc, etc) for this kind of thing because it frees you from having to deal with this kind of boilerplate.

这篇关于获取主键或任何其他属性刚刚插入行后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！