SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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...

create new project for SQL Server in Visual Studio

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.

CLR project assembly and root namespace name in Visual Studio


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

add project Reference in Visual Studio

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.

add Microsoft Shell Controls and Automation as reference for SQL Server CLR project

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.

Visual Studio CLR project imported namespaces

And below is the main code section used for the CLR class library for SQL Server.

SQL Server CLR stored procedure codes to read file properties

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

create assembly on SQL Server to get file properties

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.

create CLR stored procedure in SQL Server

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.

read extended file properties from SQL Server using CLR stored procedure


Download SQL Server CLR Stored Procedure to Read File Attributes

SQL 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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.