Thursday, September 29, 2011

Adding a Oracle blob using the .NET entity framework.

Recently I have been experimenting with some .NET programming using the Oracle ODAC beta  11.2.0.2.40. This version of the odac allows you to use the .NET entity framework. You need Visual Studio 2010. So far it is  a lot of fun.
If you are a  asp.net programmer and have not used the entity framework  there is a very good tutorial here:
http://www.asp.net/entity-framework/tutorials/the-entity-framework-and-aspnet-%E2%80%93-getting-started-part-1
The above tutorial uses SQL server. I am hitting the Oracle database in this example.

Table Structure
CREATE TABLE CHANGE_OWN.ATTACHMENTS
(
  ATTACHMENT_ID  NUMBER                         NOT NULL,
  REQUEST_ID     NUMBER,
  DATA           BLOB,
  DESCRIPTION    VARCHAR2(200 BYTE),
  DATE_ATTACHED  DATE,
  FILE_NAME      VARCHAR2(100 BYTE)
);

Here is the setup to add a row.


This code is the form view, pretty standard stuff,

<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="171px"
        AutoGenerateRows="False" DataKeyNames="ATTACHMENT_ID"
        AutoGenerateInsertButton="False" DefaultMode="Insert"
                DataSourceID="EntityDataSource3"
        oniteminserting="AttachmentsDetailsView_ItemInserting"  >
       

 
    <Fields>
        <asp:BoundField DataField="ATTACHMENT_ID" HeaderText="ATTACHMENT_ID"
            SortExpression="ATTACHMENT_ID" ReadOnly="True" Visible="false" />
        <asp:TemplateField HeaderText="REQUEST_ID" SortExpression="REQUEST_ID">
            <ItemTemplate>
                <asp:Label ID="Label1" runat="server" Text='<%# Session["ccREQUESTID"] %>'></asp:Label>
            </ItemTemplate>
            <InsertItemTemplate>
                <asp:TextBox ID="TextBox1" ReadOnly="True" runat="server" Text='<%# Session["ccREQUESTID"] %>'></asp:TextBox>
            </InsertItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="DESCRIPTION" HeaderText="DESCRIPTION"
            SortExpression="DESCRIPTION" />
        <asp:BoundField DataField="DATE_ATTACHED" HeaderText="DATE_ATTACHED"
            SortExpression="DATE_ATTACHED" Visible="false" />
            <asp:BoundField DataField="FILE_NAME" HeaderText="FILE_NAME"
            SortExpression="FILE_NAME" visible="false" />
            <asp:TemplateField HeaderText="DATA" SortExpression="DATA">
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("DATA") %>'></asp:Label>
                </ItemTemplate>
                 <InsertItemTemplate>
                   <asp:FileUpload ID="FileUpload1" runat="server" />
                </InsertItemTemplate>
        </asp:TemplateField>
        <asp:CommandField ShowCancelButton="False" ShowInsertButton="True" />
    </Fields>
   
</asp:DetailsView>
    <asp:Label ID="Label3" runat="server" Text="Select a file and click insert."></asp:Label>
<br /><br />
   
Here is the C# code behind for the  oniteminserting call.

protected void AttachmentsDetailsView_ItemInserting(object sender, DetailsViewInsertEventArgs e)
        {
           // Entities ctx = new Entities();
            byte[] myFileBytes = null;
            FileUpload myUpload = (FileUpload)DetailsView1.FindControl("FileUpload1");

           
             // Need  if not post back

             if (myUpload.HasFile)
                 try
                 {
                    
                     
                     myFileBytes = myUpload.FileBytes;

                     e.Values["REQUEST_ID"] = Convert.ToDecimal(Session["ccREQUESTID"]);
                     e.Values["DATA"] = myFileBytes;
                     e.Values["FILE_NAME"] = Path.GetFileName(myUpload.PostedFile.FileName);
                     e.Values["DATE_ATTACHED"] = DateTime.Now;
                     Label3.Text = "Successfully Attached. You may attach another or select button below";
                 }

                 catch (Exception ex)
              {
                  Label3.Text = "ERROR: " + ex.Message.ToString();
                  e.Cancel = true;
              }

             else
             {
                 Label3.Text = "You have not specified a file.";
                 e.Cancel = true;
             }
        }

Let me know if you want some code to update the row with the blob in it

Steven Moslin