通过SqlClr制作Sql自动化批量执行脚本
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了通过SqlClr制作Sql自动化批量执行脚本，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4905字，纯文字阅读大概需要8分钟。
内容图文
通过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 {
9this.FileName = fileName;
10this.FilePath = FilePath;
11 }
12privatestring _FileName;
1314publicstring FileName
15 {
16get { return _FileName; }
17set { _FileName = value; }
18 }
19privatestring _FilePath;
2021publicstring FilePath
22 {
23get { return _FilePath; }
24set { _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)]
7publicstatic IEnumerable GetScriptFilePath(SqlString fileRootPath)
8 {
910 IList<FilePathModel> list = new List<FilePathModel>();
11if (Directory.Exists(fileRootPath.Value))
12 {
13 DirectoryInfo di = new DirectoryInfo(fileRootPath.Value);
14foreach (FileInfo fi in di.GetFiles())
15 {
16 list.Add(new FilePathModel { FileName=fi.Name,FilePath=fi.FullName});
17 }
18 }
19return list;
20 }
21publicstaticvoid FillTable(object obj, out SqlString fileName, out SqlString filePath)
22 {
23 fileName = "";
24 filePath = "";
25 FilePathModel fpModel = obj as FilePathModel;
26if (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);
12if (!File.Exists(filePathNew))
13 {
14 fi.Create();
15 }
16using (StreamWriter sw = fi.AppendText())
17 {
18 sw.WriteLine(strName.Value + "||" + Time.Value);
19 }
20returnnew SqlString("完成");
21 }
22else23 {
24returnnew SqlString("失败");
25 }
26 }
27 };
5.2写执行脚本：
--
开启sqlclr
sp_configure ‘show advanced options‘, 1;
GORECONFIGURE;
GO
sp_configure ‘clr enabled‘, 1;
GORECONFIGURE;
GO--使用.net frameworkALTERdatabase Test SET TRUSTWORTHY ONALTER assembly DataImprot
with permission_set = external_access
go--
--开启【xp_cmdshell】权限exec sp_configure ‘xp_cmdshell‘, @configvalue=1reconfigurewith override
go--开启【opendatasource】权限exec sp_configure @configname=‘Ad Hoc Distributed Queries‘, @configvalue=1reconfigurewith override
--测试DECLARE@fileRootPathnvarchar(100)
DECLARE@logFilePathnvarchar(100)
DECLARE@serverNamenvarchar(100)
DECLARE@dataBaseNamenvarchar(100)
DECLARE@loginNamenvarchar(100)
DECLARE@passWordnvarchar(100)
--服务器名SET@ServerName=‘PACTERA_GZF-PC‘--数据库名SET@dataBaseName=‘Test‘--用户名SET@loginName=‘sa‘--密码SET@passWord=‘sa‘--脚本根路径SET@fileRootPath=‘D:\ImportDataScript‘--日志文件路径.txtSET@logFilePath=‘D:\ImportDataScript\LogFile‘DECLARE@FilePathTabletable
(
[FileName]nvarchar(100),
FilePath nvarchar(100)
)
createtable #CurFilePathTable
(
Id intidentity(1,1) primarykey,
[FileName]nvarchar(100),
FilePath nvarchar(100),
BeginTime datetime,
EndTime datetime,
ExcuteDate float
)
insertinto@FilePathTableselect[FileName], [FilePath]from dbo.GetScriptFilePath(@fileRootPath)
declare@FileNamenvarchar(100)
declare@FilePathnvarchar(100)
declare@BeginTimedatetimedeclare@EndTimedatetimedeclare@sqlStrnvarchar(200)
declare cur_FilePath cursorforselect[FileName], [FilePath]from@FilePathTableopen cur_FilePath
fetchnextfrom cur_FilePath into@FileName, @FilePathwhile (@@fetch_status=0)
beginset@BeginTime=getdate()
set@sqlStr=‘exec xp_cmdshell ‘‘osql -S ‘+@ServerName+‘ -U ‘+@loginName+‘ -P ‘+@passWord+‘ -i ‘+@FilePath+‘‘‘‘exec master..sp_executesql @sqlStrset@EndTime=getdate()
print@FileNameinsertinto #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)))
fetchnextfrom cur_FilePath into@FileName, @FilePathendclose cur_FilePath
deallocate cur_FilePath
select*FROM #CurFilePathTable
DROPTABLE #CurFilePathTable
5.3总结：
感觉SqlClr就像是插件模型，通过嵌入.dll来实现更多的功能。
利用SqlClr我们可以做许事情比如我们也可以在sqlserver端实现数据的加密解密等。
原文：http://www.cnblogs.com/guozefeng/p/3705064.html
内容总结
以上是互联网集市为您收集整理的通过SqlClr制作Sql自动化批量执行脚本全部内容，希望文章能够帮你解决通过SqlClr制作Sql自动化批量执行脚本所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。