//A stream of bytes that represnts the binary file

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

//The reader reads the binary data from the file stream

BinaryReader reader = new BinaryReader(fs);

//Bytes from the binary reader stored in BlobValue array

byte[] BlobValue = reader.ReadBytes((int)fs.Length);

fs.Close();

reader.Close();

SqlConnection BlobsDatabaseConn = new SqlConnection("Data Source = .; Initial Catalog = BlobsDatabase; Integrated Security = SSPI");
SqlCommand SaveBlobeCommand = new SqlCommand();

SaveBlobeCommand.Connection = BlobsDatabaseConn;

SaveBlobeCommand.CommandType = CommandType.Text;

SaveBlobeCommand.CommandText = "INSERT INTO BlobsTable(BlobFileName, BlobFile)" + "VALUES (@BlobFileName, @BlobFile)";

SqlParameter BlobFileNameParam = new SqlParameter("@BlobFileName", SqlDbType.NChar);

SqlParameter BlobFileParam = new SqlParameter("@BlobFile", SqlDbType.Binary);

SaveBlobeCommand.Parameters.Add(BlobFileNameParam);

SaveBlobeCommand.Parameters.Add(BlobFileParam);

BlobFileNameParam.Value = filePath.Substring(filePath.LastIndexOf("\\") + 1);

BlobFileParam.Value = BlobValue;

try

{

    SaveBlobeCommand.Connection.Open();

    SaveBlobeCommand.ExecuteNonQuery();

    MessageBox.Show(BlobFileNameParam.Value.ToString() + " saved to database.","BLOB Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);

}

catch(Exception ex)

{

    MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

finally

{

    SaveBlobeCommand.Connection.Close();

}

Retrieving a BLOB from the database:

To retrieve a BLOB value from database we use FileStream and BinaryWriter classes.

The next example will show you the process of retrieving a BLOB to a database.

NOTE: you will see that we set the CommandBehavior to SquentialAccess when we call ExecuteReader() method, this allow us to use the GetBytes() method of the SqlDataRader, so we can read the BLOB from database in smaller, user-definable amounts.

string SavePath = @"D:\\My BLOBs";

SqlConnection SaveConn = new SqlConnection("Data Source = .; Initial Catalog = BlobsDatabase; Integrated Security = SSPI");

SqlCommand SaveCommand = new SqlCommand();

SaveCommand.CommandText = "Select BlobFileName, BlobFile from BlobsTable where BlobFileName = @BlobFileName";

SaveCommand.Connection = SaveConn;

SaveCommand.Parameters.Add("@BlobFileName", SqlDbType.NVarChar).Value = "My Movie.wmv";

//the index number to write bytes to

long CurrentIndex = 0;

//the number of bytes to store in the array

int BufferSize = 100;

//The Number of bytes returned from GetBytes() method

long BytesReturned;

//A byte array to hold the buffer

byte[] Blob = new byte[BufferSize];

SaveCommand.Connection.Open();

//We set the CommandBehavior to SequentialAccess

//so we can use the SqlDataReader.GerBytes() method.

SqlDataReader reader = SaveCommand.ExecuteReader(CommandBehavior.SequentialAccess);

while (reader.Read())

{

    FileStream fs = new FileStream(SavePath + "\\" + reader["BlobFileName"].ToString(), FileMode.OpenOrCreate, FileAccess.Write);

    BinaryWriter writer = new BinaryWriter(fs);

    //reset the index to the beginning of the file

    CurrentIndex = 0;

    BytesReturned = reader.GetBytes(1, //the BlobsTable column indexCurrentIndex, // the current index of the field from which to begin the read operationBlob, // Array name to write tha buffer to0, // the start index of the array to start the write operationBufferSize // the maximum length to copy into the buffer); 

    while (BytesReturned == BufferSize)

    {

        writer.Write(Blob);

        writer.Flush();
        CurrentIndex += BufferSize;

        BytesReturned = reader.GetBytes(1, CurrentIndex, Blob, 0, BufferSize);

    } 

    writer.Write(Blob, 0, (int)BytesReturned);

    writer.Flush(); writer.Close(); 

    fs.Close();

}
 

reader.Close();