Reading output sql server stored procedure parameter in C#

Question

I am expecting two output ids from an inserts statements in SQL Server 2008. I believe I have done something wrong in initializing AddParam as its not inserting data. Stored Procedure itself, also code without parameter been tested and it worked.

Stored Procedure

USE [mydb]
GO

SET ANSI_NULLS ON 
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[p_assessment_dfn_statementAnswer_insert]
@StatementID AS bigint,
@StaffID AS int,
@AssessmentID As bigint,
@StatementText AS nvarchar(MAX), 
@StatementDate AS Date,
@StatementAnswerID AS bigint OUTPUT,
               --
@SignatureCheck AS bit,
@SignatureDate AS Date,
@ElectronicSignatureID AS bigint OUTPUT

 AS
 SET NOCOUNT ON 
 SET XACT_ABORT ON 

-- local variables
DECLARE @l_object    AS SYSNAME = OBJECT_NAME(@@PROCID)
   ,@l_error_msg AS NVARCHAR(2000)   


 BEGIN TRY

   BEGIN TRAN

 INSERT INTO [adb_TestDb].[dbo].[Assessment_Statement_Answer]
       ([StatementID],[StaffID],[AssessmentID],[StatementText],[Date])
 VALUES (@StatementID, @StaffID, @AssessmentID, @StatementText, @StatementDate)


        SET @StatementAnswerID = @@IDENTITY;

 IF(@StatementAnswerID>0)
 BEGIN
    INSERT INTO [adb_TestDb].[dbo].[Assessment_ElectronicSignature]([AssessmentID],[ElectronicSignatureCheck],[SignatureDateAndTime])
    VALUES (@AssessmentID, @SignatureCheck,@SignatureDate)

        SET @ElectronicSignatureID = @@IDENTITY;
 END


  IF(@ElectronicSignatureID>0)
  BEGIN
    INSERT INTO [adb_TestDb].[dbo].[AssessorSignature]([AssessmentElectronicSignatureID],[StatementAnswerID],[AssessorID])
    VALUES(@ElectronicSignatureID, @StatementAnswerID, @AssessmentID)

   END
 COMMIT TRAN 


END TRY

BEGIN CATCH

 -- rollback any open/uncomitted transactions
IF XACT_STATE() IN ( -1, 1) ROLLBACK TRANSACTION            

 -- return an error containing the object, error number and error description
SELECT @l_error_msg = 'Error number : ' + CAST(ERROR_NUMBER()AS VARCHAR) + '. ' + ERROR_MESSAGE()          
RAISERROR (@l_error_msg,16,1)

END CATCH

ViewModel

    public class AssessmentStatementAnswerViewModel
{
    public AssessmentStatementAnswerViewModel() { }


        public long StatementAnswerID { get; set; }

        public long StatementID { get; set; }

        public int StaffID { get; set; }

        public long AssessmentID { get; set; }

        public string StatementText { get; set; }

        public DateTime Date { get; set; }

        public string SubmitStatus { get; set; }
        //

        public long AssessmentElectronicSignatureID { get; set; }

        public bool ElectronicSignatureCheck { get; set; }

        public DateTime SignatureDateAndTime { get; set; }

}

C# class where I need help

 private static long InsertStatementAnswer_DF_P(ADB.Model.AssessmentStatementAnswerViewModel assessmentAnswerObj, MSSQL sql)
   {
       bool killConnection = Utils.getConnection(ref sql);

       long returnValue = 0;

       try
       {
           sql.SetSProc("[dbo].[p_assessment_dfn_statementAnswer_insert]");
           sql.AddParam("@StatementID", assessmentAnswerObj.StatementID);
           sql.AddParam("@StaffID", assessmentAnswerObj.StaffID);
           sql.AddParam("@AssessmentID", assessmentAnswerObj.AssessmentID);
           sql.AddParam("@StatementText", assessmentAnswerObj.StatementText);
           sql.AddParam("@StatementDate", assessmentAnswerObj.Date);              
           sql.AddParam("@SignatureCheck", assessmentAnswerObj.ElectronicSignatureCheck);
           sql.AddParam("@SignatureDate", assessmentAnswerObj.SignatureDateAndTime);

           sql.AddOutputParam("@StatementAnswerID", SqlDbType.BigInt, 0);
           sql.AddOutputParam("@ElectronicSignatureID", SqlDbType.BigInt, 0);


           sql.ExecuteNonQuery();


       }
       finally
       {
           if (killConnection)
               sql.Dispose();
       }

       return returnValue;
   }

Do you get an error message?

Daniel
– Daniel

2015-10-06 17:52:55 +00:00
Commented Oct 6, 2015 at 17:52 — Daniel
– Daniel, Commented Oct 6, 2015 at 17:52

Nikita Shrivastava · Accepted Answer · 2015-10-06 09:53:06Z

1

Set @StatementAnswerID and @ElectronicSignatureID as Output parameters :

cmd.Parameters("@StatementAnswerID").Direction = ParameterDirection.Output;
cmd.Parameters("@ElectronicSignatureID").Direction = ParameterDirection.Output;

Execute the Stored procedure. Read as follows:

cmd.Parameters("@StatementAnswerID").Value;
cmd.Parameters("@ElectronicSignatureID").Value;

answered Oct 6, 2015 at 9:53

Nikita Shrivastava

3,01812 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rakin · Accepted Answer · 2015-10-06 09:57:51Z

private static long InsertStatementAnswer_DF_P(ADB.Model.AssessmentStatementAnswerViewModel assessmentAnswerObj, MSSQL sql)
   {
       bool killConnection = Utils.getConnection(ref sql);

       long returnValue = 0;

       try
       {
           sql.SetSProc("[dbo].[p_assessment_dfn_statementAnswer_insert]");
           sql.AddParam("@StatementID", assessmentAnswerObj.StatementID);
           sql.AddParam("@StaffID", assessmentAnswerObj.StaffID);
           sql.AddParam("@AssessmentID", assessmentAnswerObj.AssessmentID);
           sql.AddParam("@StatementText", assessmentAnswerObj.StatementText);
           sql.AddParam("@StatementDate", assessmentAnswerObj.Date);              
           sql.AddParam("@SignatureCheck", assessmentAnswerObj.ElectronicSignatureCheck);
           sql.AddParam("@SignatureDate", assessmentAnswerObj.SignatureDateAndTime);

         //  sql.AddOutputParam("@StatementAnswerID", SqlDbType.BigInt, 0);
          //  sql.AddOutputParam("@ElectronicSignatureID", SqlDbType.BigInt, 0);
SqlParameter outPutParameter1 = new SqlParameter();
outPutParameter1 .ParameterName = “@StatementAnswerID”;
outPutParameter1 .SqlDbType = System.Data.SqlDbType.Int;
outPutParameter1 .Direction = System.Data.ParameterDirection.Output;
SqlParameter outPutParameter2 = new SqlParameter();
outPutParameter2 .ParameterName = “@ElectronicSignatureID”;
outPutParameter2 .SqlDbType = System.Data.SqlDbType.Int;
outPutParameter2 .Direction = System.Data.ParameterDirection.Output;
sql.Parameters.Add(outPutParameter2 );
sql.Parameters.Add(outPutParameter1 );
           sql.ExecuteNonQuery();
//Here Your Values
string ElectronicSignatureID= outPutParameter2 .Value.ToString();
string StatementAnswerID= outPutParameter1 .Value.ToString();


       }
       finally
       {
           if (killConnection)
               sql.Dispose();
       }

       return returnValue;
   }

Collectives™ on Stack Overflow

Reading output sql server stored procedure parameter in C#

Stored Procedure

ViewModel

C# class where I need help

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Stored Procedure

ViewModel

C# class where I need help

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related