7.8 CLR存储过程(1)

-- 使用EXTERNAL_ACCESS 权限集需要把数据库选项TRUSTWORTHY 设置为ON 
ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON;
GO
-- 修改程序集PERMISSION_SET = EXTERNAL_ACCESS
ALTER ASSEMBLY CLRUtilities
WITH PERMISSION_SET = EXTERNAL_ACCESS;

-- 从已签名的程序创建非对称密钥
-- 注意:必须使用强名称密钥文件为程序集签名USE master
GO
CREATE ASYMMETRIC KEY CLRUtilitiesKey
FROM EXECUTABLE FILE =
'C:\CLRUtilities\CLRUtilities\bin\Debug\CLRUtilities.dll'
-- 创建登录并授予其external access权限
CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin
GO

 // 以表格形式返回环境信息的存储过程
[SqlProcedure]
public static void usp_GetEnvInfo()
{
// 创建一个record- 表示行的对象
// 包括SQL表的元数据
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20),
new SqlMetaData("Value", SqlDbType.NVarChar, 256));
// 标记要发送回客户端的结果集的开始
// record参数用于构造结果集的元数据
// 
SqlContext.Pipe.SendResultsStart(record);
// 填充一些行并通过管道发送它们
record.SetSqlString(0, @"Machine Name");
record.SetSqlString(1, Environment.MachineName);
SqlContext.Pipe.SendResultsRow(record);
record.SetSqlString(0, @"Processors");
record.SetSqlString(1, Environment.ProcessorCount.ToString());
SqlContext.Pipe.SendResultsRow(record);
record.SetSqlString(0, @"OS Version");
record.SetSqlString(1, Environment.OSVersion.ToString());
SqlContext.Pipe.SendResultsRow(record);
record.SetSqlString(0, @"CLR Version");
record.SetSqlString(1, Environment.Version.ToString());
SqlContext.Pipe.SendResultsRow(record);
//标记结果集的结尾
SqlContext.Pipe.SendResultsEnd();
}

 '以表格形式返回环境信息的存储过程
<SqlProcedure()> _
Public Shared Sub usp_GetEnvInfo()
'创建一个record- 表示行的对象
'包括SQL表的元数据
Dim record As New SqlDataRecord(_
New SqlMetaData("EnvProperty", SqlDbType.NVarChar, 20), _
New SqlMetaData("Value", SqlDbType.NVarChar, 256))
'标记要发送回客户端的结果集的开始
' record参数用于构造结果集的元数据
' 
SqlContext.Pipe.SendResultsStart(record)
''填充一些行并通过管道发送它们
record.SetSqlString(0, "Machine Name")
record.SetSqlString(1, Environment.MachineName)
SqlContext.Pipe.SendResultsRow(record)
record.SetSqlString(0, "Processors")
record.SetSqlString(1, Environment.ProcessorCount.ToString())
SqlContext.Pipe.SendResultsRow(record)
record.SetSqlString(0, "OS Version")
record.SetSqlString(1, Environment.OSVersion.ToString())
SqlContext.Pipe.SendResultsRow(record)
record.SetSqlString(0, "CLR Version")
record.SetSqlString(1, Environment.Version.ToString())
SqlContext.Pipe.SendResultsRow(record)
'标记结果集的结尾
SqlContext.Pipe.SendResultsEnd()
End Sub

USE CLRUtilities;
GO
IF OBJECT_ID('dbo.usp_GetEnvInfo') IS NOT NULL
DROP PROC usp_GetEnvInfo;
GO
CREATE PROCEDURE dbo.usp_GetEnvInfo
AS EXTERNAL NAME CLRUtilities.CLRUtilities.usp_GetEnvInfo;

// 返回程序集信息的存储过程
// 使用反射
[SqlProcedure]
public static void usp_GetAssemblyInfo(SqlString asmName)
{
// 获取程序集的clr 名称
String clrName = null;
// 获取上下文
using (SqlConnection connection =
new SqlConnection("Context connection = true"))
{
connection.Open();
using (SqlCommand command = new SqlCommand())
{
//得到程序集并加载它
command.Connection = connection;
command.CommandText =
"SELECT clr_name FROM sys.assemblies WHERE name = @asmName";
command.Parameters.Add("@asmName", SqlDbType.NVarChar);
command.Parameters[0].Value = asmName;
clrName = (String)command.ExecuteScalar();
if (clrName == null)
{
throw new ArgumentException("Invalid assembly name!");
}
Assembly myAsm = Assembly.Load(clrName);
// 创建一个 record – 表示行的对象
// 包含SQL表的元数据
SqlDataRecord record = new SqlDataRecord(
new SqlMetaData("Type", SqlDbType.NVarChar, 50),
new SqlMetaData("Name", SqlDbType.NVarChar, 256));
//标记要发送回客户端的结果集的开始

　  //record参数用于构造结果集的元数据

SqlContext.Pipe.SendResultsStart(record);
// 得到程序集中的所有类型
Type[] typesArr = myAsm.GetTypes();
foreach (Type t in typesArr)
{
// SQL数据库中的类型应该是一个类或一个结构
if (t.IsClass == true)
{
record.SetSqlString(0, @"Class");
}
else
{
record.SetSqlString(0, @"Structure");
}
record.SetSqlString(1, t.FullName);
SqlContext.Pipe.SendResultsRow(record);
// 找出所有公共的静态方法
MethodInfo[] miArr = t.GetMethods();
foreach (MethodInfo mi in miArr)
{
if (mi.IsPublic && mi.IsStatic)
{
record.SetSqlString(0, @" Method");
record.SetSqlString(1, mi.Name);
SqlContext.Pipe.SendResultsRow(record);
}
}
}
// 标记结果集的结尾
SqlContext.Pipe.SendResultsEnd();
}
}
}