IPCLR

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net;
using System.IO;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetWeb1(string IPURL)
    {
        // 在此处放置代码
        return new SqlString(GetWebClient(IPURL));
    }
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetWeb2(string IPURL)
    {
        // 在此处放置代码
        return new SqlString(GetWebRequest(IPURL));
    }
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GetWeb3(string IPURL)
    {
        // 在此处放置代码
        return new SqlString(GetHttpWebRequest(IPURL));
    }

    private static string GetWebClient(string url)
    {
        string strHTML = "";
        WebClient myWebClient = new WebClient();
        Stream myStream = myWebClient.OpenRead(url);
        StreamReader sr = new StreamReader(myStream, System.Text.Encoding.GetEncoding("utf-8"));
        strHTML = sr.ReadToEnd();
        myStream.Close();
        return strHTML;
    }

    private static string GetWebRequest(string url)
    {
        Uri uri = new Uri(url);
        WebRequest myReq = WebRequest.Create(uri);
        WebResponse result = myReq.GetResponse();
        Stream receviceStream = result.GetResponseStream();
        StreamReader readerOfStream = new StreamReader(receviceStream, System.Text.Encoding.GetEncoding("utf-8"));
        string strHTML = readerOfStream.ReadToEnd();
        readerOfStream.Close();
        receviceStream.Close();
        result.Close();
        return strHTML;
    }

 

    private static string GetHttpWebRequest(string url)
    {
        Uri uri = new Uri(url);
        HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create(uri);
        myReq.UserAgent = "User-Agent:Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.2; .NET CLR 1.0.3705";
        myReq.Accept = "*/*";
        myReq.KeepAlive = true;
        myReq.Headers.Add("Accept-Language", "zh-cn,en-us;q=0.5");
        HttpWebResponse result = (HttpWebResponse)myReq.GetResponse();
        Stream receviceStream = result.GetResponseStream();
        StreamReader readerOfStream = new StreamReader(receviceStream, System.Text.Encoding.GetEncoding("utf-8"));
        string strHTML = readerOfStream.ReadToEnd();
        readerOfStream.Close();
        receviceStream.Close();
        result.Close();
        return strHTML;
    }

};

 

/*

--开启clr
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 
 
--创建cctv新用户，sa不行
create login [cctv] with password=N'123456', default_database=[master], default_language=[简体中文], check_expiration=off, check_policy=off
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'sysadmin'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'securityadmin'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'serveradmin'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'setupadmin'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'processadmin'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'diskadmin'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'dbcreator'
GO
exec sys.sp_addsrvrolemember @loginame = N'cctv', @rolename = N'bulkadmin'
GO
 
 
 
--授权程序集
grant external access assembly to cctv
 
grant unsafe assembly to cctv
 
alter database SGPZ set trustworthy on
 
 
use SGPZ
go
--创建程序集
create assembly SqlServerProject1 
 
from 'D:\SqlServerProject1.dll' 
 
with permission_set = external_access 
 
--创建clr函数
CREATE FUNCTION dbo.GetWeb1 ( @IPURL NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME
    SqlServerProject1.UserDefinedFunctions.GetWeb1
   
CREATE FUNCTION dbo.GetWeb2 ( @IPURL NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME
    SqlServerProject1.UserDefinedFunctions.GetWeb2
   
CREATE FUNCTION dbo.GetWeb3 ( @IPURL NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME
    SqlServerProject1.UserDefinedFunctions.GetWeb3
 
 
--drop function GetWeb1
--cctv用户执行，sa没有权限
 
SELECT dbo.GetWeb1('http://int.dpool.sina.com.cn/iplookup/iplookup.php?format=js 多地域测试方法：http://int.dpool.sina.com.cn/iplookup/iplookup.php?format=js&ip=24.153.31.255')

*/