Click here to Skip to main content
14,828,337 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
Posted
Updated 3-Feb-21 5:43am

1 solution

The property isn't missing; it's blank.

There's nothing in the code you've shown which would modify the JSON data.

The only explanation is that the string you're saving from your code does not match the string you're pasting into SQL Management Studio. Since we can't see how that string is generated, we can't tell you why.

You'll need to debug your code to try to find the problem.
   
Comments
raxhemanth 3-Feb-21 11:50am
   
I am deserializing and converting it into a string before passing it as a parameter to the Stored procedure.
while debugging the SQL value of the parameter is just like below:

{"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"}]}}

same as when I manually execute it in SQL Server.
Richard Deeming 3-Feb-21 11:54am
   
Then either you're looking at the wrong record, or you're looking at the wrong database.

Nothing in the code you've shown will modify the JSON data. Unless there's other code between SET NOCOUNT ON and UPDATE Student which you haven't shown, or there's a trigger on the Student table, there is no way one property from your JSON data will end up losing its value.
raxhemanth 3-Feb-21 12:21pm
   
That is what is happening. I am trying to redeploy the application to see if it can help to change the behavior.
Richard Deeming 3-Feb-21 12:23pm
   
As I said, the problem is not with the code that you've shown.

The fact that your JSON data is being altered but remaining valid means that it's highly unlikely that anything is corrupting the value.

There must be some other code that you haven't shown/found which is causing the problem.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900