TLDR; Is there a way (using a type map or some other solution) to give dynamic result sets a default name, such as "(No Column Name)" in Dapper when no column name is supplied?
I am writing a query editor that allows users to write and run user-supplied queries against MS SQL Server databases. I've been using Dapper for all our querying and it has been working beautifully for 99% of what we need. I've hit a snag though and I'm hoping someone has a solution.
The query editor is similar to SSMS. I don't know ahead of time what the script will look like, what the shape or type the result set(s) will be, or even how many result sets will be returned. For this reason, I've been batching the scripts and using Dapper's QueryMultiple to read dynamic results from the GridReader. The results are then sent to a third party UI data grid (WPF). The data grid knows how to consume dynamic data and the only thing it requires to display a given row is at least one key value pair with a non-null, but not necessarily unique key and a nullable value. So far, so good.
The simplified version of the Dapper call looks something like this:
public async Task<IEnumerable<IEnumerable<T>>> QueryMultipleAsync<T>(string sql,
object parameters,
string connectionString,
CommandType commandType = CommandType.Text,
CancellationTokenSource cancellationTokenSource = null)
{
using (IDbConnection con = _dbConnectionFactory.GetConnection(connectionString))
{
con.Open();
var transaction = con.BeginTransaction();
var sqlBatches = sql
.ToUpperInvariant()
.Split(new[] { " GO ", "\r\nGO ", "\n\nGO ", "\nGO\n", "\tGO ", "\rGO "}, StringSplitOptions.RemoveEmptyEntries);
var batches = new List<CommandDefinition>();
foreach(var batch in sqlBatches)
{
batches.Add(new CommandDefinition(batch, parameters, transaction, null, commandType, CommandFlags.Buffered, cancellationTokenSource.Token));
}
var resultSet = new List<List<T>>();
foreach (var commandDefinition in batches)
{
using (GridReader reader = await con.QueryMultipleAsync(commandDefinition))
{
while (!reader.IsConsumed)
{
try
{
var result = (await reader.ReadAsync<T>()).AsList();
if (result.FirstOrDefault() is IDynamicMetaObjectProvider)
{
(result as List<dynamic>).ConvertNullKeysToNoColumnName();
}
resultSet.Add(result);
}
catch(Exception e)
{
if(e.Message.Equals("No columns were selected"))
{
break;
}
else
{
throw;
}
}
}
}
}
try
{
transaction.Commit();
}
catch (Exception ex)
{
Trace.WriteLine(ex.ToString());
if (transaction != null)
{
transaction.Rollback();
}
}
return resultSet;
}
}
public static IEnumerable<dynamic> ConvertNullKeysToNoColumnName<dynamic>(this IEnumerable<dynamic> rows)
{
foreach (var row in rows)
{
if (row is IDictionary<string, object> rowDictionary)
{
if (rowDictionary == null) continue;
rowDictionary.Where(x => string.IsNullOrEmpty(x.Key)).ToList().ForEach(x =>
{
var val = rowDictionary[x.Key];
if (x.Value == val)
{
rowDictionary.Remove(x);
rowDictionary.Add("(No Column Name)", val);
}
else
{
Trace.WriteLine("Something went wrong");
}
});
}
}
return rows;
}
This works with most queries (and for queries with only one unnamed result column), but the problem manifests when the user writes a query with more than one unnamed column like this:
select COUNT(*), MAX(create_date) from sys.databases.
In this case, Dapper returns a DapperRow that looks something like this:
{DapperRow, = '9', = '2/14/2020 9:51:54 AM'}
So the result set is exactly what the user asks for (i.e., values with no names or aliases) but I need to supply (non-unique) keys for all data in the grid...
My first thought was to simply change the null keys in the DapperRow object to a default value (like '(No Column Name)'), as it appears to be optimized for storage so table keys are only stored once in the object (which is nice and would provide a nice performance bonus for queries with huge result sets). The DapperRow type is private though. After searching around, I found that I could cast the DapperRow to an IDictionary<string, object> to access keys and values for the object, and even set and remove values. That's where the ConvertNullKeysToNoColumnName extension method comes from. And it works... But only once.
Why? Well, it appears that when you have multiple null or empty keys in a DapperRow that gets cast to an IDictionary<string,object> and you call the Remove(x) function (where x is the entire item OR just the key for any single item with a null or empty key), all subsequent attempts to resolve other values with a null or empty key via the indexer item[key] fail to retrieve a value--even if the additional key value pairs still exist in the object.
In other words, I can't remove or replace subsequent empty keys after the first one is removed.
Am I missing something obvious? Do I just need to alter the DapperRow via reflection and hope it doesn't have any weird side affects or that the underlying data structure doesn't change later? Or do I take the performance/memory hit and just copy/map the entire potentially large result set into a new sequence to give empty keys a default value at runtime?
I suspect this is because the dynamic DapperRow object is actually not a 'normal' dictionary. It can have several entries with the same key. You can see this if you inspect the object in the debugger.
When you reference rowDictionary[x.Key], I suspect you will always get the first unnamed column.
If you call rowDictionary.Remove(""); rowDictionary.Remove("");, you actually only remove the first entry - the second is still present, even though rowDictionary.ContainsKey("") returns false.
You can Clear() and rebuild the entire dictionary.
At that point, you're really not gaining much by using a dynamic object.
if (row is IDictionary<string, object>)
{
var rowDictionary = row as IDictionary<string, object>;
if (rowDictionary.ContainsKey(""))
{
var kvs = rowDictionary.ToList();
rowDictionary.Clear();
for (var i = 0; i < kvs.Count; ++i)
{
var kv = kvs[i];
var key = kv.Key == ""? $"(No Column <{i + 1}>)" : kv.Key;
rowDictionary.Add(key, kv.Value);
}
}
}
Since you're working with unknown result structure, and just want to pass it to a grid view, I would consider using a DataTable instead.
You can still keep Dapper for parameter handling:
foreach (var commandDefinition in batches)
{
using(var reader = await con.ExecuteReaderAsync(commandDefinition)) {
while(!reader.IsClosed) {
var table = new DataTable();
table.Load(reader);
resultSet.Add(table);
}
}
}