Post
Ask Question

Working with SQL Server BLOB Data in .NET

Introduction

Although this article uses a SQL Server database, it also applies to Oracle. Just change namespaces and class names. Yet, Office Access 2007 adds this feature to its databases. However, only Access 2007 databases with the new file extension, ACCDB, are included in that feature. So be careful.

Binary Large Objects

Binary Large Objects (BLOBs) are usually data pieces with huge sizes (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 extensive, so we need to retrieve it in chunks.

Our example is pretty simple. This example stores files in a database (FileStore) and retrieves them by name. The example relies on a database that contains one table, MyFiles. And the table itself contains two columns one for the 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:

To run this code, you must add using statements to SQL.Data.SqlClient and System. IO.

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 the required values. Finally, we executed the command.

Well, to avoid 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, using stored procedures is recommended instead of coding the commands. It's highly recommended that you dispose of disposable objects like SqlConnection and SqlCommand. Try encapsulating it in a using statement.

Retrieving BLOB data

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

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 a 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 reads the data sequentially in a given chunk size by calling the GetBytes() -or GetChars for BLOB textual data.

Calling Read() of the SqlDataReader objects advances the pointer to the next row, 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.  
  4. Index in the buffer of which to begin writing t.
  5. The length (chunk size) of the data to retrieve. The buffer object will keep the current retrieved data.

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

Conclusion

It's not recommended to use MemoryStream if the data is 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 of it as soon as possible.


Similar Articles