将Wav文件保存和从MSSQL、MSDE和MySQL数据库中检索

• 下载源代码文件 - 52.7 Kb

引言

我需要能够将wav文件通过FTP上传到服务器，但使用正常的FTP连接和传输速度太慢，需要30到40秒。所以我决定使用Web服务。

但我的第一个问题是如何将wav文件保存到数据库。如果我使用的是MSSQL数据库，这并不难弄清楚。有很多关于保存和检索图像的MSSQL数据库的文章，我能够修改这些文章中的代码，以便保存和检索我的.wav文件。但大多数示例都使用了MSSQL的图像数据类型。

然而，我们使用的是MySQL数据库，我使用的Web服务是用PHP编写的，这给我带来了一些额外的难题。在我的测试中，我不得不使用base64字符串，因为那是唯一可以发送到我的Web服务的数据类型。我的PHP Web服务无法处理传递给它的字节数组。

此外，我找不到很多关于保存.wav文件的文章，因此寻找参考资料变成了一项相当艰巨的任务。

最终的结果很简单，正如您将看到的，但达到这个结果却耗费了相当多的时间和精力，并且涉及大量的试验和错误。

第一部分展示了使用MSSQL作为数据库的代码

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