SQL Server 调用dll

今天要做数据导入的工作的时候，要导入员工表了。
有个字段叫 LoginName
而数据文件里面，没有这个字段。

询问一下主管，答复是 用姓名的拼音。 自己去网上去找个DLL来处理。

对于 中文汉字的拼音，这个以前倒是用 C# 调用 Simplified Chinese Pin-YinConversion Library 测试过。

不过实在不高兴再去写段程序，把数据从数据库表里面读取出来。
然后产生一个拼音的内容，最后在更新回数据库去。

就考虑着，如果用 C# 写个SQL Server 里面的 函数。

这样直接在数据库里面

UPDATE
表
SET
LoginName = GetNamePinYin(name);

一句话就一了百了啦。
以后又有新增的用户，也可以这么折腾。

首先上网看看 C# 写 SQL Server 的例子。

执行的前提
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

然后在 VS2010 里面，创建一个 “Visual C# SQL CLR 数据库项目”

首先创建下面这样的函数 [代码是 VS2010 自动产生的]

public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Function1()
{
// 在此处放置代码
return new SqlString("Hello");
}
};

然后，尝试添加 Microsoft.International.Converters.PinYinConverter的引用
发现无法添加额外的引用。只有少数的 类库允许被引用。

折腾了一段时间以后， 最后发现

首先要在数据库的 SQL ServerManagement Studio 中的 [可编程性] 下的 [程序集]中。
先把那个 ChnCharInfo.dll加进去。

然后回到 Visual Studio 2010中，就可以添加 ChnCharInfo.dll这个引用了。

最后的C#代码如下：

using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Reflection;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using Microsoft.International.Converters.PinYinConverter;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString GetPinYin(SqlString word)
{
string result = GetPinYn(word.ToString());
// 在此处放置代码
return new SqlString(result);
}

///
/// 取得词的拼音.
///
///
///
private static string GetPinYn(string word)
{
StringBuilder buff = new StringBuilder();
for (int i = 0; i < word.Length; i++)
{
// 依次取得每一个字的发音.
string onePinYin =GetOnePinYin(word[i]);
if (!String.IsNullOrEmpty(onePinYin))
{
// 拼音加入结果列表.
// 去掉最后一个声调的数字.
buff.Append(onePinYin.Substring(0,onePinYin.Length - 1));
}
}
return buff.ToString();
}

///
/// 取得单个汉字的拼音.
///
///
///
private static string GetOnePinYin(char iChar)
{
// 初始化 Simplified Chinese Pin-Yin ConversionLibrary.
ChineseChar chineseChar = newChineseChar(iChar);
// 取得拼音列表.
ReadOnlyCollection pinyin =chineseChar.Pinyins;
foreach (string pin in pinyin)
{
if (!String.IsNullOrEmpty(pin))
{
// 对于多音字，不判断了，直接返回第一个发音.
return pin;
}
}
// 处理不了的情况下，返回空白.
return String.Empty;
}
};

写完C#函数以后. 编译通过完毕以后。
回到 SQL Server Management Studio 中的 [可编程性] 下的 [程序集]中。
先把这个编译好的 SqlServerProject1.dll 加进去。

最后执行下面的 SQL 语句， 定义好存储过程的函数。

CREATEFUNCTION[dbo].[GetNamePinYin]
(@word NVARCHAR (10))
RETURNSNVARCHAR (100)
AS
EXTERNALNAME[SqlServerProject1].[UserDefinedFunctions].[GetPinYin]
go

SELECT dbo.GetNamePinYin('张三');
SELECT dbo.GetNamePinYin('李四');
go

ZHANGSAN
(1 行受影响)

LISI
(1 行受影响)

看上去是满足预期了

不过最后实际使用的时候，还是发生了
在执行用户定义例程或聚合 "GetNamePinYin" 期间出现 .NET Framework 错误:
System.NotSupportedException: The character is not in extendedcharacter set of
Simplified Chinese.
的异常。

回头再去仔细看看，到底谁的名字这么奇怪的，呵呵。

以下是自己验证过的, 出现以下错误时处理方法:

1.引入程序集后 , 右击属性,将权限集改为＂无限制＂，否则会报:

请求“System.Data.SqlClient.SqlClientPermission, System.Data,Version=2.0.0.0, Culture=neutral,PublicKeyToken=b77a5c561934e089”类型的权限已失败。”

2.引入程序集时,出现:

针对程序集 'SqlServerProject' 的 CREATE ASSEMBLY 失败，因为程序集'SqlServerProject' 未获授权，不满足 PERMISSION_SET =EXTERNAL_ACCESS。满足以下两个条件之一时将给程序集授权: 数据库所有者(DBO)拥有 EXTERNAL ACCESSASSEMBLY 权限，且数据库具有 TRUSTWORTHY 数据库属性；或者，程序集已使用其对应登录名具有 EXTERNALACCESS ASSEMBLY 权限的证书或非对称密钥加以签名。
可以先执行以下语句:

--use master
--GRANT EXTERNAL ACCESS ASSEMBLY TO loginame

--ALTER DATABASE dbname SET TRUSTWORTHY ON

（dbname 为准备创建的程序集所在的数据库，loginame 为该数据库的所有者对应的登陆账户）

引入程序集后,新建存储过程时，如下例：

CREATE PROCEDURE [dbo].[过程名]
@account [nvarchar](50),
@password [nvarchar](50),
@sms int OUTPUT,
@mms int OUTPUT
WITH EXECUTE AS N'dbo'
AS
EXTERNAL NAME [myself_pro].[StoredProcedures].[定义的方法名]
GO