Upload and view images to/from an Oracle database using an XML Web Service

• Download source - 10.1 KB

Introduction

In this article, I am going to describe how to upload and fetch images to/from an Oracle database using an XML web service. There is a certain condition where my web application needs to communicate with a far remote database server. Remote communication is one of the major aspects of web applications, and therefore its popularity rests upon this framework. Sharing text information between remote terminals is a common task, and we never had to bother about it. But, recently I found myself in a situation where I needed to store images to a remote Oracle database as a blob data type and fetch that image later to the client. The following diagram describes the inner workings of my code:

Background

First, the client machine communicates requesting a web service to upload an image. The XML web service verifies whether the user is authenticated or not. If it is an authorized user, it converts the image as bytes and sends it to an Oracle database. The following code describes the internal workings.

Using the code

First, create the following table in the Oracle database:

Create table tblImageService
(
  Image_id timestamp default sysdate,
  image_data blob,
  constraint pk_image_id primary key(image_id)
);

Create the following stored procedure to insert an image to the Oracle database:

CREATE OR REPLACE PROCEDURE InsertImage
( image_data in blob )

AS

BEGIN

Insert into tblImageService(image_data) values(image_data);

END;

Now, create a web service and expose the following subroutine as a web method for uploading images:

[WebMethod]
public int FireImage(string user_name, string password,
    byte[] news_image)
{
    // Initiate connection with oracle
    OracleConnection Conxn1 = 
      new OracleConnection("user id=test;password=test;data source=jamb");

    try
    {
        //Verify the user
        if (user_name == "user_name" && password == "password")
        {

            try
            {
                //Open the connection
                Conxn1.Open();

                byte[] tempBuff = news_image; 

                OracleTransaction tx;
                tx = Conxn1.BeginTransaction();

                OracleCommand cmd;
                cmd = Conxn1.CreateCommand();

                cmd.CommandText = "declare xx blob; begin " + 
                                  "dbms_lob.createtemporary(xx, false, 0);" + 
                                  " :tempblob := xx; end;"; // PL/SQL to read blob data
                cmd.Parameters.Add(new OracleParameter("tempblob", 
                    OracleDbType.Blob)).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();



                OracleBlob tempLob;
                tempLob = (OracleBlob)cmd.Parameters[0].Value;
                tempLob.BeginChunkWrite(); // start writing the LOB data
                tempLob.Write(tempBuff, 0, tempBuff.Length);
                tempLob.EndChunkWrite();

                cmd.Parameters.Clear();
                cmd.CommandText = "InsertImage";
                cmd.CommandType = CommandType.StoredProcedure;
                
                cmd.Parameters.Add(new OracleParameter("image_data", 
                                   OracleDbType.Blob)).Value = tempLob;
                cmd.ExecuteNonQuery(); //Execute the command to database
                tx.Commit(); // Commit the transaction

            }
            catch
            {

            }
            finally
            {
                Conxn1.Close();
            }

        }

    }
    catch (Exception ex)
    {
        string showError = "Error: " + ex.Message;
    }
    finally
    {
      
    }
    return 1;
}

In the above code, the FireImage subroutine accepts three inputs, namely user name, password, and image data in bytes. After you call this subroutine, the image data, which is sent as bytes, is converted into blob data and inserted to the Oracle database table using the stored procedure.

After this, you need to create another subroutine which fetches an image from the database, transfers it to a 64 bit string, and passes it to the client:

[WebMethod]
public string FetchNewsImage(string image_id)
{   //Initiate the connection
    OracleConnection Conxn1 = 
      new OracleConnection("user id=test;password=test;data source=jamb");

    OracleDataReader dtr = null;
    string imageString = "";
    try
    {
        Conxn1.Open();
        string id = image_id;
        OracleCommand cmd = new OracleCommand("select image_data " + 
                            "from tblImageService where to_char(" + 
                            "image_id,'DD-MON:YY-HH:MI:SS')='" + 
                            id + "'", Conxn1);
        dtr = cmd.ExecuteReader();

        byte[] arrpicture = new byte[0];



        while (dtr.Read())
        {
            arrpicture = (byte[])dtr["image_data"];
        }


        imageString = Convert.ToBase64String(arrpicture);


    }
    catch (Exception ex)
    {
        string showError = "Error: " + ex.Message;
    }
    finally
    {
        dtr.Close();
        Conxn1.Close();
    }
    return imageString;
}

The above subroutine accepts the image ID as its parameter and fetches the image from the database using that ID. After that, it pulls out the data from the database, converts it to byte[] data type, and then to a Base 64 string using the Convert.ToBase64String method.

Now, you need to develop client-side interaction for this web service. First, create a page named AddImage.aspx, and using the FileUpload server control, browse the image. Then, call the FireImage subroutine from our XML web service (don’t forget to add a reference to our above XML web service). Here is the code for calling the FireImage subroutine:

protected void btnSubmit_Click(object sender, EventArgs e)
{
    try
    {
        //Browse the image file and then convert it to byte[] data
        byte[] tempBuff = new byte[Upload1.PostedFile.InputStream.Length];

        Upload1.PostedFile.InputStream.Read(tempBuff, 0, 
                Convert.ToInt32(Upload1.PostedFile.InputStream.Length));
        Upload1.PostedFile.InputStream.Close();


        //Call ImageDataService Web Service
        newsImageDataService.ImageDataService fireImage = 
                     new newsImageDataService.ImageDataService();
        fireImage.FireImage("user_name", "password", tempBuff);


        Response.Write("Article Successfully saved!!");
    }
    catch (Exception ex)
    {
        Response.Write("Error: " + ex.Message);
    }
    finally
    {
        //do nothing
    }
}

This code saves your image to the database using the ImageDataService XML web service.

Now, you need to view the inserted image from the database. First, create the web page named viewImage.aspx and add the following code to the page load subroutine. Here is the code for you:

protected void Page_Load(object sender, EventArgs e)
{
    try
    {
        Conxn1.Open();
        OracleCommand cmd = new OracleCommand("select '<a href=/ImageService" + 
                            "/LoadImage.aspx?image_id='||to_char(" + 
                            "image_id,'DD-MON:YY-HH:MI:SS')||'>'||" + 
                            "to_char(image_id,'DD-MON:YY-HH:MI:SS')|| " +
        " '' as news_url from tblImageService ", Conxn1);

        OracleDataReader dtr = cmd.ExecuteReader();
        while (dtr.Read())
        {
            this.lblImage.Text = "<ul>" +
                                "<li>" + dtr["news_url"].ToString() + "</li>" +
                                "</ul>";
        }
    }
    catch(Exception ex)
    {
        Response.Write("Error: " + ex.Message);
    }
    finally
    {
        Conxn1.Close();
    }
}

The above code generates the link of the image that is fetched from the database, placing the appropriate query string to pass. Now, you need to build another page which actually generates the image. We name page name as LoadImage.aspx. Actually, all the processing is done by this page:

protected void Page_Load(object sender, EventArgs e)
{
    string id = Request.QueryString["image_id"].ToString();

    try
    {
        newsImageDataService.ImageDataService getImage = 
               new newsImageDataService.ImageDataService();
        byte[] arrpicture = 
               System.Convert.FromBase64String(getImage.FetchNewsImage(id));
        Response.ContentType = "Image/Jpeg";
        Response.BinaryWrite(arrpicture);
    }
    catch (Exception ex)
    {
        Response.Write("Error: " + ex.Message);
    }
    finally
    {
    }
}

The above code call the FetchNewsImage subroutine which accepts the ID of the image. Once you pass the image ID to call the FetchNewsImage subroutine, it returns a Base 64 string. After you get the Base 64 string from the subroutine, you can convert it back to byte[] data using the System.Convert.FromBase64String method. At last, you write the byte[] data as an image using the Response.BinaryWrite method. That’s it!!

Points of interest

First, I tried to transfer the image as a string using the .ToString() method. After converting this string to byte[], it came up with a funny output… Later, I discovered that I need to convert byte[] data as 64 bit string to retain its original state.

History

• Version 1.01