All geek questions in one place

How to replenish a byte array using SqlDataReader?

This applies to: byte [] and is effectively passed by reference

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

Inside the loop, I call the database and return a large object ( varbinary[max] ). I am currently encountering OutOfMemory exceptions, so I am trying to reduce footprint in a bunch of large objects (LOH).

So, I am creating an array of bytes for the largest file that I have downloaded, and just in case I will add some additions. For instance:

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

Then I pass this currentFile database method. We are currently using 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 specified in the second article above.

Here are my questions (and feel free to fix me if I ask different questions)

  • How do you effectively replenish byte[] without creating a new object?

  • The above code does not use CommandBehavior.SequentialAccess , which is necessary so as not to create a new object. How to use EnterpriseLibrary with CommandBehavior s?

I am calling the database and returning a byte[] array

Update

So, after some time I decided to manually fill in the byte array. The link is now successfully transmitted.

  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 am sure there is a more efficient way to write this. And has not been fully tested. But the link is finally working.

-one
source share
1 answer

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; } }

It works now.

0
source

Source: https://habr.com/ru/post/1394479/

More articles:


All Articles