Disadvantages of storing BLOB data in Databases

Source: Internet
Author: User
Document directory
  • Disadvantages of storing BLOB data in Databases
  • Write BLOB data to the database
  • Reading BLOB data from a database
Disadvantages of storing BLOB data in Databases

Consider which resources may be better stored in the file system than in the database. The image referenced through HTTP href is a good example. This is because:

Retrieving images from a database results in higher overhead than using a file system.

The disk storage on the database San is usually more expensive than that on the disk used in the web server farm.

NoteBy carefully designing metadata policies, you can eliminate the need to store images, movies, Microsoft Office documents, and other resources in the database. Metadata can be indexed and contain pointers to resources stored in the file system.

Write BLOB data to the database

The following code describes how to use ADO. Net to write binary data obtained from a file to the image field in SQL Server.

public void StorePicture( string filename ){  // Read the file into a byte array  using(FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))  {    byte[] imageData = new Byte[fs.Length];    fs.Read( imageData, 0, (int)fs.Length );  }  using( SqlConnection conn = new SqlConnection(connectionString) )  {    SqlCommand cmd = new SqlCommand("StorePicture", conn);    cmd.CommandType = CommandType.StoredProcedure;    cmd.Parameters.Add("@filename", filename );    cmd.Parameters["@filename"].Direction = ParameterDirection.Input;    cmd.Parameters.Add("@blobdata", SqlDbType.Image);    cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;    // Store the byte array within the image field    cmd.Parameters["@blobdata"].Value = imageData;    conn.Open();    cmd.ExecuteNonQuery();  }}
Reading BLOB data from a database

InExecutereaderMethod CreationSqldatareaderUseCommandbehavior. sequentialaccessEnumeration value. If this enumeration value is not used, the reader extracts data from the server to the client one row at a time. If a row contains blob columns, a large amount of memory may be occupied. You can use enumeration values for more precise control, because only BLOB data is referenced (for exampleGetbytesThis method can be used to control the number of bytes read. The following code snippet describes this.

// Assume previously established command and connection// The command SELECTs the IMAGE column from the tableconn.Open();using(SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)){  reader.Read();  // Get size of image dataa€“pass null as the byte array parameter  long bytesize = reader.GetBytes(0, 0, null, 0, 0);  // Allocate byte array to hold image data  byte[] imageData = new byte[bytesize];  long bytesread = 0;  int curpos = 0;  while (bytesread < bytesize)  {    // chunkSize is an arbitrary application defined value     bytesread += reader.GetBytes(0, curpos, imageData, curpos, chunkSize);    curpos += chunkSize;  }}// byte array 'imageData' now contains BLOB from database

NoteUseCommandbehavior. sequentialaccessThe column data must be accessed in strict order. For example, if BLOB data is in 3rd columns and you still need data in 1st columns and 2nd columns, you must read 3rd columns and 1st columns before reading 2nd columns.


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.