articles

Home / DeveloperSection / Articles / Handling Binary Large Objects in a Database in C#

Handling Binary Large Objects in a Database in C#

Chris Anderson9626 02-Feb-2013

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:

Handling Binary Large Objects in a Database in C#

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.

Handling Binary Large Objects in a Database in C#

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.

Handling Binary Large Objects in a Database in C#

6.    Browse image and change the image path.

7.    Click on Update to replace the photograph in the database as shown below:

Handling Binary Large Objects in a Database in C#

8.    Click on Display to view updated image from the database.

Handling Binary Large Objects in a Database in C#

Thanks for reading this article. I think this will help you a lot.


Updated 07-Sep-2019
hi I am software developer at mindstick software pvt. ltd.

Leave Comment

Comments

Liked By