0

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 ?

5
  • can you show how the stored procedure is called in SSMS? Commented Nov 8, 2014 at 20:55
  • Question is updated with the code sample that executes the stored procedure. Commented Nov 8, 2014 at 21:04
  • is that correct? the @DateFrom and @DateTo values are different (executed in SSMS vs. C#). Commented Nov 8, 2014 at 21:06
  • 1
    That is correct, I just went full retard. Thank you for noticing that. Please make an answer, I'll accept it. Commented Nov 8, 2014 at 21:12
  • No problem, we've all been there :) Commented Nov 8, 2014 at 21:18

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.