瓶水相逢

上一篇【用 SQL 語法查詢符合條件範圍的 IP】中的 IP2INT 的 SQL 語法實在是太醜陋了，所以利用 SQL 可呼叫 CLR 的方式來撰寫查詢。

1. 用 C# 撰寫 SQL Function，請自行編譯成 IPUtility.dll

using System; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public class IPUtility {     [SqlFunction]     public static SqlInt32 IsInRange(SqlString ip, SqlString startIP, SqlString endIP)     {         if (IPToLong(startIP) <= IPToLong(ip) && IPToLong(ip) <= IPToLong(endIP))         {             return 1;         }         return 0;     }     private static long IPToLong(SqlString ip)     {         string[] ipClass = ip.ToString().Split();         string strhexip = string.Format("{0:X2}{1:X2}{2:X2}{3:X2}", int.Parse(ipClass[0]), int.Parse(ipClass[1]), int.Parse(ipClass[2]), int.Parse(ipClass[3]));         return Convert.ToInt64(strhexip, 16);     } }

2. 在 SQL 引入 IPUtility 並建立 Function

-- Enable CLR integration (啟動 SQL Server 2005 CLR 功能) sp_configure 'clr enabled', 1 GO RECONFIGURE GO -- Register Assembly (註冊組件) USE master CREATE ASSEMBLY IPUtility FROM 'C:\TMP\IPUtility.dll' -- 請依照 dll 放置位置填寫 WITH PERMISSION_SET = Safe GO ---- Drop assembly --DROP ASSEMBLY IPUtility --GO -- Create the managed user-defined function USE master GO CREATE FUNCTION dbo.IsInRange (     @IP nvarchar(15), @startIP nvarchar(15), @endIP nvarchar(15) -- 需要三參數: 比對 IP, 範圍起始 IP , 範圍結束 IP ) RETURNS int AS EXTERNAL NAME IPUtility.IPUtility.IsInRange GO -- Test Run (測試 Function) DECLARE @IPTable TABLE (IP nvarchar(15) ) INSERT INTO @IPTable VALUES ( N'172.16.57.103') INSERT INTO @IPTable VALUES ( N'172.16.58.103') INSERT INTO @IPTable VALUES ( N'172.16.59.103') INSERT INTO @IPTable VALUES ( N'172.16.60.103') INSERT INTO @IPTable VALUES ( N'172.16.61.103') INSERT INTO @IPTable VALUES ( N'172.16.62.103') SELECT IP FROM @IPTable WHERE dbo.IsInRange(IP, '172.16.58.103', '172.16.61.109') = 1 GO

OUTPUT:

172.16.58.103
172.16.59.103
172.16.60.103
172.16.61.103