While working with data, you may need to move large objects between the client application and the database server. In some situations, you may be able to treat large objects (LOB) data just like any other data, but in many cases you may be forced to look at alternative approaches. Consider an application that receives images from a database and displays them in a DataGridView control. This application works appropriately when the images are of small size. However, if the images are of large size, a more economic technique is to read the images and write them to a disk file by using streaming techniques. This will handle the data in chunks and will conserve the system resources.
LOBs are of different formats, such as:
· Binary Large Object (BLOB): If a LOB is stored in a database in a binary format, it is referred to as BLOB.
· Character Large Object (CLOB): If a LOB is stored in the database in a textual format, it is referred to as CLOB.
The below example demonstrates, how to handle BLOB data:
To create the required application, you need to perform the following tasks:
· Design a Windows application.
· Add the code.
· Execute the application and verify the output.
To design a Windows application, you need to perform the following steps:
1. Select Start -> All Programs -> Microsoft Visual Studio 2010. The Start page – Microsoft Visual Studio window is displayed.
2. Select File -> New -> Project. The New Project dialog box is displayed.
3. Select the project type as Visual C# from the Project types pane and Windows Application from the Templates pane.
4. Type the name of the project as CrudBLOB in the Name textbox.
5. Specify the location where the new project is to be created in the Location combobox.
6. Clock the OK button.
7. Design the form as shown below:
To add the code, you need to perform the following steps:
1. Select View -> Code and ensure that the following namespaces are defined:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
Declare the following class level variable:
Image curImage;
string curFileName;
string connectionString = "Data Source=.\\SQLEXPRESS; Initial Catalog=sample; Integrated Security=true";
Double click the Browse button in the design view of the form to create the click event handler. Type the following code in the event handler of the button:
private void btnBrowse_Click(object sender, EventArgs e)
{
OpenFileDialog openDlg = new OpenFileDialog();
if (openDlg.ShowDialog() == DialogResult.OK)
{
curFileName = openDlg.FileName;
txtFileName.Text = curFileName;
}
}
Double click the Insert button in the design view of the form to create the click event handler. Type the following code in the event handler of the button:
private void btnInsert_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtFileName.Text.Trim()))
{
FileStream file = new FileStream(curFileName, FileMode.OpenOrCreate, FileAccess.Read);
byte[] rawdata = new byte[file.Length];
file.Read(rawdata, 0, Convert.ToInt32(file.Length));
file.Close();
string sql = "SELECT * FROM STUDENTS";
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter);
DataSet ds = new DataSet("STUDENTS");
adapter.Fill(ds, "STUDENTS");
DataRow dr = ds.Tables["STUDENTS"].NewRow();
dr["Name"] = txtName.Text.Trim();
dr["Photo"] = rawdata;
ds.Tables["STUDENTS"].Rows.Add(dr);
adapter.Update(ds, "STUDENTS");
connection.Close();
MessageBox.Show("Image Saved");
}
}
Double click the Display button in the design view of the form to create the click event handler. Type the following code in the event handler of the button:
private void btnDisplay_Click(object sender, EventArgs e)
{
SqlConnection cn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT Name, Photo from Students where Id = " + txtStudentId.Text.Trim(), cn);
FileStream fs;
BinaryWriter bw;
//Set the BLOB buffer size.
int buffersize = 100;
// Declare the BLOB byte[] buffer.
byte[] outbyte = new byte[buffersize];
//Declare a variable that will store the bytes returned from
//GetBytes.
long retval;
//startIndex represents the starting position in the BLOB buffer.
long startIndex = 0;
//Open the connection and read data into the DataReader.
cn.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
while (myReader.Read())
{
//retreive the id
string name = myReader.GetString(0);
//create a file to store the image
string filename = @"d:\Student\" + name + ".bmp";
//Write the BLOB to a .bmp file
fs = new FileStream(filename, FileMode.OpenOrCreate, FileAccess.Write);
//Stream the BLOB to the FileStream object, fs
bw = new BinaryWriter(fs);
//reset the starting position
startIndex = 0;
//Read the bytes into outbyte[] and store the number of
//bytes returned
retval = myReader.GetBytes(1, startIndex, outbyte, 0, buffersize);
//Continue to read and write while bytes are remaining
while (retval == buffersize)
{
bw.Write(outbyte);
bw.Flush();
// Reposition the starting position to the end of the
// last buffer and fill the buffer
startIndex += buffersize;
retval = myReader.GetBytes(1, startIndex, outbyte, 0, buffersize);
}
//Write the remaining buffer.
bw.Write(outbyte, 0, (int)retval - 1);
bw.Flush();
bw.Close();
fs.Close();
curImage = Image.FromFile(filename);
pictureBox1.Image = curImage;
pictureBox1.Invalidate();
}
cn.Close();
}
Double click the Update button in the design view of the form to create the click event handler. Type the following code in the event handler of the button:
private void btnUpdate_Click(object sender, EventArgs e)
{
const int bufferSize = 100;
byte[] buffer = new byte[bufferSize];
long currentIndex = 0;
byte[] photoPtr;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand cmd = connection.CreateCommand())
{
//Retreive a pointer to the photograph using the TEXTPTR function
cmd.CommandText = "SELECT TEXTPTR(Photo) from Students where Id = " + txtStudentId.Text.Trim();
photoPtr = (byte[])cmd.ExecuteScalar();
}
using (SqlCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "UPDATETEXT Students.Photo @Pointer @Offset null @Data";
SqlParameter ptrParam = cmd.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
ptrParam.Value = photoPtr;
SqlParameter photoParam = cmd.Parameters.Add("@Data", SqlDbType.Image);
SqlParameter offsetParam = cmd.Parameters.Add("@Offset", SqlDbType.Int);
offsetParam.Value = 0;
using (FileStream file = new FileStream(txtFileName.Text.Trim(), FileMode.Open, FileAccess.Read))
{
//Read the chunks of the file into the buffer
// and send the chunks to the database
int count = file.Read(buffer, 0, bufferSize);
while (count != 0)
{
photoParam.Value = buffer;
photoParam.Size = count;
cmd.ExecuteNonQuery();
currentIndex += count;
offsetParam.Value = currentIndex;
count = file.Read(buffer, 0, bufferSize);
}
}
}
}
MessageBox.Show("Photograph replaced!");
}
Execute the application and verify the output:
To execute the application and verify the output, you need to perform the following steps:
1. Press F5 or select Debug -> Start Debugging to execute the application. The Form1 is displayed.
2. Enter name and browse the image as shown in the following figure.
3. Click on Insert to insert details in a database.
4. Enter ID of the above inserted details.
5. Click the Display button. The inserted image will be displayed, as shown in the following figure.
6. Browse image and change the image path.
7. Click on Update to replace the photograph in the database as shown below:
8. Click on Display to view updated image from the database.
Thanks for reading this article. I think this will help you a lot.
John Smith
09-Feb-2013