Iterate twice through a DataReader

I need to iterate 开发者_开发问答twice through a DataReader. Is there a way to do this without using DataSets and without running the query twice? I'm using C#, by the way.

Thanks

The short answer is no, you run through the enumeration for the result sets and then you're done. What should probably be done is to dump the results to some kind of simplified raw results that you can iterate over as much as you want (avoiding the overhead of the DataSet).

If you take a peek at MSDN, it notes that the SqlDataReader is "forward only" which would suggest again that this isn't possible.

Its a forward-only reader, you cannot go back. Your best bet is that instead of iterating twice the data reader, do both operations you want to do in one iteration.

You can cache the results yourself but that's like destroying the purpose of the DataReader, so if you need to have the data handy many times, you have to choose another way to access it.

It is possbile but not so simple, because data reader mutates on each Read() call. So you need to store previous values somewhere to access it next time. Using data set is not so bad idea here.

The IDataReader spec does not allow the list to be reset, however check the specific implementation also implements IEnumerator or has a GetEnumerator() function there is a Reset command as part of that interface.

I have a solution to this problem, this might not be the best but I used this many times without an issue. You can create a subquery in the selection clause that will produce how many rows will be returned. example:

SELECT productid, 
  name, 
  price, 
  (SELECT COUNT(*) FROM tblProduct WHERE categoryid=1 AND price <= 1000) 
     AS 'RowCount' 
FROM tblProduct 
WHERE categoryid=1 AND price <= 1000;

Late in the game but here is some code to help with iterating through a dataset. And to be true to the question, this is not a datareader but a dataset instead as some suggested. I'm only iterating once but you can iterate through the dataset twice, or as many times as you like :) Cheers

...

        StringBuilder VBAstrBldr = new StringBuilder();
        List<object> objects = new List<object>();
        string destPath = string.Empty;
        string _filePath = string.Empty;
        string timestampSuffix = string.Empty;

...

    string commandTextDC = @" SELECT  [CategoryID]
                                    ,[AreaNo]
                                    ,[CategoryDesc]
                                    ,[CategoryNo]
                                    ,[CatCodeDisableDate]
                                    ,[CatCodeDeprecateDate]
                            FROM [dbo].[Categories] ";

...

using (var connection = new SqlConnection(_dbLogConn))
{
    connection.Open();
    using (var tran = connection.BeginTransaction())
    {
        using (var command = new SqlCommand(commandTextDC, connection, tran))
        {
            try
            {
                var adapter = new SqlDataAdapter(command);
                DataSet thedataSet = new DataSet();
                adapter.Fill(thedataSet);

                VBAstrBldr.Clear();

                foreach (DataTable table in thedataSet.Tables)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        VBAstrBldr.Append(Environment.NewLine);
                        foreach (DataColumn column in table.Columns)
                        {
                            object item = row[column];
                            // read column and item
                            VBAstrBldr.Append("columnName: " + column.ColumnName.ToString() + " Value: " + item.ToString());
                            VBAstrBldr.Append(Environment.NewLine);
                        }
                    }
                }

                _filePath = Path.GetDirectoryName(System.AppDomain.CurrentDomain.BaseDirectory);
                destPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "VBAwithDC" + timestampSuffix + ".txt");

                using (StreamWriter sw = new StreamWriter(System.IO.File.Create(destPath)))
                {
                    sw.Write(VBAstrBldr);
                }
            }
            catch (Exception Ex)
            {
                string msg = Ex.Message.ToString();
                tran.Rollback();
                throw;
            }
        }
    }
}