Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

Working with SQL Server BLOB Data in .NET

Rate me:
Please Sign up or sign in to vote.
4.75/5 (6 votes)
11 May 2010CPL3 min read 47.6K   542   32   6
Binary Large Objects (BLOBs) are pieces of data that have -usually- exceptionally large size (such as pictures or audio tracks).

I wrote this article in Arabic too. Check out the Arabic version here.

Binary Large Objects (BLOBs) are pieces of data that have -usually- exceptionally large size (such as pictures or audio tracks). These values are stored in SQL Server in an image column.

Sometimes, the term BLOB is also applied to large character data values, such as those stored in text or ntext columns.

Also, you can store BLOB data in a binary column, but it doesn't take larger than 8000 bytes. And image columns are more flexible.

Working with BLOB data is a bit strange because:

  1. You don't know how much size will be the retrieved data.
  2. The data may be very large so we need to retrieve it in chunks.

Our example is fairly simple. This example stores files in a database (FileStore) and retrieves it by name. The example relies on a database that contains one table, MyFiles. And the table itself contains two columns, one for filename (PK) and the other is an image column for the file itself.

Storing BLOB Data

Storing BLOB data in a database is the easiest part:

In order to run this code, you must add using statements to Sql.Data.SqlClient and System.IO.

C#
static void StoreFile(string filename)
{
    SqlConnection connection = new SqlConnection
        ("Server=(local) ; Initial Catalog = FileStore ; Integrated Security = SSPI");

    SqlCommand command = new SqlCommand
        ("INSERT INTO MyFiles VALUES (@Filename, @Data)", connection);

    command.Parameters.AddWithValue("@Filename", Path.GetFileName(filename));
    command.Parameters.AddWithValue("@Data", File.ReadAllBytes(filename));

    connection.Open();

    command.ExecuteNonQuery();

    connection.Close();
}

Code Explanation

First, we created a connection to the SQL Server database. And then, we created the SqlCommand object that will hold the T-SQL Insert statement. After that, we filled the command parameters with required values. Finally, we executed the command.

Well, for avoiding SQL-Injection attacks, it’s recommended that you use parameters instead of hard-coding the argument. Moreover, you can't represent binary values as strings.
Frankly, it’s recommended using stored procedures instead of coding the commands.

It’s highly recommended that you dispose disposable objects like SqlConnection and SqlCommand. Try encapsulating it in a using statement.

Retrieving BLOB Data

Retrieving BLOB data is a bit complex than storing it. The following method demonstrates this:

C#
static byte[] RetrieveFile(string filename)
{
    SqlConnection connection = new SqlConnection
       ("Server=(local) ; Initial Catalog = FileStore ; Integrated Security = SSPI");

    SqlCommand command = new SqlCommand
        ("SELECT * FROM MyFiles WHERE Filename=@Filename", connection);

    command.Parameters.AddWithValue("@Filename", filename);

    connection.Open();

    SqlDataReader reader =
        command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);

    reader.Read();

    MemoryStream memory = new MemoryStream();

    long startIndex = 0;
    const int ChunkSize = 256;
    while (true)
    {
        byte[] buffer = new byte[ChunkSize];

        long retrievedBytes = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize);

        memory.Write(buffer, 0, (int)retrievedBytes);

        startIndex += retrievedBytes;

        if (retrievedBytes != ChunkSize)
            break;
    }

    connection.Close();

    byte[] data = memory.ToArray();

    memory.Dispose();

    return data;
}

Code Explanation

After connecting to the database and writing our query, we executed the query by calling ExecuteReader() method of the command object to get read-only forward-only pointer to the retrieved rows.

By default, SqlDataReader reads entire rows -that can be gigabytes of data.- By specifying CommandBehavior.SequentialAccess, it reads the data sequentially in a given chunk size by calling the GetBytes() -or GetChars for BLOB textual data- method.

Calling Read() of the SqlDataReader objects advances the pointer to the next row which is the first single row -if found- in our example.

The GetBytes() method takes five arguments:

  1. The column index
  2. The index of which to start reading
  3. The buffer object that will keep current retrieved data
  4. Index in buffer of which to begin writing t
  5. The length (chunk size) of the data to retrieve

It is worth mentioning that this method returns number of bytes retrieved.
After calling this method, we used a MemoryStream object to write all data retrieved to.
Finally, we retrieve data by calling MemoryStream’s ToArray() function. (I think the code is now clear.)

It’s not recommended to use MemoryStream if the data is very huge.

SqlConnection, SqlCommand, SqlDataReader, and MemoryStream are all disposable objects.
Because the MemoryStream object may contain the retrieved data, it’s highly recommended that you dispose it as soon as possible.

For a complete example, download the sample project FileStore. This project uses a database for storing files and retrieving it. This database contains only one table, its definition is as follows:

FileStore File Table Definition

For creating the database, the project also includes a SQL Query file that contains the commands for creating it. Simply execute the file.
The project has been created using Visual Studio 2008 and .NET Framework 2.0.

Posted in Data Access Tagged: .NET, CodeProject, CSharp, SQL Server

License

This article, along with any associated source code and files, is licensed under The Common Public License Version 1.0 (CPL)


Written By
Technical Lead
Egypt Egypt
Mohammad Elsheimy is a developer, trainer, and technical writer currently hired by one of the leading fintech companies in Middle East, as a technical lead.

Mohammad is a MCP, MCTS, MCPD, MCSA, MCSE, and MCT expertized in Microsoft technologies, data management, analytics, Azure and DevOps solutions. He is also a Project Management Professional (PMP) and a Quranic Readings college (Al-Azhar) graduate specialized in Quranic readings, Islamic legislation, and the Arabic language.

Mohammad was born in Egypt. He loves his machine and his code more than anything else!

Currently, Mohammad runs two blogs: "Just Like [a] Magic" (http://JustLikeAMagic.com) and "مع الدوت نت" (http://WithdDotNet.net), both dedicated for programming and Microsoft technologies.

You can reach Mohammad at elsheimy[at]live[dot]com

Comments and Discussions

 
Questionwhat about this for retrieving the file?? Pin
miketucker1723-Aug-10 7:20
miketucker1723-Aug-10 7:20 
AnswerRe: what about this for retrieving the file?? Pin
Mohammad Elsheimy20-Sep-10 1:23
Mohammad Elsheimy20-Sep-10 1:23 
Question? Pin
Fiwel17-May-10 12:06
Fiwel17-May-10 12:06 
AnswerRe: ? Pin
Mohammad Elsheimy18-May-10 11:01
Mohammad Elsheimy18-May-10 11:01 
GeneralLink broken.. Pin
Member 704194018-Mar-10 4:31
Member 704194018-Mar-10 4:31 
GeneralRe: Link broken.. Pin
Mohammad Elsheimy21-Mar-10 5:53
Mohammad Elsheimy21-Mar-10 5:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.