CODEWITHSUNDEEP
sqljsonsql-serverstored-proceduresazure-sql-database
Prem Raj
Prem Raj

Reputation: 1

Is it possible to replace the whole JSON in a column with a new JSON

I have a code which executes a script where I want to update a column in a table with a completely new JSON data. I know it can be also done using Json_Modify but I want to know if it is possible to replace the old JSON data with new.

Code which executes the script as a raw SQL query:

public async Task Run(Args args)
{
    var scrPath = Path.Combine(Directory.GetCurrentDirectory(), "FolderName", args.subFolderName);

    var dataChangeSqlPath = Path.Combine(scrPath, args.DataChangeSqlFileName);
    var query = await this.GetSql(dataChangeSqlPath);
    await this.db.Database.ExecuteSqlRawAsync(query);
}

private async Task<string> GetSql(string path)
{
    var sql = await File.ReadAllTextAsync(path);
    return sql.ToString();
}

And this is the script it is trying to execute:

And this is the script it is trying to execute:

DECLARE @answers_json NVARCHAR(max);

BEGIN TRY   
    BEGIN TRANSACTION
        SELECT
             @answers_json = N'{"CONST_TRUE":{"type":"specified","boolValue":true}}'

        BEGIN
            UPDATE [dbo].[ColumnName] 
            SET  
               AnswersJson= @answers_json
            WHERE 
                Id=2 AND Number=1;

    END
    COMMIT
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
    SELECT
        @error_message = ERROR_MESSAGE(),
        @error_severity = ERROR_SEVERITY(),
        @error_state = ERROR_STATE();
    RAISERROR (@error_message, @error_severity, @error_state);
END CATCH

It throws and error:

System.FormatException: Input string was not in a correct format.

But when I execute the script directly in SQL Server then it works fine.

Any help/suggestion is much appreciated.

Upvotes: 0

Views: 322

Answers (1)

Madhuraj Vadde
Madhuraj Vadde

Reputation: 1227

Thank you Charlieface and Prem Raj. Posting your discussions as answer to help other community members.

Use correct parameterization which ExecuteRawSql supports. For example ExecuteRawSqlAsync("INSERT YourTable (col) VALUES ({0})", yourJsonVariable)

Upvotes: 0

Related Questions