Adding columns to a datareader

  • Thread starter Thread starter John Dann
  • Start date Start date
J

John Dann

Is there a way of adding a derived column to a datareader once the
data reader has been populated or do I need to use a dataset/datatable
for this?

(I need to apply a function to one of the columns in a way that can't
be done within a Select expression and so the only way I can see of
doing this is - in theory at least - to add a column to the dr and
then to populate that added column.)

Presumably if I do add a column to either dr or ds/dt I'll need to
iterate through every row to create the new value. Or is there a way
of applying a function to a column in a single operation?

TIA
JGD
 
A DataReader is a read-only, forward-only construct. It can't be modified.
You'll need a DataTable for what you want to do.
 
John,
As Scott pointed out, a Data Reader is read-only, forward-only construct. I
would consider creating a CalculatingDataReader that acts as a proxy to the
actually Data Reader, while adding support for calculated columns.

I would create a CalculatingDataReader class by implementing the
System.Data.IDataReader & System.Collections.IEnumerable classes, delegating
a number of methods to an underlying IDataReader (the real data reader),
except for those columns that are deemed to be "calculating".

Here is a start:

Public MustInherit Class CalculatingDataReader
Implements IDataReader
Implements IEnumerable

Private ReadOnly m_reader As IDataReader

Protected Sub New(ByVal reader As IDataReader)
m_reader = reader
End Sub

Public Sub Close() Implements IDataReader.Close
m_reader.Close()
End Sub

Public Function GetValue(ByVal i As Integer) As Object Implements
IDataRecord.GetValue
If i >= m_reader.FieldCount Then
Return GetCalculatedValue(i - m_reader.FieldCount)
Else
Return m_reader.GetValue(i)
End If
End Function

Protected MustOverride Function GetCalculatedValue(ByVal i As Integer)
As Object

....

End Class

Where GetCalculatedValue is the method that you override to calculate that
specific column.

Public Class MyCalculatingDataReader
Inherits CalculatingDataReader

Public Sub New(ByVal reader As IDataReader)
MyBase.New(reader)
End Sub

Protected Overrides Function GetCalculatedValue(ByVal i As Integer) As
Object
Return "This is a calculated value, really!"
End Function

End Class

Then to use it, you can do something like:

Dim command As SqlClient.SqlCommand
Dim reader As New MyCalculatingDataReader(command.ExecuteReader())

While reader.Read()

Hope this helps
Jay
 
John,

I see from the other answers that my message is maybe to cryptic.
I think to overcome the itterations as you asked for, that you can do this.

Create one dataset
Create two selects
Fill the two tables they should have the same keys otherwise you can not
itterate through it.
Create a datarelation for the two tables using that key
Use the datarelation to add a column to your primary table.

I hope this makes it more clear?

Cor
 
Back
Top