通过SqlClr制作Sql自动化批量执行脚本

简介: 原文:通过SqlClr制作Sql自动化批量执行脚本通过SqlClr制作Sql自动化批量执行脚本      在与同事一起做项目时,看到同事用sqlclr做批量执行脚本,感觉挺新奇的就上网搜集资料自己模仿跟做了个案例, 感觉挺不错的,现在想和大家分享一下,可能存在些错误的地方,大家就做个小参考吧.... 1.我们在做数据迁移或是数据库结构修改时,通常会写一些脚本文件之后逐个运行。
原文: 通过SqlClr制作Sql自动化批量执行脚本

通过SqlClr制作Sql自动化批量执行脚本

     在与同事一起做项目时,看到同事用sqlclr做批量执行脚本,感觉挺新奇的就上网搜集资料自己模仿跟做了个案例,

感觉挺不错的,现在想和大家分享一下,可能存在些错误的地方,大家就做个小参考吧....

1.我们在做数据迁移或是数据库结构修改时,通常会写一些脚本文件之后逐个运行。但是如果有数十或数百个脚本文件,

   那么就可以通过SqlClr制作Sql自动化执

2.比如现在ImportDataScript文件夹内有些脚本文件:

   

3.我们想让这9个脚本文件自动的依次执行,并且输出最终的执行情况并且生成一个日志写到ImportDataScript文件夹内的

   LogFile文件夹内的Logg.txt中。

4.我们预期结果:

   执行结果:(执行每个文件的开始时间、结束时间、执行总时间)

   

   输出日志:(名称、执行时间)

   

5.思路:首先我们通过sqlclr创建一个表值函数来获取脚本文件的本地路径的集合,然后遍历这个集合并通过sql exec xp_cmdshell命令

   来执行指定路径下的脚本文件,并通过sqlclr创建一个记录日志的的标量函数来逐条记录执行日志。

5.1创建sqlclr项目

5.1.1创建实体类:

 1 public class FilePathModel
 2     {
 3         public FilePathModel()
 4         {
 5 
 6         }
 7         public FilePathModel(string fileName, string filePath)
 8         {
 9             this.FileName = fileName;
10             this.FilePath = FilePath;
11         }
12         private string _FileName;
13 
14         public string FileName
15         {
16             get { return _FileName; }
17             set { _FileName = value; }
18         }
19         private string _FilePath;
20 
21         public string FilePath
22         {
23             get { return _FilePath; }
24             set { _FilePath = value; }
25         }
26     }

5.1.2创建表值函数:

 1 public partial class UserDefinedFunctions
 2 {
 3   [Microsoft.SqlServer.Server.SqlFunction
 4   (DataAccess = DataAccessKind.Read,
 5    TableDefinition = "FileName nvarchar(100),FilePath nvarchar(100)",
 6    FillRowMethodName = "FillTable", IsDeterministic = true)]
 7     public static IEnumerable GetScriptFilePath(SqlString fileRootPath)
 8     {
 9         
10         IList<FilePathModel> list = new List<FilePathModel>();
11         if (Directory.Exists(fileRootPath.Value))
12         {
13             DirectoryInfo di = new DirectoryInfo(fileRootPath.Value);
14             foreach (FileInfo fi in di.GetFiles())
15             {
16                 list.Add(new FilePathModel { FileName=fi.Name,FilePath=fi.FullName});
17             }
18         }
19         return list;
20     }
21   public static void FillTable(object obj, out SqlString fileName, out SqlString filePath)
22     {
23         fileName = "";
24         filePath = "";
25         FilePathModel fpModel = obj as FilePathModel;
26         if (fpModel != null)
27         {
28             fileName = fpModel.FileName;
29             filePath = fpModel.FilePath;
30         }
31     }
32 };

5.1.3创建写入日志的标量函数:

 1 public partial class UserDefinedFunctions
 2 {
 3     [Microsoft.SqlServer.Server.SqlFunction]
 4     public static SqlString ImportLog(SqlString pathStr, SqlString strName, SqlString Time)
 5     {
 6         // 在此处放置代码
 7 
 8         if (Directory.Exists(pathStr.Value))
 9         {
10             string filePathNew = Path.Combine(pathStr.Value, "Logg.txt");
11             FileInfo fi = new FileInfo(filePathNew);
12             if (!File.Exists(filePathNew))
13             {
14                 fi.Create();
15             }
16             using (StreamWriter sw = fi.AppendText())
17             {
18                 sw.WriteLine(strName.Value + "||" + Time.Value);
19             }
20             return new SqlString("完成");
21         }
22         else
23         {
24             return new SqlString("失败");
25         }
26     }
27 };

5.2写执行脚本:

--开启sqlclr
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'clr enabled', 1; 
GO 
RECONFIGURE; 
GO
--使用.net framework
ALTER database Test SET TRUSTWORTHY ON
ALTER assembly DataImprot
with permission_set = external_access
go
--
--开启【xp_cmdshell】权限
exec sp_configure 'xp_cmdshell', @configvalue = 1
reconfigure with override
go

--开启【opendatasource】权限
exec sp_configure @configname = 'Ad Hoc Distributed Queries', @configvalue = 1
reconfigure with override

--测试
DECLARE @fileRootPath nvarchar(100)
DECLARE @logFilePath nvarchar(100)
DECLARE @serverName nvarchar(100)
DECLARE @dataBaseName nvarchar(100)
DECLARE @loginName nvarchar(100)
DECLARE @passWord nvarchar(100)
--服务器名
SET @ServerName='PACTERA_GZF-PC'
--数据库名
SET @dataBaseName='Test'
--用户名
SET @loginName='sa'
--密码
SET @passWord='sa'
--脚本根路径
SET @fileRootPath='D:\ImportDataScript'
--日志文件路径.txt
SET @logFilePath='D:\ImportDataScript\LogFile'
DECLARE @FilePathTable table
(
    [FileName] nvarchar(100),
    FilePath nvarchar(100)
)
create table #CurFilePathTable
(
    Id int identity(1,1) primary key,
    [FileName] nvarchar(100),
    FilePath nvarchar(100),
    BeginTime datetime,
    EndTime datetime,
    ExcuteDate float
)
insert into @FilePathTable select [FileName], [FilePath] from dbo.GetScriptFilePath(@fileRootPath)
declare @FileName nvarchar(100)
declare @FilePath nvarchar(100)
declare @BeginTime datetime
declare @EndTime datetime
declare @sqlStr nvarchar(200)
declare cur_FilePath cursor for select [FileName], [FilePath] from @FilePathTable
open cur_FilePath
  fetch next from cur_FilePath into @FileName, @FilePath
while (@@fetch_status = 0)
begin
    set @BeginTime = getdate()
    set @sqlStr = 'exec xp_cmdshell ''osql -S '+@ServerName+' -U '+@loginName+' -P '+@passWord+' -i ' + @FilePath + ''''
    exec master..sp_executesql @sqlStr 
    set @EndTime = getdate()
    print @FileName
    insert into #CurFilePathTable ([FileName], FilePath, BeginTime,EndTime,ExcuteDate) values (@FileName, @FilePath, @BeginTime,@EndTime,datediff(second, @BeginTime, @EndTime))
    select dbo.ImportLog(@logFilePath,@FileName,convert(varchar(10),datediff(second, @BeginTime, @EndTime)))
    fetch next from cur_FilePath into @FileName, @FilePath
end
close cur_FilePath
deallocate cur_FilePath

select * FROM #CurFilePathTable
DROP TABLE #CurFilePathTable

5.3总结:

     感觉SqlClr就像是插件模型,通过嵌入.dll来实现更多的功能。

     利用SqlClr我们可以做许事情比如我们也可以在sqlserver端实现数据的加密解密等。

 

 

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
SQL脚本相除
【5月更文挑战第6天】
20 3
|
7天前
|
JSON 监控 测试技术
Groovy脚本编写员工上网行为监控自动化测试
本文介绍了如何使用Groovy脚本创建一个自动化工具来监控员工的网络活动。通过编写简单脚本记录员工访问的网站并打印信息,可进一步扩展为将数据保存至数据库。此外,通过设定定时任务,实现了每30分钟自动监控一次的功能。最后,展示了如何将监控数据转换为JSON格式并使用HTTP POST请求提交到网站,以实现数据的自动化上报,有助于企业保障网络安全、保护数据并提升工作效率。
66 5
|
7天前
|
JavaScript 前端开发
Playwright执行 JavaScript 脚本:探索浏览器自动化的新境界
在Web自动化中,Playwright提供`page.evaluate()`和`page.evaluate_handle()`来执行JavaScript脚本。`page.evaluate()`返回脚本执行结果,而`page.evaluate_handle()`返回JSHandle。示例展示了如何使用它们,如打印网页标题、操作元素及获取页面内容。通过这些方法,可以处理常规方法难以操作的网页元素。
12 2
|
7天前
|
测试技术 API 开发者
使用 Playwright 脚本录制简化自动化测试:完全指南
Playwright提供了一种脚本录制功能,帮助开发者通过交互式操作自动生成测试脚本,提升测试效率。使用Playwright的命令行工具`codegen`,如`playwright codegen python &lt;url&gt;`,可以记录浏览器操作并生成Python测试脚本。生成的脚本使用Playwright API模拟用户交互,如`page.click()`和`page.fill()`。这种方法简化了自动化测试脚本的编写,促进了测试流程的加速。
20 1
|
7天前
|
IDE 测试技术 开发工具
从零开始:使用 Playwright 脚本录制实现自动化测试
Playwright提供了一种便捷的脚本录制功能,类似于Selenium IDE。通过运行`playwright codegen`命令,你可以启动一个浏览器并记录你的操作,生成Python或异步代码。在示例中,展示了如何录制登录百度的过程,生成的代码可以直接用于自动化测试。Playwright Inspector允许你编辑和转换测试代码,支持生成Pytest格式的测试用例,方便Python开发者使用。这个功能使Playwright成为强大的Web自动化测试工具。
12 3
|
7天前
|
SQL 关系型数据库 MySQL
|
7天前
|
SQL 关系型数据库 MySQL
|
7天前
|
SQL 关系型数据库 MySQL
SQL脚本字符串替换
【5月更文挑战第3天】
20 4
|
7天前
|
SQL 关系型数据库 MySQL
SQL脚本列转行
【5月更文挑战第2天】
13 0
|
7天前
|
SQL 关系型数据库 MySQL
SQL脚本行转列
【5月更文挑战第1天】
13 0

热门文章

最新文章