What is the best way to quickly serialize a DataReader without using DataTable (or using a DataTable that could have duplicate column names)

dapper datareader datatable

Question

We cache our queries extensively using a decorator that wraps IDbConnection/IDbCommand. When something calls 'ExecuteReader()' it uses the command.text as the cache key (e.g. SELECT * FROM Foo) and we're also able to apply tags to our decorators (e.g. new FooDbCommand("UserCacheTag")) to manage this. The Reader is converted to a DataTable to be plainly serializable, and converted back using the DataTables api (CreateDataReader).

All this works brilliantly, but there's a flaw.

If the incoming recordset has duplicate column names, as the IDataReader gets translated into a DataTable the duplicate column names are incremented with a number (e.g. 'PersonID1').

How can we make our IDataReader, which allows duplicate column names, cacheable (prefereably serializable) and then convert it back to an IDataReader with the original column names without worrying about column names being altered in between states?

Additional information:

We use Dapper primarily with the dynamic return type to avoid these issues. We'd like to start using the multimapping more. But if a type, say 'Person', is returned multiple times in one query, duplicate column names occur.

For instance:

SELECT pEmployee.*, pManager.* 
FROM Persons pEmployee 
LEFT OUTER JOIN Persons pManager ON pManager.PersonID = pEmployee.ManagerPersonID

Popular Answer

I accomplished this by creating my own DataTable class that stores the original column name into the 'Caption' property, and my own IDataReader that overwrites the GetName() method to look to the schema table. It's fairly straight-forward, but I sure do wish a DataTable simply had an 'AllowDuplicateColumnNames' property.

MyDataTable

When loading the DataReader, this will set the 'Caption' property to the original column name. When creating the DataReader, it will rename the 'ColumnName' property of the schematable back to the original name (currently stored in the Caption column).

internal class MyDataTable : DataTable
    {
        public MyDataTable()
        {

        }

        /// <summary>
        /// This is a helper method which accounts for duplicate column names.
        /// In the loading process, it will rewrite the caption to be the original column name
        /// which can be a duplicate.
        /// </summary>
        /// <param name="dataReader"></param>
        public void LoadIDataReader(IDataReader dataReader)
        {
            var schemaTable = dataReader.GetSchemaTable();

            base.Load(dataReader);

            for (var i = 0; i < schemaTable.Rows.Count; i++)
            {
                var originalColumnName = schemaTable.Rows[i]["ColumnName"] as string;
                var currentColumnName = this.Columns[i].ColumnName;

                if(originalColumnName != currentColumnName)
                {
                    this.Columns[i].Caption = originalColumnName;
                }
            }
        }

        public IDataReader CreateIDataReader()
        {
            var dataReader = base.CreateDataReader();

            var schemaTable = dataReader.GetSchemaTable();

            for(var i = 0; i < schemaTable.Rows.Count; i++)
            {
                //this is reverse from above
                var originalColumnName = this.Columns[i].Caption;
                var currentColumnName = schemaTable.Rows[i]["ColumnName"] as string;

                if (originalColumnName != currentColumnName)
                {
                    schemaTable.Rows[i]["ColumnName"] = originalColumnName;
                }
            }

            return new MyDataReader(dataReader);
        }
    }

MyDataReader : IDataReader

This accepts a datareader and simply acts as a decorator for the GetName() method.

internal class MyDataReader : IDataReader
    {
        private IDataReader _dataReader;

        public MyDataReader(IDataReader dataReader)
        {
            _dataReader = dataReader;
        }

        //other IDataReader methods not included for clarity

        public string GetName(int i)
        {
            return this.GetSchemaTable().Rows[i]["ColumnName"] as string;
        }
    }


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why