0

My C# ExecuteSqlCommand is not returning output from stored procedure.

I actually need last inserted row's Id

Here is my Stored Proceedure

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

I have tested this stored proceedure and it is working perfectly in SQL Server Management Studio

Here Is Stored Procedure Execution Image as I am using SCOPE_IDENTITY() and returning it as OUTPUT throught @LastCreatedId

You can see in image data is inserting perfectly in database but in C# I can send data correctly to database but can not retentive OUTPUT data of @LastCreatedId

And here is my C# code to use this stored procedure

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;
                }
            }
        }

I get {} value from returnCode.Value And if I try to cast it to integer it gives me exception Specified cast is not valid.

What should I do to possibly retrieve value of @LastCreatedId as OUTPUT value from the Stored Procedure

1
  • 1
    You probably want Database.ExecuteReader, not Database.ExecuteSqlCommand Commented Aug 26, 2016 at 7:05

3 Answers 3

1

Just add

SELECT @LastCreatedId = CAST(SCOPE_IDENTITY() as int) 

in stored procedure.. Also do check it contains the int value using the below cast operation

if (returnCode.Value != DBNull.Value) 
return Convert.ToInt32(returnCode.Value);
Sign up to request clarification or add additional context in comments.

Comments

1

Since you are basically trying to get ID via SCOPE_IDENTITY() why not try using: ExecuteScalar().

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

I took the answer from the post below: Return value from SQL Server Insert command using c#

Cheers!

1 Comment

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.
1

I had the same issue, Try adding the word OUTPUT after the parameter is mentioned in the ExecuteSQLCommand call like this

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

Your full code would be like:

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;
            }
        }
    }

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.