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

4353

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

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

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

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

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

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

   http://images.cnitblog.com/i/401614/201405/031304567362159.png

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

   LogFile文件夹内的Logg.txt中。

4.我们预期结果：

   执行结果：（执行每个文件的开始时间、结束时间、执行总时间）

   http://images.cnitblog.com/i/401614/201405/031304191271202.png

   输出日志：（名称、执行时间）

   http://images.cnitblog.com/i/401614/201405/031306239865680.png

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

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

5.1创建sqlclr项目

5.1.1创建实体类：

public class FilePathModel

    {

        public FilePathModel()

        {

        }

        public FilePathModel(string fileName, string filePath)

        {

            this.FileName = fileName;

            this.FilePath = FilePath;

        }

        private string _FileName;

        public string FileName

        {

            get { return _FileName; }

            set { _FileName = value; }

        }

        private string _FilePath;

        public string FilePath

        {

            get { return _FilePath; }

            set { _FilePath = value; }

        }

    }

5.1.2创建表值函数：

public partial class UserDefinedFunctions

{

  [Microsoft.SqlServer.Server.SqlFunction

  (DataAccess = DataAccessKind.Read,

   TableDefinition = "FileName nvarchar(100),FilePath nvarchar(100)",

    public static IEnumerable GetScriptFilePath(SqlString fileRootPath)

    {

        

        IList<FilePathModel> list = new List<FilePathModel>();

        if (Directory.Exists(fileRootPath.Value))

        {

            DirectoryInfo di = new DirectoryInfo(fileRootPath.Value);

            foreach (FileInfo fi in di.GetFiles())

            {

                list.Add(new FilePathModel { FileName=fi.Name,FilePath=fi.FullName});

            }

        }

        return list;

    }

  public static void FillTable(object obj, out SqlString fileName, out SqlString filePath)

    {

        fileName = "";

        filePath = "";

        FilePathModel fpModel = obj as FilePathModel;

        if (fpModel != null)

        {

            fileName = fpModel.FileName;

            filePath = fpModel.FilePath;

        }

    }

};

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

public partial class UserDefinedFunctions

{

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString ImportLog(SqlString pathStr, SqlString strName, SqlString Time)

    {

        // 在此处放置代码

        if (Directory.Exists(pathStr.Value))

        {

            string filePathNew = Path.Combine(pathStr.Value, "Logg.txt");

            FileInfo fi = new FileInfo(filePathNew);

            if (!File.Exists(filePathNew))

            {

                fi.Create();

            }

            using (StreamWriter sw = fi.AppendText())

            {

                sw.WriteLine(strName.Value + "||" + Time.Value);

            }

            return new SqlString("完成");

        }

        else

        {

            return new SqlString("失败");

        }

    }

};

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端实现数据的加密解密等。