SqlDataReader skipping results in some result sets

Question

I am serializing results from data reader into JSON inside WEB API application. I am not using predefined classes to hold data from the reader. Resulting JSON is being compared with results from sql server management studio. Same stored procedure with the same parameters is being executed in both cases. There are 8 result sets. 5th result set, which should have 20 rows is returning 0 results. 7th result set contains 2 rows instead of 8.

In both cases, I'm using code found here on SO, which I changed a little bit: 1st case - creating object for serialization manually:

public JsonResult<Dictionary<string, List<Dictionary<string, object>>>> Get()
    {
        Dictionary<string, List<Dictionary<string, object>>> r = null;
        using (var conn = new SqlConnection(_connString))
        {
            using (var command = new SqlCommand("getReportData", conn) { CommandType = CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@DateFrom", new DateTime(2014, 6, 1, 0, 0, 0));
                command.Parameters.AddWithValue("@DateTo", new DateTime(2014, 6, 1, 0, 0, 0));
                command.Parameters.AddWithValue("@UserIDList", "1,2,3,4,5,6,7");
                conn.Open();
                command.ExecuteNonQuery();
                reader = command.ExecuteReader();
                r = Serialize(reader);
            }
        }

        return Json(r, new JsonSerializerSettings {Formatting = Formatting.Indented});
    }

public Dictionary<string, List<Dictionary<string, object>>> Serialize(SqlDataReader reader)
    {
        string resultSetName = "resultSet_";
        int resultSetCount = 1;

        var sets = new Dictionary<string, List<Dictionary<string, object>>>();
        var results = new List<Dictionary<string, object>>();

        var cols = new List<string>();
        for (var i = 0; i < reader.FieldCount; i++)
            cols.Add(reader.GetName(i));

        while (reader.Read())
            results.Add(SerializeRow(cols, reader));

        sets.Add(resultSetName + resultSetCount, results);

        while (reader.NextResult())
        {

            resultSetCount++;
            var resultsTmp = new List<Dictionary<string, object>>();
            var colsTmp = new List<string>();

            for (var i = 0; i < reader.FieldCount; i++)
            {
                colsTmp.Add(reader.GetName(i));
            }

            while (reader.Read())
            {
                resultsTmp.Add(SerializeRow(colsTmp, reader));
            }

            var setTmp = new Dictionary<string, List<Dictionary<string, object>>>();

            sets.Add(resultSetName + resultSetCount, resultsTmp);
        }

        return sets;
    }
    private Dictionary<string, object> SerializeRow(IEnumerable<string> cols,
                                                    SqlDataReader reader)
    {
        var result = new Dictionary<string, object>();
        foreach (var col in cols)
            result.Add(col, reader[col]);

        _loopRowCount++;
        return result;
    }

2nd case - using data adapter, composing dictionary and serializing result to json:

public JsonResult<Dictionary<string, DataTable>> Get()
    {
        string setName = "resultSet_";
        int setCount = 1;
        var dataTables = new Dictionary<string, DataTable>();
        using (var connection = new SqlConnection(_connString))
        {
            using (var command = new SqlCommand("getReportData", connection) { CommandType = CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@DateFrom", new DateTime(2014, 6, 1, 0, 0, 0));
                command.Parameters.AddWithValue("@DateTo", new DateTime(2014, 6, 1, 0, 0, 0));
                command.Parameters.AddWithValue("@UserIDList", "1,2,3,4,5,6,7");
                connection.Open();

                var adapter = new SqlDataAdapter(command);
                var set = new DataSet();
                adapter.SelectCommand = command;

                adapter.Fill(set);

                foreach (DataTable t in set.Tables)
                {
                    dataTables.Add(setName + setCount, t);
                    setCount++;
                }
            }
        }

        return Json(dataTables, new JsonSerializerSettings { Formatting = Formatting.Indented });
    }

Here is the code that executes stored procedure in SSMS

USE [DB]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[getReportData]
        @DateFrom = '2013-06-01 00:00:00',
        @DateTo = '2013-06-30 00:00:00',
        @UserIDList = '1,2,3,4,5,6,7'

SELECT  'Return Value' = @return_value
GO

Both methods miss exactly the same data while executing stored procedure clearly returns more data. I repeat, same stored procedure, same parameters in all three cases.

One odd thing is that production code uses this stored procedure but maps data from the reader to the predefined classes and no data is missing.

Why would reader "ignore" data that is obviously returned by procedure and how to prevent this situation ?

Answer 1

From your posted code, the values of @DateFrom and @DateTo are different when executed in SSMS vs. C#.