How to retrieve complete a big json from sql server and not chunks in C#?

Question

I have a problem with SQL Server and C# ASP.NET MVC, first, I create a stored procedure that retrieves a json from database this works correctly when I use EXEC procedure name in Management Studio.

Stored procedure result (intentionally not formatted):

[{"Id":1,"Nombre":"Aguascalientes","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":2,"Nombre":"Baja California","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":3,"Nombre":"Baja California Sur","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":4,"Nombre":"Campeche","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":5,"Nombre":"Coahuila","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":6,"Nombre":"Colima","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":7,"Nombre":"Chiapas","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":8,"Nombre":"Chihuahua","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":9,"Nombre":"Distrito Federal","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":10,"Nombre":"Durango","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":11,"Nombre":"Guanajuato","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":12,"Nombre":"Guerrero","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":13,"Nombre":"Hidalgo","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":14,"Nombre":"Jalisco","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":15,"Nombre":"México","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":16,"Nombre":"Michoacán","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":17,"Nombre":"Morelos","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":18,"Nombre":"Nayarit","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":19,"Nombre":"Nuevo León","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":20,"Nombre":"Oaxaca","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":21,"Nombre":"Puebla","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":22,"Nombre":"Querétaro","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":23,"Nombre":"Quintana Roo","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":24,"Nombre":"San Luis Potosí","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":25,"Nombre":"Sinaloa","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":26,"Nombre":"Sonora","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":27,"Nombre":"Tabasco","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":28,"Nombre":"Tamaulipas","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":29,"Nombre":"Tlaxcala","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":30,"Nombre":"Veracruz","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":31,"Nombre":"Yucatán","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":32,"Nombre":"Zacatecas","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}}]

Check the result completes the array with the square bracket. But when I execute that query using this method in C#:

    public async Task<List<T>> ListAsync<T>(Type tclass) where T : class, new ()
    {
        var connString = _connectionSettings.ConnString;

        using (_conn = new SqlConnection(connString))
        {
            try
            {
                await _conn.OpenAsync();

                _command = new SqlCommand($"List{tclass.Name}", _conn)
                {
                    CommandType = CommandType.StoredProcedure
                };

                _command.Parameters.Clear();

                using (_dataReader = await _command.ExecuteReaderAsync())
                {
                    List<T> data = null;

                    while (await _dataReader.ReadAsync())
                    {
                        data = JsonConvert.DeserializeObject<List<T>>(_dataReader[0].ToString());
                    }

                    return data ?? null;
                }
            }
            catch (Exception exception)
            {
                log.Error(exception.Message, exception);
                return null;
            }
        }
    }

I only obtain this from json (intentionally not formatted):

[{"Id":1,"Nombre":"Aguascalientes","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":2,"Nombre":"Baja California","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":3,"Nombre":"Baja California Sur","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":4,"Nombre":"Campeche","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":5,"Nombre":"Coahuila","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":6,"Nombre":"Colima","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":7,"Nombre":"Chiapas","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":8,"Nombre":"Chihuahua","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":9,"Nombre":"Distrito Federal","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":10,"Nombre":"Durango","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":11,"Nombre":"Guanajuato","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":12,"Nombre":"Guerrero","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":13,"Nombre":"Hidalgo","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":14,"Nombre":"Jalisco","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":15,"Nombre":"México","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":16,"Nombre":"Michoacán","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":17,"Nombre":"Morelos","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":18,"Nombre":"Nayarit","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":19,"Nombre":"Nuevo León","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":20,"Nombre":"Oaxaca","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":21,"Nombre":"Puebla","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero":484}},{"Id":22,"Nombre":"Querétaro","Pais":{"Id":"MX","Nombre":"México","ISO":"MEX","Numero

A invalid json, the json is not complete and newtonsoft make this invalid (Is obvious) but why the data is chunked?

This line `return data ?? null;` is redundant. You're [checking if data is null and returning null if it actually is null](https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/operators/null-coalescing-operator) `return data;` will do exactly the same. — ikerbera, Dec 04 '18 at 15:29
@ikerbera Ok, thats redundant no problem, the problem is the error above, i can't reach that point because json throw an exception. — Victor, Dec 04 '18 at 15:31
Your string is 2033 characters long; this is a magic number. Both XML and JSON results are returned in chunks when they exceed this length, and require multiple `.Read` calls to stitch together (as if the data was in multiple rows). Use `ExecuteReaderAsync(CommandBehavior.SequentialAccess)` and `.GetTextReader(0)` to stream. For XML there's an `ExecuteXmlReader` method to simplify this, for JSON you have to do the heavy lifting yourself. — Jeroen Mostert, Dec 04 '18 at 15:33
@Damien_The_Unbeliever The length of the strings in C# is 2033 and the result of the procedure is 2970 characters — Victor, Dec 04 '18 at 15:33
@JeroenMostert Ok I put CommandBehavior.SequentialAccess, but how to retrieve GetTextReader(0) like string?, with a simple cast? — Victor, Dec 04 '18 at 15:38
You'll have to loop and call `textReader.ReadAsync` on a buffer -- or you can use `.ReadToEnd()`, but you still have to add the results to a `StringBuilder` and wrap it in a `while (_dataReader.ReadAsync())` loop to get everything because you'll get chunks. Even that is not very efficient since you could feed the data directly to the JSON deserializer. A little wrapper class for this to turn the `DataReader` weirdness into a regular stream would come in handy, so you can have an `SqlCommand.ExecuteTextReader` / `ExecuteJsonReader` extension method, but I'm too lazy to write one right now. — Jeroen Mostert, Dec 04 '18 at 15:45
@JeroenMostert I can't access to SqlComand.ExecuteTextReader in SQL command. — Victor, Dec 04 '18 at 15:51
Yes, because there's no such thing. I suggested to write one. :-) Only `SqlDataReader.GetTextReader` exists at the moment. Interestingly, if you already *have* the exact correct JSON you would like to stream to the client this way, deserializing it first (and then re-serializing) is needless overhead! You can just stream the results directly with a custom `IActionResult` that 1) reads a buffer from the `TextReader` and 2) writes this buffer directly to the `HttpContext.Response.Body`. — Jeroen Mostert, Dec 04 '18 at 15:54
Conversely, if you do *not* have the correct result immediately but need some additional processing, then reading it from SQL Server as JSON is needless overhead -- TDS (the native protocol) is far more efficient. Read the rows directly into objects without converting them to JSON first, with an ORM like Dapper. — Jeroen Mostert, Dec 04 '18 at 15:56
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/184705/discussion-between-victor-and-jeroen-mostert). — Victor, Dec 04 '18 at 16:07
You are reading Async and not waiting for all the data to get returned. — jdweng, Dec 04 '18 at 16:08
@jdweng: that's what the `await`s are for. Not the problem in this case. — Jeroen Mostert, Dec 04 '18 at 16:15
The method is async so isn't the method going to return before everything is finhished? — jdweng, Dec 04 '18 at 16:26
Possible duplicate of [FOR JSON path returns less number of Rows on AZURE SQL](https://stackoverflow.com/questions/43951119/for-json-path-returns-less-number-of-rows-on-azure-sql) — David Browne - Microsoft, Dec 04 '18 at 16:41
I see that answer, but how can return the declared variable nvarchar in the stored procedure? @DavidBrowne-Microsoft — Victor, Dec 04 '18 at 17:58
If you want the JSON in an NVarchar(max) parameter, then set it like this `set @json = (select * from sys.objects for json auto) ` and bind a SqlParameter named "@json" of type nvarchar(max). You could use a stored procedure output parameter too, but it doesn't sound like that's what the procedure does. — David Browne - Microsoft, Dec 04 '18 at 18:07
`CREATE PROCEDURE ListState AS BEGIN DECLARE @JSON NVARCHAR(MAX) SET @JSON = ( SELECT State.Id, State.Nombre, JSON_QUERY((SELECT * FROM Country WHERE Country.Id = State.Pais FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Pais FROM State FOR JSON AUTO); END GO EXEC ListState;` Not working, only says command execute succesfully, can help me with this? @DavidBrowne-Microsoft — Victor, Dec 04 '18 at 18:16

score 2 · Accepted Answer · answered Dec 04 '18 at 18:24

If you want to return a variable from a stored procedure, instead of a resultset, then use an output parameter:

CREATE PROCEDURE ListState @JSON nvarchar(max) out
AS 
BEGIN 
  SET @JSON = ( SELECT State.Id, State.Nombre, JSON_QUERY((SELECT * FROM Country WHERE Country.Id = State.Pais FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Pais FROM State FOR JSON AUTO); 

END 
GO

And access the value from a SqlParameter on the client.

Or return the value in a single-row, single-column resultset

CREATE PROCEDURE ListState 
AS 
BEGIN 
  SET @JSON = ( SELECT State.Id, State.Nombre, JSON_QUERY((SELECT * FROM Country WHERE Country.Id = State.Pais FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS Pais FROM State FOR JSON AUTO); 
  select @JSON doc;
END 
GO

And access the value from SqlCommand.ExecuteScalar() or SqlCommand.ExecuteReader() on the client.

Thank you so much for helping me! – Victor Dec 04 '18 at 18:30 — Victor, Dec 04 '18 at 18:30

How to retrieve complete a big json from sql server and not chunks in C#?

1 Answers1