c# ExecuteSqlCommand没有从存储过程返回输出

本文关键字：存储过程 返回 输出 ExecuteSqlCommand | 更新日期: 2023-09-27 18:04:10

我的c# ExecuteSqlCommand没有从存储过程返回输出。

我需要最后插入的行Id

这是我的存储过程

ALTER PROCEDURE [dbo].[PatientInsert]
@FirstName nvarchar(Max),
@MiddleName nvarchar(Max),
@LastName nvarchar(Max),
@Gender nvarchar(Max),
@CNICNumber nvarchar(Max),
@DateOfBirth datetime,
@Country nvarchar(max),
@StateOrProvince nvarchar(max),
@City nvarchar(max),
@Address nvarchar(max),
@CellNumber nvarchar(max),
@TelephoneNumber nvarchar(max),
@Email nvarchar(max),
@NOKFirstName nvarchar(max),
@NOKLastName nvarchar(max),
@NOKRelation nvarchar(max),
@NOKCNICNumber nvarchar(max),
@NOKCellNumber nvarchar(max),
@Title int,
@PicturePath nvarchar(max),
@LastCreatedId INT  OUTPUT
AS 
BEGIN 
     SET NOCOUNT ON 
INSERT INTO [dbo].[Patients]
           ([FirstName]
           ,[MiddleName]
           ,[LastName]
           ,[Gender]
           ,[CNICNumber]
           ,[DateOfBirth]
           ,[Country]
           ,[StateOrProvince]
           ,[City]
           ,[Address]
           ,[CellNumber]
           ,[TelephoneNumber]
           ,[Email]
           ,[NOKFirstName]
           ,[NOKLastName]
           ,[NOKRelation]
           ,[NOKCNICNumber]
           ,[NOKCellNumber]
           ,[Title]
           ,[PicturePath])
     VALUES
          (
            @FirstName ,
            @MiddleName ,
            @LastName ,
            @Gender ,
            @CNICNumber ,
            @DateOfBirth,
            @Country ,
            @StateOrProvince ,
            @City ,
            @Address ,
            @CellNumber ,
            @TelephoneNumber ,
            @Email ,
            @NOKFirstName ,
            @NOKLastName ,
            @NOKRelation ,
            @NOKCNICNumber ,
            @NOKCellNumber ,
            @Title,
            @PicturePath
        )
        SELECT @LastCreatedId = SCOPE_IDENTITY()
END

我已经测试了这个存储过程，它在SQL Server Management Studio中工作得很好

这里是存储过程执行图像，因为我使用SCOPE_IDENTITY()并通过@LastCreatedId将其返回为OUTPUT

你可以看到，在图像中，数据完美地插入到数据库中，但在c#中，我可以正确地向数据库发送数据，但不能保留@LastCreatedId的OUTPUT数据

下面是我使用这个存储过程的c#代码 
public int PatientInsert(Patient PatientInstance)
        {
            using (var context = new HMContext())
            {
                try
                {
                    //Int32 LastCreatedId = new Int32();
                    var returnCode = new SqlParameter("@LastCreatedId", SqlDbType.Int);
                    returnCode.Direction = ParameterDirection.Output;
                    insertRow =
                    context.Database.ExecuteSqlCommand("PatientInsert @FirstName ,@MiddleName,"
                    +"@LastName,@Gender,@CNICNumber,@DateOfBirth,@Country,@StateOrProvince,@City,"
                    + "@Address,@CellNumber,@TelephoneNumber,@Email,@NOKFirstName,@NOKLastName,@NOKRelation,"
                    + "@NOKCNICNumber,@NOKCellNumber,@Title,@PicturePath,@LastCreatedId",
                    new SqlParameter("@FirstName", PatientInstance.FirstName),
                    new SqlParameter("@MiddleName", PatientInstance.MiddleName),
                    new SqlParameter("@LastName", PatientInstance.LastName),
                    new SqlParameter("@Gender", PatientInstance.Gender),
                    new SqlParameter("@CNICNumber", PatientInstance.CNICNumber),
                    new SqlParameter("@DateOfBirth", PatientInstance.DateOfBirth.Value.ToShortDateString()),
                    new SqlParameter("@Country", PatientInstance.Country),
                    new SqlParameter("@StateOrProvince", PatientInstance.StateOrProvince),
                    new SqlParameter("@City", PatientInstance.City),
                    new SqlParameter("@Address", PatientInstance.Address),
                    new SqlParameter("@CellNumber", PatientInstance.CellNumber),
                    new SqlParameter("@TelephoneNumber", PatientInstance.TelephoneNumber),
                    new SqlParameter("@Email", PatientInstance.Email),
                    new SqlParameter("@NOKFirstName", PatientInstance.NOKFirstName),
                    new SqlParameter("@NOKLastName", PatientInstance.NOKLastName),
                    new SqlParameter("@NOKRelation", PatientInstance.NOKRelation),
                    new SqlParameter("@NOKCNICNumber", PatientInstance.NOKCNICNumber),
                    new SqlParameter("@NOKCellNumber", PatientInstance.NOKCellNumber),
                    new SqlParameter("@Title", PatientInstance.Title.ToString()),
                    new SqlParameter("@PicturePath", (PatientInstance.PicturePath==null?"":PatientInstance.PicturePath)),
                    returnCode
                    );
                    return (int)returnCode.Value;
                }
                catch (Exception exp)
                {
                    var me = exp.Message;
                    return -1;
                }
            }
        }

我从returnCode.Value得到{}的值如果我试图将其转换为整数就会出现异常Specified cast is not valid.

我应该怎么做才能从存储过程

c# ExecuteSqlCommand没有从存储过程返回输出

中检索@LastCreatedId的值作为输出值

直接添加

SELECT @LastCreatedId = CAST(SCOPE_IDENTITY() as int)
存储过程中的

。还要使用下面的强制转换操作

检查它是否包含int值
if (returnCode.Value != DBNull.Value) 
return Convert.ToInt32(returnCode.Value);

既然你基本上试图通过SCOPE_IDENTITY()获得ID，为什么不尝试使用:ExecuteScalar()。

执行查询，并返回查询返回的结果集中第一行的第一列。其他列或行将被忽略。

我从下面的帖子中得到了答案:SQL Server Insert命令的返回值

干杯!

我有同样的问题，尝试在ExecuteSQLCommand调用中提到的参数后面添加OUTPUT这个词，就像这样

context.Database.ExecuteSqlCommand("...,@LastCreatedId OUTPUT",...

你的完整代码应该是这样的:

public int PatientInsert(Patient PatientInstance)
    {
        using (var context = new HMContext())
        {
            try
            {
                //Int32 LastCreatedId = new Int32();
                var returnCode = new SqlParameter("@LastCreatedId", SqlDbType.Int);
                returnCode.Direction = ParameterDirection.Output;
                insertRow =
                context.Database.ExecuteSqlCommand("PatientInsert @FirstName ,@MiddleName,"
                +"@LastName,@Gender,@CNICNumber,@DateOfBirth,@Country,@StateOrProvince,@City,"
                + "@Address,@CellNumber,@TelephoneNumber,@Email,@NOKFirstName,@NOKLastName,@NOKRelation,"
                + "@NOKCNICNumber,@NOKCellNumber,@Title,@PicturePath,@LastCreatedId OUTPUT",
                new SqlParameter("@FirstName", PatientInstance.FirstName),
                new SqlParameter("@MiddleName", PatientInstance.MiddleName),
                new SqlParameter("@LastName", PatientInstance.LastName),
                new SqlParameter("@Gender", PatientInstance.Gender),
                new SqlParameter("@CNICNumber", PatientInstance.CNICNumber),
                new SqlParameter("@DateOfBirth", PatientInstance.DateOfBirth.Value.ToShortDateString()),
                new SqlParameter("@Country", PatientInstance.Country),
                new SqlParameter("@StateOrProvince", PatientInstance.StateOrProvince),
                new SqlParameter("@City", PatientInstance.City),
                new SqlParameter("@Address", PatientInstance.Address),
                new SqlParameter("@CellNumber", PatientInstance.CellNumber),
                new SqlParameter("@TelephoneNumber", PatientInstance.TelephoneNumber),
                new SqlParameter("@Email", PatientInstance.Email),
                new SqlParameter("@NOKFirstName", PatientInstance.NOKFirstName),
                new SqlParameter("@NOKLastName", PatientInstance.NOKLastName),
                new SqlParameter("@NOKRelation", PatientInstance.NOKRelation),
                new SqlParameter("@NOKCNICNumber", PatientInstance.NOKCNICNumber),
                new SqlParameter("@NOKCellNumber", PatientInstance.NOKCellNumber),
                new SqlParameter("@Title", PatientInstance.Title.ToString()),
                new SqlParameter("@PicturePath", (PatientInstance.PicturePath==null?"":PatientInstance.PicturePath)),
                returnCode
                );
                return (int)returnCode.Value;
            }
            catch (Exception exp)
            {
                var me = exp.Message;
                return -1;
            }
        }
    }
相关文章：