如何获得刚刚添加的行id

My c# button adding some data in one table. how can i get id of this row? does anybody have idea?

我的c#按钮在一个表中添加了一些数据。如何获得这一行的id ?有人有想法吗?

  private static void InsertIntoTransportation(string ZednadebNumber, DateTime DateTime, int SellerID, int BuyerID, string TransStart, string TransEnd, decimal Quantity, string LoadType, string LoadName, int DriverID, decimal Cost, decimal FuelUsed, decimal salary)
    {
        try
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Transportation (DriverID, ClientIDAsSeller, ClientIDAsBuyer, ZednadebNumber, LoadName, TransStart, TransEnd, LoadType, Quantity, Cost, FuelUsed, DateTime) VALUES (@DriverID, @ClientIDAsSeller, @ClientIDAsBuyer, @ZednadebNumber, @LoadName, @TransStart, @TransEnd, @LoadType, @Quantity, @Cost, @FuelUsed, @DateTime)", new SqlConnection(Program.ConnectionString)))
            {
                cmd.Parameters.AddWithValue("@DriverID", DriverID);
                cmd.Parameters.AddWithValue("@ClientIDAsSeller", SellerID);
                cmd.Parameters.AddWithValue("@ClientIDAsBuyer", BuyerID);
                cmd.Parameters.AddWithValue("@ZednadebNumber", ZednadebNumber);
                cmd.Parameters.AddWithValue("@LoadName", LoadName);
                cmd.Parameters.AddWithValue("@TransStart", TransStart);
                cmd.Parameters.AddWithValue("@TransEnd", TransEnd);
                cmd.Parameters.AddWithValue("@LoadType", LoadType);
                cmd.Parameters.AddWithValue("@Quantity", Quantity);
                cmd.Parameters.AddWithValue("@Cost", Cost);
                cmd.Parameters.AddWithValue("@FuelUsed", FuelUsed);
                cmd.Parameters.AddWithValue("@DateTime", DateTime);
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                SqlCommand cmd1 = new SqlCommand("INSERT INTO Salary (DriverID, TransportationID, Salary) VALUES (@DID, @@IDENTITY, @Salary)", new SqlConnection(Program.ConnectionString));
                cmd1.Parameters.AddWithValue("@DID", DriverID);
                cmd1.Parameters.AddWithValue("@Salary", salary);
                cmd1.Connection.Open();
                cmd1.ExecuteNonQuery();
                cmd1.Connection.Close();
                cmd.Connection.Close();
            }
        }
        catch (Exception)
        {

            MessageBox.Show("ვერ ვახერხებ მონაცემთა ბაზასთან კავშირს, დაწრმუნდით თქვენი კომპიუტერის ინტერნეტთან კავშირის გამართულობაში.", "შეცდომა !!!", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

3 个解决方案

#1

My c# button adding some data in one table. how can i get id of this row? does anybody have idea?

我的c#按钮在一个表中添加了一些数据。如何获得这一行的id ?有人有想法吗?

Your code example provided inserts two rows.

您的代码示例提供了插入两行。

Either way, simply alter the statement to included "; SELECT SCOPE_IDENTITY()" at the end, and get a scalar result from the command.

无论哪种方式，只需将语句修改为include;最后选择SCOPE_IDENTITY()，并从命令中获得标量结果。

Assuming your row ID is a standard integer field (rather than a bigint)

假设您的行ID是一个标准的整型字段(而不是一个bigint)

// Edit your command text to have "; SELECT SCOPE_IDENTITY()" at the end. 
int? insertedId = cmd.ExecuteScalar() as int?;
if (insertedId.HasValue) 
 { 
    //  success! 
 }

Note that if your row isn't using auto incrementing integer/big-int, then this won't work.

注意，如果您的行不使用自动递增的整型/大整数，那么这将不起作用。

In addition, I would strongly suggest that you consider moving your statements to stored procedures, if this is for anything other than test/sample code.

此外，我强烈建议您考虑将您的语句移动到存储过程中，如果这不是用于测试/示例代码的话。

Separating your SQL code out of your C# means that you can re-use common statements (think of them as Methods). Alternatively, consider using an ORM solution.

将SQL代码从c#中分离出来意味着可以重用公共语句(将它们视为方法)。或者，考虑使用ORM解决方案。

#2

On an MS SQL Server, My preference is for the output clause. You wouldn't be executing a non-query, it would be a reader (even though it's technically an insert) - the resultset from your insert will then contain one row with the field(s) you choose. This is useful if you have a ROWVERSION (TIMESTAMP) column that you also need for updates that might follow (prevents you from doing another read).

在MS SQL服务器上，我更喜欢output子句。您不会执行一个非查询，而是一个读取器(尽管技术上它是一个插入)——您的insert中的resultset将包含一行，其中包含您选择的字段。如果您有一个ROWVERSION (TIMESTAMP)列，您还需要它进行后续更新(防止您进行另一次读取)，那么这是非常有用的。

Edit - Just use OUTPUT, not OUTPUT INTO (which is more useful for SQL Server side code than C#).

编辑——只使用输出，而不是输出到(这对于SQL服务器端代码比c#更有用)。

This works well with sprocs and triggers too, as opposed to scope_identity (which, when using instead-of triggers is in a different scope and so returns null), or @@identity and all of it's issues.

这对于sprocs和触发器也很有效，而与scope_identity(在使用instead-of触发器时，它在不同的范围内，因此返回null)或@identity和所有的问题相反。

#3

-1

Some of my co-worker developers will perfrom a query after the insert to return the identity value. As a DBA there are other ways to get it as indicated by Alexander. While the query will produce extra IO on the network at least it should still be in cache and not cause any more Disk IO.

我的一些同事开发人员将在插入之后检查查询以返回标识值。正如Alexander所指出的，作为DBA，还有其他方法可以获得它。虽然查询将在网络上产生额外的IO，但至少它仍然应该在缓存中，不会导致更多的磁盘IO。

#1