九乡新闻网轩辕ol贴吧:C# 编写SQL SERVER 2005 的存储过程 - 9who - 博客园
来源：百度文库 编辑：九乡新闻网 时间：2024/04/30 00:17:06
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);//split（）分割成字符串数组
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 = "<无归属地数据>";
}
}
}
}
///
/// INT32 转为二进制字符串
///
///
///
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');
}
///
/// 二进制字符串转为整数
///
///
///
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
之后你就可以看到名为 get_ip_addr_info 的存储过程，调用方法与普通T/SQL存储过程相同