从ADO中的SQL Server获取返回值IDENT_CURRENT.净,c#

本文关键字：CURRENT IDENT 返回值 中的 ADO SQL Server 获取 | 更新日期: 2023-09-27 18:11:23

我使用SQL Server 2014，我有一个返回int值的存储过程:

@ShippingDataID = IDENT_CURRENT ('ShippingData');

如果我查询sql的工作，我看到返回值，但在c# ADO。我总是得到NULL。我尝试了一些我在这里看到的东西，但没有任何效果，我很高兴得到帮助。

这是我的c#代码: 
using (con)
{
    SqlCommand cmd = new SqlCommand("dbo.spSetShippingData", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpNewShippingData", dt);
    tvpParam.SqlDbType = SqlDbType.Structured;
    cmd.Parameters.Add("@ShippingDataID", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    cmd.ExecuteNonQuery();
    val = (int)cmd.Parameters["@ShippingDataID"].Value;
    returnValue = new SqlParameter();
    returnValue.Direction = ParameterDirection.ReturnValue;
    cmd.Parameters.Add(returnValue);
    cmd.Parameters.Add("@ShippingDataID", SqlDbType.Int).Direction = ParameterDirection.Output;
    val = (int)cmd.Parameters["@ShippingDataID"].Value;
    //cmd.ExecuteNonQuery();
}
con.Close();
这是我的SQL代码: 
 CREATE PROCEDURE [dbo].[spSetShippingData] 
      @tvpNewShippingData dbo.TVPShippingDataTableType readonly
 AS
 BEGIN
     DECLARE @ShippingDataID INT = 0;
     INSERT INTO dbo.ShippingData(FirstName, LastName, Email, Phone, AdditionalPhone, City, Street, StreetNumber, Entrance, ApartmentNumber, PostalCode, CompanyID)
        SELECT 
            FirstName, LastName, Email, Phone, AdditionalPhone, 
            City, Street, StreetNumber, Entrance, 
            ApartmentNumber, PostalCode, CompanyID
        FROM 
            @tvpNewShippingData
    SET @ShippingDataID = IDENT_CURRENT ('ShippingData');
    SELECT @ShippingDataID;
END

从ADO中的SQL Server获取返回值IDENT_CURRENT.净,c#

如果您想通过返回参数(而不是out参数)接收IDENT_CURRENT的值，那么您需要在存储过程

中调用return语句
CREATE PROCEDURE [dbo].[spSetShippingData] @tvpNewShippingData 
    dbo.TVPShippingDataTableType readonly
AS
BEGIN
    INSERT INTO dbo.ShippingData
        (FirstName,LastName,Email,Phone,AdditionalPhone,City,
        Street,StreetNumber,Entrance,ApartmentNumber,PostalCode,CompanyID)
    SELECT FirstName,LastName,Email,Phone,AdditionalPhone,City,
        Street,StreetNumber,Entrance,ApartmentNumber,PostalCode,CompanyID
    FROM @tvpNewShippingData
    RETURN IDENT_CURRENT ('ShippingData');
END

现在你的c#代码应该在一个ReturnValue参数中接收返回值

cmd.Parameters.Add("@ShippingDataID", SqlDbType.Int).Direction =    ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
val = (int)cmd.Parameters["@ShippingDataID"].Value;

说我更喜欢更简单的方法

CREATE PROCEDURE [dbo].[spSetShippingData] @tvpNewShippingData 
    dbo.TVPShippingDataTableType readonly
AS
BEGIN
    INSERT INTO dbo.ShippingData
        (FirstName,LastName,Email,Phone,AdditionalPhone,City,
        Street,StreetNumber,Entrance,ApartmentNumber,PostalCode,CompanyID)
    SELECT FirstName,LastName,Email,Phone,AdditionalPhone,City,
        Street,StreetNumber,Entrance,ApartmentNumber,PostalCode,CompanyID
    FROM @tvpNewShippingData
    SELECT IDENT_CURRENT ('ShippingData');
END

在c#中调用ExecuteScalar而不是ExecuteNonQuery而不使用返回值

object result = cmd.ExecuteScalar();
int val = (result == null ? (int)0 : Convert.ToInt32(result);

ExecuteScalar将返回代码执行的最后一个SELECT的第一行的第一列，因此您不需要构建参数。

您可以在程序末尾添加

SET @ShippingDataID = IDENT_CURRENT ('ShippingData'); 
SELECT @ShippingDataID AS RETURN_ID

在c#中

您可以使用DataAdapter

获取值
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
int shipingId =Convert.ToInt32(dt.Rows[0]["RETURN_ID"].ToString());

相关文章：