Read from MS SQL BLOB column

In SQL SERVER 2005 the most common data type for storing BLOBs was an IMAGE  datatype. When SQL Server 2008 and later SQL SERVER  2008 R2 occured the image datatypes coexists with several new and recomended by Mictosoft BLOB datatypes such varbinary(MAX). Let`s have a look:

• varbinary(max) /binary(n) variables store variable-length binary data of approximately n bytes, may store a maximum of 2 gigabytes.
• image variables store up to 2 gigabytes of data and are commonly used to store any type of data file (not just images).

But when you`re designing a new database structure be patient and choose the newest one type according to the Microsoft note:

"ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
Fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set."

Now it`s time for coding. Asume that You working on a some business application that needs to retrieve a BLOB from the database by using clear ADO.NET. Code below is a simple C#  function which takes two parameters and returning a a new stream which contains BLOB data.


 private Stream ReadBlod(string blobCode, SqlConnection conn)
        {
            //SELECT from BLOB table
            string lSQLFileContent = "SELECT TOP 1 blob_File FROM blob_table WHERE blob_code = @code";
            //Stream for BLOB  object
            Stream lFileStream = Stream.Null;
            using (SqlCommand blobCmd = new SqlCommand(lSQLFileContent))
            {
                //Setting up command
                blobCmd.Connection = conn;
                blobCmd.Parameters.AddWithValue("@code", blobCode);


                //Test and open connection
                if (conn.State == ConnectionState.Closed)
                    conn.Open();


                //EXENTIAL Execution
                using (SqlDataReader reader = blobCmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    reader.Read();
                    long bytesize = reader.GetBytes(0, 0, null, 0, 0);
                    byte[] imageData = new byte[bytesize];
                    long bytesread = 0;
                    int curpos = 0;
                    while (bytesread < bytesize)
                    {
                        //Iteataion by 2kk bytes until EOF
                 bytesread += reader.GetBytes(0, curpos, imageData, curpos, 255);
                 curpos += 255;
                    }
                    //Creating stream from byte array
                    lFileStream = new MemoryStream(imageData);
                }
            }
            return lFileStream;
        }


Thank You.


Using list:

using System.Data.SqlClient;
using System.IO;


More info:

• SQL SERVER data types http://msdn.microsoft.com/en-us/library/ms187752.aspx
• Varbinary: http://msdn.microsoft.com/en-us/library/ms188362.aspx
• CommandBehavior Enumeration:  http://msdn.microsoft.com/en-us/library/system.data.commandbehavior.aspx