I don't want DbDataAdapter.Fill to call SqlDataReader.NextResult

[James K.]

I have a single SqlCommand that returns 2 SqlDataReaders - something
like this:

SELECT TOP 100 * from Table1
SELECT TOP 100 * from Table2

I want to get these two SqlDataReaders into two separate DataTables
for output later. (This is just an example; the SqlCommand can return
any number of SqlDataReaders.)

My code (slightly simplified) looks like this:

<SNIP>
reader = commandRunningSql.ExecuteReader(CommandBehavior.Default);
while (reader.FieldCount > 0)
{
dataTable = new DataTable();

// code for this is below
minimalDbDataAdapter.FillFromReader(dataTable, reader);

if (dataTable.Rows.Count > 0)
{
// this binds the dataTable to a dataGrid and
// saves the datagrid for later.
ShowDataTable(dataTable);
}

reader.NextResult(); // this is always false
}
reader.Close();
</SNIP>

And here is the pertinent DbDataAdapter code:

<SNIP>
public class MinimalDbDataAdapter : DbDataAdapter
{
public int FillFromReader(System.Data.DataTable dataTable,
System.Data.IDataReader dataReader)
{
// here is the problem; see comments below
return base.Fill(dataTable, dataReader);
}
// other methods omitted.
}
</SNIP>

The problem appears to be that DbDataAdapter.Fill calls
DbDataAdapter.FillNextResult which in turn calls
SqlDataReader.NextResult. This causes all the SqlDataReaders to be
filled into just one DataTable. I would like my
MinimalDbDataAdapter.FillFromReader function to fill the DataTable
with just the first SqlDataReader and let me call reader.NextResult
later.

Is there a way?

Thanks,
James

 

[Miha Markic]

Hi James,

The only way I know it might work:
Create your own MyDataReader class.
Create a constructor that takes real DataReader and stores it in a variable.
Implement IDataReader interface and map it to equivalent real DataReader's
methods.
Modify NextResult so it always returns false - so the FillFromReader will
fill only one table at a time.

Let me know if it works,

 

[James K.]

That worked perfectly. Thanks! In case anyone else ever needs it, this
will save you 5 minutes:

public class MySqlDataReader : IDataReader
{
private SqlDataReader sqlDataReader;

public MySqlDataReader(SqlDataReader _sqlDataReader)
{
sqlDataReader = _sqlDataReader;
}

public int Depth
{
get
{
return sqlDataReader.Depth;
}
}

public bool IsClosed
{
get
{
return sqlDataReader.IsClosed;
}
}

public int RecordsAffected
{
get
{
return sqlDataReader.RecordsAffected;
}
}

public int FieldCount
{
get
{
return sqlDataReader.FieldCount;
}
}

public DataTable GetSchemaTable()
{
return sqlDataReader.GetSchemaTable();
}

public bool NextResult()
{
return false; // we do not want it to do this.
}

public bool ForceNextResult()
{
return sqlDataReader.NextResult();
}

public bool Read()
{
return sqlDataReader.Read();
}

public void Close()
{
sqlDataReader.Close();
}

public bool GetBoolean(int i)
{
return sqlDataReader.GetBoolean(i);
}

public byte GetByte(int i)
{
return sqlDataReader.GetByte(i);
}

public long GetBytes(int i, long dataIndex, byte[] buffer, int
bufferIndex, int length)
{
return sqlDataReader.GetBytes(i, dataIndex, buffer,
bufferIndex, length);
}

public char GetChar(int i)
{
return sqlDataReader.GetChar(i);
}

public long GetChars(int i, long dataIndex, char[] buffer, int
bufferIndex, int length)
{
return sqlDataReader.GetChars(i, dataIndex, buffer,
bufferIndex, length);
}

public IDataReader GetData(int i)
{
return sqlDataReader.GetData(i);
}

public string GetDataTypeName(int i)
{
return sqlDataReader.GetDataTypeName(i);
}

public DateTime GetDateTime(int i)
{
return sqlDataReader.GetDateTime(i);
}

public decimal GetDecimal(int i)
{
return sqlDataReader.GetDecimal(i);
}

public double GetDouble(int i)
{
return sqlDataReader.GetDouble(i);
}

public Type GetFieldType(int i)
{
return sqlDataReader.GetFieldType(i);
}

public float GetFloat(int i)
{
return sqlDataReader.GetFloat(i);
}

public Guid GetGuid(int i)
{
return sqlDataReader.GetGuid(i);
}

public short GetInt16(int i)
{
return sqlDataReader.GetInt16(i);
}

public int GetInt32(int i)
{
return sqlDataReader.GetInt32(i);
}

public long GetInt64(int i)
{
return sqlDataReader.GetInt64(i);
}

public string GetName(int i)
{
return sqlDataReader.GetName(i);
}

public int GetOrdinal(string name)
{
return sqlDataReader.GetOrdinal(name);
}

public string GetString(int i)
{
return sqlDataReader.GetString(i);
}

public object GetValue(int i)
{
return sqlDataReader.GetValue(i);
}

public int GetValues(object[] values)
{
return sqlDataReader.GetValues(values);
}

public bool IsDBNull(int i)
{
return sqlDataReader.IsDBNull(i);
}

public object this[string s]
{
get
{
return sqlDataReader;
}
}

public object this[int i]
{
get
{
return sqlDataReader;
}
}

public void Dispose()
{
}
}

James K.