SqlServer Clr Functions Utility

• Download source code - 368.3 KB

Introduction

.NET Framework is very useful in developing software.

So why not use it in SqlServer as well?

Below, you can find the functions I developed to simplify my work and have query in an easier manner.

Using the Code

First of all, you need to understand what is CLR and how to enable it.

Here an article that can be useful in "Preparing the database":

• https://www.codeproject.com/Articles/21149/Invoking-a-WCF-Service-from-a-CLR-Trigger

For every function, I attached scripts to deploy them:

• https://sourceforge.net/projects/sqlserver-clr-functions/files/latest/download?source=navbar

Functions are:

• ParametersMem
• Impersonate
• FindFiles
• FileMove
• Zip
• FindRowInFile
• GetWebPage
• InlineAggr
• INFORMATION_SCHEMA_COLUMNS
• RowToColumn
• SendMail
• DownloadFromNetwork

1 - ParametersMem

https://www.codeproject.com/Tips/1204621/Clr-Functions-Utility-ParametersMem

• SetParameterMem
• GetParameters
• DelParameterMem
• ClearMem

These functions consent to keep in memory for each session id some parameters.

Imagine you have management studio with 2 opened query editors. Each one of them has 2 different session ids, for example, id1, id2. Now try this example:

on id1:

select [dbo].[SetParameterMem]('@p1','0')
select [dbo].[SetParameterMem]('@p2','3')

select [dbo].[GetParameterMem]('@p1')
select * from [dbo].[GetParametersMem]('')

cid	spid	par	val	date_modify
edadd901-5cf1-4348-97df-8205b9b024ef	53	@p1	0	22/08/2017 09:42
edadd901-5cf1-4348-97df-8205b9b024ef	53	@p2	3	22/08/2017 09:42

on id2:

select [dbo].[SetParameterMem]('@p1','qwerty')
select [dbo].[SetParameterMem]('@p5','asd')

select [dbo].[GetParameterMem]('@p5')
select * from [dbo].[GetParametersMem]('')

cid	spid	par	val	date_modify
52ac2412-ec59-4e94-aa39-053af30affcd	54	@p1	qwerty	22/08/2017 09:47
52ac2412-ec59-4e94-aa39-053af30affcd	54	@p5	asd	22/08/2017 09:47

I developed this because in a web server, you have just one running user that execute queries, but if you set the connection string with "pooling=false", you can have a new session id every time.

Try to open a new connection. The first thing to do is: doing:

select [dbo].[SetParameterMem]('@user','user_1')

Now until you keep open this connection, you have in memory the @user parameter with its value ‘user_1’. That could identify the user that is working on web application. Now, in every db object, you can get this value with:

select [dbo].[GetParameterMem]('@user')

What happens when I close the session? Nothing, parameters remains in memory until you exec...

select [dbo].[ClearMem]()

that removes all and only closed sessions.

To remove only some parameters, you can execute:

select [dbo].[DelParameterMem]('@user')

To have all parameters settled in memory from different sessions, execute:

select * from [dbo].[GetParametersMem]('*')

cid	spid	par	val	date_modify
edadd901-5cf1-4348-97df-8205b9b024ef	53	@p1	0	22/08/2017 09:47
edadd901-5cf1-4348-97df-8205b9b024ef	53	@p2	3	22/08/2017 09:42
52ac2412-ec59-4e94-aa39-053af30affcd	54	@p5	asd	22/08/2017 09:47
52ac2412-ec59-4e94-aa39-053af30affcd	54	@p1	qwerty	22/08/2017 09:47

2. Impersonate

• SetImpersonate
• DelImpersonate

Example:

select dbo.[SetImpersonate]('domain','user','pass')
select * from dbo.FindFiles('\\192.168.1.100\path','',1)

Imagine you are on a SQL Server and you need to access the same external path.

For example, you have a backup and you need to copy it, but you don't have the permission to access the server where the instance is.

With this function, you can force the access of the account.

[SetImpersonate] uses the same logic of [SetParameterMem]. Indeed, if you try:

select * from dbo.GetParametersMem('')

cid	spid	par	val	date_modify
f8908399-0cac-4e4e-a479-7940044708a9	56	*domain	domain	08/08/2017 10:30
f8908399-0cac-4e4e-a479-7940044708a9	56	*user	user	08/08/2017 10:30
f8908399-0cac-4e4e-a479-7940044708a9	56	*pass	pass	08/08/2017 10:30

If you want to remove the impersonate, just exec:

select dbo.DelImpersonate()

3. FindFiles

This function allows to get all files in one directory:

select * from [dbo].[FindFiles]('C:\folder\','',1)

parameters

• @path: It's the directory where the files are searched.
• @filter: It's the extension to filter the files
• @subdir
  • 0 -> only in the path
  • 1 -> also in the subdirectories

4. FileMove

This function allows to copy/move/delete a file.

select dbo.FileMove('c:\frompath', 'file.txt', 'c:\topath', null, 0, 1)

parameters

• @pathFrom: It's the source directory
• @fileFrom: It's the source file
• @pathTo: It's the destination path
• @fileTo: It's the name of file in case of copy/move.
  if null, it will have the same name of source
• @action
  • 0 -> copy and paste
  • 1 -> cut and paste
  • 2 -> delete
• @overwrite: 1 to overwrite, 0 to create a new one (it will give you an error in case the file already exists)

5. Zip

This function allows to zip/unzip files.

select [dbo].[Zip]('c:\pathfrom','zipfile.zip','pathto','-1')
select [dbo].[Zip]('c:\pathfrom,c:\pathfrom\file1.txt,c:\pathfrom2\','zipfile.zip','pathto','0')

parameters

• @pathFrom: It's the directory where the zip file is (unzip case) or the directory/file to zip (zip case)
• @zip: It's the zip file
• @pathTo: It's the destination folder where files are extracted (unzip case) or where zip file is created (zip case)
• @level: It's the compression level
  • <0 -> unzip process
  • 1/9 -> zip process (value means the compression level)

6 - FindRowInFile

This function returns files that contain a word in a specific row and column.

Example 1

select * from FindRowInFile('path',null,'ToFind',2,'','|',3,'')

Example 2

select * from FindRowInFile('path','file.txt','ToFind',2,'','|',3,'')

Example 3

select * from FindRowInFile('path',null,'ToFind',2,'20170101','|',3,'')

parameters

• @path: It's the directory
• @file: “null” if you want to get all the files. Otherwise, just files with the value you enter are checked
• @find: It's the word to be found
• @pos: It's the column in the text where to find it
• @date: I can define the date of last modify of files or leave it "null"
• @delimitator: The delimitator of columns in text
• @row: The row where to find the word
• @filter: The extension of filter files, else blank ('') or null

7. GetWebPage

This function downloads and returns the web page from a URL. It also executes the JavaScript of the page.

Example

declare @s nvarchar(max)
select @s=node
from [dbo].[GetWebPage]('https://www.google.it/','')
print @s

and this extracts only from the tag "td":

declare @s nvarchar(max)
select @s=node
from [dbo].[GetWebPage]('https://www.google.it/','//td')
print @s

and has sense using SQL XML functions:

select CONVERT(xml,node) from [dbo].[GetWebPage]('https://www.google.it/','//td')

parameters

• url: It's the web page you want to download
• node: It's the node from which the page is extracted

8. InlineAggr

This function helps to have a simple string concatenation:

Example

select c1,[dbo].[InlineAggr](c2,';',0,0),[dbo].[InlineAggr](c3,';',1,-1)
from
(
select 'a1'c1,'b1'c2,'c1'c3 union all
select 'a1'c1,'bfd1'c2,'cwe1'c3 union all
select 'a1'c1,'bas1'c2,'cfffff1'c3 union all
select 'a1'c1,'bas1'c2,'cfffff1'c3 union all
select 'a2'c1,'b2'c2,'c2'c3 union all
select 'a3'c1,'b3'c2,'c3'c3 union all
select 'a4'c1,'b4'c2,'c4'c3 union all
select 'a5'c1,'b5'c2,'c5'c3 union all
select 'a6'c1,'b6'c2,'c6'c3
)t
group by c1

parameters

• @value: It's the column to concatenate
• @del: It's the delimitator of values
• @distinct: If you need to keep only one distinct value.
  Could be 0 (all) or 1 (distinct)
• @sort: 0 if you don’t need to sort values, 1 to sort by asc and -1 for desc order

9. INFORMATION_SCHEMA_COLUMNS

It's the same of SQL view, but it can extract the information from different databases without dynamic query

Example

select * from [dbo].[INFORMATION_SCHEMA_COLUMNS]('test')

10. RowToColumn

This function transposes a row in a column.

Example

select * from [dbo].[RowToColumn]('path','file','|',1,-1)

parameters

• path: It's the path of file
• file: The file to be opened
• delimitator: It's the delimitator of ttext columns;
  in case of Excel, this will be ignored.
• xls: if <0 it means it's a text file. Otherwise, it's the sheet of Excel

11. SendMail

This function can send Email with an attachment.

Example

declare @rtn int
select @rtn=dbo.SendMail('smpt','user','pass',port,
            EnableSsl,'from','to1,to2,toN','cc1,cc2,ccN','subject','body','c:\attach')
select @rtn

parameters

• smtp: It's the smtp server
• user: It's the user email
• pass: It's the email pass
• port: It's the port number of email
• EnableSsl: Boolean that enables(1) ssl or not(0)
• from: It's the sender email
• to: Recipient email. could be concatenated with ',' for multiple addresses
• cc: Email of who is in CC. could be concatenated with ',' for multiple addresses
• subject: It's the subject
• body: It's the body
• attach: It's the folder where the attach is. could be concatenated with ',' for multiple files

12 - DownloadFromNetwork

This function downloads a file from a URL.

Example

select * from dbo.DownloadFromNetwork('domain', 'user', 'pass', 'url', 'path', 'file')

parameters

• domain: It's the domain of the user to access url
• user: It's the user to access url
• pass: It's the pass
• url: The URL that contains the file to be downloaded
• path: The directory where to save the file
• file: The name of downloaded file

Points of Interest

Unless it is known that Clr is not frequently used, I found it very useful in terms of improvements and performances compared to SQL language.