[转载]C# 编写SQL SERVER 2005 的存储过程

以下是一个查询IP地址归属地的CLR存储过程，三步：

1、用C#来做DLL，代码如下：

//====================================================================
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

public class AddrInfo
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void getAddrInfo(SqlString ip, out SqlString info)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            //IP地址转为数字
            string[] tmp = ip.Value.Split(new string[]{"."},StringSplitOptions.None);
            Int64 ipn = ToInt(ToBinary(tmp[0]) + ToBinary(tmp[1]) + ToBinary(tmp[2]) + ToBinary(tmp[3]));

            connection.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.CommandType = System.Data.CommandType.Text;

            cmd.CommandText = "select addr_info from CZIP where ipn1<="
                                + ipn.ToString() + " and ipn2>="
                                + ipn.ToString();
            SqlDataReader dr = cmd.ExecuteReader();

            using (dr)
            {
                if (dr.Read())
                {
                    info = dr.GetString(0);
                }
                else
                {
                    info = "<无归属地数据>";
                }
            }
        }
    }

    /// <summary>
    /// INT32 转为二进制字符串
    /// </summary>
    /// <param name="piNumber"></param>
    /// <returns></returns>
    private static String ToBinary(string num)
    {
        int piNumber = Int32.Parse(num);
        string strBinary = "";
        int iTmp;

        do
        {
            iTmp = piNumber % 2;
            strBinary = iTmp.ToString() + strBinary;
            piNumber = piNumber / 2;
        } while (piNumber != 0);

        return strBinary.PadLeft(8, '0');
    }

    /// <summary>
    /// 二进制字符串转为整数
    /// </summary>
    /// <param name="pStrBinary"></param>
    /// <returns></returns>
    private static long ToInt(string pStrBinary)
    {
        char[] charArr = pStrBinary.ToCharArray();
        long iReturnValue = 0;
        int iPow = 0;

        for (int i = 0; i < charArr.Length; i++)
        {
            if (charArr[i].ToString() != "0" && charArr[i].ToString() != "1")
            { throw new Exception("输入字符串格式不正确!"); }
        }

        for (int i = 0; i < charArr.Length; i++)
        {
            iPow = Convert.ToInt32(charArr[i].ToString());
            iReturnValue += iPow * Convert.ToInt64(Math.Pow(2, charArr.Length - i - 1));
        }

        return iReturnValue;
    }
}
//===========================================================================

2、将编译后的DLL放到SQL SERVER 2005所在的服务器上，假设路径为  c:\clr\ipLib.dll 以下方法注册程序集

CREATE ASSEMBLY [ipLib]
FROM 'c:\clr\ipLib.dll'

3、创建存储过程

CREATE PROCEDURE get_ip_addr_info
(
@ip nvarchar(20),
@info nvarchar(500) OUTPUT
)
AS EXTERNAL NAME ipLib.AddrInfo.getAddrInfo