c# - 从 C# 中的存储过程返回行 ID？

Question

我正在尝试获取插入行的行 ID。

这是 Fuel 类:

public class Fuel
{
    private Int32 fuelId;
    private DateTime fuelDate;

    public Fuel(DateTime fuelDate)
    {
        this.fuelDate = fuelDate;
    }

    public Fuel(Int32 fuelId, DateTime fuelDate)
    {
        this.fuelId = fuelId;
        this.fuelDate = fuelDate;
    }

    public Int32 FuelId() { return fuelId; }
    public DateTime FuelDate() { return fuelDate; }
}

这是试图写入数据库的方法:

protected String connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;
        AttachDbFilename=" + "C:\\Users\\keith_000\\Documents\\Visual Studio 2017\\Projects\\RubberDress\\RubberDress\\Rubber.mdf" + ";" +
        "Integrated Security=True;" +
        "Connect Timeout=30";

public Int32 InsertFuelPurchase(Fuel myAddFuel)
{
        Int32 myFuelId;

        using (SqlConnection mySqlConn = new SqlConnection(connectionString))
        {
            mySqlConn.Open();

            using (SqlCommand myComm = new SqlCommand("FUELINSERT", mySqlConn))
            {
                myComm.CommandType = CommandType.StoredProcedure;
                myComm.Parameters.Add("@FUELDATE", SqlDbType.DateTime).Value = myAddFuel.FuelDate();
                myComm.Parameters.Add("@FUELID", SqlDbType.Int).Direction = ParameterDirection.Output;
                myFuelId = Convert.ToInt32(myComm.Parameters["@FUELID"].Value);
                mySqlConn.Close();
            }
        }

        return myFuelId;
    }

存储过程是

CREATE PROCEDURE [dbo].[FUELINSERT]
    @FUELDATE DATETIME,
    @FUELID INT OUTPUT
AS
    INSERT INTO FUELPURCHASE (FUELPURCHASEDATE)  
    VALUES (@FUELDATE)

    SELECT @FUELID = SCOPE_IDENTITY()

正在写入下表

CREATE TABLE [dbo].[FUELPURCHASE] 
(
    [FUELPURCHASEID] INT IDENTITY (1, 1) NOT NULL,
    [FUELPURCHASEDATE] DATETIME NOT NULL

    PRIMARY KEY CLUSTERED ([FUELPURCHASEID] ASC)
);

当前返回的结果为0。

当我调试该方法时，我发现它从存储过程返回 0。

谁能指出我必须在哪里解决问题？

Answer 1

当然您需要执行命令，(ExecuteScalar、ExecuteNonQuery，甚至 ExecuteReader 都会调用 SP 并运行您的 SqlCommand)但是您不需要 out 参数，只需在存储过程中选择 SCOPE_IDENTITY() 并使用 ExecuteScalar 执行命令

CREATE PROCEDURE [dbo].[FUELINSERT]
    @FUELDATE DATETIME
AS
    INSERT INTO FUELPURCHASE (FUELPURCHASEDATE) VALUES (@FUELDATE)
    SELECT SCOPE_IDENTITY()

现在你可以调用 SP 了

....
using (SqlCommand myComm = new SqlCommand("FUELINSERT", mySqlConn))
{
    myComm.CommandType = CommandType.StoredProcedure;
    myComm.Parameters.Add("@FUELDATE", SqlDbType.DateTime).Value = myAddFuel.FuelDate();
    myFuelId = Convert.ToInt32(myComm.ExecuteScalar());
}
....