Disadvantages of storing BLOB data in Databases

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.