Background

I have been asked about that how would you put an image directly into the database rather saving it in a file system, that’s number one. Then second one was, how would you show the same image or any other image in the PictureBox control. To answer these question I thought of writing a blog itself rather than giving conceptual answers. So here we go!!!

Summary

This is a step-by-step tutorial for uploading or adding an image directly into the database and viewing any uploaded image in the PictureBox control in Windows Forms Application.

In VB 6.0 there is no way of saving the binary large object (BLOB) into the database pro-grammatically, it only has the option to show the binary image from database to PictureBox. But over here in this article we will use MemoryStream object from System.IO base class to copy an image from the db to directly into the PictureBox control.

In order to perform this tutorial I have used following

- Microsoft Visual Studio 2010
- Microsoft SQL Server 2008 R2 (you can also use the MS Access for this)

Also I can assume that most of guys knows the Visual C# .NET Windows Forms applications, Binary Large Object(BLOB) in databases, ADO.NET data access.

Let’s Start

  1. As a first step we have to create a table for storing and retrieving our image(s). So let’s create the table first using following query
    CREATE TABLE tblBLOB
    (
        BLOBID INT IDENTITY NOT NULL,
        BLOBData IMAGE NOT NULL
    )

    Note: For SQL Server users please select/create a database before you run this query

    Creating Table

    Creating Table

  2. Next step would be opening the Visual Studio and creating the Windows Forms application project

    Creating Project

    Creating Project

  3. Once the project is created the then default windows form will appear in the design mode. Over here you have to add to buttons and one picture box control using ToolBox toolbar. I have renamed the form to BLOBTestForm. After the design is complete it should ideally look like this
    Form Design

    Form Design

    Note: I have added a small label for displaying some messages. As of now it not visible, once we will the program and do any action it will show you the appropriate message.

  4. Once your form design is complete then in the code behind we have to put some code to make it work. We will start with using statements as follows
    using System.Data.SqlClient;
    using System.IO;
    using System.Drawing.Imaging;
  5. As database is involved we have make the connection string as well. Following declaration we can put it inside the public class BLOBTestForm : System.Windows.Forms.Form class declaration, configure the connection string as per your settings.
    String strConn;
    strConn = @"Data Source=myServer;Initial Catalog=Example;User Id=myUser;Password=myPassword;";
  6. Now all necessary things are done, it’s time to add some real code on the Button 1(File to Database) click event. This code will read the local image file using FileStream object and then convert it into byte array and pass it to the database using parameterized Command object. Change the file path as necessary.
    try
    {
         SqlConnection conn = new SqlConnection(strConn);
         SqlCommand sqlCmd = new SqlCommand("INSERT INTO tblBLOB (BLOBData) VALUES (@BLOBData)", conn);
         String sBLOBFilePath = @"D:\goal.jpg";//Modify this path as needed.
    
         //Read jpg into file stream, and from there into Byte array.
         FileStream fsBLOBFile = new FileStream(sBLOBFilePath, FileMode.Open, FileAccess.Read);
         Byte[] bytBLOBData = new Byte[fsBLOBFile.Length];
         fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length);
         fsBLOBFile.Close();
    
         //Create parameter for insert command and add to SqlCommand object.
         SqlParameter prm = new SqlParameter("@BLOBData", SqlDbType.VarBinary, bytBLOBData.Length, ParameterDirection.Input, false,
         0, 0, null, DataRowVersion.Current, bytBLOBData);
         sqlCmd.Parameters.Add(prm);
    
         //Open connection, execute query, and close connection.
         conn.Open();
         sqlCmd.ExecuteNonQuery();
         conn.Close();
         lblMsg.Text = "File imported successfully into the database.";
    }
    catch (Exception ex)
    { MessageBox.Show(ex.Message); }
  7. Now we have added code for file to database, now we will add the code for database to picture box control. You can add the following code to Button 2(Database to PictureBox) click event. This piece of code retrieves the image bytes from tblBLOB table in the database and puts it into the DataSet object, copies the recently uploaded image into byte array and then converts that into the MemoryStream array, finally we will load this MemoryStream into the image property of PictureBox control.
    try
    {
        SqlConnection conn = new SqlConnection(strConn);
        conn.Open();
    
        //Retrieve BLOB from database into DataSet.
        SqlCommand sqlCmd = new SqlCommand("SELECT BLOBID, BLOBData FROM tblBLOB ORDER BY BLOBID", conn);
        SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
        DataSet ds = new DataSet();
        sqlDA.Fill(ds, "tblBLOB");
        int c = ds.Tables["tblBLOB"].Rows.Count;
    
        if (c > 0)
        {   //BLOB is read into Byte array, then used to construct MemoryStream,
            //then passed to PictureBox.
            Byte[] byteBLOBData = new Byte[0];
            byteBLOBData = (Byte[])(ds.Tables["tblBLOB"].Rows[c - 1]["BLOBData"]);
            MemoryStream stmBLOBData = new MemoryStream(byteBLOBData);
            pbBLOB.Image = Image.FromStream(stmBLOBData);
            lblMsg.Text = "File read from the database successfully.";
        }
        conn.Close();
    }
    catch (Exception ex)
    { MessageBox.Show(ex.Message); }
  8. Coding is done, let’s compile and Run this application, to do so press F5.
  9. Once the our Form will appear you first click on File to Database button. So this will add the file into the database. It will look like this

    File to database

    File to database

  10. Once the label shows successful message then you can click on the Database to PictureBox button. This will load the recently added image from the database to PictureBox control. The final output will look like this

    Database to PictureBox

    Database to PictureBox

That’s it.

Download

http://dhirajranka.com/misc/BLOBTest.zip