利用SQLCLR创建表值函数读取img标签下的图片路径

最新推荐文章于 2021-01-08 14:33:28 发布

chinet_bridge

最新推荐文章于 2021-01-08 14:33:28 发布

阅读量821

点赞数

本文链接：https://blog.csdn.net/chinet_bridge/article/details/50395425

版权

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Text;
using System.Collections;

public partial class ReadImgSrc
{
    public static String Img_Replace(string input)
    {
        string Pattern = @"<\s?img[^>]+?>";
        Regex reg = new Regex(Pattern, RegexOptions.IgnoreCase);
        MatchCollection colMatch = reg.Matches(input);
        string strImage = "";
        if (colMatch.Count > 0)
        {
            foreach (Match m in colMatch)
            {
                string html = Regex.Replace(m.Value, @"(?i)(<img[^>]*?src=(['""\s]?))((http://)?([^/\\]+[/\\])+?)([^\.]+\.[^'"">]+)\2([^>]*?>)", @"$3$6");
                strImage += html + ",";
            }
        }
        return strImage.TrimEnd(',');
    }

    [SqlFunction(TableDefinition = "path varchar(4000)", FillRowMethodName = "FillRow")]
    public static IEnumerable DoSplit(String htm)
    {
        return Img_Replace(htm).Split(',');
    }

    public static void FillRow(Object obj, out SqlChars path)
    {
        path = new SqlChars(obj.ToString());
    }

}

先打开VS2013, 敲好数据库项目altlib的代码,注意目标框架选择Framework 3.5

接着设置目标平台 Sql2008：

最后生成dll.嵌入Sqlserver 数据库的程序集中，除填入dll的物理路径以外，就是选择程序集所有者为dbo.

再到Sql2008上安装sql 脚本.

EXEC sp_configure 'show advanced options', '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled', '1'
go
reconfigure;
EXEC sp_configure 'show advanced options', '1'; 
go
  ALTER DATABASE dbname set TRUSTWORTHY on;
go

EXEC sp_dbcmptlevel dbname, 90;

--创建表值函数  
CREATE FUNCTION dbo.ReadImage(@htm nvarchar(4000))  
RETURNS TABLE (path nvarchar(200))  
AS  
    EXTERNAL name altlib.ReadImgSrc.DoSplit
GO  



DECLARE @str VARCHAR(1000)
SET @str='<IMG height=375  src="http://www.mycreative.com.cn/upload/2010/4/tiancheng2.jpg" width=500><IMG  src="http://www.mycreative.com.cn/upload/2010/4/s2.jpg"> '
SET @str=@str+'<IMG  src="35/40/0130000025s.jpg" ></tr><tr><td></td></tr><tr><gggsgs><IMG  src="http://a0.att.hudong.com/35/40/0130000025s.jpg" width=88 >'
SELECT * FROM  dbo.ReadImage(@str)