How to retrieve audio from Oracle database using C#, VB.NET

Download Source Code

Introduction

In this article I will explain how to retrieve audio stored as BLOB field in Oracle database using C# and VB.NET

Description

In my previous article I explained How to store audios in Oracle from ASP.NET using C#, VB.NET. In this article I will explain how to retrieve audios stored in Oracle 11g Express Edition database into audio folder using C# and VB.NET. We have an AUDIOS table to store audios in the database as following

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

We have inserted some records in the AUDIOS table as following

2014-03-19 23_07_17-Oracle SQL Developer _ system-HR

Step 1: Create a new Console Application using Visual C# or Visual Basic

2014-03-18 23_38_12-New Project

Step 2: Add reference to System.Data.OracleClient in your project

2014-03-19 22_23_10-Add Reference

Step 3:

Add following in Program.cs file in case of Visual C# application

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

namespace RetrieveAudioFromOracle
{
    class Program
    {
        static void Main(string[] args)
        {
            RetrieveAudios();
        }

        private static void RetrieveAudios()
        {
            string ConString = @"Data Source=XE;User Id=hr;Password=********;";
            string CmdString = string.Empty;

            using (OracleConnection con = new OracleConnection(ConString))
            {
                try
                {
                    CmdString = "SELECT AUDIO_NAME AS Audio, AUDIO_DATA, AUDIO_EXTENSION AS Extension, CREATION_DATE AS DateTime FROM AUDIOS";
                    OracleCommand cmd = new OracleCommand(CmdString, con);
                    con.Open();
                    OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);

                    while (reader.Read())
                    {
                        int bufferSize = 1024;
                        int columnIndex = 1;
                        long bytesReturned, startIndex = 0;
                        byte[] audioData = new byte[bufferSize];
                        string audioName = string.Empty;

                        audioName = reader["Audio"].ToString();
                        Console.WriteLine("Downloading : " + audioName);
                        if (!Directory.Exists("audio"))
                        {
                            Directory.CreateDirectory("audio");
                        }

                        using (BinaryWriter bw = new BinaryWriter(new FileStream("audio/" + audioName, FileMode.OpenOrCreate, FileAccess.ReadWrite)))
                        {
                            bytesReturned = reader.GetBytes(columnIndex, startIndex, audioData, 0, bufferSize);

                            while (bytesReturned == bufferSize)
                            {
                                bw.Write(audioData);
                                bw.Flush();
                                startIndex += bufferSize;
                                bytesReturned = reader.GetBytes(columnIndex, startIndex, audioData, 0, bufferSize);
                            }

                            bw.Write(audioData, 0, (int)bytesReturned - 1);
                            bw.Close();
                        }
                    }
                    Console.WriteLine("Download complete!");
                    Console.ReadKey();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                finally
                {
                    if (con.State == System.Data.ConnectionState.Open)
                        con.Close();
                    Console.ReadKey();
                }
            }
        }
    }
}

Add following in Module1.vb in case of a Visual Basic application

Imports System.IO
Imports System.Data
Imports System.Data.OracleClient

Module Module1

    Sub Main()
        RetrieveAudios()
    End Sub

    Public Sub RetrieveAudios()
        Dim ConString As String = "Data Source=XE;User Id=hr;Password=********;"
        Dim CmdString As String = String.Empty

        Using con As New OracleConnection(ConString)
            Try
                CmdString = "SELECT AUDIO_NAME AS Audio, AUDIO_DATA, AUDIO_EXTENSION AS Extension, CREATION_DATE AS DateTime FROM AUDIOS"
                Dim cmd As New OracleCommand(CmdString, con)
                con.Open()
                Dim reader As OracleDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)

                While (reader.Read())
                    Dim bufferSize As Integer = 1024
                    Dim columnIndex As Integer = 1
                    Dim bytesReturned As Long = 0
                    Dim startIndex As Long = 0
                    Dim audioData(bufferSize) As Byte
                    Dim audioName As String = String.Empty

                    audioName = reader("Audio").ToString()
                    Console.WriteLine("Downloading : " + audioName)
                    If (Directory.Exists("audio") = False) Then
                        Directory.CreateDirectory("audio")
                    End If

                    Using bw As New BinaryWriter(New FileStream("audio/" + audioName, FileMode.OpenOrCreate, FileAccess.ReadWrite))

                        bytesReturned = reader.GetBytes(columnIndex, startIndex, audioData, 0, bufferSize)

                        While (bytesReturned = bufferSize)
                            bw.Write(audioData)
                            bw.Flush()
                            startIndex += bufferSize
                            bytesReturned = reader.GetBytes(columnIndex, startIndex, audioData, 0, bufferSize)
                        End While

                        bw.Write(audioData, 0, Integer.Parse(bytesReturned - 1))
                        bw.Close()
                    End Using
                End While
                Console.WriteLine("Download complete!")
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            Finally
                If (con.State = ConnectionState.Open) Then
                    con.Close()
                End If
                Console.ReadKey()
            End Try
        End Using

    End Sub

End Module

Here, we have created a method named RetrieveAudios to retrieve audios from the database and store them to the file system. RetrieveAudios method navigates through each records in AUDIOS table using an OracleDataReader object and retrieves bytes stored in the BLOB field AUDIO_DATA. These bytes are then written to a file inside audio folder using a BinaryWriter object.

Run the application to download audio files

Console_RetrieveAudioFromOracle_

All audios in the database are downloaded to the audio folder inside debug folder of the application

2014-03-19 22_37_32-audio

Leave a Reply

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