1、什么是SQLCLR
SQL CLR (SQL Common Language Runtime) 是自 SQL Server 2005 才出现的新功能，它将.NET Framework中的CLR服务注入到 SQL Server 中，使得.NET代码可在SQL Server服务器进程中执行。
通过在 Microsoft SQL Server 中托管 CLR（称为 CLR 集成），开发人员可以在托管代码中编写存储过程、触发器、用户定义函数、用户定义类型和用户定义聚合函数, 改变了以前只能通过T-SQL语言来实现这些功能的局面。因为托管代码在执行之前会编译为本机代码，所以，在有些方案中可以大大提高性能。
2、在项目中遇到一些业务逻辑比较复杂的操作，例如对数据进行清洗，Load 100M的数据，要对这些数据每个进行多种合法性规则验证、多种筛选条件的筛选，计算派生列等等操作。如果在数据库中实现比较困难，所以采用了存储过程加CLR结合的方法。在存储过程中可以调用CLR方法，执行一些存储过程难以实现的操作，利用它们各自的优势提高性能。
3、以下是一个简化的例子，旨在说明实现步骤。
3.1 新建类库项目CLRBridge（特别注意，框架要选择3.5及以下版本，不然部署时会报版本不支持的错误）
CLR桥接器，只定义了两个存储过程
a、Hello 仅作为测试用
b、InvokeDataProcessEngine 实际用到的存储过程，输入参数提供必要的一些参数，输出参数返回执行的结果以及提示信息。
仅定义这么一个执行的接口，传入实际工作的类名称，实例化，执行，这样可以简化部署，因为可能工作类会增加删除，不用每次都部署CLRBridge Assembly
3.2 新建控制台项目DataProcess
实际执行DataProcess的项目
a、在CLR.Bridge.MainEntry中会启动进程DataProcess.exe
b、StoryBoard是一个基类，提供Create、Start等虚方法，子类可对其重写，满足自己需求。
Create主要是根据输入参数实例化StoryBoard，而真正的逻辑操作在Start里面。
c、Program的main函数接受输入参数，实例化相应的StoryBoard，并调用Start，输出结果。
3.3 新建控制台项目testCLR
testCLR：因在sql查询分析器里面不方便调试，这个项目主要是对CLRBridge进行测试的
项目结构如下图
4、代码
4.1 CLRBridge\MainEntry.cs
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Security.Permissions;
using System.Text;
using System.Text.RegularExpressions;
namespace CLRBridge
{
public class Utility
{
//连接字符串，
//#if DEBUG
//使用testCLR.exe测试时
//public static string ConnectionString = @"Database=TestClr;Server=.;User ID=sa;Password=FAS@dev321;Connection Timeout=90;Asynchronous Processing = True;";
//#else
//使用数据库的查询分析器、或者在数据库存储过程中测试时
public static string ConnectionString = @"Context Connection=true";
//#endif
}
public class DataProcessEngineConfigurationInfo
{
//在数据库的Info表中存储exe的路径及名称，不写死，后续如有变动方便修改
public string DataProcessEngineExeName { get; private set; }
public string DataProcessEngineBaseLocation { get; private set; }
public string DataProcessEngineExeFullPath
{
get
{
return Path.Combine(DataProcessEngineBaseLocation, DataProcessEngineExeName);
}
}
public DataProcessEngineConfigurationInfo()
{
Get();
}
private void Get()
{
using (SqlConnection conn = new SqlConnection(Utility.ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("select * from info", conn))
{
cmd.CommandType = System.Data.CommandType.Text;
using (SqlDataReader sqlReader = cmd.ExecuteReader())
{
// ApplicationSettings Info
while (sqlReader.Read())
{
if (((string)sqlReader["Tag"]).Trim() == "DataProcessEngineExeName")
DataProcessEngineExeName = ((string)sqlReader["Value"]).Trim();
else if (((string)sqlReader["Tag"]).Trim() == "DataProcessEngineBaseLocation")
DataProcessEngineBaseLocation = ((string)sqlReader["Value"]).Trim();
}
}
}
}
}
}
public class MainEntry
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static int Hello()
{
return 10;
}
[Microsoft.SqlServer.Server.SqlProcedure]
public static int InvokeDataProcessEngine(string storyboardName, short parameterCategory, short parameterDataType, string parameterValue, string outputParameterName, out string outputParameterValue, out string outputMessage)
{
int resultCode = -1;
ProcessStartInfo start = new ProcessStartInfo();
//start.FileName = @"C:\zxc\CLRBridge\DataProcess\bin\Debug\DataProcess.exe";
DataProcessEngineConfigurationInfo config = new DataProcessEngineConfigurationInfo();
start.FileName = config.DataProcessEngineExeFullPath; //
start.Arguments = string.Format("\"{0}\" \"{1}\" \"{2}\" \"{3}\"", storyboardName, parameterCategory, parameterDataType, parameterValue);
start.UseShellExecute = false;
start.RedirectStandardOutput = true;
//启动外部进程，外部进程根据输入的参数实例化相应的类，执行相关费时且逻辑复杂的操作
using (Process p = new Process())
{
p.StartInfo = start;
p.Start();
p.WaitForExit();
using (StreamReader reader = p.StandardOutput)
{
string result = reader.ReadToEnd();
Match m = Regex.Match(result, "Result code:(\\d+),");
Match m2 = Regex.Match(result, string.Format("OutputParameter {0}:(\\d+),", outputParameterName));
int.TryParse(m.Groups[1].Value, out resultCode);
outputParameterValue = m2.Groups[1].Value.Trim();
Match m3 = Regex.Match(result, "OutputMessage: (.+),");
outputMessage = m3.Groups[1].Value.Trim();
}
}
return resultCode;
}
}
}
4.2 DataProcess
4.2.1 DataProcess\Storyboard.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataProcess
{
public abstract class Storyboard
{
public string ParameterName { get; set; }
public object ParameterId { get; set; }
private string _name;
public virtual string OutputParameterName { get; protected set; }
public virtual string OutputParameterValue { get; protected set; }
public abstract void Start();
public virtual void PreStart()
{
// Nothing to do.
}
public virtual void PostStart()
{
// Nothing to do.
}
public virtual void OnStoryboardCompleted()
{
// Nothing to do.
}
public virtual void OnStoryboardSuccessful()
{
// Nothing to do.
}
public virtual void OnStoryboardError()
{
// Nothing to do.
}
public Storyboard()
{
string fullTypename = this.GetType().FullName;
int lastIndexOfDot = fullTypename.LastIndexOf('.');
_name = fullTypename.Substring(lastIndexOfDot + 1, fullTypename.Length - lastIndexOfDot - 1);
}
public virtual string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}
public string CurrentOperand { get; set; }
public static Storyboard Create(string storyboardName, ParameterResolver paramResolver)
{
//使用配置文件配置需要实例化的类名称及命名空间，运行时依赖注入，
//1、这样避免写很多if else，或者select case (如下注释掉的部分)
//2、解耦，在增加storybord类型时，只需要在配置文件中添加即可
//StoryboradConfigNode sbConfig = sbNameOperandMap[storyboardName.ToLowerInvariant()];
//Type t = Type.GetType(string.Format("{0}.{1}", sbConfig.Namespace, sbConfig.Name));
Type t = Type.GetType(string.Format("{0}.{1}", "DataProcess", "LVNStoryBoard"));
object parameterId = paramResolver.Resolve();
Storyboard sb = (Storyboard)Activator.CreateInstance(t, parameterId);
sb.CurrentOperand = storyboardName;
//sb.StoryboardConfig = sbConfig;
//sb.BindStoryboardConfig();
//if (storyboardName.ToLowerInvariant() == "loadandvalidate")
//{
// Guid fileDefinitionId = (Guid)paramResolver.Resolve();
// sb = new LNVStoryboard(fileDefinitionId);
// IStoryboardConfig storyboardConfig = (IStoryboardConfig)EngineConfigurationManager.Instance.GetConfig(sb.Name);
// sb.StoryboardConfig = storyboardConfig;
// MessageLoggerManager.GetLogger().DebugVerbose("Leaving Storyboard.Create...");
//}
//else if (storyboardName.ToLowerInvariant() == "viewandupdatefilter")
//{
// Guid viewAndUpdateId = (Guid)paramResolver.Resolve();
// sb = new ViewAndUpdateStoryboard(viewAndUpdateId);
// IStoryboardConfig storyboardConfig = (IStoryboardConfig)EngineConfigurationManager.Instance.GetConfig(sb.Name);
// sb.StoryboardConfig = storyboardConfig;
// MessageLoggerManager.GetLogger().DebugVerbose("Leaving Storyboard.Create...");
//}
if (sb != null)
{
return sb;
}
else
throw new Exception(string.Format("Storyboard {0} is not supported...", storyboardName));
}
}
public class ParameterResolver
{
public short ParameterCategory { get; set; }
public short ParameterDataType { get; set; }
public string ParameterValue { get; set; }
public virtual object Resolve()
{
throw new NotImplementedException();
}
protected ParameterResolver() { }
public static ParameterResolver CreateParameterResolver(short parameterCategory, short parameterDataType, string parameterValue)
{
if (parameterCategory == 0)
{
SimpleParameterResolver paramResolver = new SimpleParameterResolver();
paramResolver.ParameterCategory = parameterCategory;
paramResolver.ParameterDataType = parameterDataType;
paramResolver.ParameterValue = parameterValue;
return paramResolver;
}
else
throw new Exception(string.Format("ParameterResolver {0} not support yet...", parameterCategory));
}
}
public class SimpleParameterResolver : ParameterResolver
{
public override object Resolve()
{
switch (this.ParameterDataType)
{
case 0: return ParameterValue;
case 1: return decimal.Parse(ParameterValue);
case 2: return int.Parse(ParameterValue);
case 3: return short.Parse(ParameterValue);
case 4: return new Guid(ParameterValue);
case 5: return char.Parse(ParameterValue);
default: return null;
}
}
}
}
4.2.2 DataProcess\LVNStoryBoard.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataProcess
{
class LVNStoryBoard:Storyboard
{
//fileDefinitionId为输入参数，可能在SolveData的时候需要，但是有时候可能并不需要输入参数
public LVNStoryBoard(int fileDefinitionId)
{
this.ParameterId = fileDefinitionId;
this.ParameterName = "FileDefinitionId";
}
public override string OutputParameterName
{
get
{
return "DataJobId";
}
}
public override void Start()
{
//会有很多个串行或者并行的操作在这里执行
string result= SolveData();
this.OutputParameterValue = result;
}
public string SolveData()
{
//例如对数据库中的数据进行运算
//例如LoadDataAndValid，包括以下Action，
//FetchCommonPropertiesAction、CreateDataJobAction、FetchDataJobPropertiesAction、GetFilterInfoAction、FetchImportStatusAction等等
//每个Action也有各自的业务逻辑，这些业务逻辑在存储过程中实现比较困难
//数据操作后最终的结果会存入数据库中
return "1000";
}
}
}
4.2.3 DataProcess\Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace DataProcess
{
class Program
{
static void Main(string[] args)
{
string storyboardName = string.Empty;
short parameterCategory = -1;
short parameterDataType = -1;
string parameterValue = string.Empty;
int errorCode = -900000;
string outputMessage = "";
bool IsDebug = false; //也可以写在配置文件里面
if (IsDebug)
{
//也可以写在配置文件里面
storyboardName = "LVNStoryBoard";
parameterCategory = 0;
parameterDataType = 2; //int
parameterValue = "1";
}
else
{
storyboardName = args[0].Trim();
parameterCategory = short.Parse(args[1].Trim());
parameterDataType = short.Parse(args[2].Trim());
parameterValue = args[3].Trim();
}
ParameterResolver paramResolver = ParameterResolver.CreateParameterResolver(parameterCategory, parameterDataType, parameterValue);
Storyboard sb = null;
try
{
sb = Storyboard.Create(storyboardName, paramResolver);
sb.PreStart();
sb.Start();
sb.PostStart();
errorCode = 0;
outputMessage = "Successful";
sb.OnStoryboardSuccessful();
}
catch (Exception ex)
{
outputMessage = "Error: " + ex.Message;
if (sb != null)
sb.OnStoryboardError();
}
string outputParameterName = string.Empty;
string outputParameterValue = string.Empty;
if (sb != null)
{
outputParameterName = sb.OutputParameterName;
outputParameterValue = sb.OutputParameterValue;
}
if (errorCode != 0)
{
Console.WriteLine(string.Format("Result code:{0}, (DataProcessEngine failed)...", errorCode));
Console.WriteLine(string.Format("OutputParameter {0}:{1}, ...", outputParameterName, outputParameterValue));
Console.WriteLine(string.Format("OutputMessage: {0},", PostProcessOutputMessage(outputMessage)));
}
else
{
Console.WriteLine(string.Format("Result code:{0}, (DataProcessEngine successfully)...", errorCode));
Console.WriteLine(string.Format("OutputParameter {0}:{1}, ...", outputParameterName, outputParameterValue));
Console.WriteLine(string.Format("OutputMessage: {0},", PostProcessOutputMessage(outputMessage)));
}
if (sb != null)
sb.OnStoryboardCompleted();
}
private static string PostProcessOutputMessage(string outputMessage)
{
return outputMessage.Replace(Environment.NewLine, " ");
}
}
}
4.3 testCLR\Program.cs
using CLRBridge;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace testCLR
{
class Program
{
static void Main(string[] args)
{
//InvokeDataProcessEngine(string storyboardName, short parameterCategory, short parameterDataType,
//string parameterValue, string outputParameterName, out string outputParameterValue,
//out string outputMessage)
string s;
string msg;
MainEntry.InvokeDataProcessEngine("LVNStoryBoard", 0, 2, "1", "DataJobId", out s, out msg);
}
}
}
5、新建数据库TestCLR，并新建表
USE [TestCLR] GO /****** Object: Table [dbo].[Info] Script Date: 12/30/2015 10:13:24 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Info]( [Tag] [nvarchar](50) NULL, [Value] [nvarchar](50) NULL ) ON [PRIMARY] GO DELETE Info
--项目DataProcess.exe所在的实际路径及名称 INSERT [Info] ([Tag],[Value]) VALUES ( 'DataProcessEngineBaseLocation','C:\CLRBridge\DataProcess\bin\Debug\') INSERT [Info] ([Tag],[Value]) VALUES ( 'DataProcessEngineExeName','DataProcess.exe')
6、部署CLR
--show clr state sp_configure 'clr enabled' --enable clr --1,enable clr\0,disable clr exec sp_configure 'clr enabled',1 --TRUSTWORTHY 数据库属性用于指明 SQL Server 实例是否信任该数据库以及其中的内容 ALTER DATABASE TESTCLR SET TRUSTWORTHY ON; --create a Assmbies create ASSEMBLY [CLRBridge] FROM 'C:\zxc\CLRBridge\CLRBridge\bin\Debug\CLRBridge.dll' WITH PERMISSION_SET = UNSAFE GO CREATE PROCEDURE [dbo].[usp_InvokeDataProcessEngine] @storyboardName [nvarchar](100), @parameterCategory [smallint], @parameterDataType [smallint], @parameterValue [nvarchar](4000), @outputParameterName [nvarchar](100), @outputParameterValue [nvarchar](4000) OUTPUT, @outputMessage [nvarchar](4000) OUTPUT WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRBridge].[CLRBridge.MainEntry].[InvokeDataProcessEngine] GO CREATE PROCEDURE [dbo].[Hello] -- @name [nvarchar](4000) OUTPUT WITH EXECUTE AS CALLER AS EXTERNAL NAME [CLRBridge].[CLRBridge.MainEntry].[Hello] GO
部署后在sql中会有两个存储过程Hello,usp_InvokeDataProcessEngine及新建的Assembly CLRBridge
7、在sql中进行测试
DECLARE @return_value int
DECLARE @outputval BIGINT, @message NVARCHAR(4000)
EXEC @return_value = usp_InvokeDataProcessEngine 'LVNStoryBoard', 0, 2, '810', 'DataJobId', @outputval OUTPUT, @message OUTPUT
PRINT @return_value
PRINT @outputval
PRINT @message
DECLARE @return_value1 int
EXEC @return_value1=hello
PRINT @return_value1
8、在部署过程中可能出现的问题
create ASSEMBLY [CLRBridge]
FROM 'C:\CLRBridge\CLRBridge\bin\Debug\CLRBridge.dll'
WITH PERMISSION_SET = UNSAFE
报错 1
Could not obtain information about Windows NT group/user
解决
ALTER DATABASE TESTCLR SET TRUSTWORTHY ON;
原因：TRUSTWORTHY 数据库属性用于指明 SQL Server 实例是否信任该数据库以及其中的内容
http://www.cnblogs.com/chendaoyin/archive/2013/12/23/3487182.html
http://blog.csdn.net/chen_xizhang/article/details/5952608
报错2 ：System.Security.SecurityException
解决：
create ASSEMBLY [CLRBridge]
FROM 'C:\zxc\CLRBridge\CLRBridge\bin\Debug\CLRBridge.dll'
WITH PERMISSION_SET = UNSAFE
报错3：在数据库中执行
EXEC @return_value = usp_InvokeDataProcessEngine 'LVNStoryBoard', 0, 2, '810', 'DataJobId', @outputval OUTPUT, @message OUTPUT
总是报 Press Acess Deny的错误system.ComponentModel.Win32Expcetion: Access is denied
后来把DataProcess.exe的路径换了地方就好了，开始放在VS2013默认project路径下。这个路径下的文件好像是受保护的，我有时候手动都不能删除。
9、源码下载
参考：
SQL Server CLR全功略之一---CLR介绍和配置
