Memory effective way to read BLOB data in C#/SQL 2005

by

See this excellent article here or this blog post for a long explanation how to do it.

Basically, you need to use a SqlDataReader and specify SequentialAccess to it when you create it – then you can read (or write) the BLOB from the database in chunks of whatever size is best for you.

Basically something like:

SqlDataReader myReader = getEmp.ExecuteReader(CommandBehavior.SequentialAccess);

while (myReader.Read())
{
   int 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)
   {
      // write the buffer to the output, e.g. a file
      ....

      // 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 last buffer to the output, e.g. a file
   ....
}

// Close the reader and the connection.
myReader.Close();

Marc

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. Get top 1 row of each group
  3. SQL update query using joins
  4. How to update two tables in one statement in SQL Server 2005?
  5. Referring to a Column Alias in a WHERE Clause
  6. SQL query to select dates between two dates
  7. Convert Month Number to Month Name Function in SQL
  8. Create a date from day month and year with T-SQL
  9. SET versus SELECT when assigning variables?
  10. Selecting COUNT(*) with DISTINCT
  11. Generate a resultset of incrementing dates in TSQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. SQL – Subtracting a depleting value from rows
  16. How to print VARCHAR(MAX) using Print Statement?
  17. What’s the best way to select the minimum value from several columns?
  18. Concatenate values based on ID
  19. Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
  20. Splitting delimited values in a SQL column into multiple rows
  21. SELECT FOR UPDATE with SQL Server
  22. varbinary to string on SQL Server
  23. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  24. SQL Server 2005 ROW_NUMBER() without ORDER BY
  25. Can an INNER JOIN offer better performance than EXISTS
  26. OPENXML with xmlns:dt
  27. Cumulative Total in MS Sql server [duplicate]
  28. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  29. Compare dates in T-SQL, ignoring the time part
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment