1、 概述
从.NET2.0和SQL Sever 2005开始,可以在SQL Server中集成CLR(Common Language Runtime,通用语言运行时)代码,让用户能够编写在SQL Server中运行.NET代码。可以编写存储过程和函数、将用户定义的类型用作数据库表的类型、编写自己的触发器等。这样一来,使用CRL代码能够执行使用SQL代码难以或者无法执行的很多任务。例如,CRL代码可以访问外部资源,如文件和网络资源,就像在.NET应用程序中一样。另外,这种开发技术还有一个重要的优点:应用程序和SQL Server访问使用相同的代码。这意味着无需学习太多有关SQL语言的知识,因为可以不用其高级功能,而使用.NET代码。
然而,这种技术也有其局限性。例如,在SQL Server中运行的代码不能使用.NET框架中的某些命名空间。另外,SQL Server中使用CLR代码并非是说根本不需要使用SQL代码。
要使用CLR集成,必须先在SQL Server中启用CLR集成功能。语句如下:
EXEC sp_configure ‘ clr enable ’ , 1
RECONFIGURE
在要SQL Server中执行.NET代码,必须使用System.Data命名空间中的属性和类型以特定的方式编写它们。然后,将代码编译为程序集(DLL文件),再将程序集加载到SQL Server中。
2、 例子:CLR集成标量函数
//functions.cs

Code
1
using System;
2
using System.Collections.Generic;
3
using System.Text;
4
5
using Microsoft.SqlServer.Server;
6
using System.Security.Cryptography;
7
8
public class Functions
9

{
10
[SqlFunction(IsDeterministic=true,IsPrecise=true,DataAccess=
11
DataAccessKind.None,SystemDataAccess=SystemDataAccessKind.None)]
12
public static string GetHash(string input)
13
{
14
// Convert input string to byte array.
15
UnicodeEncoding encoding = new UnicodeEncoding();
16
Byte[] inputBytes = encoding.GetBytes(input);
17
18
// Create hash bytes using SHA1 algorithm.
19
SHA1Managed algorithm = new SHA1Managed();
20
Byte[] outputBytes = algorithm.ComputeHash(inputBytes);
21
22
// Create output string
23
StringBuilder sb = new StringBuilder();
24
foreach (Byte outputByte in outputBytes)
25
{
26
sb.Append(outputByte);
27
sb.Append(",");
28
}
29
string output = sb.ToString();
30
output = output.Substring(0, output.Length - 1);
31
32
// Return output bytes as string.
33
return output;
34
}
35
}
36
//创建一个sql查询:

Code
1
CREATE ASSEMBLY Ex1101 FROM
2
'L:\DataBase\ScalarFunctions\bin\Release\ScalarFunctions.dll'
3
WITH PERMISSION_SET = SAFE
4
GO
5
6
CREATE FUNCTION dbo.GetHash(@input nvarchar(1000)) RETURNS nvarchar(1000)
7
AS
8
EXTERNAL NAME Ex1101.Functions.GetHash - - 程序集名.类名.方法名(类中的方法)
9
GO
10
11
SELECT dbo.GetHash('Extremely sensitive information.') AS Hash
12
GO
13
14
DROP FUNCTION dbo.GetHash
15
DROP ASSEMBLY Ex1101
16
GO
3、 例子:CLR集成的表值函数
// Functions.cs

Code
1
using System;
2
using System.Collections.Generic;
3
using System.Text;
4
5
using Microsoft.SqlServer.Server;
6
using System.Collections;
7
using System.Diagnostics;
8
9
public class Functions
10

{
11
[SqlFunction(IsDeterministic = false, IsPrecise = false,
12
DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None,
13
FillRowMethodName = "FillRow")]
14
public static IEnumerable Initialize(string categoryName)
15
{
16
// Check if category exists.
17
if (!PerformanceCounterCategory.Exists(categoryName))
18
{
19
return null;
20
}
21
22
// Get category.
23
PerformanceCounterCategory category = new PerformanceCounterCategory(categoryName);
24
25
// Check for multi-instance property.
26
if (category.CategoryType == PerformanceCounterCategoryType.MultiInstance)
27
{
28
return null;
29
}
30
31
// Return counters.
32
return category.GetCounters();
33
}
34
35
public static void FillRow(object counter, out string counterName, out double categoryValue)
36
{
37
// Obtain column information from counter.
38
counterName = (counter as PerformanceCounter).CounterName;
39
categoryValue = (counter as PerformanceCounter).NextValue();
40
}
41
}
42
//sql查询:

Code
1
USE FolktaleDB
2
GO
3
4
CREATE ASSEMBLY Ex1102 FROM
5
'L:\DataBase\TableFunctions\bin\Release\TableFunctions.dll'
6
WITH PERMISSION_SET = UNSAFE
7
GO
8
9
CREATE FUNCTION dbo.GetCounters(@CategoryName nvarchar(250))
10
RETURNS TABLE (CounterName nvarchar(1000),CounterValue float)
11
AS
12
EXTERNAL NAME Ex1102.Functions.Initialize
13
GO
14
15
SELECT * FROM dbo.GetCounters('Server')
16
GO
17
18
DROP FUNCTION dbo.GetCounters
19
DROP ASSEMBLY Ex1102
20
GO
21
4、 例子:CLR集成的聚合函数
//Function.cs

Code
1
using System;
2
using System.Collections.Generic;
3
using System.Text;
4
5
using Microsoft.SqlServer.Server;
6
using System.IO;
7
8
[SqlUserDefinedAggregate(Format.UserDefined,
9
IsInvariantToDuplicates = true, IsInvariantToNulls = true,
10
IsNullIfEmpty = true, MaxByteSize = 54)]
11
public class FirstLetters : IBinarySerialize
12

{
13
// Internal storage.
14
private List<char> currentState;
15
16
public List<char> CurrentState
17
{
18
get
19
{
20
return currentState;
21
}
22
}
23
24
public void Init()
25
{
26
// Create fresh state.
27
currentState = new List<char>();
28
}
29
30
public void Accumulate(string val)
31
{
32
// Chack val, add new letter if necessary.
33
if (val != null && val.Length > 0 && char.IsLetter(val[0]) && !currentState.Contains(val.ToUpper()[0]))
34
{
35
currentState.Add(val.ToUpper()[0]);
36
}
37
}
38
39
public void Merge(FirstLetters source)
40
{
41
// Add letters from source to current letters.
42
foreach (char sourceChar in source.CurrentState)
43
{
44
if (!currentState.Contains(sourceChar))
45
{
46
currentState.Add(sourceChar);
47
}
48
}
49
}
50
51
public string Terminate()
52
{
53
// Sort data.
54
currentState.Sort();
55
56
// Make string from data.
57
StringBuilder sb = new StringBuilder();
58
foreach (char letter in currentState)
59
{
60
sb.Append(letter);
61
sb.Append(",");
62
}
63
string result = sb.ToString();
64
65
// Trim trailing comma and return string.
66
result = result.Substring(0, result.Length - 1);
67
return result;
68
}
69
70
public void Read(BinaryReader r)
71
{
72
// Get stored string and build List<char> from it.
73
string serializedState = r.ReadString();
74
currentState = new List<char>(serializedState.ToCharArray());
75
}
76
77
public void Write(BinaryWriter w)
78
{
79
// Get storage string as concatenated list of letters.
80
string serializedState = new string(currentState.ToArray());
81
w.Write(serializedState);
82
}
83
}
84
//sql查询:

Code
1
USE FolktaleDB
2
GO
3
4
CREATE ASSEMBLY Ex1103 FROM
5
'L:\DataBase\AggregateFunctions\bin\Release\AggregateFunctions.dll'
6
WITH PERMISSION_SET = SAFE
7
GO
8
9
CREATE AGGREGATE dbo.GetFirstLetters(@input nvarchar(1000)) RETURNS nvarchar(51)
10
EXTERNAL NAME Ex1103.FirstLetters
11
GO
12
13
SELECT dbo.GetFirstLetters(Name) AS FirstLetters FROM Character
14
GO
15
16
DROP AGGREGATE dbo.GetFirstLetters
17
DROP ASSEMBLY Ex1103
18
GO
5、例子:CLR存储过程
// Sprocs.cs

Code
1
using System;
2
using System.Collections.Generic;
3
using System.Text;
4
5
using Microsoft.SqlServer.Server;
6
using System.Data;
7
using System.Data.SqlTypes;
8
using System.Data.SqlClient;
9
10
public enum ReturnValue
11

{
12
OK = 0,
13
NoStory,
14
LocationExists,
15
UnknownError
16
}
17
18
public class Sprocs
19

{
20
[SqlProcedure]
21
public static int AddStoryLocation(Guid storyId, string location,
22
out SqlGuid locationId)
23
{
24
// Init locationId to null value before processing.
25
locationId = SqlGuid.Null;
26
27
try
28
{
29
// Check data and process addition.
30
using (SqlConnection conn =
31
new SqlConnection("context connection=true"))
32
{
33
// Verify that story exists.
34
SqlCommand getStoryCmd = new SqlCommand(
35
"SELECT Name FROM Story WHERE StoryId = @StoryId", conn);
36
getStoryCmd.Parameters.Add(new SqlParameter("@StoryId",
37
SqlDbType.UniqueIdentifier)).Value = storyId;
38
conn.Open();
39
string storyName = getStoryCmd.ExecuteScalar() as string;
40
conn.Close();
41
42
// If no story exists, return message and exit.
43
if (storyName == null)
44
{
45
SqlContext.Pipe.Send("No story with an ID of "
46
+ storyId.ToString() + " exists. Unable to add location.");
47
return (int)ReturnValue.NoStory;
48
}
49
50
// Look for existing location.
51
SqlCommand getLocationCmd = new SqlCommand(
52
"SELECT LocationId FROM Location WHERE Location = @Location",
53
conn);
54
getLocationCmd.Parameters.Add(new SqlParameter("@Location",
55
SqlDbType.VarChar, 100)).Value = location;
56
conn.Open();
57
Guid? existingLocationId = getLocationCmd.ExecuteScalar() as Guid?;
58
conn.Close();
59
60
if (existingLocationId.HasValue)
61
{
62
// Set locationId
63
locationId = existingLocationId.Value;
64
65
// If location exists, check for existing story location.
66
SqlCommand getStoryLocationCmd = new SqlCommand(
67
"SELECT StoryLocationId FROM StoryLocation "
68
+ "WHERE StoryId = @StoryId AND LocationId = @LocationId",
69
conn);
70
getStoryLocationCmd.Parameters.Add(new SqlParameter("@StoryId",
71
SqlDbType.UniqueIdentifier)).Value = storyId;
72
getStoryLocationCmd.Parameters.Add(new SqlParameter(
73
"@LocationId", SqlDbType.UniqueIdentifier)).Value =
74
locationId.Value;
75
conn.Open();
76
Guid? existingStoryLocationId =
77
getStoryLocationCmd.ExecuteScalar() as Guid?;
78
conn.Close();
79
80
// If story location exists, return message and exit.
81
if (existingStoryLocationId.HasValue)
82
{
83
SqlContext.Pipe.Send("Story already has the " + location
84
+ " location.");
85
return (int)ReturnValue.LocationExists;
86
}
87
}
88
else
89
{
90
// If location doesn't exist, add it and get its ID.
91
SqlCommand insertLocationCmd = new SqlCommand(
92
"INSERT INTO Location (Location) VALUES (@Location)",
93
conn);
94
insertLocationCmd.Parameters.Add(new SqlParameter("@Location",
95
SqlDbType.VarChar, 100)).Value = location;
96
conn.Open();
97
insertLocationCmd.ExecuteNonQuery();
98
existingLocationId = getLocationCmd.ExecuteScalar() as Guid?;
99
conn.Close();
100
101
// Set locationId
102
locationId = existingLocationId.Value;
103
104
// Report addition.
105
SqlContext.Pipe.Send("The " + location
106
+ " location did not exist, it has been added.");
107
}
108
109
// Add StoryLocation.
110
SqlCommand insertStoryLocationCmd = new SqlCommand(
111
"INSERT INTO StoryLocation (StoryId, LocationId) "
112
+ "VALUES (@StoryId, @LocationId)", conn);
113
insertStoryLocationCmd.Parameters.Add(new SqlParameter("@StoryId",
114
SqlDbType.UniqueIdentifier)).Value = storyId;
115
insertStoryLocationCmd.Parameters.Add(new SqlParameter(
116
"@LocationId", SqlDbType.UniqueIdentifier)).Value =
117
locationId.Value;
118
conn.Open();
119
insertStoryLocationCmd.ExecuteNonQuery();
120
conn.Close();
121
122
// Report addition.
123
SqlContext.Pipe.Send("Story location added.");
124
}
125
}
126
catch (Exception ex)
127
{
128
// Return error and status.
129
SqlContext.Pipe.Send("An exception occurred. Message: " + ex.Message);
130
return (int)ReturnValue.UnknownError;
131
}
132
133
// Return status of OK.
134
return (int)ReturnValue.OK;
135
}
136
}
//sql查询:

Code
1
USE FolktaleDB
2
GO
3
4
CREATE ASSEMBLY Ex1104 FROM
5
'L:\DataBase\StoredProcedures\bin\Release\StoredProcedures.dll'
6
WITH PERMISSION_SET = SAFE
7
GO
8
9
CREATE PROCEDURE dbo.AddStoryLocation
10
(
11
@storyId uniqueidentifier,
12
@location nvarchar(100),
13
@locationId uniqueidentifier OUTPUT
14
)
15
AS EXTERNAL NAME Ex1104.Sprocs.AddStoryLocation
16
GO
17
18
DECLARE @storyId uniqueidentifier
19
DECLARE @location nvarchar(100)
20
DECLARE @locationId uniqueidentifier
21
DECLARE @result int
22
23
SET @storyId = 'da47837d-c6a5-490b-96cf-808137d0e760'
24
SET @location = 'Jungle'
25
26
EXEC @result = dbo.AddStoryLocation @storyId, @location, @locationId OUTPUT
27
28
SELECT @result AS StatusCode, @locationId AS LocationId
29
GO
30
31
DROP PROCEDURE dbo.AddStoryLocation
32
DROP ASSEMBLY Ex1104
33
GO
34
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步