SqlServer Clr 函数实用工具
3.20/5 (10投票s)
我开发这个 DLL 是为了拥有一个更强大的实用工具并简化存储过程和查询。
引言
.NET Framework 在软件开发中非常有用。
那么为什么不在 SqlServer 中也使用它呢？
下面，您可以找到我开发的函数，以简化我的工作并更轻松地进行查询。
Using the Code
首先，您需要了解什么是 CLR 以及如何启用它。
这里有一篇关于“准备数据库”的文章可能会有所帮助
对于每个函数，我都附带了部署它们的脚本
函数包括
ParametersMemImpersonateFindFilesFileMoveZipFindRowInFileGetWebPageInlineAggrINFORMATION_SCHEMA_COLUMNSRowToColumnSendMailDownloadFromNetwork
1 - ParametersMem
https://codeproject.org.cn/Tips/1204621/Clr-Functions-Utility-ParametersMem
SetParameterMemGetParametersDelParameterMemClearMem
这些函数允许为每个会话 ID 在内存中保留一些参数。
想象您有管理工作室，其中打开了两个查询编辑器。每个编辑器都有两个不同的会话 ID，例如，id1，id2。现在尝试这个例子
在 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 |
在 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 |
我开发这个是因为在 Web 服务器中，您只有一个正在运行的用户执行查询，但是如果您将连接字符串设置为 "pooling=false"，那么您每次都可以获得一个新的会话 ID。
尝试打开一个新连接。首先要做的是：执行
select [dbo].[SetParameterMem]('@user','user_1')
现在，只要您保持此连接打开，内存中就会有带有其值“user_1”的@user参数。这可以识别正在 Web 应用程序上工作的用户。现在，在每个数据库对象中，您都可以使用以下方式获取此值
select [dbo].[GetParameterMem]('@user')
当我关闭会话时会发生什么？什么也不会，参数会一直保留在内存中，直到您执行...
select [dbo].[ClearMem]()
它会移除所有且仅移除已关闭的会话。
要仅移除部分参数，您可以执行
select [dbo].[DelParameterMem]('@user')
要获取从不同会话中设置到内存中的所有参数，请执行
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
SetImpersonateDelImpersonate
示例
select dbo.[SetImpersonate]('domain','user','pass')
select * from dbo.FindFiles('\\192.168.1.100\path','',1)
想象一下，您正在 SQL Server 上，需要访问相同的外部路径。
例如，您有一个备份，需要复制它，但您没有权限访问实例所在的服务器。
使用此函数，您可以强制帐户访问。
[SetImpersonate] 使用与 [SetParameterMem] 相同的逻辑。实际上，如果您尝试
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 | 用户 | 08/08/2017 10:30 |
f8908399-0cac-4e4e-a479-7940044708a9 | 56 | *pass | pass | 08/08/2017 10:30 |
如果您想移除模拟，只需 exec
select dbo.DelImpersonate()
3. FindFiles
此函数允许获取一个目录中的所有文件
select * from [dbo].[FindFiles]('C:\folder\','',1)
参数
@path：要搜索文件的目录。@filter：用于筛选文件的扩展名@subdir0-> 仅在路径中- 1 -> 也包括子目录
4. FileMove
此函数允许复制/移动/删除文件。
select dbo.FileMove('c:\frompath', 'file.txt', 'c:\topath', null, 0, 1)
参数
@pathFrom：源目录@fileFrom：源文件@pathTo：目标路径@fileTo：复制/移动时文件的名称。
如果为null，它将具有与源文件相同的名称@action0-> 复制并粘贴1-> 剪切并粘贴2-> 删除
@overwrite：1表示覆盖，0表示创建新文件（如果文件已存在，则会报错）
5. Zip
此函数允许压缩/解压缩文件。
select [dbo].[Zip]('c:\pathfrom','zipfile.zip','pathto','-1')
select [dbo].[Zip]('c:\pathfrom,c:\pathfrom\file1.txt,c:\pathfrom2\','zipfile.zip','pathto','0')
参数
@pathFrom：压缩文件所在的目录（解压缩情况）或要压缩的目录/文件（压缩情况）@zip：压缩文件@pathTo：文件解压到的目标文件夹（解压情况）或压缩文件创建的文件夹（压缩情况）@level：压缩级别<0 ->解压缩过程1/9 ->压缩过程（值表示压缩级别）
6 - FindRowInFile
此函数返回包含特定行和列中某个单词的文件。
示例 1
select * from FindRowInFile('path',null,'ToFind',2,'','|',3,'')
示例 2
select * from FindRowInFile('path','file.txt','ToFind',2,'','|',3,'')
示例 3
select * from FindRowInFile('path',null,'ToFind',2,'20170101','|',3,'')
参数
@path：目录@file：“null”表示获取所有文件。否则，只检查您输入值的文件@find：要查找的单词@pos：文本中查找位置的列@date：我可以定义文件的最后修改日期，或者将其留空为 "null"@delimitator：文本中列的分隔符@row：查找单词的行@filter：过滤文件的扩展名，否则为空白 ('') 或null
7. GetWebPage
此函数从 URL 下载并返回网页。它还会执行页面的 JavaScript。
示例
declare @s nvarchar(max)
select @s=node
from [dbo].[GetWebPage]('https://www.google.it/','')
print @s
并且这只从 "td" 标签中提取
declare @s nvarchar(max)
select @s=node
from [dbo].[GetWebPage]('https://www.google.it/','//td')
print @s
并有使用 SQL XML 函数的意义
select CONVERT(xml,node) from [dbo].[GetWebPage]('https://www.google.it/','//td')
参数
url：您要下载的网页node：从中提取页面的节点
8. InlineAggr
此函数有助于实现简单的字符串连接
示例
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
参数
@value：要连接的列@del：值的定界符@distinct：如果您只需要保留一个不同的值。
可以是0（所有）或1（不同）@sort：0表示不需要对值进行排序，1表示按升序排序，-1表示按降序排序
9. INFORMATION_SCHEMA_COLUMNS
它与 SQL 视图相同，但可以在不使用动态查询的情况下从不同的数据库中提取信息
示例
select * from [dbo].[INFORMATION_SCHEMA_COLUMNS]('test')
10. RowToColumn
此函数将一行转置为一列。
示例
select * from [dbo].[RowToColumn]('path','file','|',1,-1)
参数
path：文件路径file：要打开的文件delimitator：ttext列的分隔符；
在 Excel 中，这将忽略。xls：如果<0表示它是文本文件。否则，它是 Excel 的工作表
11. SendMail
此函数可以发送带附件的电子邮件。
示例
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
参数
smtp：SMTP 服务器user：用户电子邮件pass：电子邮件密码port：电子邮件的端口号EnableSsl：布尔值，启用(1)或禁用(0) SSLfrom：发件人电子邮件to：收件人电子邮件。可以使用 ',' 连接多个地址cc：抄送人电子邮件。可以使用 ',' 连接多个地址subject：主题body：正文attach：附件所在的文件夹。可以使用 ',' 连接多个文件
12 - DownloadFromNetwork
此函数从 URL 下载文件。
示例
select * from dbo.DownloadFromNetwork('domain', 'user', 'pass', 'url', 'path', 'file')
参数
domain：访问 URL 的用户域user：访问 URL 的用户pass：密码url：包含要下载文件的 URLpath：保存文件的目录file：下载文件的名称
关注点
除非已知 CLR 不经常使用，否则与 SQL 语言相比，我发现在改进和性能方面它非常有用。