Return SqlDataReader with closed connection

Question

I have created a Sql helper class which handles most of my needs.

Amongst those, I have a function which executes a SQL statement and returns a SqlDataReader as follows:

public static SqlDataReader ExecuteCommand(string cmdText, bool useParameters)
{
    using (var sqlConnection = new SqlConnection(_connectionString.ConnectionString))
    {
        sqlConnection.Open();
        using (var sqlCommand = new SqlCommand(cmdText, sqlConnection))
        {
            if (useParameters && SqlParameterCollection.Count > 0)
            {
                sqlCommand.Parameters.AddRange(SqlParameterCollection.ToArray());
            }

            using (var sqlDataReader = sqlCommand.ExecuteReader())
            {
                return sqlDataReader;
            }
        }
    }
}

The problem with this is, obviously, the fact that it returns a sqlDataReader which requires an open connection and the fact that the connection is closed.

I have looked at returning a SqlDataAdapter instead, but after reading the following thread SqlDataAdapter vs SqlDataReader it doesn't sound like such a good idea as a general function to be used in every single scenario when you have absolutely no idea of the amount of data it is supposed to load.

So... What's a good alternative?

The only thing I can think of off the top of my head is to cycle through the rows in the SqlDataReader and do a yield return IEnumerable<IDataRecord>.

Is there any better way of achieving this or is this pretty much it?

Marc Gravell · Accepted Answer · 2013-11-04 16:47:22Z

3

You could use CommandBehavior.CloseConnection, which offloads closure to the caller - but this then makes your code dependent on the caller using the reader correctly.

Personally, I would try to minimize this type of dependency - and move the materialization code as close to the DB code as possible. In reality, there aren't that many cases when the caller needs a raw reader - or at least, there shouldn't be. I would strongly advise using tools like "dapper" or ORMs, so that you can just do something like:

return connection.Query<T>(cmdText, args).ToList();

which then doesn't leave many places for the caller to make a mess of things.

answered Nov 4, 2013 at 16:47

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cogumel0 Over a year ago

Thank you, exactly what I was after.

Axel Köhler Over a year ago

How about this use case: I have a class that handles Transformation jobs on a data warehouse (DWH). The query of each job needs to be executed on the DWH (an SQL Server), and the results need to be copied to one or more different databases. These could be SQL Servers as well, but might be different database servers as well. I want to get a reader from my Sql server layer, but might need to use it in a Postgress or MariaDB db layer. Looks like I need to break the decoupling somewhere, right?

Axel Köhler · Accepted Answer · 2022-04-26 12:19:49Z

You can create a wrapper for your connection and reader, which implements System.IDisposable. The wrapper becomes responsible for closing the connection:

public class DbReaderWrapper : IDbReaderWrapper
    {
        public DbDataReader Reader { get; private set; }
        private readonly DbConnection _connection;
        private bool _disposed = false;

        public DbReaderWrapper(DbConnection connection, DbDataReader reader)
        {
            Reader = reader;
            _connection = connection;
        }

        ~DbReaderWrapper()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if(_disposed)
            {
                return;
            }

            if(disposing)
            {
                _connection.Dispose();
            }

            _disposed = true;
        }
    }

Your ExecuteCommand() function would look something like this:

public static SqlDataReader ExecuteCommand(string cmdText, bool useParameters)
{
    var sqlConnection = new SqlConnection(_connectionString.ConnectionString)
    sqlConnection.Open();
    var sqlCommand = new SqlCommand(cmdText, sqlConnection)

    if (useParameters && SqlParameterCollection.Count > 0)
    {
        sqlCommand.Parameters.AddRange(SqlParameterCollection.ToArray());
    }

    var sqlDataReader = sqlCommand.ExecuteReader()
    return new DbReaderWrapper(sqlConnection, sqlDataReader);
}

And you can call this method with the using statement:

using var readerWrapper = sqlHelper.ExecuteCommand(query, true) {
    // Do something with the reader
}

Collectives™ on Stack Overflow

Return SqlDataReader with closed connection

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related