Skip to main content

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)

                //EXENTIAL Execution
                using (SqlDataReader reader = blobCmd.ExecuteReader(CommandBehavior.SequentialAccess))
                    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:

Popular posts from this blog

Multithread processing of the SqlDataReader - Producer/Consumer design pattern

In today post I want to describe how to optimize usage of a ADO.NET SqlDataReader class by using multi-threading. To present that lets me introduce a problem that I will try to solve.  Scenario : In a project we decided to move all data from a multiple databases to one data warehouse. It will be a good few terabytes of data or even more. Data transfer will be done by using a custom importer program. Problem : After implementing a database agnostic logic of generating and executing a query I realized that I can retrieve data from source databases faster that I can upload them to big data store through HTTP client -importer program. In other words, data reader is capable of reading data faster then I can process it an upload to my big data lake. Solution : As a solution for solving this problem I would like to propose one of a multi-thread design pattern called Producer/Consumer . In general this pattern consists of a two main classes where: Producer class is respons

Full-Text Search with PDF in Microsoft SQL Server

Last week I get interesting task to develop. The task was to search input text in PDF file stored in database as FileStream. The task implementation took me some time so I decided to share it with other developers. Here we are going to use SQL Server 2008 R2 (x64 Developers Edition), external driver from Adobe, Full-Text Search technology and FileStream technology.Because this sems a little bit comlicated let`s make this topic clear and do it step by step. 1) Enable FileStream - this part is pretty easy, just check wheter You already have enabled filestream on Your SQL Server instance - if no simply enable it as in the picture below. Picture 1. Enable filestream in SQL Server instance. 2) Create SQL table to store files  - mainly ther will be PDF file stored but some others is also be allright. Out table DocumentFile will be created in dbo schema and contain one column primary key with default value as sequential GUID. Important this is out table contains FileStream

MVC 3 Reload PartialView by using jQuery and AJAX

Sometimes it`s easier to reaload only part of the website witout reloading whole page. Thanks to AJAX technology such aproach is possible and easy to code. Let`s begin. Firstly we need to configure our environment so we must have a Visual Studio with MVC 3  Razor isntalled on it. Razor can be obtained from here . After installation process completed , the second step is to set up a new WebStie project (Picture 1.) Picture 1. After you confirm Your project type choise, next window starts. On it You should select the second web project type (with Forms autentication method)  and than choose RAZOR form dropdown (Picture 2.). Picture 2. Now You new web solution contains several folders and files by default. Their description is not a point of this article so allow myself to continue. Next step is to create a simple model for our PartialView. As an example I  created a simple _UserModel.cs in Models folder. The source code for this model is:  public class _UserModel     {