Read and Write BLOB Data to a Database Table with ODP.NET
 
Published: 27 Dec 2004
Unedited - Community Contributed
Abstract
By utilizing ODP.NET, Oracle database, and really very little effort you also can store images as a Blob for convenient storage and simple categorization.
by Steven Swafford
Feedback
Average Rating: 
Views (Total / Last 10 Days): 103052/ 255

Read and write BLOB data to a database introduction

[ Download Code ]

Objective:

The purpose of this article is to provide the mechanism to accomplish the following:

  1. Execute an insert SQL statement against an Oracle database.
  2. Saving image data to the database.
  3. Writing image data to a file from the database.

Prerequisites:

You should be familiar with an IDE such as Visual Studio .NET or equivalent. While such an IDE is not required it does make development efforts much easier. Also you should possess an understanding of ODP.NET and databases. In this case since we are using ODP.NET all further references to the database will be implied as an Oracle database.

Now that I have discussed to objective and prerequisites we will now move on to establishing the database schema related to this article.

Establish Your Database Schema

[ Download Code ]

I am a fan of utilizing any tool that makes my life easier as a developer and one such tool that I love when working with and Oracle database is PL/SQL Developer and you can find out more on this product at AllRoundAutomations.

Just to give you idea of this product take a look at this screenshot.

To establish the table which will hold the necessary data this article covers run the following:

-- Author:        Steven M. Swafford
-- Date Created:  23-NOV-2004
-- Create Table(s)
CREATE TABLE SMSTestBlob
(
 id number,
 photo BLOB,
 author varchar2(100),
 description varchar2(500)
);

Now that we have our table established the next step is to create your primary/foreign keys. To accomplish this step run the following SQL statement:

-- Author:        Steven M. Swafford
-- Date Created:  23-NOV-2004
-- Create/Recreate primary, unique and foreign key constraints
alter table SMSTESTBLOB
  add constraint PK_SMSTESTBLOB_ID primary key (ID);

Finally the sequence.

-- Author:        Steven M. Swafford
-- Date Created:  01-DEC-2004
-- Create sequence 
create sequence smstestblobid_seq
minvalue 1
maxvalue 999999999999
start with 1
increment by 1
cache 20
cycle
order;

Once you have established your table you will have a schema such as the following.

Schema

Now that the database is ready it is now time to transition to the code which will perform the necessary steps to insert our records. The code of choice is C# so sit back and prepare yourself.

Create Your Web Form to Allow Uploading the Image and Relevant Data

[ Download Code ]

There are a total of eight controls which I am using in this example of which three are RequiredFieldValidators and one is a Label. I will not discuss these controls rather you may take time to read the following.

1. TextBox Control
2. Label Control
3. RequiredFieldValidator Control
4. HtmlInputFile Control

WebForm Screenshot:
WebForm

The one unique step you need to take for the HtmlInputFile Control is to import the System.Web.UI.HtmlControls Namespace. As well you will need to add the runat="server" attribute to this control.

Once you have created your web form you should have something similar to the one above. If you view the source of this form you should have something similiar to this.

<form id="Form1" method="post" encType="multipart/form-data" runat="server">
<P>Author:<BR>
<asp:TextBox id="AuthorTextBox" runat="server" MaxLength="50"></asp:TextBox><BR>
<asp:RequiredFieldValidator id="RequiredFieldValidator2" runat="server"
 ControlToValidate="AuthorTextBox" ErrorMessage="Author name is required!">
 </asp:RequiredFieldValidator></P>
<P>Description:<BR>
<asp:TextBox id="DescriptionTextBox" runat="server" MaxLength="5000" Rows="10"
 TextMode="MultiLine" Columns="50"></asp:TextBox><BR>
<asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server"
 ControlToValidate="DescriptionTextBox" ErrorMessage="Description is required!">
 </asp:RequiredFieldValidator></P>
<P><INPUT type="file" name="FileToUpload" id="FileToUpload" runat="server">
<asp:Button id="UploadButton" runat="server" Text="Upload"></asp:Button>
<asp:RequiredFieldValidator id="UploadValidator" runat="server" 
ErrorMessage="You must select an image to upload"
 ControlToValidate="FileToUpload"></asp:RequiredFieldValidator></P>
<P>
<asp:Label id="NotificationLabel" runat="server" Visible="False">
</asp:Label></P>
</form>

Now that you have you web form ready let us know take a look in the code behind.

The Code Behind the Web Form Which Allows Your Data Collection

[ Download Code ]

There are two things that I must bring you attention to before you take a look at the source code.

1. I am using ODP.NET for the database communication. Take the time to read a previous article titled Using Oracle Data Provide for .NET
2. I have also wrote a utilities class to handle common functionality. This utility class will be discussed further in the article.

// Framework Import(s)
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;
using System.Text;


// Application Import(s)
using ASPAlliance.Utilities;


namespace ASPAllianceArticles
{
 /// <summary>
 /// Summary description for Article570.
 /// </summary>
 public class Article570 : System.Web.UI.Page
 {
  protected System.Web.UI.WebControls.Button UploadButton;
  protected System.Web.UI.WebControls.Label NotificationLabel;
  protected System.Web.UI.WebControls.RequiredFieldValidator UploadValidator;
  protected System.Web.UI.HtmlControls.HtmlInputFile FileToUpload;
  protected System.Web.UI.WebControls.TextBox AuthorTextBox;
  protected System.Web.UI.WebControls.TextBox DescriptionTextBox;
  protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
  protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;


  #region Static Constants


  // Static Constants
  private const string GENERAL_EXCEPTION_MESSAGE = "An exception has occured: ";
  private const string INVALID_FILE_FORMAT = "The file format you attempted to upload" +
   " is not a valid format! Only JPEG. GIF, and BMP file formats are allowed.";


  #endregion
 
  private void Page_Load(object sender, System.EventArgs e)
  {
   // Put user code to initialize the page here
  }


  #region Web Form Designer generated code
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: This call is required by the ASP.NET Web Form Designer.
   //
   InitializeComponent();
   base.OnInit(e);
  }
  
  /// <summary>
  /// Required method for Designer support - do not modify
  /// the contents of this method with the code editor.
  /// </summary>
  private void InitializeComponent()
  {    
   this.UploadButton.Click += new System.EventHandler(this.UploadButton_Click);
   this.Load += new System.EventHandler(this.Page_Load);


  }
  #endregion


      #region Upload Button Event


  private void UploadButton_Click(object sender, System.EventArgs e)
  {


   OracleConnection dbConn = null;
         string author = AuthorTextBox.Text.Trim();
         string imageDescription = DescriptionTextBox.Text.Trim();
   int imgLength = 0;
   string imgContentType = null;
   string imgFileName = null;
   string sqlStmt = null;
   bool success = false;


         ASPAlliance.Utilities.OracleDatabaseHelper dbHelper =
            new ASPAlliance.Utilities.OracleDatabaseHelper();
 
         try
         {
            // Establish the database connection
            dbConn = dbHelper.openDatabaseConnection();


      // Image attributes
   Stream imgStream = FileToUpload.PostedFile.InputStream;
   imgLength = FileToUpload.PostedFile.ContentLength;
   imgContentType = FileToUpload.PostedFile.ContentType;
      imgFileName = FileToUpload.PostedFile.FileName;


      // validate the file type is acceptable
   if (imgContentType == "image/jpeg" || imgContentType == "image/gif" || 
   imgContentType == "image/pjpeg"
     || imgContentType == "image/bmp")
    {


    Byte[] ImageContent = new byte[imgLength]; int intStatus; intStatus = 
    imgStream.Read(ImageContent, 0, imgLength);


    // define the sql to perform the database insert
    sqlStmt = "INSERT INTO smstestblob (id, photo, author," +
      "description ) VALUES (smstestblobid_seq.nextval, :1, :2, :3)";


    // Establish a new OracleCommand
    OracleCommand cmd = new OracleCommand();


    // Set command to create your SQL statement
    cmd.CommandText = sqlStmt;


    // Set the OracleCommand to your database connection
    cmd.Connection = dbConn;
        
    // Set the command type to text
    cmd.CommandType = CommandType.Text;


    OracleParameter paramImage = new OracleParameter("image",
      OracleDbType.Blob);
    paramImage.Value = ImageContent;
    paramImage.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(paramImage);


    OracleParameter paramAuthor = new OracleParameter("author",
      OracleDbType.Varchar2, 100);
    paramAuthor.Value = author;
    paramAuthor.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(paramAuthor);


    OracleParameter paramDescription = new OracleParameter("imgDesc",
      OracleDbType.Varchar2, 500);
    paramDescription.Value = imageDescription;
    paramDescription.Direction = ParameterDirection.Input;
    cmd.Parameters.Add(paramDescription);


    // Execute the SQL Statement
    cmd.ExecuteNonQuery();
   
    NotificationLabel.Text = "Upload Successful";
    NotificationLabel.Visible = true;
    success = true;
    }
    else
    {
     NotificationLabel.Text = INVALID_FILE_FORMAT;
     NotificationLabel.Visible = true;
    }
   }
         catch(OracleException ex)
         {
            NotificationLabel.Text = GENERAL_EXCEPTION_MESSAGE + ex.Message;
            NotificationLabel.Visible = true;
         }
         finally
         {
            // Close the database connection
            dbHelper.closeDatabaseConnection(dbConn);
         }


   // redirect only if the boolean value of success is true
   if (success)
   {
    Response.Redirect("Article570_1.aspx");
   }


  }


      #endregion


 }
}

Now I will discuss the utility class that I previously spoke of.

Utility Class to Handle Common Functionality

[ Download Code ]

While this is not necessary I find it useful to separate my common functionality into it's own project. For this example I am using this utility class for my database handling. You could easily integrated thing such as sending emails, logging errors, and string functions to name a few.

In this class I have two methods and a constant string that I have defined for the database connection string. Once again I am taking advantage of the ability to define values in a single local and in the case where I define the database connection string this value is stored in the Web.config file, of which below is an example.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <appSettings>
    <add key="OracleConnectionString" 
     value="User ID=userid;Password=password;Data Source=datasource" />
  </appSettings>
</configuration>

Now to reference this connection string in you class simply use the System.Configuration namespace. Be sure to import your ODP.NET Oracle namespaces as well.

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Configuration;
using System.Collections;


namespace ASPAlliance.Utilities
{
 /// <summary>
 /// Summary description for OracleDatabaseHelper.
 /// </summary>
 public class OracleDatabaseHelper
 {


      // Read the connection strings from the configuration file
      // Note: Modify User Id, Password, Data Source per your
      // database setup this is contained with the Web.config
      public static readonly string CONN_STRING = 
      ConfigurationSettings.AppSettings["OracleConnectionString"];


      /// <summary>
      /// Establishes your database connection
      /// </summary>
      /// <returns>a database connection instance</returns>
      public OracleConnection openDatabaseConnection()
      {
         OracleConnection dbConn = new OracleConnection(CONN_STRING);
         dbConn.Open();


         return dbConn;
      }


      public void closeDatabaseConnection(OracleConnection dbConn)
      {
         // Check if connection is open or closed
         if (dbConn != null)
         {
            if (dbConn.State == ConnectionState.Open)
            {
               dbConn.Close();
               dbConn.Dispose();
            }
         }
      }


 }
}

Now when you are ready to instantiate this class an example would be as so.

ASPAlliance.Utilities.OracleDatabaseHelper dbHelper = 
new ASPAlliance.Utilities.OracleDatabaseHelper();

In the code behind the web form you will notice when I have my database connection wrapped in a try/catch/finally statement. I will not go into the details of this rather if you are not familiar with try-catch-finally statement read Customizing Error Pages and C# Programmers Reference: try-catch-finally.

Example:

 try
         { 


            // Establish the database connection
            dbConn = dbHelper.openDatabaseConnection();


            // Execute the SQL Statement
            cmd.ExecuteNonQuery();
   
         }
         catch(OracleException ex)
         {
            NotificationLabel.Text = ex.Message;
            NotificationLabel.Visible = true;
         }
         finally
         {
            // Close the database connection
            dbHelper.closeDatabaseConnection(dbConn);
         }

At this point you should be able to execute this application in your web browser and store an image and the other relevant information in the database and in this case the database is an Oracle instance. From here the next step is to create another web form which will display the stored images.

Establish a Web Form that Utilizes a Datagrid to Display Information

[ Download Code ]

Previously you were instructed how to store images and relevant data in your database. Now you will need a method to allow your users to view records and then the ability to view these images.

DataGrid

The web form above has two controls, a DataGrid and a Label. The Label is used for display any errors that may arise. The following is the code for this web form.

<%@ Page language="c#" Codebehind="Article570_1.aspx.cs" 
AutoEventWireup="false" Inherits="ASPAllianceArticles.Article570_1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
   <HEAD>
      <title>Article570_1</title>
      <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
      <meta content="C#" name="CODE_LANGUAGE">
      <meta content="JavaScript" name="vs_defaultClientScript">
      <meta content="http://schemas.microsoft.com/intellisense/ie5" 
       name="vs_targetSchema">
   </HEAD>
   <body>
      <form id="Form1" method="post" runat="server">
         <P>
            <asp:DataGrid id="ImageDataGrid" runat="server" 
            BorderColor="#CC9966" BorderStyle="None" BorderWidth="1px"
               BackColor="White" CellPadding="4" AutoGenerateColumns="False">
               <FooterStyle ForeColor="#330099" BackColor="#FFFFCC"></FooterStyle>
               <SelectedItemStyle Font-Bold="True" ForeColor="#663399" BackColor="#FFCC66">
                </SelectedItemStyle>
               <ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
               <HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000">
               </HeaderStyle>
               <Columns>
                  <asp:HyperLinkColumn Text="id" Target="_blank" 
                  DataNavigateUrlField="ID" 
                  DataNavigateUrlFormatString="Article570_2.aspx?imageId={0}"
                  DataTextField="id" HeaderText="View Image" NavigateUrl="link.aspx" 
                  DataTextFormatString="View Image">
                     <HeaderStyle HorizontalAlign="Center" Width="20%"></HeaderStyle>
                  </asp:HyperLinkColumn>
                  <asp:BoundColumn DataField="author" HeaderText="Author">
                     <HeaderStyle HorizontalAlign="Center" Width="40%"></HeaderStyle>
                  </asp:BoundColumn>
                  <asp:BoundColumn DataField="description" HeaderText="Description">
                     <HeaderStyle HorizontalAlign="Center" Width="40%"></HeaderStyle>
                  </asp:BoundColumn>
               </Columns>
               <PagerStyle HorizontalAlign="Center" ForeColor="#330099" BackColor="#FFFFCC">
               </PagerStyle>
            </asp:DataGrid></P>
         <P>
            <asp:Label id="NotificationLabel" runat="server" Visible="False"></asp:Label></P>
      </form>
   </body>
</HTML>

The next step will be to bind the data to the DataGrid. Once again take notice of the utility class that I previously spoke of.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


// Application Import(s)
using ASPAlliance.Utilities;


namespace ASPAllianceArticles
{
 /// <summary>
 /// Summary description for Article570_1.
 /// </summary>
 public class Article570_1 : System.Web.UI.Page
 {
      protected System.Web.UI.WebControls.DataGrid ImageDataGrid;
      protected System.Web.UI.WebControls.Label NotificationLabel;
   
  private void Page_Load(object sender, System.EventArgs e)
  {
         OracleConnection dbConn = null;
   string sqlStmt = null;
         ASPAlliance.Utilities.OracleDatabaseHelper dbHelper =
            new ASPAlliance.Utilities.OracleDatabaseHelper();


         try
         {
            // Establish the database connection
            dbConn = dbHelper.openDatabaseConnection();


            // define the sql to perform the database insert
            sqlStmt = "select t.id, t.author, t.description" +
               " from smstestblob t order by t.id";


            // Establish a new OracleCommand
            OracleCommand cmd = new OracleCommand();


            // Set command to create your SQL statement
            cmd.CommandText = sqlStmt;


            // Set the OracleCommand to your database connection
            cmd.Connection = dbConn;
        
            // Set the command type to text
            cmd.CommandType = CommandType.Text;


            // Execute the SQL Statement
            OracleDataReader oraReader = cmd.ExecuteReader();
   
            ImageDataGrid.DataSource = oraReader;
            ImageDataGrid.DataBind();
         }
         catch(OracleException ex)
         {
            NotificationLabel.Text = ex.Message;
            NotificationLabel.Visible = true;
         }
         finally
         {
            // Close the database connection
            dbHelper.closeDatabaseConnection(dbConn);
         }
  }


  #region Web Form Designer generated code
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: This call is required by the ASP.NET Web Form Designer.
   //
   InitializeComponent();
   base.OnInit(e);
  }
  
  /// <summary>
  /// Required method for Designer support - do not modify
  /// the contents of this method with the code editor.
  /// </summary>
  private void InitializeComponent()
  {    
         this.Load += new System.EventHandler(this.Page_Load);


      }
  #endregion
 }
}

The end result when this application is executed in your browser will look similar to the following screenshot.

Now for the final step which will display the selected image.

Display the Selected Image

[ Download Code ]

This is where the big payoff comes in. Once a user selects an image they wish to view you will need to accomplish the following steps.

Create yet another web form only this time the only control you need to add is a Label and again the Label is used form displaying any errors that may arise otherwise we will display the image. Here is the code for the web form.

<%@ Page language="c#" Codebehind="Article570_2.aspx.cs" 
AutoEventWireup="false" Inherits="ASPAllianceArticles.Article570_2" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
   <HEAD>
      <title>Article570_2</title>
      <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
      <meta name="CODE_LANGUAGE" Content="C#">
      <meta name="vs_defaultClientScript" content="JavaScript">
      <meta name="vs_targetSchema" 
      content="http://schemas.microsoft.com/intellisense/ie5">
   </HEAD>
   <body>
      <form id="Form1" method="post" runat="server">
         <P>
            <asp:Label id="NotificationLabel" runat="server" Visible="False"></asp:Label></P>
      </form>
   </body>
</HTML>

The requirement here is the photo id which is used to pull the correct record from the database and display the image using the BinaryWrite method.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


// Application Import(s)
using ASPAlliance.Utilities;


namespace ASPAllianceArticles
{
 /// <summary>
 /// Summary description for Article570_2.
 /// </summary>
 public class Article570_2 : System.Web.UI.Page
 {
      protected System.Web.UI.WebControls.Label NotificationLabel;
   
  private void Page_Load(object sender, System.EventArgs e)
  {
         string imageId = Request.QueryString["imageId"];
         OracleConnection dbConn = null;
         ASPAlliance.Utilities.OracleDatabaseHelper dbHelper =
            new ASPAlliance.Utilities.OracleDatabaseHelper();


         try
         {
            // Establish the database connection
            dbConn = dbHelper.openDatabaseConnection();


            // define the sql to perform the database insert
            string sqlStmt = "select photo from smstestblob where id = " + imageId;


            


            // Establish a new OracleCommand
            OracleCommand cmd = new OracleCommand();


            // Set command to create your SQL statement
            cmd.CommandText = sqlStmt;


            // Set the OracleCommand to your database connection
            cmd.Connection = dbConn;
        
            // Set the command type to text
            cmd.CommandType = CommandType.Text;


            // Execute the SQL Statement
            OracleDataReader oraReader = cmd.ExecuteReader();


            if ( oraReader.Read())
            {
               Response.BinaryWrite( (byte[]) oraReader["photo"] );
            }
   
            //ImageDataGrid.DataSource = oraReader;
            //ImageDataGrid.DataBind();
         }
         catch(OracleException ex)
         {
            NotificationLabel.Text = ex.Message;
            NotificationLabel.Visible = true;
         }
         finally
         {
            // Close the database connection
            dbHelper.closeDatabaseConnection(dbConn);
         }
  }


  #region Web Form Designer generated code
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: This call is required by the ASP.NET Web Form Designer.
   //
   InitializeComponent();
   base.OnInit(e);
  }
  
  /// <summary>
  /// Required method for Designer support - do not modify
  /// the contents of this method with the code editor.
  /// </summary>
  private void InitializeComponent()
  {    
         this.Load += new System.EventHandler(this.Page_Load);


      }
  #endregion
 }
}

At this point when you clicked view image on the previous webform you will have the image presented to your user via their browser.

As this article now comes to a close, you should successfully be able to establish the database schema to handle a Blob, build the web form for the end user to utilize, and finally see the usefulness of the separation of common functionality whether you utilize a class or the Web.config file. Good luck and I would relish the idea of hearing any success stories born from this article so be sure to post you comments.

Feel free to discuss this article at my Blog.



User Comments

Title: Read/Write BLOB data when file > 32K   
Name: Roger Rowe
Date: 3/7/2005 1:03:00 PM
Comment:
Go see this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322796
This shows you how to insert larger data - your version will die for data larger than 32K - a PLSQL limitation).






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 10/25/2014 2:56:05 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search