获取SCOPE_IDENTITY从SQL Server上的插入 [英] Getting SCOPE_IDENTITY from SQL Server on Insert

问题描述

我想这是为时已晚，我太累了，看看有什么我做错了。下面是我尝试：

I guess it is too late and I'm too tired to see what I'm doing wrong. Here is what I'm trying:

int imageId = imageDal.AddImage(new SqlParameter[]
        {
            new SqlParameter("@IMAGE_ID", 
        SqlDbType.Int, Int32.MaxValue, ParameterDirection.Output,
        true, 0, 0,"IMAGE_ID", DataRowVersion.Current,DBNull.Value),

        new SqlParameter("@IMAGE", 
        SqlDbType.Image, 11, ParameterDirection.Input,
        true, 0, 0,"IMAGE", DataRowVersion.Current,image)
        });

public int AddImage(SqlParameter[] spParams)
{
    SqlHelper.ExecuteNonQuery(BaseDAL.ConnectionStringImages, INSERT_IMAGE_SQL, spParams);
    return Convert.ToInt32(spParams[0].Value);
}

存储过程：

[dbo].[sp_insert_image]
    -- Add the parameters for the stored procedure here
    @IMAGE_ID int OUT,
    @IMAGE image
AS
BEGIN
    INSERT INTO images
    (IMAGE)
    VALUES
    (@IMAGE)
    SELECT @IMAGE_ID = SCOPE_IDENTITY();
END
GO

我得到的DBNull为spParams [0] .value的。我试着在我的存储过程中设置@IMAGE_ID的价值恒定却又如此的问题是不是与我的存储过程，它并没有改变任何东西（这就是我认为）。

I get DBNull as spParams[0].Value. I've tried setting value of @IMAGE_ID to a constant in my stored procedure yet it didn't change anything so the problem isn't with my stored procedure (that is what I think).

当我执行从SQL Management Studio中的过程中，我看到了inserted_id返回..

When I execute the procedure from sql management studio, I see the inserted_id returning..

推荐答案

我结束了的ExecuteScalar并直接从SP返回SCOPE_IDENTITY（）

I ended up with executescalar and returning the SCOPE_IDENTITY() directly from SP.

如果没有这样做，并从获得的SqlParameter价值的另一种方式，我很乐意听到了这一点。

If there is another way of doing it and getting the value from the sqlparameter, I'd love to hear that out.

这篇关于获取SCOPE_IDENTITY从SQL Server上的插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！