Add record to SqlDataReader

Question

Is there any way I can push a new record to SqlDataReader after i pull a table down? I have this piece of trash code that I need to modify, and this seems like the easiest way to do what I need. I understand that this should not be done, and if you have to do it there is something seriously wrong with your logic, but is there a way?

Answer 1

SqlDataReaders are forward read only so I doubt you can add a record in (regardless of whether you have pulled the whole table down). In fact anything that inherits DbDataReader is forward read only.

I'm guessing you need to do some manipulation with the records. Maybe what you can do instead is use the SqlDataReader to fill a DataTable and put a new record into the DataTable. But then you'd need to change your code to juggle a DataTable.

Answer 2

Easiest way from that point is to just manually create a command with a command string of an insert(parametized if not sanitized/clean data, best to do that anyways, but could make code bulkier). Code for that should be quite small, considering you already have everything else setup.

Answer 3

When you say "push a new record to"... do you mean you want to add a record to the results? Or do you mean you want to do an INSERT?

The INSERT cannot be done with a reader; however, you can do things with readers. Of course, it would be simpler to update the original query so that you UNION the data.

In particular: you can't create your own SqlDataReader, but you can create your own bespoke IDataReader implementation; this could wrap the SqlDataReader, simply proxying data from the inner SqlDataReader until the SqlDataReader.Read() method returns false - then you could swap to returning you own data, returning true until you have run out of data. Not trivial to implement (mainly because you need to implement a lot of methods to write your own IDataReader), but certainly not impossible.

Answer 4

You need to expand your question a bit,

If, for example, you need to walk through a million records and update a field on the same table while walking through the data.

You can create a second SqlConnection to your db and execute update statements on the table (prone to locking issues), or better still insert all your changes into a temp table and merge the changes back into the original table after you are done with the reader.

There is little question I am tempted to ask, can this piece of logic be replaced with a single SQL UPDATE statement?