How to store audios in Oracle database from ASP.NET using C#, VB.NET

Introduction

Here, I will explain how to save audios (.wav, .mp3, etc.) in Oracle 11g Express Edition database from ASP.NET using C# and VB.NET

2014-03-15 00_11_13-Mozilla Firefox

Description

Here first we will create a table AUDIOS in Oracle database to store audios. Then we will create an ASP.NET application to store audios in the AUDIOS table.

Step 1 : Create AUDIOS table as following

2014-03-15 00_12_44-Oracle SQL Developer _ Table HR.AUDIOS@system-HR

CREATE TABLE "AUDIOS"
  (
    "AUDIO_NAME" VARCHAR2(30 CHAR),
    "AUDIO_DATA" BLOB,
    "AUDIO_EXTENSION" VARCHAR2(5 CHAR),
    "CREATION_DATE"   DATE
  );

Step 2 : Create a new ASP.NET Web Application

2014-03-15 00_26_50-New Project

Step 3 : Right click on project in the Solution Explorer and add reference to System.Data.OracleClient

Step 4: Write following in HTML source of Default.aspx page to draw controls to upload audios in the database

<!DOCTYPE html>
<html>
<head runat="server">
    <title></title>
    <style type="text/css">
        .fileupload
        {
            border: #7b9ebd 1px solid;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div style="width:500px; border:2px solid Navy;">
            <div style="width:400px; padding:5px; margin:auto; display:table;">
                <asp:GridView ID="gvAudios" runat="server" CellPadding="4"
                    EnableModelValidation="True" ForeColor="#333333" GridLines="None"
                    Width="400px">
                    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                    <EditRowStyle BackColor="#999999" />
                    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                </asp:GridView>
            </div>

            <div style="clear:both; height:20px;"></div>

            <div style="width: 400px; padding:5px;margin:auto; display:table;">
                    <asp:Label ID="lblAudio" runat="server" Text="Select Audio"></asp:Label>
                    <asp:FileUpload ID="fileUploadAudio" runat="server" size="40" CssClass="fileupload" />
                    <asp:Button ID="btnSave" runat="server" Text="Save" CssClass="button3"
                        onclick="btnSave_Click" />
            </div>

            <div style="clear:both; height:10px;"></div>

            <div style="width:400px; padding:5px;margin:auto; display:table;">
                <asp:Label ID="lblMessage" runat="server" Text="" BackColor="Yellow" ForeColor="Red" Width="400px"></asp:Label>
            </div>
        </div>
    </form>
</body>
</html>

Step 5: Write following in code behind file (Default.aspx.cs or Default.aspx.vb) of Default.aspx page. It contains two methods UploadAudio() and DisplayAudioList(). UploadAudio uploads audio to the database and DisplayAudioList displays list of uploaded audios in a GridView

C# Code

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.OracleClient;

namespace MediaStore_Oracle_CS
{
    public partial class _Default : System.Web.UI.Page
    {
        string ConString = @"Data Source=XE;User Id=hr;Password=********;";
        string CmdString = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            DisplayAudioList();
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            if (fileUploadAudio.HasFile)
            {
                UploadAudio(fileUploadAudio.FileName, fileUploadAudio.FileBytes,
                    (new System.IO.FileInfo(fileUploadAudio.FileName)).Extension);
            }
            else
            {
                lblMessage.Text = "Please select a valid file!";
            }
        }

        private void UploadAudio(string FileName, byte[] FileData, string FileExtension)
        {
            using (OracleConnection con = new OracleConnection(ConString))
            {
                try
                {
                    CmdString = "INSERT INTO AUDIOS(AUDIO_NAME, AUDIO_DATA, AUDIO_EXTENSION, CREATION_DATE) "+
                        "VALUES(:AUDIO_NAME, :AUDIO_DATA, :AUDIO_EXTENSION, :CREATION_DATE)";
                    OracleCommand cmd = new OracleCommand(CmdString, con);

                    cmd.Parameters.AddWithValue("AUDIO_NAME", FileName);
                    cmd.Parameters.AddWithValue("AUDIO_DATA", FileData);
                    cmd.Parameters.AddWithValue("AUDIO_EXTENSION", FileExtension);
                    cmd.Parameters.AddWithValue("CREATION_DATE", DateTime.Now);

                    con.Open();
                    OracleString AudioID = string.Empty;
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        lblMessage.Text=(FileName+" added sucessfully to the database!");
                        DisplayAudioList();
                    }
                }
                catch (Exception ex)
                {
                    lblMessage.Text=ex.Message;
                }
                finally
                {
                    if (con.State == System.Data.ConnectionState.Open)
                        con.Close();
                }
            }

        }

        private void DisplayAudioList()
        {
            using(OracleConnection con=new OracleConnection(ConString))
            {
                try
                {
                    gvAudios.DataSource = null;
                    CmdString = "SELECT AUDIO_NAME AS Audio, AUDIO_EXTENSION AS Extension, CREATION_DATE AS DateTime FROM AUDIOS";
                    OracleDataAdapter oda = new OracleDataAdapter(CmdString, con);
                    DataSet ds = new DataSet();
                    oda.Fill(ds);
                    gvAudios.DataSource = ds;
                    gvAudios.DataBind();
                }
                catch (Exception ex)
                {
                    lblMessage.Text = ex.Message;
                }
                finally
                {
                    if (con.State == System.Data.ConnectionState.Open)
                        con.Close();
                }
            }
        }
    }
}

VB.NET Code

Imports System.Data.OracleClient
Imports System.Data

Public Class _Default
    Inherits System.Web.UI.Page

    Dim ConString As String = "Data Source=XE;User Id=hr;Password=********;"
    Dim CmdString As String = String.Empty

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        DisplayAudioList()
    End Sub

    Protected Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If fileUploadAudio.HasFile Then
            UploadAudio(fileUploadAudio.FileName, fileUploadAudio.FileBytes,
                    (New System.IO.FileInfo(fileUploadAudio.FileName)).Extension)
        Else
            lblMessage.Text = "Please select a valid file!"
        End If
    End Sub

    Protected Sub UploadAudio(ByVal FileName As String, ByVal FileData() As Byte, ByVal FileExtension As String)
        Using con As New OracleConnection(ConString)
            Try
                CmdString = "INSERT INTO AUDIOS(AUDIO_NAME, AUDIO_DATA, AUDIO_EXTENSION, CREATION_DATE) " +
                        "VALUES(:AUDIO_NAME, :AUDIO_DATA, :AUDIO_EXTENSION, :CREATION_DATE)"

                Dim cmd As New OracleCommand(CmdString, con)
                cmd.Parameters.AddWithValue("AUDIO_NAME", FileName)
                cmd.Parameters.AddWithValue("AUDIO_DATA", FileData)
                cmd.Parameters.AddWithValue("AUDIO_EXTENSION", FileExtension)
                cmd.Parameters.AddWithValue("CREATION_DATE", DateTime.Now)

                con.Open()

                If (cmd.ExecuteNonQuery() > 0) Then
                    lblMessage.Text = (FileName + " added sucessfully to the database!")
                    DisplayAudioList()
                End If
            Catch ex As Exception
                lblMessage.Text = ex.Message
            Finally
                If (con.State = ConnectionState.Open) Then
                    con.Close()
                End If
            End Try
        End Using
    End Sub

    Protected Sub DisplayAudioList()
        Using con As New OracleConnection(ConString)
            Try
                gvAudios.DataSource = Nothing
                CmdString = "SELECT AUDIO_NAME AS Audio, AUDIO_EXTENSION AS Extension, CREATION_DATE AS DateTime FROM AUDIOS"
                Dim oda As OracleDataAdapter = New OracleDataAdapter(CmdString, con)
                Dim ds As DataSet = New DataSet()
                oda.Fill(ds)
                gvAudios.DataSource = ds
                gvAudios.DataBind()
            Catch ex As Exception
                lblMessage.Text = ex.Message
            Finally
                If (con.State = ConnectionState.Open) Then
                    con.Close()
                End If
            End Try
        End Using
    End Sub

End Class

Download Source Code

2 thoughts on “How to store audios in Oracle database from ASP.NET using C#, VB.NET

  1. Pingback: How to retrieve audio from Oracle database using C#, VB.NET | DEEPAK SHARMA

  2. yahoo

    Pretty section of content. I just stumbled upon your website
    and in accession capital to assert that I get in fact enjoyed account your blog posts.

    Anyway I will be subscribing to your augment and even I achievement you access consistently fast.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *