How do you refill a byte array using SqlDataReader

Question

This is in reference to: byte[] and efficiently passing by reference

And the SqlDataReader found in this post: Getting binary data using SqlDataReader

Inside a loop, I'm calling a database and returning a large object (varbinary[max]). Currently, I'm running into OutOfMemory exceptions, so I'm trying to reduce the footprint in the Large Object Heap (LOH).

So, I'm creating a byte array for the largest file that I'd download and adding some padding just in case. For instance:

byte[] currentFile = new byte[largestFileSize * 1.1];

I then pass this currentFile the database method. Currently, we use the EnterpriseLibrary to access the database:

DbCommand storedProcedure = MedicareDatabase.Db.GetStoredProcCommand(spName);

storedProcedure.CommandTimeout = 5000;

if (parameters != null)
{
    foreach (Param parameter in parameters)
    {
        if (parameter != null)
        {
            MedicareDatabase.Db.AddInParameter(storedProcedure, parameter.ParameterName, parameter.DbType, parameter.Value);
        }
    }
}

try
{
    BinaryWriter bw;                        // Streams the BLOB to the FileStream object.

    int bufferSize = 100;                   // Size of the BLOB buffer.
    byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
    long retval;                            // The bytes returned from GetBytes.
    long startIndex = 0;                    // The starting position in the BLOB output.

    var myReader = MedicareDatabase.Db.ExecuteReader(storedProcedure);

    while (myReader.Read())
    {
        bw = new BinaryWriter();

        // Reset the starting byte for the new BLOB.
        startIndex = 0;

        // Read the bytes into outbyte[] and retain the number of bytes returned.
        retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);

        // Continue reading and writing while there are bytes beyond the size of the buffer.
        while (retval == bufferSize)
        {
            bw.Write(outbyte);
            bw.Flush();

            // Reposition the start index to the end of the last buffer and fill the buffer.
            startIndex += bufferSize;
            retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize);
        }

        // Write the remaining buffer.
        bw.Write(outbyte, 0, (int)retval - 1);
        bw.Flush();

        // Close the output file.
        bw.Close();
    }

This is a modification of the code listed in the second article above.

Here are my questions (and feel free to correct me if I should be asking different questions)

1. How do you efficiently refill the byte[] without creating a new object?

2. The above code doesn't use the CommandBehavior.SequentialAccess which is needed to also not create a new object. How do I use the EnterpriseLibrary with CommandBehaviors?

I'm calling the database and returning have a byte[] array

Updated

So after some time, I've decided to manually populate the byte array. The reference is now being passed successfully.

        SqlConnection pubsConn = null;
        SqlCommand logoCMD = null;
        SqlDataReader myReader = null;

        try
        {
            pubsConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MedicareAccess"].ConnectionString);
            logoCMD = new SqlCommand("esMD.proc_WS_SelectBiztalkBinary", pubsConn);
            logoCMD.CommandType = CommandType.StoredProcedure;

            SqlParameter submissionSetParamter = logoCMD.Parameters.Add("@submissionSetId", SqlDbType.UniqueIdentifier);
            submissionSetParamter.Value = currentDocument.SubmissionSetId;

            SqlParameter fileNameParam = logoCMD.Parameters.Add("@fileName", SqlDbType.VarChar, 100);
            fileNameParam.Value = currentDocument.FullFileName;


            int bufferSize = 100;                   // Size of the BLOB buffer.
            byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
            long retval;                            // The bytes returned from GetBytes.
            long startIndex = 0;                    // The starting position in the BLOB output.

            // Open the connection and read data into the DataReader.
            pubsConn.Open();
            myReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess);

            Array.Clear(data, 0, data.Length);

            if (myReader == null)
            {
                return;
            }

            while (myReader.Read())
            {
                currentDocument.Size = (int)myReader.GetBytes(0, 0, null, 0, 0);
                int locationCounter = 0;
                // Reset the starting byte for the new BLOB.
                startIndex = 0;

                // Read the bytes into outbyte[] and retain the number of bytes returned.
                retval = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);

                // Continue reading and writing while there are bytes beyond the size of the buffer.
                while (retval == bufferSize)
                {
                    for (int i = 0; i < retval; i++)
                    {
                        data[locationCounter] = outbyte[i];
                        locationCounter++;
                    }

                    // Reposition the start index to the end of the last buffer and fill the buffer.
                    startIndex += bufferSize;
                    retval = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (myReader != null)
            {
                myReader.Dispose();
                myReader.Close();
                myReader = null;
            }

            if (pubsConn != null)
            {
                pubsConn.Dispose();
                pubsConn.Close();
                pubsConn = null;
            }
        }

I'm sure that there is a more efficient way to write this. And hasn't been fully tested. But the reference is finally working.

Answer 1

So I replaced the main While loop with the following code:

            if (myReader.Read())
            {
                currentDocument.Size = myReader.GetBytes(0, 0, null, 0, 0);

                // Reset the starting byte for the new BLOB.
                long startIndex = 0;
                int bufferSize = 8196;                   // Size of the BLOB buffer.
                byte[] outbyte = new byte[bufferSize];  // The BLOB byte[] buffer to be filled by GetBytes.
                long bytesInBuffer = 0;                            // The bytes returned from GetBytes.

                // Continue reading and writing while there are bytes beyond the size of the buffer.
                while (startIndex < currentDocument.Size)
                {
                    bytesInBuffer = myReader.GetBytes(0, startIndex, outbyte, 0, bufferSize);
                    Array.Copy(outbyte, 0, currentDocument.Data, startIndex, bytesInBuffer);
                    startIndex += bytesInBuffer;
                }
            }

Works now.