How to serialize a List

Question

We have a very large List<object> that we are turning into a json string and then sending to a stored procedure. To keep things simple for this post, let's say the object looks like this:

public class Customer
{
    public string Name { get; set; }
}

And one of the customers is "Bob's Repair Shop". When the list with this value in it gets serialized, it will contain this:

{
    "Name": "Bob's Repair Shop"
}

That's ok, right up until we want to send this json into a stored procedure. When it gets there, that single quote throws an error (which I understand).

Originally we handled this by doing a .replace("'", "~") on the json string in the C# code, and then doing the reverse in the stored proc. That worked for a while. But what we are running into now is that the object we have a List<> of is much more complex that this little example, and we are also dealing with several hundred thousand records in the list. When the json string is this large, the .replace("'", "~") throws an Out of Range exception. And let's be honest, this .replace("'", "~") approach is a hack anyway.

What I'm hoping to find is a way to serialize our List<object> in such a way that the single quotes are escaped during the serialization process. Is this possible? And if so, how?

EDIT:

I really should have said this originally. Sorry I missed it. Here's how the stored proc is receiving the json string:

ALTER PROCEDURE [dbo].[name_of_proc]
    @jsonString NVARCHAR(MAX)
AS

And then we convert the @jsonString to a table variable with OPENJSON

EDIT 2:

how the sp is called:

    public void UpdateBulk(List<object> myObject)
    {
        string json = Newtonsoft.Json.JsonConvert.SerializeObject(myObject);
        json.replace("'", "~");
        var dParam = new DynamicParameters();
        dParam.Add("@jsonString", json);
        QuerySP<myObject>("name_of_proc", dParam);
    }

    protected IEnumerable<T> QuerySP<T>(string storedProcedure, object param = null)
    {
        using (var db = this.Context)
        {
            var output = db.Query<T>(storedProcedure, param: param, commandType: CommandType.StoredProcedure,commandTimeout: 32767);
            return output;
        }
    }

EDIT 3: The thread pointed to as a possible duplicate asks how to change this:

{"key" : "value"}

to this:

{'key' : 'value'}

That's not what I'm asking. I'm asking how to change this:

{
    "Name": "Bob's Repair Shop"
}

To this:

{
    "Name": "Bob\'s Repair Shop"
}

But that won't solve the `out of range` error when doing a `.replace()` on a massive string. — Casey Crookston, Aug 23 '19 at 14:36
Are you not using a SQL parameter to send the JSON to a sproc? — , Aug 23 '19 at 14:36
You should (always!) use SqlParameters: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters — Peter B, Aug 23 '19 at 14:36
Not a solution for performance, but sounds like the values need to be parameterized. — Hayden, Aug 23 '19 at 14:37
Ok, I shoudl have said this in the OP. I will clarify how we are sending to the stored proc. One second — Casey Crookston, Aug 23 '19 at 14:37
I edited the OP. Also... thank you for the duplicate points! (I promise I did look before posting and didn't find those!) — Casey Crookston, Aug 23 '19 at 14:42
@CaseyCrookston That's how the sproc receives it. How are you sending the JSON to the sproc? Are you doing `exec sprocname ''`? If so, you aren't using sql parameters to pass your data to the DB. I'm trying to see how the single quote would cause an error when you pass it as a parameter. — , Aug 23 '19 at 14:42
Show the code that executes the SP call. You're probably using string concatenation where you should use a parameterized query. — CodeCaster, Aug 23 '19 at 14:43
What are `DynamicParameters` and `QuerySP()`? That's not framework code. Show a [mre]. What everybody here is trying to get at, is that if you properly use parameterized queries, you don't to replace the single quotes. Problem here probably is that you're using homegrown SQL-generating code that messes stuff up. — CodeCaster, Aug 23 '19 at 14:49
@CodeCaster, We are using Dapper. And this is an example straight out of the Dapper documenation. Hardly "home grown". — Casey Crookston, Aug 23 '19 at 14:55
@CaseyCrookston My gut feeling is that some aspect of how you're passing the JSON to the sproc is incorrect. The single-quote shouldn't be an issue if its passed as a parameter properly. Maybe I'm wrong, but I feel confident there's an issue there. — , Aug 23 '19 at 14:57
`QuerySP` doesn't seem to be part of Dapper: https://github.com/StackExchange/Dapper/search?q=QuerySP&unscoped_q=QuerySP — BurnsBA, Aug 23 '19 at 15:01
@BurnsBA, you are right. I added the definition for `QuerySP` — Casey Crookston, Aug 23 '19 at 15:06
@Amy, you could be right. Although it really does seem like we are correctly using parameters here. I'll keep digging. But in the meantime... I'm still hoping for an answer to my actual question. Is it possible to handle special characters during the serialization process? — Casey Crookston, Aug 23 '19 at 15:07
So what's the actual error you get when you execute the SP with the original JSON? — CodeCaster, Aug 23 '19 at 15:08
@CaseyCrookston Well, I see nothing wrong with that. This is very puzzling. Thanks for humoring me for a bit :) — , Aug 23 '19 at 15:20
@CodeCaster: Msg 102, Level 15, State 1, Line 7 Incorrect syntax near 's'. Msg 105, Level 15, State 1, Line 7 Unclosed quotation mark after the character string '' — Casey Crookston, Aug 23 '19 at 15:20
I am trying to put together a simple, stripped down example in a console app — Casey Crookston, Aug 23 '19 at 15:20
Your desired output, `{"Name":"Bob\'s repair shop"}` would not be valid JSON - backslash needs to be escaped... do you want `{"Name":"Bob\\'s repair shop"}`? — steve16351, Aug 23 '19 at 15:23
But it says line 7. What's happening inside your SP, exactly? — CodeCaster, Aug 23 '19 at 15:31
@Amy, ok, I think you are on to something here. I made a super stripped-down example in a console app using "Bob's Repair Shop" and I am not getting the same error we are seeing in our production app. So you are correct that the JSON is not the problem. — Casey Crookston, Aug 23 '19 at 15:31
Thank you to everyone who commented here. You've helped me think through this more critically and realize I was barking up the wrong tree. — Casey Crookston, Aug 23 '19 at 15:33
@EJoshuaS, I don’t think this is a duplicate. That question is about enclosing keys and values with single rather than double quotes. This question is about escaping single quotes within values. — steve16351, Aug 24 '19 at 08:41

steve16351 · Accepted Answer · 2019-08-23T15:38:07.240

You could add a custom JSON converter to perform the replacement on individual string instances while the serialization is taking place. This is still using string.Replace, but all the string instances should be small at this point.

public class EscapeQuotes : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(string);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        var escapedValue = ((string)value).Replace("'", "\\'");
        writer.WriteValue(escapedValue);
    }
}

Usage:

Customer myObject = new Customer() { Name = "Bob's repair shop" };
var output = JsonConvert.SerializeObject(myObject, new EscapeQuotes());

Output:

{"Name":"Bob\\'s repair shop"}

Awesome Steve! I really appreciate you just answering my question. Love it. It turns out that the json isn't the problem. I don't know what is (yet) but none the less, I am grateful for this. — Casey Crookston, Aug 23 '19 at 15:34

1 Answers1