json message:
{"MessageHeader":{"MessageID":"2a149121-b7b7-4d93-80a1-8f99df730a22","MessageTimeStamp":"2021-02-03T08:13:52.4597917Z","MessageType":"Fee","SenderID":"ARK-US","ReceiverID":"Parents"},"Response":{"SchoolID":"133997360","Type":"College","Region":"Arkansas","Students":[{"StudentID":"ZXH3Y9W","Firstname":"Test","LastName":"","Class":1,"Section":"II"}]}}
when i run the stored procedure from SQL server I am able to see the message as it is, but when it is executed from c# class file using ef core the message is saving in db as below.
{"MessageID":"2a149121-b7b7-4d93-80a1-8f99df730a22","MessageTimeStamp":"2021-02-03T08:13:52.4597917Z","MessageType":"Fee","SenderID":"ARK-US","ReceiverID":"Parents"},"Response":{"SchoolID":"133997360","Type":"College","Region":"Arkansas","Students":[{"StudentID":"ZXH3Y9W","Firstname":"","LastName":"","Class":1,"Section":"II"}]}}
Firstname is missing.
Procedure :
ALTER PROCEDURE [dbo].[USP_UpdateStudentConfig]
-- Add the parameters for the stored procedure here
@Id BIGINT,
@ResponseMessage NVARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--------------------------------------------------------------------------
---------------------------------------------------------------------------
UPDATE
Student
SET
ResponseMessage = @ResponseMessage
,ModifiedOn = GETUTCDATE()
,ModifiedBy = 'Response-Service'
,ProcessingStatusId = 4
,CorrelationID = NULL
WHERE ID = @Id
END
inputs : Responsemessage as the json with full name, 29 as the identity column as below.
exec [USP_UpdateStudentConfig] 29,'{"MessageHeader":{"MessageID":"2a149121-b7b7-4d93-80a1-8f99df730a22","MessageTimeStamp":"2021-02-03T08:13:52.4597917Z","MessageType":"Fee","SenderID":"ARK-US","ReceiverID":"Parents"},"Response":{"SchoolID":"133997360","Type":"College","Region":"Arkansas","Students":[{"StudentID":"ZXH3Y9W","Firstname":"Test","LastName":"","Class":1,"Section":"II"}]}}'
public void UpdateConfigMessageQueue(string Resp, long Id)
{
var Response = new SqlParameter("@Response", Resp);
configResp.IsNullable = true;
var ConfigId = new SqlParameter("@ID", Id);
ConfigId.IsNullable = true;
_gCSFDbContext.Database.ExecuteSqlCommand("USP_UpdateStudentConfig @ID,@Response", ConfigId , Response );
}
can anyone help me understanding what could be the issue ?
What I have tried:
initially, the procedure is not working in SQL Server itself so I had to remove the unnecessary parameters in the procedure to make it work from the SQL server.
Earleir :
ALTER PROCEDURE [dbo].[USP_UpdateStudentConfig]
-- Add the parameters for the stored procedure here
@Id BIGINT,
@ResponseMessage NVARCHAR(MAX) ,
@messageid = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--------------------------------------------------------------------------
---------------------------------------------------------------------------
UPDATE
Student
SET
ResponseMessage = @ResponseMessage
,ModifiedOn = GETUTCDATE()
,ModifiedBy = 'Response-Service'
,ProcessingStatusId = 4
,CorrelationID = @messageid
WHERE ID = @Id
END
now :
<pre>ALTER PROCEDURE [dbo].[USP_UpdateStudentConfig]
-- Add the parameters for the stored procedure here
@Id BIGINT,
@ResponseMessage NVARCHAR(MAX)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--------------------------------------------------------------------------
---------------------------------------------------------------------------
UPDATE
Student
SET
ResponseMessage = @ResponseMessage
,ModifiedOn = GETUTCDATE()
,ModifiedBy = 'Response-Service'
,ProcessingStatusId = 4
,CorrelationID = NULL
WHERE ID = @Id
END