你如何使用SqlDataReader对象填充的字节数组？ [英] How do you refill a byte array using SqlDataReader?
问题描述
这是在参考：的byte []，并通过引用
以及 SqlDataReader的在这篇文章中发现：的使用获取的二进制数据SqlDataReader的
And the SqlDataReader found in this post: Getting binary data using SqlDataReader
在一个循环，我打电话数据库，并返回一个大对象（ VARBINARY [MAX] ）。目前，我遇到了内存不足例外，所以我试图减少在大对象堆（LOH）的足迹
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];
然后我通过这个 currentFile 数据库的方法。目前，我们使用 EnterpriseLibrary 来访问数据库：
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();
}
这是上面的第二篇文章中列出的code的修改。
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)
-
你如何有效地补充了
字节[]而不创建新的对象？
以上code不使用 CommandBehavior.SequentialAccess 这是需要也不创建新的对象。我如何使用 EnterpriseLibrary 与的CommandBehavior S'
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
更新
所以，一段时间后，我决定手动填充字节数组。参考目前正在顺利通过。
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.
推荐答案
所以我换下了主力While循环具有以下code：
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;
}
}
现在的作品。
这篇关于你如何使用SqlDataReader对象填充的字节数组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
