How to return a MySqlDataReader before the reader is closed

Question

Okay, my code is currently:

    public MySqlDataReader CreateQuery(string queryString, string connectionString )
    {

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            using (MySqlCommand command = new MySqlCommand(queryString, connection))
            {
                command.Connection.Open();
                command.ExecuteNonQuery();

                MySqlDataReader reader = command.ExecuteReader();
                return reader;
            }
        }
    }

In another function, I've got:

using(MySqlDataReader readers = _connection.CreateQuery(query, connectString))

Currently, in this form, when I return reader into a readers, there is no value. I've stepped through, and verified that at the point of the return, reader had the correct information in it. Yet readers has no values. I'm probably missing something completely silly. But any and all help in this will be appreciated. Thanks!

You're closing the underlying connection before returning the reader. — Lee, Jul 25 '12 at 20:30
Have you tried to return an `IEnumerable` instead? `using (IDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { yield return (IDataRecord)rdr; } } ` — Tim Schmelter, Jul 25 '12 at 20:31
@Lee: Yeah, I had figured that out. I'm just trying to figure out how not to, if it's possible. — PiousVenom, Jul 25 '12 at 20:34
@TimSchmelter: I haven't tried that. But I shall now. I've never heard of IDataRecord, so this might take me a few. But I'll update. Thanks. — PiousVenom, Jul 25 '12 at 20:54

score 5 · Accepted Answer · edited Feb 12 '13 at 06:19

With the using command, your code is actually going to dispose of the connection and the command before control is returned to the caller. Essentially your connection object and your command object created in the using statements are disposed before the calling code is able to use it so its no longer usable.

You probably want to do something with the results of the query rather than the reader itself. Perhaps load it into a table? Something like:

public DataTable CreateQuery(string queryString, string connectionString )
    {
        DataTable results =  new DataTable("Results");
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            using (MySqlCommand command = new MySqlCommand(queryString, connection))
            {
                command.Connection.Open();
                command.ExecuteNonQuery();

                using (MySqlDataReader reader = command.ExecuteReader())
                    results.Load(reader);
            }
        }
       return results;
    }

In the end, I ended up using something similar to this. I still get my information, and the reader/connections are closing properly. Thank you. — PiousVenom, Jul 31 '12 at 13:37
-1, DataTable has no `Fill` method. Instead you must have meant `Load`. You can do the same using `Fill` but on `DbDataAdapter` — nawfal, Feb 12 '13 at 06:17

score 2 · Answer 2 · answered Jul 25 '12 at 21:18

2

Instead of returning the Datareader or copy all records into an in-memory DataTable that you return as a whole, you could return an IEnumerable<IDataRecord> which is the basic interface a DataReader record implements.

So this should work since the yield causes the Connection to keep alive:

public IEnumerable<IDataRecord> CreateQuery(string queryString, string connectionString)
{
    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
        using (MySqlCommand command = new MySqlCommand(queryString, connection))
        {
            command.Connection.Open();
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    yield return (IDataRecord)reader;
                }
            }
         }
    }
}

If you want for example take only the first 5 records:

var records = CreateQuery("SELECT * FROM ais.country;", Properties.Settings.Default.MySQL).Take(5);
foreach (IDataRecord rec in records)
{
    String country = rec.GetString(rec.GetOrdinal("Name"));
}

answered Jul 25 '12 at 21:18

Tim Schmelter

450,073
74
686
939

The problem with this is that the function that is calling CreateQuery() is using the data from the query to create lists based upon the columns. I have 12 lists that I'm trying to populate off the one query. – PiousVenom Jul 25 '12 at 21:31
@KevinH.: How are you populating the lists(what kind of lists?), what problem do you have? – Tim Schmelter Jul 25 '12 at 21:38
Initially, I was populating them with: http://pastebin.com/rBDay0Ru . I can do a foreach on the records, and populate each list similar to what you've got: http://pastebin.com/F1D2qeqX . But I'm not certain how correct it is. My program compiled and did everything it was supposed to do. But some of my displayed items were incorrect. I just ran this first test, so I don't know where it's going wrong on that yet. Will update soon. – PiousVenom Jul 25 '12 at 21:46
My problem now is that with the information in the lists, I'm doing some comparisons, and adding specific data into another list. For example, where I should be getting 1918 entrys into the specific list, I'm only getting 1352. – PiousVenom Jul 25 '12 at 22:01
Note: you don't need to dispose DataTables(remove the `using`). You must loop the rows of the DataTable and add the appropriate fields to your lists. My streaming(!) approach looks ok in your [pastebin](http://pastebin.com/F1D2qeqX). Show your comparisons. Maybe you should do such comparisons in dbms. – Tim Schmelter Jul 25 '12 at 22:08
HAHA, sorry. Copy/paste fail. That was a different set. Instead of DataTables before, I was using MySqlDataReader in that 'using'. – PiousVenom Jul 25 '12 at 22:21
Tim, you dont require the `yield return (IDataRecord)reader`, just `yield return reader` would do, but semantically former makes more sense. It was not good design from MS to have `IDataReader` inherit from `IDataRecord`. Instead `IDataRecord` should have been a field in `IDataReader`.. – nawfal Feb 10 '13 at 18:45

How to return a MySqlDataReader before the reader is closed

2 Answers2