Read File Properties using SQL Server CLR Stored Procedure
SQL Server CLR tutorial shows how to read file properties using SQL CLR stored procedure created by using given Visual Studio CLR project code samples for database programmer and administrators. Using given SQL Server CLR stored procedure, users can pass the path of a file on the server as a parameter and get file properties like last write time, edited by user, size of the file, type of the document and its extension, etc.
Create Visual Studio Project for SQL Server CLR Stored Procedure
Let's start our tutorial with creating our new CLR project in Visual Studio.
Launch Visual Studio
Create new project by following menu options: File > New > Project...
From Installed Templates select Visual Basic Class Library project template.
Of course you build your CLR project with class library template using C-Sharp too.
I choosed to name my SQL Server CLR project as SQLCLRFileAttributes.
When our project is created, right click on project in Solution Explorer and choose Properties from context menu.
Set root namespace name as I did by giving Kodyaz in this tutorial sample.
Root name space will be used while registering this CLR library on SQL Server database later in our tutorial.
Project References in Visual Studio
To reach file properties on stored on the server disk files, we need helper libraries. Developers have to add these Windows libraries to the project as reference in order to use them in VB codes.
Add following libraries as project reference.
Programmers require each of following assemblies to add as reference to their SQL Server project:
Shell32.dll
To add SQL Server Management Objects dll files as project Reference in Visual Studio on Solution Explorer windows, right click on the References under project name. Select Add Reference... on context menu
Click Browse button on Visual Studio Reference Manager dialog screen.
Switch to COM tab Type Libraries.
Scroll down until you reach Microsoft Shell Controls and Automation library.
Select above assembly file and press Add button. Then click OK to close Reference Manager dialog screen.
SQL Server CLR Stored Procedure Codes
Microsoft .NET Developers are now ready to switch to code editor on Visual Studio IDE for developing CLR objects for SQL Server. Double click on the .vb class file from Solution Explorer which will display it in Code Editor view in Visual Studio. You can rename the .vb class file before you proceed further to a more suitable name. The class name is used during creation of SQL Server CLR objects in SQL Server Management Studio as we will see later. I renamed the default class file name to FileAttributes.vb in this tutorial.
Please note that I added following import namespace code line in Visual Studio project file.
Especially, Shell32 namespace is required to reach file properties and attributes.
Here is the imports section which is placed at the top of the FileAttributes.vb code file.
And below is the main code section used for the CLR class library for SQL Server.
Here below I copy the VB.NET codes which you can use directly or by converting to C-Sharp according to your project language selection.
I use FileInfo() method to validate the file path in order to check if target file exists on the server at given path.
Then I create an instance of Shell32.Shell object.
Using this object, I create the Folder and FolderItem objects.
Because of this code blocks, registering Shell32.dll file and importing the Shell32 namespace is important.
Shell32 Folder class enables us to get file properties using GetDetailsOf() method.
Every file propery read using Folde class GetDetailsOf() method is stored in columns of SqlDataRecord instance and finally returned back to SQL Server with the execution of code line SqlContext.Pipe.SendResultsEnd()
Imports Shell32
Imports System.IO
Imports Microsoft.SqlServer.Server
Public Class FileAttributes
<Microsoft.SqlServer.Server.SqlProcedure>
Public Shared Sub getExtendedFileProperties(pFilePath As String)
Try
Dim f As New FileInfo(pFilePath)
If Not f.Exists() Then
Exit Sub
End If
' Columns of the result set for file attributes as propert name and value pair
Dim propertyName As SqlMetaData
propertyName = New SqlMetaData("Property", SqlDbType.NVarChar, 128)
Dim propertyValue As SqlMetaData
propertyValue = New SqlMetaData("Value", SqlDbType.NVarChar, 128)
Dim shell As New Shell32.Shell
Dim shFolder As Shell32.Folder
shFolder = shell.NameSpace(Path.GetDirectoryName(pFilePath))
Dim folderItem As FolderItem
folderItem = shFolder.ParseName(Path.GetFileName(pFilePath))
' Create new row with columns
Dim rowFileProperty As SqlDataRecord
rowFileProperty = New SqlDataRecord(New SqlMetaData() {propertyName, propertyValue})
' Mark the begining of the result-set.
SqlContext.Pipe.SendResultsStart(rowFileProperty)
rowFileProperty.SetSqlString(0, "Path")
rowFileProperty.SetSqlString(1, pFilePath)
SqlContext.Pipe.SendResultsRow(rowFileProperty)
rowFileProperty.SetSqlString(0, "Name")
rowFileProperty.SetSqlString(1, Path.GetFileName(pFilePath)) ' file name with extension
SqlContext.Pipe.SendResultsRow(rowFileProperty)
Dim n As Integer = 0
Do While shFolder.GetDetailsOf(shFolder.Items, n) <> ""
' PropertyName
rowFileProperty.SetSqlString(0, shFolder.GetDetailsOf(shFolder.Items, n))
' PropertyValue
rowFileProperty.SetSqlString(1, shFolder.GetDetailsOf(folderItem, n))
' Send the row back to the client.
SqlContext.Pipe.SendResultsRow(rowFileProperty)
n = n + 1
Loop
' Mark the end of the result-set.
SqlContext.Pipe.SendResultsEnd()
Catch ex As Exception
' error
End Try
End Sub
End Class
If above code is implemented on .vb file, developers are ready to Build project in Visual Studio IDE
In Visual Studio, using top menu follow Build > Rebuild Solution for building your SQL Server SMO project. By default project will be build in debug mode. After you complete all development task, before deploying your solution to productive environmen, build it in release mode and then deploy it.
Create Assembly and CLR Stored Procedure in SQL Server
After Visual Studio project is built and assembly file of the class library is created, copy the .dll file where we can use it on the SQL Server instance. As seen in below Create Assembly SQL command, the full path of the .dll file is referred while registering the SQL Server CLR library.
CREATE ASSEMBLY SqlCLRFileReadAssembly
FROM 'C:\Kodyaz\SQL-CLR-library\SQLCLRFileAttributes.dll'
WITH PERMISSION_SET = UNSAFE
In above Create Assembly T-SQL syntax, you can use the project name as the assembly name to prevent confusions about the source of the registered .dll file on SQL Server database
After CLR project assembly is registered on a database on SQL Server, functions and methods in the class library can be created as CLR stored procedures and CLR functions.
CREATE PROCEDURE sp_getExtendedFileProperties
@FileName NVARCHAR(256)
AS
EXTERNAL NAME SqlCLRFileReadAssembly.[Kodyaz.FileAttributes].getExtendedFileProperties
-- AssemblyName.[RootNamespace.ClassName].SQLFunctionName
GO
Assembly Name: This is the assemly name used during Create Assembly statement for the CLR project .dll file registration to SQL Server
Root Namespace: This is defined in Visual Studio solution properties window in Application tab
Class Name: Public class name defined in Visual Studio project
SQLFunction Name: SQL CLR function name or sub routine name defined in Visual Studio class file.
SQL developers and database administrators can use following sample code to read extended file properties about a specific file from SQL Server using CLR stored procedure.
declare @file nvarchar(256)
set @file = N'C:\Kodyaz\Kodyaz CLR Projects for SQL Server.pdf'
exec sp_getExtendedFileProperties @file
Following screenshot is displaying only a very small portion of the returned file properties using SQL Server CLR stored procedure. For example, the total number of all file properties is 289 for this SQL CLR stored procedure execution.
Download SQL Server CLR Stored Procedure to Read File Attributes
You can download SQL Server CLR project which is ready to be opened in Visual Studio for review and improvement by using following link: SQL Server CLR Stored Procedure to read File Properties sample project.
SQL Server programmers can use sample SQL Server CLR Stored Procedure Visual Studio project freely in their developments. I hope you find this SQL Server tutorial useful which features CLR stored procedure development.