如何将音频插入 SQL Server

Question

Insert Into [dbo].[Letterland] ([letter],[letterImage])
   Select 'a', BulkColumn,
   From Openrowset (bulk 'H:DataDll-imageannie.png', Single_blob) as img 
Insert Into [dbo].[Letterland] ([letterDescAudio])
    Select 'a', BulkColumn, BulkColumn
   From Openrowset (bulk 'H:DataD11-imageaa.wav' Single_blob) as img 
Insert Into [dbo].[Letterland] ([letterSound])
    Select 'a', BulkColumn, BulkColumn
   From Openrowset (bulk 'H:DataDll-imagea.wav', Single_blob) as img 

这是我尝试过的，但我知道这是不正确的。我正在尝试将数据作为单行插入。

[dbo].[Letterland] ([Letter], [letterImage], [letterDescAudio],  [letterSound])

Answer 1

好吧，您需要先将三个 blob 加载到变量中，然后执行单个INSERT将它们全部插入到表中 - 如下所示：

-- declare a VARBINARY(MAX) variable to hold the "image"
DECLARE @Image VARBINARY(MAX)
-- load the "image"
SELECT @Image = BulkColumn,
FROM Openrowset (bulk 'H:DataDll-imageannie.png', Single_blob) as img 
-- declare a VARBINARY(MAX) variable to hold the "Desc Audio" and load it
DECLARE @DescAudio VARBINARY(MAX)
SELECT @DescAudio = BulkColumn
FROM Openrowset (bulk 'H:DataD11-imageaa.wav' Single_blob) as img 
-- declare a VARBINARY(MAX) variable to hold the "Sound" and load it
DECLARE @Sound VARBINARY(MAX)
SELECT @Sound = BulkColumn
FROM Openrowset (bulk 'H:DataDll-imagea.wav', Single_blob) as img 
-- now do the INSERT with all bits ready to go    
INSERT INTO dbo.Letterland (letter, letterImage, letterDescAudio, letterSound)
VALUES ('a', @Image, @DescAudio, @Sound)

Answer 2

您可以做的是将文件路径保存到数据库中。假设您的音频文件位于路径"~/audio/1.mp3"中。因此，您可以将其插入数据库表中，如下所示：

string audio = <your path to audio>;
INSERT INTO [TABLE NAME] VALUES(audio);