Saving and Retrieving Wav files from MSSQL, MSDE and MySQL databases

• Download source files - 52.7 Kb

Introduction

I needed to be able to FTP wav files to a server but using a normal FTP connection and transfer took too long, 30 to 40 seconds. So I decided a web service was what I needed.

But my first problem was figuring out how to save the wav files to a database. It wasn't too hard to figure out if I was using a MSSQL database. There were many articles about saving images and retrieving images from a MSSQL database and I was able to modify the code from those articles so that I could save and retrieve my .wav files. But the majority of those examples used the image datatype for MSSQL.

However, we were using a MySQL database and the webservice I was using was written in PHP, so that gave me some addition problems to overcome. In my tests I had to use a base64 string because that was the only data type I could send to my web service. My PHP web service could not handle a byte array being passed to it.

In addition, I could not find many articles about saving .wav files, so finding reference material turned out to be a considerable task.

The final result was fairly simple as you will see, but getting to that result was rather time consuming and involved a lot of trial and error.

This first section shows the code for using MSSQL as the database

Private Sub PrepareWavFile()

    Dim strInFile As String
    Dim strOutFile As String

    strInFile = Application.StartupPath() & "\" & Me.TextBox1.Text
    strOutFile = Application.StartupPath() & "\" & Me.TextBox2.Text

    If File.Exists(strInFile) = False Then Exit Sub

    Dim fs As FileStream
    Dim br As BinaryReader
    Dim info As New FileInfo(strInFile)
    Dim WavFile(CInt(info.Length)) As Byte

    Try
        fs = New FileStream(strInFile, FileMode.Open, FileAccess.Read)
        br = New BinaryReader(fs)
        WavFile = br.ReadBytes(CInt(info.Length))
        intWavLength = Int(CType(WavFile.Length, Integer))
    Finally
        ' make sure objects are closed in case the thread
        ' was aborted in the middle of this method
        If Not (br Is Nothing) Then br.Close()
        If Not (fs Is Nothing) Then fs.Close()
    End Try

    SaveWavToSQLTable(WavFile)

End Sub

Public Sub SaveWavToSQLTable(ByVal bwavfile() As Byte)

    'MS SQL/MSDE> wavfiles table; 
    '+-----------+-------------+------+-----+----------+ 
    '| Field | Type | Null | Key | Size | 
    '+-----------+-------------+------+-----+----------+ 
    '| ID | Int | | | 4 | 
    '| filename | Nvarchar(25)| YES | | 50 | 
    '| Soundfile | nText | YES | | 16 | 
    '+-----------+-------------+------+-----+----------+ 
    Dim DBInsertCmd As SqlClient.SqlCommand
    Dim sSQL As String
    Dim strConn As String

    'set these parameters to your own database
    strConn = "data source=Local;initial catalog=TestDB;" &_
              "integrated security=SSPI;" &_
              "persist security info=False;packet size=4096"
    DBInsertCmd = New SqlClient.SqlCommand
    DBInsertCmd.Connection = New SqlClient.SqlConnection(strConn)
    ' This part is to convert byte array to Base64 string 
    Dim strBase64WavFile As String = Convert.ToBase64String(bwavfile)
    
    sSQL = "INSERT INTO wavfiles(filename, soundfile) values (@filename, " &_
           "@bwavfile)"
    DBInsertCmd.CommandText = sSQL
    With DBInsertCmd.Parameters
        .Clear()
        .Add("@filename", Me.TextBox2.Text)
        .Add("@bwavfile", strBase64WavFile)
    End With

    DBInsertCmd.Connection.Open()
    Try
        DBInsertCmd.ExecuteNonQuery()
        MessageBox.Show("Wav File Saved")
    Catch oExcept As Exception
        MessageBox.Show(oExcept.Message)
    End Try

    DBInsertCmd.Connection.Close()
    DBInsertCmd = Nothing
End Sub

Private Sub retrieveSQLwavFile()
    Dim DBSelectCmd As SqlClient.SqlCommand
    Dim sSQL As String
    Dim strConn As String
    Dim strOutFile As String

    strOutFile = Application.StartupPath() & "\" & Me.TextBox4.Text
    strConn = "data source=Local;initial catalog=TestDB;integrated " &_
              "security=SSPI;persist security info=False;packet size=4096"

    DBSelectCmd = New SqlClient.SqlCommand
    DBSelectCmd.Connection = New SqlClient.SqlConnection(strConn)
    sSQL = "SELECT Photo FROM Image WHERE FileName=(@filename)"

    DBSelectCmd.CommandText = sSQL
    With DBSelectCmd.Parameters
        .Clear()
        .Add("@filename", Me.TextBox3.Text)
    End With
    DBSelectCmd.Connection.Open()
     
    Dim b() As Byte
    Try
        b = DBSelectCmd.ExecuteScalar()

        Dim K As Long
        K = UBound(b)

        Dim WriteFs As New FileStream(strOutFile, FileMode.Create, _
                                      FileAccess.Write)
        WriteFs.Write(b, 0, K)
        WriteFs.Close()

        MessageBox.Show("Wav file has been retrieved and written to " &_
                        "application folder")
    Catch oExcept As Exception
        MessageBox.Show(oExcept.Message)
    End Try

    DBSelectCmd.Connection.Close()
    DBSelectCmd = Nothing
End Sub