C# & SQL Server query returning null columns on large table

Viewed 788

I'm running the following query on a table that has about 600 columns:

SELECT * 
FROM Table 
WHERE [Owner] = 1234

I am running this query using EF code-first and Dapper and I'm hitting the same problem with both.

In particular many rows that DO have a value are returned as DBNull from the query (I verified using SQL Server Management Studio that the columns have data). Curiously enough, it only happens when requesting all the columns (whether using * or pulling them explicitly).

For example, if column Status has value "A", the query returns DBNull as its value. But if instead of the above code (that pulls the 600 columns) I use this query:

SELECT [Status] 
FROM Table 
WHERE [Owner] = 1234

The Status column is correctly populated.

Here is the Dapper code I'm using to process the results:

public IList<Dictionary<string, string>> GetData() {
    var sql = "SELECT * FROM Table WHERE [Owner] = 1234";
    var cn = new SqlConnection(serverConnectionString);
    var rows = new List<Dictionary<string, string>>();

    using (var reader = cn.ExecuteReader(sql))
    {
        while (reader.Read())
        {
            var dict = new Dictionary<string, string>();

            for (var i = 0; i < reader.FieldCount; i++)
            {
                var propName = reader.GetName(i).ToLowerInvariant();

                // This is set to DBNull for most, but not all,
                // columns if querying the ~600 columns in the Table
                var propValue = reader.GetValue(i); 

                dict[propName] = propValue?.ToString();
            }

            rows.Add(dict);
        }
    }

    return rows;
}

I can't make head or tail of this behavior. Any help would be greatly appreciated.

1 Answers
Related