Try to search your question here, if you can't find : Ask Any Question Now ?

SQL Server stored procedure returns -1 using Dapper in ASP.Net Core

HomeCategory: stackoverflowSQL Server stored procedure returns -1 using Dapper in ASP.Net Core
Avatarsourav asked 4 months ago

I have an ASP.Net Core console application where I am calling a SQL Server stored procedure using Dapper. It inserts a row into the table.

I always get a -1 returned (as a result of the INSERT command).

Can’t determine why. Any ideas?

However, I can execute the stored procedure in SSMS successfully – just fine.
The executing in SSMS and the results:

DECLARE @PaymentIDOUT int, @ReturnCode int

EXECUTE @ReturnCode = PaymentDetailsInsertSingleItem  0, 4, 6, 'Cash', '2019-03-25T18:39:14.433', 5393, 1, 1, 'A', 1, @PaymentIDOUT OUTPUT

SELECT @PaymentIDOUT
SELECT @ReturnCode

enter image description here

Here’s the Table:

CREATE TABLE [dbo].[PaymentDetails]
(
    [PaymentID] [bigint] IDENTITY(1,1) NOT NULL,
    [PlanID] [int] NULL,
    [WorkouttypeID] [int] NULL,
    [Paymenttype] [nvarchar](50) NULL,
    [PaymentFromdt] [datetime] NULL,
    [PaymentTodt] [datetime] NULL,
    [PaymentAmount] [numeric](18, 0) NULL,
    [NextRenwalDate] [datetime] NULL,
    [CreateDate] [datetime] NULL,
    [Createdby] [int] NULL,
    [ModifyDate] [datetime] NULL,
    [ModifiedBy] [int] NULL,
    [RecStatus] [char](1) NULL,
    [MemberID] [bigint] NULL,
    [MemberNo] [nvarchar](30) NULL,

    CONSTRAINT [PK_PaymentDetails] 
        PRIMARY KEY CLUSTERED ([PaymentID] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Here’s the ASP.Net Core console app code:

using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace Tester
{
    class Program
    {
        static void Main(string[] args)
        {
            bool result;

            Console.WriteLine("Dan here in Tester."); 
            result = RenewalPayment();

            Console.WriteLine("Result: " + result);
            Console.ReadLine();
        }

        public static bool RenewalPayment()
        {
            var databaseConnection = "Server=  
 (localdb)\mssqllocaldb;Database=DBGymAngular7NetcoreEF;
Trusted_Connection=True;MultipleActiveResultSets=true";

            using (var con = new SqlConnection(databaseConnection))
            {
                con.Open();

                // Transaction.
                var sqlTransaction = con.BeginTransaction();

                var storedProcparameters = new DynamicParameters();

                storedProcparameters.Add("@PaymentID", 0);
                storedProcparameters.Add("@PlanID", 4);
                storedProcparameters.Add("@WorkouttypeID", 6);
                storedProcparameters.Add("@Paymenttype", "Cash");
                storedProcparameters.Add("@PaymentFromdt",
  "2019-03-25T18:39:14.433");
                storedProcparameters.Add("@PaymentAmount", 5393);
                storedProcparameters.Add("@CreateUserID", 1);
                storedProcparameters.Add("@ModifyUserID", 1);
                storedProcparameters.Add("@RecStatus", "A");
                storedProcparameters.Add("@MemberID", 1);
                storedProcparameters.Add("@PaymentIDOUT", dbType:
 DbType.Int32, direction: ParameterDirection.Output);

                int resultPayment = 
con.Execute("PaymentDetailsInsertSingleItem", storedProcparameters, 
sqlTransaction, 0, CommandType.StoredProcedure);

                if (resultPayment > 0)
                {
                    sqlTransaction.Commit();
                    int paymentId = storedProcparameters.Get<int> 
("PaymentIDOUT");
                    return true;
                }
                else
                {
                    sqlTransaction.Rollback();
                    return false;
                }
            }
        }
    }
}

enter image description here

Here’s the stored procedure:

IF EXISTS (SELECT * FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'[dbo].[PaymentDetailsInsertSingleItem]') 
              AND type in (N'P', N'PC'))
BEGIN
    DROP PROCEDURE [dbo].PaymentDetailsInsertSingleItem
END
GO

CREATE procedure [dbo].[PaymentDetailsInsertSingleItem]
    @PaymentID        BIGINT = 0,
    @PlanID           INT = NULL,
    @WorkouttypeID    INT = NULL,
    @Paymenttype      NVARCHAR(50) = NULL,
    @PaymentFromdt    DATETIME = NULL,
    @PaymentAmount    NUMERIC(18, 0) = NULL,
    @CreateUserID     INT = NULL,
    @ModifyUserID     INT = NULL,
    @RecStatus        CHAR(1) = NULL,
    @MemberID         BIGINT = NULL,
    @PaymentIDOUT     INT OUTPUT
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @ReturnValue    INT
   DECLARE @period         INT
   DECLARE @PaymentTodt    DATETIME
   DECLARE @tempdate       DATETIME
   DECLARE @NextRenwalDate DATETIME

   IF (@PaymentID = 0)  
      BEGIN
        SET @period = ( SELECT PeriodID
                        FROM dbo.PlanMaster
                        WHERE PlanID = @PlanID )

       SET @tempdate = @PaymentFromdt
       SET @PaymentTodt = DATEADD(mm, @period, @PaymentFromdt)
       SET @NextRenwalDate = DATEADD(mm, @period, @PaymentFromdt)

       INSERT INTO dbo.PaymentDetails (
         PlanID
         ,WorkouttypeID
         ,Paymenttype
         ,PaymentFromdt
         ,PaymentTodt
         ,PaymentAmount
         ,NextRenwalDate
         ,CreateDate
         ,Createdby
         ,ModifyDate
         ,ModifiedBy
         ,RecStatus
         ,MemberID
         ,MemberNo
        )
        VALUES ( @PlanID
         ,@WorkouttypeID
         ,@Paymenttype
         ,@PaymentFromdt
         ,@PaymentTodt
         ,@PaymentAmount
         ,@NextRenwalDate
         ,GETDATE()
         ,@CreateUserID
         ,NULL
         ,@ModifyUserID
         ,@RecStatus
         ,@MemberID
         ,NULL )

         IF (@@ERROR != 0)
            BEGIN
              SET @PaymentIDOUT = @@ERROR
              SELECT @@ERROR
            END
        ELSE
            BEGIN
              SET @PaymentIDOUT = SCOPE_IDENTITY()
              SELECT 0
            END
      END
END
1 Answers
Best Answer
AvatarAmit answered 4 months ago
Your Answer

18 + 12 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com