如何在我的C#代码中将@NEXT_ID分配给int变量. [英] How can I assign the @NEXT_ID to a int variable in my c# code.

问题描述

CREATE PROC GETNEXTID @OPERATION VARCHAR (10)
AS
BEGIN
DECLARE @NEXT_ID INT

IF @OPERATION='USERID'
    BEGIN
        SELECT @NEXT_ID= ISNULL(COUNT(USER_ID),0)+1 FROM USER_DETAILS
    END
ELSE IF @OPERATION='FILEID'
    BEGIN
        SELECT @NEXT_ID= ISNULL(COUNT(FILE_ID),0)+1 FROM FILE_DETAILS
    END
ELSE IF @OPERATION='LOGID'
    BEGIN
        SELECT @NEXT_ID= ISNULL(COUNT(LOG_ID),0)+1 FROM LOG_OPERATION
    END
ELSE IF @OPERATION='FAMILYID'
    BEGIN
        SELECT @NEXT_ID=ISNULL(COUNT(FAMILY_ID),0)+1 FROM FAMILY_DETAILS
    END
RETURN @NEXT_ID
END

推荐答案

这种设计很危险:如果两个用户想要添加一些东西会发生什么?信息数据库在同一时间?例如.两个用户都已经查询了"Next_ID"，但是他们都还没有将值提交给数据库...
最好让数据库为您计算值，例如通过触发器和序列(Oracle)或通过使用带有标识标志的整数(Microsoft SQL Server).并摆脱您的存储过程(我承认几年前曾写过类似的编码恐怖文章).
使用SQL Server，您可以在插入查询后立即通过查询来检索值:Select Ident_Current(''MyTable'')或SELECT SCOPE_IDENTITY()

That design is dangerous: what happens if two users want to add some information the database at the same time? E.g. both users already queried the "Next_ID", but none of them has yet committed the values to the db...
Better have the database calculate the value for you, e.g. by a trigger and a sequence (Oracle) or by using an integer with the identity flag (Microsoft SQL Server). And get rid of your stored procedure (I admit that I wrote similar coding horrors some years ago).
With SQL server, you can retrieve the value by a query immediately after your insert query: Select Ident_Current(''MyTable'') or SELECT SCOPE_IDENTITY()

SqlCommand cmd = new SqlCommand("GETNEXTID",conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();
object next_id = cmd.ExecuteScalar(); //Cast to your desired type. E.g. int
conn.Close();

希望对您有所帮助.

Hope it helps.

另一种方法是创建"@NEXT_ID"参数作为输出参数，并在过程执行后获取其值.

下面的链接用于在storeprocedure中创建输出参数

http://www. infopathdev.com/blogs/jimmy/archive/2009/08/01/executing-a-stored-procedure-with-output-parameters.aspx [ http://stackoverflow.com/questions/7770924/how-to- use-output-parameter-in-stored-procedure [ ^ ]

Another way is that create ''@NEXT_ID'' parameter as output parameter and get it''s value after procedure execution.

below link for creating output parameter in storeprocedure

http://www.infopathdev.com/blogs/jimmy/archive/2009/08/01/executing-a-stored-procedure-with-output-parameters.aspx[^]

accessing and passing value for output parameters in c#

http://stackoverflow.com/questions/7770924/how-to-use-output-parameter-in-stored-procedure[^]

这篇关于如何在我的C#代码中将@NEXT_ID分配给int变量.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！