如何获得最后插入的id?

I have this code:

我有这段代码:

string insertSql = 
    "INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId)";

using (SqlConnection myConnection = new SqlConnection(myConnectionString))
{
   myConnection.Open();

   SqlCommand myCommand = new SqlCommand(insertSql, myConnection);

   myCommand.Parameters.AddWithValue("@UserId", newUserId);
   myCommand.Parameters.AddWithValue("@GameId", newGameId);

   myCommand.ExecuteNonQuery();

   myConnection.Close();
}

When I insert into this table, I have an auto_increment int primary key column called GamesProfileId, how can i get the last inserted one after this so I can use that id to insert into another table?

当我插入到这个表中时，我有一个名为GamesProfileId的auto_increment int主键列，我如何才能在此之后插入最后一个id，以便使用该id插入另一个表?

14 个解决方案

#1

For SQL Server 2005+, if there is no insert trigger, then change the insert statement (all one line, split for clarity here) to this

对于SQL Server 2005+，如果没有插入触发器，则更改insert语句(所有一行，将其分割为清晰)。

INSERT INTO aspnet_GameProfiles(UserId,GameId)
OUTPUT INSERTED.ID
VALUES(@UserId, @GameId)

For SQL Server 2000, or if there is an insert trigger:

对于SQL Server 2000，或者如果有插入触发器:

INSERT INTO aspnet_GameProfiles(UserId,GameId) 
VALUES(@UserId, @GameId);
SELECT SCOPE_IDENTITY()

And then

然后

 Int32 newId = (Int32) myCommand.ExecuteScalar();

#2

You can create a command with CommandText equal to

您可以创建一个命令，命令文本等于

INSERT INTO aspnet_GameProfiles(UserId, GameId) OUTPUT INSERTED.ID VALUES(@UserId, @GameId)

and execute int id = (int)command.ExecuteScalar.

并执行int id = (int)命令。

This MSDN article will give you some additional techniques.

这篇MSDN的文章将提供一些额外的技巧。

#3

string insertSql = 
    "INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId)SELECT SCOPE_IDENTITY()";

int primaryKey;

using (SqlConnection myConnection = new SqlConnection(myConnectionString))
{
   myConnection.Open();

   SqlCommand myCommand = new SqlCommand(insertSql, myConnection);

   myCommand.Parameters.AddWithValue("@UserId", newUserId);
   myCommand.Parameters.AddWithValue("@GameId", newGameId);

   primaryKey = Convert.ToInt32(myCommand.ExecuteScalar());

   myConnection.Close();
}

This vil work :)

维尔工作:)

#4

I had the same need and found this answer ..

我也有同样的需要，找到了这个答案。

This creates a record in the company table (comp), it the grabs the auto ID created on the company table and drops that into a Staff table (staff) so the 2 tables can be linked, MANY staff to ONE company. It works on my SQL 2008 DB, should work on SQL 2005 and above.

这将在公司表(comp)中创建一个记录，它将在公司表上创建的自动ID，并将其放入Staff表(Staff)中，这样就可以将两个表链接到一个公司。它适用于我的SQL 2008 DB，应该适用于SQL 2005及以上版本。

===========================

= = = = = = = = = = = = = = = = = = = = = = = = = = =

CREATE PROCEDURE [dbo].[InsertNewCompanyAndStaffDetails]

 @comp_name varchar(55) = 'Big Company',

 @comp_regno nchar(8) = '12345678',

 @comp_email nvarchar(50) = '[e-mail]',

 @recID INT OUTPUT

-- The '@recID' is used to hold the Company auto generated ID number that we are about to grab

——“@recID”用于保存我们将要获取的公司自动生成的ID号

AS
 Begin

  SET NOCOUNT ON

  DECLARE @tableVar TABLE (tempID INT)

-- The line above is used to create a tempory table to hold the auto generated ID number for later use. It has only one field 'tempID' and its type INT is the same as the '@recID'.

——上面的行用于创建一个临时表，用于保存自动生成的ID号，以便以后使用。它只有一个字段“tempID”，其类型INT与“@recID”相同。

  INSERT INTO comp(comp_name, comp_regno, comp_email) 

  OUTPUT inserted.comp_id INTO @tableVar

-- The 'OUTPUT inserted.' line above is used to grab data out of any field in the record it is creating right now. This data we want is the ID autonumber. So make sure it says the correct field name for your table, mine is 'comp_id'. This is then dropped into the tempory table we created earlier.

——“输出插入。上面的线用于从它正在创建的记录中的任何字段中获取数据。我们需要的数据是ID自动编号。所以要确保表的字段名是正确的，我的是comp_id。然后将其放入我们先前创建的临时表中。

  VALUES (@comp_name, @comp_regno, @comp_email)

  SET @recID = (SELECT tempID FROM @tableVar)

-- The line above is used to search the tempory table we created earlier where the ID we need is saved. Since there is only one record in this tempory table, and only one field, it will only select the ID number you need and drop it into '@recID'. '@recID' now has the ID number you want and you can use it how you want like i have used it below.

——上面的行用于搜索之前创建的、保存所需ID的临时表。由于在这个临时表中只有一条记录，而且只有一个字段，因此它将只选择所需的ID号并将其放入“@recID”。“@recID”现在已经有了您想要的ID号，您可以按照自己的方式使用它，就像我下面使用的一样。

  INSERT INTO staff(Staff_comp_id) 
  VALUES (@recID)

 End

-- So there you go. You can actually grab what ever you want in the 'OUTPUT inserted.WhatEverFieldNameYouWant' line and create what fields you want in your tempory table and access it to use how ever you want.

好了。实际上，您可以在“插入”中获取所需的内容。WhatEverFieldNameYouWant' line并在临时表中创建您想要的字段，并访问该字段以使用任何您想要的方式。

I was looking for something like this for ages, with this detailed break down, I hope this helps.

我一直在寻找这样的东西，有了这个详细的分解，我希望这能有所帮助。

#5

In pure SQL the main statement kools like:

在纯SQL中，主语句kools如下:

INSERT INTO [simbs] ([En]) OUTPUT INSERTED.[ID] VALUES ('en')

Square brackets defines the table simbs and then the columns En and ID, round brackets defines the enumeration of columns to be initiated and then the values for the columns, in my case one column and one value. The apostrophes enclose a string

方括号定义了表simbs，然后是列En和ID，圆括号定义了要初始化的列的枚举，然后是列的值，在我的例子中是一列和一个值。撇号包含一个字符串

I will explain you my approach:

我将向你解释我的方法:

It might be not easy to understand but i hope useful to get the big picture around using the last inserted id. Of course there are alternative easier approaches. But I have reasons to keep mine. Associated functions are not included, just their names and parameter names.

这可能不容易理解，但我希望使用最后插入的id来了解全局。当然，还有其他更简单的方法。但我有理由保留我的。不包括相关函数，只包括它们的名称和参数名称。

I use this method for medical artificial intelligence The method check if the wanted string exist in the central table (1). If the wanted string is not in the central table "simbs", or if duplicates are allowed, the wanted string is added to the central table "simbs" (2). The last inseerted id is used to create associated table (3).

我用这个方法对于医学人工智能方法检查是否想要的字符串中存在*表(1)。如果想要的字符串不是*表中的“simbs”,或如果允许重复,希望字符串添加到*表“simbs”(2)。最后inseerted id用于创建表(3)有关。

    public List<int[]> CreateSymbolByName(string SymbolName, bool AcceptDuplicates)
    {
        if (! AcceptDuplicates)  // check if "AcceptDuplicates" flag is set
        {
            List<int[]> ExistentSymbols = GetSymbolsByName(SymbolName, 0, 10); // create a list of int arrays with existent records
            if (ExistentSymbols.Count > 0) return ExistentSymbols; //(1) return existent records because creation of duplicates is not allowed
        }
        List<int[]> ResultedSymbols = new List<int[]>();  // prepare a empty list
        int[] symbolPosition = { 0, 0, 0, 0 }; // prepare a neutral position for the new symbol
        try // If SQL will fail, the code will continue with catch statement
        {
            //DEFAULT und NULL sind nicht als explizite Identitätswerte zulässig
            string commandString = "INSERT INTO [simbs] ([En]) OUTPUT INSERTED.ID VALUES ('" + SymbolName + "') "; // Insert in table "simbs" on column "En" the value stored by variable "SymbolName"
            SqlCommand mySqlCommand = new SqlCommand(commandString, SqlServerConnection); // initialize the query environment
                SqlDataReader myReader = mySqlCommand.ExecuteReader(); // last inserted ID is recieved as any resultset on the first column of the first row
                int LastInsertedId = 0; // this value will be changed if insertion suceede
                while (myReader.Read()) // read from resultset
                {
                    if (myReader.GetInt32(0) > -1) 
                    {
                        int[] symbolID = new int[] { 0, 0, 0, 0 };
                        LastInsertedId = myReader.GetInt32(0); // (2) GET LAST INSERTED ID
                        symbolID[0] = LastInsertedId ; // Use of last inserted id
                        if (symbolID[0] != 0 || symbolID[1] != 0) // if last inserted id succeded
                        {
                            ResultedSymbols.Add(symbolID);
                        }
                    }
                }
                myReader.Close();
            if (SqlTrace) SQLView.Log(mySqlCommand.CommandText); // Log the text of the command
            if (LastInsertedId > 0) // if insertion of the new row in the table was successful
            {
                string commandString2 = "UPDATE [simbs] SET [IR] = [ID] WHERE [ID] = " + LastInsertedId + " ;"; // update the table by giving to another row the value of the last inserted id
                SqlCommand mySqlCommand2 = new SqlCommand(commandString2, SqlServerConnection); 
                mySqlCommand2.ExecuteNonQuery();
                symbolPosition[0] = LastInsertedId; // mark the position of the new inserted symbol
                ResultedSymbols.Add(symbolPosition); // add the new record to the results collection
            }
        }
        catch (SqlException retrieveSymbolIndexException) // this is executed only if there were errors in the try block
        {
            Console.WriteLine("Error: {0}", retrieveSymbolIndexException.ToString()); // user is informed about the error
        }

        CreateSymbolTable(LastInsertedId); //(3) // Create new table based on the last inserted id
        if (MyResultsTrace) SQLView.LogResult(LastInsertedId); // log the action
        return ResultedSymbols; // return the list containing this new record
    }

#6

I tried the above but they didn't work, i found this thought, that works a just fine for me.

我试过上面的方法，但是没有效果，我发现这个想法，对我来说很好。

var ContactID = db.GetLastInsertId();

Its less code and i easy to put in.

它的代码更少，我很容易输入。

Hope this helps someone.

希望这可以帮助别人。

#7

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace DBDemo2
{
    public partial class Form1 : Form
    {
        string connectionString = "Database=company;Uid=sa;Pwd=mypassword";
        System.Data.SqlClient.SqlConnection connection;
        System.Data.SqlClient.SqlCommand command;

        SqlParameter idparam = new SqlParameter("@eid", SqlDbType.Int, 0);
        SqlParameter nameparam = new SqlParameter("@name", SqlDbType.NChar, 20);
        SqlParameter addrparam = new SqlParameter("@addr", SqlDbType.NChar, 10);

        public Form1()
        {
            InitializeComponent();

            connection = new System.Data.SqlClient.SqlConnection(connectionString);
            connection.Open();
            command = new System.Data.SqlClient.SqlCommand(null, connection);
            command.CommandText = "insert into employee(ename, city) values(@name, @addr);select SCOPE_IDENTITY();";

            command.Parameters.Add(nameparam);
            command.Parameters.Add(addrparam);
            command.Prepare();

        }

        private void Form1_Load(object sender, EventArgs e)
        {
        }

        private void buttonSave_Click(object sender, EventArgs e)
        {


            try
            {
                int id = Int32.Parse(textBoxID.Text);
                String name = textBoxName.Text;
                String address = textBoxAddress.Text;

                command.Parameters[0].Value = name;
                command.Parameters[1].Value = address;

                SqlDataReader reader = command.ExecuteReader();
                if (reader.HasRows)
                {
                    reader.Read();
                    int nid = Convert.ToInt32(reader[0]);
                    MessageBox.Show("ID : " + nid);
                }
                /*int af = command.ExecuteNonQuery();
                MessageBox.Show(command.Parameters["ID"].Value.ToString());
                */
            }
            catch (NullReferenceException ne)
            {
                MessageBox.Show("Error is : " + ne.StackTrace);
            }
            catch (Exception ee)
            {
                MessageBox.Show("Error is : " + ee.StackTrace);
            }
        }

        private void buttonSave_Leave(object sender, EventArgs e)
        {

        }

        private void Form1_Leave(object sender, EventArgs e)
        {
            connection.Close();
        }
    }
}

#8

You can also use a call to SCOPE_IDENTITY in SQL Server.

您还可以在SQL Server中使用对SCOPE_IDENTITY的调用。

#9

After this:

在这之后:

INSERT INTO aspnet_GameProfiles(UserId, GameId) OUTPUT INSERTED.ID VALUES(@UserId, @GameId)

Execute this

执行这个

int id = (int)command.ExecuteScalar;

It will work

它将工作

#10

There are all sorts of ways to get the Last Inserted ID but the easiest way I have found is by simply retrieving it from the TableAdapter in the DataSet like so:

有各种各样的方法来获取最后插入的ID，但是我发现的最简单的方法是从数据集中的TableAdapter中检索它，比如:

<Your DataTable Class> tblData = new <Your DataTable Class>();
<Your Table Adapter Class> tblAdpt = new <Your Table Adapter Class>();

/*** Initialize and update Table Data Here ***/

/*** Make sure to call the EndEdit() method ***/
/*** of any Binding Sources before update ***/
<YourBindingSource>.EndEdit();

//Update the Dataset
tblAdpt.Update(tblData);

//Get the New ID from the Table Adapter
long newID = tblAdpt.Adapter.InsertCommand.LastInsertedId;

Hope this Helps ...

希望这有助于……

#11

After inserting any row you can get last inserted id by below line of query.

插入任何一行之后，您可以在查询行下面获得最后插入的id。

INSERT INTO aspnet_GameProfiles(UserId,GameId) VALUES(@UserId, @GameId); SELECT @@IDENTITY

插入aspnet_GameProfiles(UserId,GameId)值(@UserId， @GameId);选择@@IDENTITY

#12

if your id int type and set to auto increment then try this one

如果你的id int类型被设置为自动递增，那么试试这个

SELECT * FROM TABLE WHERE ID = (SELECT MAX(ID) FROM TABLE)

#13

Use SELECT SCOPE_IDENTITY() in query

在查询中使用SELECT SCOPE_IDENTITY()

#14

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[spCountNewLastIDAnyTableRows]
(
@PassedTableName as NVarchar(255),
@PassedColumnName as NVarchar(225)
)
AS
BEGIN
DECLARE @ActualTableName AS NVarchar(255)
DECLARE @ActualColumnName as NVarchar(225)
    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName
    SELECT @ActualColumnName = QUOTENAME( COLUMN_NAME )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME = @PassedColumnName
    DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql = 'select MAX('+ @ActualColumnName + ') + 1  as LASTID' + ' FROM ' + @ActualTableName 
    EXEC(@SQL)
END