DataReader's structure to empty DataTable

Question

Given an existing DataReader (in this case an OleDbDataReader), I can construct a DataTable using DataTable.Load:

// C#
OleDbDataReader dr = SomeMethodThatReturnsADataReader();
var dt = new DataTable();
dt.Load(dr);

' VB.NET
Dim dr As OleDbDataReader = SomeMethodThatReturnsADataReader();
Dim dt = New DataTable()
dt.Load(dr);

However, this loads the data from the DataReader into the DataTable.

I want to create a DataTable that matches the structure of an existing DataReader but doesn't contain any data.

How can I do this?

Edit

On further reflection, I understand there is no built-in method to do this. However, can the problem be broken down into two parts?

Extract the schema information from the DataReader
Load that schema information into a new DataTable

You may need to explain further - those DataReaders are just declared; we cant create DataReaders, they are always created for us. So where did yours actually come from? — Ňɏssa Pøngjǣrdenlarp, Aug 25 '16 at 00:41
@Plutonix The `DataReader` is actually created elsewhere; I've modified the code to clarify that. — Zev Spitz, Aug 25 '16 at 01:00
I'd create another method to return a DataTable with the table def loaded - the DataReader isnt really meant for this. Given the way they can lock up a connection, it may not be a good idea to have a reader just laying around. — Ňɏssa Pøngjǣrdenlarp, Aug 25 '16 at 01:04
@Plutonix Not an issue. The actual code is an iterator function that returns a `DataReader` for each iteration, and wraps the `yield return` / `Yield` inside a `using`. I didn't want to include this, so as not to distract from my question. — Zev Spitz, Aug 25 '16 at 01:11
There's no simple way to do it, i.e. no single method call. You'd have to call `GetSchemaTable` and then construct the schema of your `DataTable` based on that result. That's presumably what the `DataTable.Load` method does internally. — jmcilhinney, Aug 25 '16 at 01:16
@jmcilhinney Do I need to iterate over the results of `GetSchemaTable` by hand? Or is there some method that can use the schema table to construct a new `DataTable` or define the structure of an existing `DataTable`? — Zev Spitz, Aug 25 '16 at 01:18
Why are you fixated on getting it from *that* datareader? A datatable 'built' from the same table as shown will later work with that reader. — Ňɏssa Pøngjǣrdenlarp, Aug 25 '16 at 01:19
`GetSchemaTable` returns a `DataTable` within which each row represents a column in the schema. You'll need to manually loop over the rows and, for each one, create a `DataColumn`, configure it based on the field values of the schema row and then add it to the target `DataTable`. — jmcilhinney, Aug 25 '16 at 01:22
Why not just populate a dataset or a datatable from your code that generates the datareader. So instead of calling executereader from the command object, call the fill method of the dataadapter and fill your dataset or datatable — F0r3v3r-A-N00b, Aug 25 '16 at 01:40
@F0r3v3r-A-N00b Because the decision (of whether or not to load the data) is up to the calling code. (To clarify, the datareader is being exposed via an iterator function, which wraps the `yield` in a `using`). — Zev Spitz, Aug 25 '16 at 01:43
Check out sarathkumar's answer from [here](http://stackoverflow.com/questions/18961938/populate-data-table-from-data-reader). — Jim Hewitt, Aug 25 '16 at 01:49
@ZevSpitz perhaps return an empty structure before entering the using scope, save it to a datatable. If your code decides to return a structure, then return table.Clone. You can return an empty structure by sending an "always false" parameter. Like if your query needs a parameter > 0 to be able to return a result, then send it a value of 0 so that it will return an empty table. — F0r3v3r-A-N00b, Aug 25 '16 at 01:55

score 2 · Answer 1 · answered Aug 25 '16 at 03:53

The DataAdapter Class, that is the base class of the OleDbDataAdapter and others, has the Protected FillSchema Method that takes an IDataReader to fill an empty DataTable's schema to match the reader's fields.

You need to construct a small utility class to make use of this feature.

Friend Class ReaderAdapter
    Inherits System.Data.Common.DataAdapter

    Public Function GetTypedTable(dataReader As IDataReader) As DataTable
        Dim ret As New DataTable
        FillSchema(ret, Data.SchemaType.Source, dataReader)
        Return ret
    End Function
End Class

Example use:

Dim rdr As OleDb.OleDbDataReader = cmd.ExecuteReader
Dim ra As New ReaderAdapter()
Dim emptyTypedTable As DataTable = ra.GetTypedTable(rdr)

DataReader's structure to empty DataTable

1 Answers1