asp net core - NullReferenceException: how to return mysql data reader from method?

Question

I've created a method to return mysql data reader.

public class DbHelper : BaseService
{
    public DbHelper(string connectionString) : base(connectionString)
    { }
    public static MySqlDataReader GetDataReader(string query)
    {
        using (MySqlConnection connection = new MySqlConnection(_connectionString))
        {
            connection.Open();
            using (MySqlCommand command = new MySqlCommand(query, connection))
            {
                MySqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
        }
    }
}

and next I've written:

var query = ""; //here my query string
var rdr = DbHelper.GetDataReader(query);

while (rdr.Read())
{
}

but I receive a null reference exception on this line return reader;

_connectionString and query are valorized correctly.

How can I fix? What am i doing wrong?

Answer 1

The problem is the using around the creation of the connection. When you exit from the using block everything is disposed. Thus the DataReader cannot work because it cannot use the connection disposed in the using block.

A first very simple workaround is to execute the loop inside the using block and at each loaded record call the delegate received in the input parameters

public static void FillWithDataReader(string query, Action<IDataRecord> filler)
{
    using (MySqlConnection connection = new MySqlConnection(_connectionString))
    {
        connection.Open();
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
            MySqlDataReader reader = command.ExecuteReader();
            while(reader.Read())
                filler(reader);
        }
    }
}

The caller could call the FillWithDataReader in this way

FillWithDataReader("SELECT something FROM somewhere", FillMyData);
.....

// This is called at each loop inside the FillWithDataReader    
public void FillMyData(IDataRecord record)
{
    string aSomething = record.GetString(record.GetOrdinal("something"));
    .....
}

Now the connection used by the reader to fetch records is still open and you can still separate the logic to extract data from the database from the logic used to fill your models or user interface objects.