reading from IDbCommand using an inherited custom IDataReader

Question

I have made a custom class inherits IDataReader and have successfully implemented a custom ServerWriter sqlBulkCopy with the custom class which uses a C# object instead of DataTable.

That proved to be a more efficient approach as I suspected.

Now I am trying to Read and I have some problems

This is the IDataReader:

// get Server Data Table Object IDataReader
public class GetSDTOIDataReaderM : IDataReader
{
   //private IEnumerator<MyTestObject> enumerator = null;

   public MySqlDbTableObject.Raw.SqlDbTableM propSqlTbl { get; set; }

   // implementing relevant GetValue(), filedCount() and Read()
   // this is how I did with WriteToServer
   public bool Read()
   {
       return ++this.ReaderRowsCount < this.propSqlTbl.Table.ElementAt(0).Row.Count;
   }
}

The object is mapped exactly as its corresponded SqlTable.

Setting an SqlCommand to ExecuteReader() on my custom IDataReader did not work so I have tried with IDbCommand which then it did compile, but still when trying to read I get an error:

Unable to cast object of type 'System.Data.SqlClient.SqlDataReader' to type 'GetSDTOIDataReaderM'

this.propComunicator.AsGetSDTOCom().SqlCmd.Connection.Open();
//this is the line of the error
using (this.propComunicator.AsGetSDTOCom().SDTOIDataReader = (Raw.Comunicator.Raw.GetSDTOIDataReaderM)this.propComunicator.AsGetSDTOCom().SqlCmd.ExecuteReader())
{

}

As write to server it did work, how can I implement it correctly with Read?

UPDATE

A little more about my modules

public class TransManSF
{
    public enum CommunicatorTypeS { Reader, Adapter, GetSDTOReaderA, SqlBcpyIDataReaderM };
    public enum DbIntreactorTypeS { TableObject, TableRowObject, Sproc, SqlCmd };
    public enum SqlCmdActionS { NotSet, SELECT, INSERT, UPDATE, DROP };
    public enum SqlCmdStrSelModeS { Single, RecentEfected, count, All, Top10, Top100, Top1000 };
    public enum SqlCmdStrSelOrderByS { NotSet = 0, Asc = 1, Desc = 2 };
    public enum SqlCmdStrSetterModeS { Single, Multi};
    public enum STDOTypeS { NotSet, ServerWriter, ServerTableGetter, ServerBiDerctional }
    public enum SprocTypeS { Sproc, TvPSrpoc }
    public enum TransExecActionTypeS { WriteToServer, GetTable }
}

public static TransMan.Raw.ComunicatorCLF.BulkCopyComSCL AsBulkCopyCom(this TransMan.Raw.ComunicatorCLF.ComunicatorM SelfCom)
{
    return (TransMan.Raw.ComunicatorCLF.BulkCopyComSCL)SelfCom;
}

virtual public void ExecuteTransaction(AppMods.DataBase.TransManSF.TransExecActionTypeS parSelectedTransType, TransManSF.SqlCmdStrSelOrderByS parExecOrderByS, int OrderByOrdinal = 0)
{
    if (parSelectedTransType == AppMods.DataBase.TransManSF.TransExecActionTypeS.WriteToServer)
        using (this.propComunicator.AsBulkCopyCom().Conn)
        {
            this.propComunicator.AsBulkCopyCom().Conn.Open();
            using (var IDRdrServerWriter = this.propComunicator.AsBulkCopyCom().ServerWriter)
            {
                var Eng = this.propExecuter.AsBulkCopyExec().Engine;
                Eng.BulkCopyTimeout = 240;
                Eng.WriteToServer(IDRdrServerWriter);

            }
            this.propComunicator.AsBulkCopyCom().Conn.Close();
        }
    else if (parSelectedTransType == AppMods.DataBase.TransManSF.TransExecActionTypeS.GetTable)
    {
        var DreaderCom = this.propComunicator.AsDReaderCom();
        using (DreaderCom.SqlCmd.Connection)
        {
            DreaderCom.SqlCmd.Connection.Open();
            using (DreaderCom.Driver = DreaderCom.SqlCmd.ExecuteReader())
            {
                if (DreaderCom.Driver.HasRows) while (DreaderCom.Driver.Read())
                    {
                        for (int i = 0; i < DreaderCom.Driver.FieldCount; i++)
                        {
                            var CurRdrColumn = DreaderCom.Driver.GetValue(i);
                            this.Table[i, 0] = CurRdrColumn;
                        }
                    }
            }
            DreaderCom.SqlCmd.Connection.Close();
        }                           
    }
}


public struct customComConfgBulkCopyA
{
      public TransManSF.CommunicatorTypeS ComType;
      public customComConfgBulkCopyA(TransManSF.CommunicatorTypeS ctrComType = TransManSF.CommunicatorTypeS.SqlBcpyIDataReaderM)
      {
          this.ComType = ctrComType;
      }
}
public sealed class BulkCopyComSCL :CustomComA
{
     public new Configurations.comunicator.customComConfgBulkCopyA Meta;
     public SqlConnection Conn { get; set; }
     public Raw.SqlBcpyIDataReaderM ServerWriter { get; set; }
}

public class SqlDbTableM : SqlDB1stTransA
{
     virtual public DbSchema.Raw.TableDictionaryA TableDict { get; set; }
     public virtual new TransMan.Raw.Configurations.SDB1stTransConfgF.SDTOMetaA Meta { get; set; }
     virtual public Raw.ColumnSetsCollM Table { get; set; }
     public override TransMan.Raw.ComunicatorCLF.ComunicatorM propComunicator
     {
         get
         {
              return base.propComunicator;
         }
         set
         {
              base.propComunicator = value;
         }
     }
     public override TransMan.Raw.Executers.ExecuterM propExecuter
     {
         get
         {
             return base.propExecuter;
         }
         set
         {
             base.propExecuter = value;
         }
     }
     public SqlDbTableM(TransManSF.STDOTypeS ctrTransType)
            : base(TransManSF.DbIntreactorTypeS.TableObject) { }
}

public sealed class GetSDTOComSCL : CustomComA
{
    public new Configurations.comunicator.customComConfgGetSDTOReaderA Meta;
    public new IDbCommand SqlCmd;
    public Raw.GetSDTOIDataReaderM SDTOIDataReader { get; set; }
}

Answer 1

Short answer: you can't change the type returned by SqlCommand.ExecuteReader().

It will always be an instance of SqlDataReader.

Various solutions:

1 Wrap things where it makes sense to you.

If you go to the extreme, write your own IDbProviderFactory that wraps a SqlConnection with its own connection, command and datareader class.

For instance, that's what Miniprofiler does to instrument all SQL requests and collect execution times, number of rows, etc. Source code

Or you may wrap just the reader with some kind of extension method, like so: sqlCmd.ExecuteReader().AsMyReader(). What AsMyReader() would do is return a custom IDataReader that does your stuff and delegates to the actual SqlDataReader.

2 Use existing helper libraries

I am not exactly sure what you're trying to achieve when reading stuff, but there exists good libraries in this space. No need to come up with your own solutions.

FastMember (Nuget) allows creating IDataReader interfaces around any IEnumerable<T> or DataTable. It is made to be easily usable as a seed to SqlBulkCopy to easily insert data with max. performance into a SQL Server table.

Also, to ease executing SQL queries and parsing their results, Dapper is awesome.