(十一)SQL Server CLR 集成

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

 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4
 5using Microsoft.SqlServer.Server;
 6using System.Security.Cryptography;
 7
 8public class Functions
 9{
10[SqlFunction(IsDeterministic=true,IsPrecise=true,DataAccess=
11DataAccessKind.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查询：

 

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

 

 

3、   例子：CLR集成的表值函数

 

// Functions.cs

 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4
 5using Microsoft.SqlServer.Server;
 6using System.Collections;
 7using System.Diagnostics;
 8
 9public 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查询：

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

 

4、   例子：CLR集成的聚合函数

//Function.cs

 

 1using System;
 2using System.Collections.Generic;
 3using System.Text;
 4
 5using Microsoft.SqlServer.Server;
 6using System.IO;
 7
 8[SqlUserDefinedAggregate(Format.UserDefined,
 9    IsInvariantToDuplicates = true, IsInvariantToNulls = true,
10    IsNullIfEmpty = true, MaxByteSize = 54)]
11public 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查询：

 

 

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

 

5、例子：CLR存储过程

      // Sprocs.cs

  1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4
  5using Microsoft.SqlServer.Server;
  6using System.Data;
  7using System.Data.SqlTypes;
  8using System.Data.SqlClient;
  9
 10public enum ReturnValue
 11{
 12    OK = 0,
 13    NoStory,
 14    LocationExists,
 15    UnknownError
 16}
 17
 18public 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查询：

 1USE FolktaleDB
 2GO
 3
 4CREATE ASSEMBLY Ex1104 FROM
 5  'L:\DataBase\StoredProcedures\bin\Release\StoredProcedures.dll'
 6WITH PERMISSION_SET = SAFE
 7GO
 8
 9CREATE PROCEDURE dbo.AddStoryLocation
10(
11    @storyId uniqueidentifier,
12    @location nvarchar(100),
13    @locationId uniqueidentifier OUTPUT
14)
15AS EXTERNAL NAME Ex1104.Sprocs.AddStoryLocation
16GO
17
18DECLARE @storyId uniqueidentifier
19DECLARE @location nvarchar(100)
20DECLARE @locationId uniqueidentifier
21DECLARE @result int
22
23SET @storyId = 'da47837d-c6a5-490b-96cf-808137d0e760'
24SET @location = 'Jungle'
25
26EXEC @result = dbo.AddStoryLocation @storyId, @location, @locationId OUTPUT
27
28SELECT @result AS StatusCode, @locationId AS LocationId
29GO
30
31DROP PROCEDURE dbo.AddStoryLocation
32DROP ASSEMBLY Ex1104
33GO
34