Working with binary large objects (BLOBs)

sample:WorkingWithBLOBs.zip

Introduction:

You can define a BLOB as a large photo, document, audio etc. saved in binary formats that you want to save in a database.
Saving and retrieving BLOBs in a database is more complex than querying string or numeric data.

The BLOB may be very large and if you try to move it in one piece will consume a lot of system memory and that for sure will affect your application performance.

To reduce the amount of system memory you have to break up the BLOB into smaller pieces.

There are a lot of classes that are designed for moving large amount of binary data like BinaryRader, BinaryWriter which exists in System.IO namespace. In the next paragraphs you will see how to use all of this.

Saving a BLOB value to the database:
 
To save a BLOB value to database we use FileStream and BinaryReader classes.

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

string

filePath = @"D:""My Movie.wmv";

 

//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();

SaveCommand.Connection.Close();

To fully understand the concept you need to try to write this code yourself.

Note: The database and the full source code in the source code area with this article.

posted on 2008-08-19 10:31 恭喜发财 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/haowenbiao/archive/2008/08/19/1270931.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值