The ability to insert a file into a VARBINARY(MAX) or VARCHAR(MAX) column using OPENROWSET is an awesome tool. The obstacle comes when you wish to insert all or specific files from a foler into sql. To do this you can use the System.IO namespace and utilizing directory and file objects you can iterate through all, or selected files in a path specified in the above clr stored procedure. After deploying this proc I would recommend adding a default value to the extn input parameter of '*.*', which indicates all files.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_sp_bulkload
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub clr_sp_bulkload(ByVal clm As SqlString, ByVal tbl As SqlString, _
ByVal dir As SqlString, ByVal extn As SqlString)
'Declare variables and assign the values form the sql parameters
'Trim the sql parameters to remove leading or trailing spaces that
'might cause issues in code
Dim sp As SqlPipe = SqlContext.Pipe()
Dim cmd As New SqlCommand()
Dim column As String = clm.ToString.Trim
Dim table As String = tbl.ToString.Trim
Dim dr As String = dir.ToString.Trim
Dim flnm As String
Dim ext As String = extn.ToString.Trim
'Assure tha the directory exists
If Directory.Exists(dr) Then
'If the directory exists then loop through the files in the directory
'path using the optional argument of the search criteria
For Each flnm In Directory.GetFiles(dr, ext)
Dim fi As New FileInfo(flnm)
'Enter the try block to assure that there is not a lock on
'the file that would cause an error
Try
'The command is built using the input parameters
cmd.CommandText = " INSERT " & table & _
" SELECT * FROM OPENROWSET(BULK '" & _
fi.FullName.ToString & "',SINGLE_BLOB) AS x;"
'This will send the insert command to the Messages tab which will make it
'easier to troubleshoot as it executes the query
sp.Send(" INSERT " & table & _
" SELECT * FROM OPENROWSET(BULK '" & _
fi.FullName.ToString & "',SINGLE_BLOB) AS x;")
sp.ExecuteAndSend(cmd)
Catch ex As Exception
sp.Send("File " & fi.FullName.ToString & " is not accessible")
End Try
Next
Else
sp.Send("The direcory does not exists or is inaccessible")
End If
End Sub
End Class
SQL CODE
Alter the proc to set a default value for the extn input parameter that will accept all files
ALTER PROCEDURE [dbo].[clr_sp_bulkload]
@clm [nvarchar](4000),
@tbl [nvarchar](4000),
@dir [nvarchar](4000),
@extn [nvarchar](4000) = '*.*'
AS
EXTERNAL NAME [clr_sp_bulkload].[clr_sp_bulkload.clr_sp_bulkload].[clr_sp_bulkload]
GO
Create a temp table to test the procedure
CREATE TABLE #temp(
col1 VARBINARY(MAX)
EXEC dbo.clr_sp_bulkload 'col1', '#temp', 'C:\Documents and Settings\user\My Documents'
SELECT *
FROM [#temp]
|