Is it possible to return an OleDbDataReader object from a method?

Question

Is it possible to return an OleDb.OleDbDataReader object from a function?

If so how would you go about doing that?

My current code returns the data reader object but when I try to read that object I get an error saying System.InvalidOperationException: 'Invalid attempt to call Read when reader is closed.'

My code:

 OleDbDataReader queryData = sendQueryReturnData("SELECT * FROM users WHERE username = ?;", Parameters);
 while (queryData.Read()) //error on this line
 {
     //stuff
 }

Answer 1

One way to keep queryData in-scope is to make it a field of an IDisposable type and don't let any other method close it, like this:

using System;
using System.Data.OleDb;

namespace TwitterSeachTest
{
    public class MyDataClass : IDisposable
    {
        OleDbDataReader queryData;
        OleDbCommand command;
        OleDbConnection conn;

        public object[] Parameters { get; set; } = new object[0];

        public void DoReadData()
        {
            sendQueryReturnData("SELECT * FROM users WHERE username = ?;", Parameters);
            while (queryData.Read()) //error on this line
            {
                //stuff
            }
        }

        private void sendQueryReturnData(string queryString, object parameters)
        {
            this.conn = new OleDbConnection("connectionstring");
            this.command = new OleDbCommand(queryString, conn);
            conn.Open();

            this.queryData = command.ExecuteReader();

            // your code
        }

        #region IDisposable Support
        private bool disposedValue = false; // To detect redundant calls

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    this.queryData?.Close();
                    this.command?.Dispose();
                    this.conn?.Close();
                }

                // TODO: free unmanaged resources (unmanaged objects) and override a finalizer below.
                // TODO: set large fields to null.

                disposedValue = true;
            }
        }

        // TODO: override a finalizer only if Dispose(bool disposing) above has code to free unmanaged resources.
        // ~MyDataClass()
        // {
        //   // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
        //   Dispose(false);
        // }

        // This code added to correctly implement the disposable pattern.
        public void Dispose()
        {
            // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
            Dispose(true);
            // TODO: uncomment the following line if the finalizer is overridden above.
            // GC.SuppressFinalize(this);
        }
        #endregion
    }
}

I put your code in DoReadData and the difference is that queryData is now a field instead of a local variable.

I also added some example code for sendQueryReturnData. Notice that it assigns the results of command.ExecuteReader to the queryData field. Don't use using here.

Finally, implement the dispose pattern with IDispose. The consequence of doing this is that whoever uses this class must now use a using statement or call Dispose.

That said, typically it's easier to just read the data from the database and close/dispose DB objects as soon as you're done. Instead, create a DTO that represents the data, populate and return a List<MyDataDto> and then close/dispose resources. This reduces the ambiguity of when and who has responsibility for releasing those resources.