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.

Answer 2

It sounds like you are trying to "wrap" the function call... So you have a central place to get connection handle, passing in a query and having the reader sent back so you can process it there. As other has commented, the whole closing, getting disposed of, cleanup, etc... What you could do is create an additional parameter to your function that accepts an Action expecting the data reader. You read what you want and return, then close out what you need to. Below is just a SAMPLE to implement.

Again, not perfect, but principles.. A centralized class will get connection, open, close, release. Prepare the query command you have already established and then passes back and lets the Action method actually handle the reading as different queries will have their own return structure/columns, etc. which is what I THINK you are trying to wrap up.

You would still want your try/catch, such as no valid open/close connection, add your dispose calls, but the principle might be what you are looking for.

    public class MyQueryWrapper
    {
        public GetMyData(OleDbCommand cmd, Action<OleDbDataReader> letYouReadIt)
        {
            using (var conn = new OleDbConnection(yourConnectionString))
            {
                conn.Open();
                cmd.Connection = conn;
                using (var rdr = cmd.ExecuteReader())
                {
                    // Now, call YOUR routine with the reader object while
                    // it is still active...
                    letYouReadIt(rdr);
                }
                conn.Close();
            }
        }
    }


    public class YourOtherClass
    {
        public void GetUser()
        {
            var mqr = new MyQueryWrapper();
            var sqlcmd = new OleDbCommand();
            // prepare the command and parameters.
            myUsers = new List<someUsers>();
            mqr.GetMyData(sqlcmd, ReadRecordsHere);
        }

        private List<someUsers> myUsers;

        public void ReadRecordsHere(OleDbReader rdr)
        {
            while (rdr.Read())
            {
                // read record, add to the "myUsers" list
                // keep reading until all records, then get out
            }
        }
    }