如何获取新插入记录的ID并将其设置为变量? [英] How to get the ID of an newly inserted record and set as a variable?

问题描述

你好

我正在努力返回刚插入SQL数据库中的记录的ID，使用ASP.NET(C#)和Code Behind来处理插入和SqlDataSources作为连接

(SqlDataSource1.InsertParameter ["xxxxxxxx"].DefaultValue ="xxxxxxx";)等.

理想情况下，我想插入记录，然后将插入的记录的ID返回给变量，以便可以在下一个网页(或当前页面，如果可能)上使用它，或将其设置为查询字符串.

我对.NET中所有这些的工作方式知之甚少(在经典的ASP大声笑中曾经很简单).
我尝试在插入后执行SELECT = SCOPE_ID()，但不确定是否可行，或者不确定对变量的返回值.

目前，我必须在下一页上使用SELECT(MAX)来获取最后的记录ID，但是我知道一旦多个用户开始输入记录，这并不是一种安全的方法.

请谨慎对待您的回复，因为我可能不完全理解它们.

在此先感谢

Codemagpie.

解决方案

1.将输出参数添加到您的存储过程中，并使用SCOPE_IDENTITY()进行设置，如下所示(假设您正在使用身份"字段):

 创建 过程 [dbo].[SAVE_DATA]
(
       @ Param1 
    ， @ Param2 
    ， @ GeneratedID   INT  输出
 )
 AS 
开始
     插入  INTO  TableName 值( @ Param1 ， @ Param2 )- 插入语句
     选择  @ GeneratedID  =  SCOPE_IDENTITY (); - 填充新生成的ID 
 END  

2.按以下方法在Save方法中检索新生成的ID:

 公共  int 保存( int  Param1， int  Param2)
{
         int  GeneratedId =  0 ;
        字符串 SP = " ;
        尝试
        {
            使用(DbCommand dbCommand = Database.GetStoredProcCommand(SP))
            {
                Database.AddInParameter(dbCommand，" ，DbType. Int32 ，Param1)；
                Database.AddInParameter(dbCommand，" ，DbType. Int32 ，Param2)；
                // 输出参数
                Database.AddOutParameter(dbCommand，" ，DbType. Int64 ， 64 )；
                Database.ExecuteNonQuery(dbCommand);
                // 在执行后读取输出参数值
                GeneratedId = Convert.ToInt64(Database.GetParameterValue(dbCommand，" ));
            }
        }
        捕获(例外)
        {
            // 在此处处理异常
        }
        返回 GeneratedId;
} 

请注意，我正在使用企业库应用程序块进行数据库访问.您也可以在ADO.NET中做到这一点.请参见 http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx [ ^ ]

仅尝试
选择@@ IDENTITY
scope_identity仅在当前会话内返回值

Hello

I''m struggling to return the ID of the record I just inserted into a SQL database, using ASP.NET (C#) with Code Behind to handle the inserts and SqlDataSources as the connection

(SqlDataSource1.InsertParameter["xxxxxxxx"].DefaultValue = "xxxxxxx";) etc.

Ideally, I would like to insert the record, and then return the ID of that inserted record to a variable so I can use it on the next webpage (or current page if possible) or set as a querystring.

I have limited knowledge of how all this works in .NET (use to be simple in classic ASP lol).

I''ve tried to execute the SELECT = SCOPE_ID() after the Insert but not sure if this works, or how to return the value if it does work to a variable.

At the moment I''m having to use SELECT(MAX) on the next page to get the last record ID, but I know this is not a safe method, once multiple users start entering records.

Please be gentle with your replies as I might not understand them fully to start with.

Thanks in advance

Codemagpie.

解决方案

1. Add an output parameter to your stored procedure and set it using the SCOPE_IDENTITY() as follows (Assuming that you are using Identity field):

CREATE PROCEDURE [dbo].[SAVE_DATA]
(
      @Param1
    , @Param2
    , @GeneratedID INT OUTPUT
 )
AS
BEGIN
     INSERT INTO TableName Values(@Param1,@Param2) --Insert statement
     SELECT @GeneratedID = SCOPE_IDENTITY(); --Populate the newly generated ID
END

2. Retrieve the newly generated ID in the Save method as follows:

public int Save(int Param1,int Param2)
{
        int GeneratedId = 0;
        string SP = "SAVE_DATA";
        try
        {
            using (DbCommand dbCommand = Database.GetStoredProcCommand(SP))
            {
                Database.AddInParameter(dbCommand, "@Param1", DbType.Int32, Param1);
                Database.AddInParameter(dbCommand, "@Param1", DbType.Int32, Param2);
                //The Output Parameter
                Database.AddOutParameter(dbCommand, "@GeneratedId", DbType.Int64, 64);
                Database.ExecuteNonQuery(dbCommand);
                //Read the Output Parameter Value afte execution
                GeneratedId = Convert.ToInt64(Database.GetParameterValue(dbCommand, "@GeneratedId"));
            }
        }
        catch (Exception ex)
        {
            //Handle exception here
        }
        return GeneratedId;
}

Please note that, I am using the Enterprise Library application block for database access. You can do it in ADO.NET also. See http://msdn.microsoft.com/en-us/library/ks9f57t0.aspx[^]

try only
SELECT @@IDENTITY
scope_identity returns value in only within current session

这篇关于如何获取新插入记录的ID并将其设置为变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！