9 处理SQLCLR - xp44m

9.1 SQLCLR能否取代T-SQL

SQL CLR是实现专门执行计算的程序的最佳选择

9.2 不使用Visual Studio创建存储过程

9.2.1 启用SqlClr

在management studio中运行如下查询代码启用服务器设置：

代码

--启用或禁用数据库的SQLCLR功能
--1, 启用SQLCLR
--0, 禁用SQLCLR
EXEC SP_CONFIGURE 'CLR ENABLED',1
RECONFIGURE WITH OVERRIDE

--获取SQLCLR的状态
EXEC SP_CONFIGURE 'CLR ENABLED'

--获取SQLCLR的属性
SELECT * FROM SYS.DM_CLR_PROPERTIES

9.2.2 创建源代码

代码

Imports Microsoft.SqlServer.Server
Namespace VbTestNamespace
    Public Class VbTestClass
        Public Shared Sub SayHi()
            SqlContext.Pipe.Send("Hi VB World from Sql server!")
        End Sub
    End Class
End Namespace

9.2.3 使用上下文对象

9.2.4 编译代码

9.2.5 加载程序集

CREATE ASSEMBLY VbProcs FROM 'c:\HiVbWorld.dll'
WITH PERMISSION_SET=SAFE
GO

9.2.6 修改执行权限

启用数据库Lake的trustWorthy属性

USE MASTER
ALTER DATABASE Lake SET TRUSTWORTHY ON

为CSL赋予EXTERNAL_ACCESS权限

USE MASTER
GRANT EXTERNAL ACCESS ASSEMBLY TO CSL

为CSL赋予UNSAFE权限

USE MASTER
GRANT UNSAFE ASSEMBLY TO CSL

9.2.7 注册存储过程

CREATE PROCEDURE SayVbHi
AS EXTERNAL NAME VbProcs.[VbTestNamespace.VbTestClass].SayHi

9.2.8 执行存储过程

9.2.9 刷新程序集

ALTER ASSEMBLY VbProcs FROM 'c:\HiVbWorld.dll'
GO

9.2.10 查看已经安装的程序集及其权限

SELECT * FROM sys.assemblies

9.2.11 使用参数传输数据

vb函数如下：

Public Shared Sub GetGreeting(ByVal name As String, ByRef greeting As String)
greeting = String.Format("Hello from VB, {0}", name)
End Sub

注册包含参数的vb存储过程

CREATE PROCEDURE GetVbGreeting
@name NVARCHAR(50),
@greeting NVARCHAR(100) OUTPUT
AS EXTERNAL NAME VbProcs.[VbTestNamespace.VbTestClass].GetGreeting

执行存储过程

DECLARE @result NVARCHAR(100)
EXEC GetVbGreeting 'Glenn', @result OUTPUT
PRINT @result

9.3 使用Visual Studio创建存储过程

存储过程模板生成的代码：

代码

修改自动生成的代码得到以下代码：

<Microsoft.SqlServer.Server.SqlProcedure(Name:="HiVb")> _
Public Shared Sub HelloVb()
SqlContext.Pipe.Send("Hello from VB!")
End Sub

执行存储过程：

EXEC HiVb

9.4 传递行集数据

9.4.1 传递代码中生成的行集数据

代码

执行存储过程：

EXEC GetVbWords 'This is a test of the GetWords stored procedure'

9.4.1.1 在SQLCLR中使用SqlConnection对象

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub LastEmployeeOrdersVb()
        Dim rec As New SqlDataRecord(New SqlMetaData("EmployeeId", SqlDbType.Int), _
                                     New SqlMetaData("LastOrders", SqlDbType.NVarChar, 50))
        Dim employees As New DataTable("Employees")
        Using cn As New SqlConnection()
            cn.ConnectionString = "context connection=true"
            Using cmd = cn.CreateCommand
                cmd.CommandText = "SELECT EmployeeId From Employees" _
                                & " Order by EmployeeId ASC"

                cn.Open()
                Using rdr As SqlDataReader = cmd.ExecuteReader
                    employees.Load(rdr)
                End Using
            End Using

            SqlContext.Pipe.SendResultsStart(rec)
            For Each dr As DataRow In employees.Rows
                Dim empId As Integer = dr("EmployeeId")
                Using cmd As SqlCommand = cn.CreateCommand
                    cmd.CommandText = String.Format("select top 3 OrderID from Orders " _
                                                    & "Where EmployeeId={0} " _
                                                    & "Order by OrderDate Desc", empId)

                    Using rdr As SqlDataReader = cmd.ExecuteReader
                        Dim orders As String = ""
                        While (rdr.Read)
                            If orders.Length > 0 Then orders &= ", "
                            orders &= rdr(0).ToString
                        End While
                        rec.SetInt32(0, empId)
                        rec.SetString(1, orders)
                        SqlContext.Pipe.SendResultsRow(rec)
                    End Using
                End Using
            Next
        End Using
        SqlContext.Pipe.SendResultsEnd()
    End Sub
End Class

9.4.2 传递数据库行集中的数据

代码

使用SqlContext.Pipe.ExecuteAndSend方法简化代码

代码

9.5 创建用户自定义函数

9.5.1 使用标量函数

标量函数的定义：

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function PadVb(ByVal inputValue As Integer, ByVal width As Integer) As SqlString
        Return New SqlString(inputValue.ToString.PadLeft(width, "0"c))
    End Function
End Class

测试：

SELECT dbo.PadVb(supplierid,5) as ID, CompanyName FROM suppliers

9.5.2 使用流表值函数(TVF)

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Imports System.Security.Principal

Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction( _
    FillRowMethodName:="FillRow", _
    TableDefinition:="Name nvarchar(32), Length bigint, Modified DateTime", _
    SystemDataAccess:=SystemDataAccessKind.Read)> _
    Public Shared Function FileList(ByVal directoryName As String, _
                                    ByVal pattern As String) As IEnumerable

        Dim files() As FileInfo
        Dim OriginalContext As WindowsImpersonationContext
        OriginalContext = SqlContext.WindowsIdentity.Impersonate

        Try
            Dim di As New DirectoryInfo(directoryName)
            files = di.GetFiles(pattern)
        Finally
            If OriginalContext IsNot Nothing Then
                OriginalContext.Undo()
            End If
        End Try
        Return files
    End Function

    'the fill row method that cracks the FileRecord
    'and returns the individual columns.
    Public Shared Sub FillRow(ByVal obj As Object, _
                              ByRef fileName As SqlString, _
                              ByRef fileLength As SqlInt64, _
                              ByRef fileModified As SqlDateTime)
        If obj IsNot Nothing Then
            Dim file As FileInfo = obj
            fileName = file.Name
            fileLength = file.Length
            fileModified = file.LastWriteTime
        Else
            fileName = SqlString.Null
            fileLength = SqlInt64.Null
            fileModified = SqlDateTime.Null
        End If
    End Sub
End Class

测试：

SELECT * FROM dbo.FileListVb('C:\','*.*')

9.6 处理用户自定义聚集

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedAggregate( _
    Format.Native, isNullIfEmpty:=True, isInvariantToNulls:=True)> _
Public Structure DateTimeSpanVb

    Private minDate As SqlDateTime
    Private maxDate As SqlDateTime

    Public Sub Init()
        minDate = SqlDateTime.Null
        maxDate = SqlDateTime.Null
    End Sub

    Public Sub Accumulate(ByVal value As SqlDateTime)
        If value.IsNull Then Return
        If minDate.IsNull Or value.CompareTo(minDate) < 0 Then
            minDate = value
        End If
        If maxDate.IsNull Or value.CompareTo(maxDate) > 0 Then
            maxDate = value
        End If
    End Sub

    Public Sub Merge(ByVal Group As DateTimeSpanVb)
        Accumulate(Group.minDate)
        Accumulate(Group.maxDate)
    End Sub

    Public Function Terminate() As SqlString
        If maxDate.IsNull Or minDate.IsNull Then Return SqlString.Null

        Dim ts As TimeSpan = maxDate.Value - minDate.Value

        Return New SqlString(ts.ToString)
    End Function
End Structure

测试：

SELECT dbo.DateTimeSpanVb(ShippedDate) AS TimeSpan

另一个用户自定义聚集示例：

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Collections.Generic

Imports System.IO

<Serializable()> _
<SqlUserDefinedAggregate(Format.UserDefined, _
    IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, _
    MaxByteSize:=8000)> _
Public Structure JoinString
    Implements IBinarySerialize

    Private items As List(Of String)

    Public Sub Init()
        items = New List(Of String)
    End Sub

    Public Sub Accumulate(ByVal value As SqlString)
        If value.IsNull Then Return
        items.Add(value.ToString())
    End Sub

    Public Sub Merge(ByVal Group As JoinString)
        items.AddRange(Group.items)
    End Sub

    Public Function Terminate() As SqlString
        Return New SqlString(String.Join(","c, items.ToArray))
    End Function

    Public Sub Read(ByVal r As BinaryReader) Implements Microsoft.SqlServer.Server.IBinarySerialize.Read
        items = New List(Of String)
        Dim count = r.ReadInt32()
        For i = 0 To count - 1
            items.Add(r.ReadString())
        Next
    End Sub

    Public Sub Write(ByVal w As BinaryWriter) Implements Microsoft.SqlServer.Server.IBinarySerialize.Write
        w.Write(items.Count)
        For i = 0 To items.Count - 1
            w.Write(items(i))
        Next
    End Sub
End Structure

9.7 处理触发器

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class Triggers
    <Microsoft.SqlServer.Server.SqlTrigger( _
        Name:="ReturnDifferenceVb", Target:="Products", Event:="FOR UPDATE")> _
    Public Shared Sub ReturnDifferenceVb()
        Using cn As New SqlConnection()
            cn.ConnectionString = "Context connection=true"
            cn.Open()
            Using cmd As SqlCommand = cn.CreateCommand
                cmd.CommandText = "SELECT i.ProductID,(i.UnitPrice-d.UnitPrice) AS AmountChanged" _
                        & "FROM INSERTED i JOIN DELETED d ON i.ProductID=d.ProductID " _
                        & "ORDER BY ProductID ASC"

                SqlContext.Pipe.ExecuteAndSend(cmd)
            End Using
        End Using
    End Sub
End Class

9.8 处理用户自定义类型

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _
Public Structure DistanceVb
    Implements INullable, IComparable

    Public ReadOnly Property Feet As Integer
        Get
            Return CInt(totalInches / 12)
        End Get
    End Property

    Public ReadOnly Property Inches As Integer
        Get
            Return totalInches Mod 12
        End Get
    End Property

    Public Overrides Function ToString() As String
        ' Put your code here
        Return String.Format("{0 ft. {1} in.", Feet, Inches)
    End Function

    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
        Get
            ' Put your code here
            Return m_Null
        End Get
    End Property

    Public Shared ReadOnly Property Null As DistanceVb
        Get
            Dim h As DistanceVb = New DistanceVb
            h.m_Null = True
            Return h
        End Get
    End Property

    Public Shared Function Parse(ByVal s As SqlString) As DistanceVb
        If s.IsNull Then
            Return Null
        End If
        Dim u As DistanceVb = New DistanceVb
        ' Put your code here
        Dim distance As String = s.Value
        If distance = "null" Then Return Null
        distance = distance.ToLower
        Dim feet As Integer = 0
        Dim inches As Integer = 0
        Dim parts() As String = distance.Split(" "c)
        Dim feetLocation As Integer = Array.IndexOf(parts, "ft.")
        If feetLocation > 0 Then
            feet = Integer.Parse(parts(feetLocation - 1))
        End If
        Dim inchesLocation As Integer = Array.IndexOf(parts, "in.")
        If inchesLocation > 0 Then
            inches = Integer.Parse(parts(inchesLocation - 1))
        End If
        u.totalInches = (feet * 12) + inches
        Return u
    End Function

    Function CompareTo(ByVal obj As Object) As Integer _
        Implements icomparable.compareto
        Dim other As DistanceVb = CType(obj, DistanceVb)
        Return totalInches - other.totalInches
    End Function

    Public totalInches As Integer
    Private m_Null As Boolean
End Structure

测试UDT:

代码

create table UdtTestVb(Id int not null, distance distancevb not null)

insert into UdtTestVb values(1,'2 ft. 5 in.')
insert into UdtTestVb values(2,'15 in.')
insert into UdtTestVb values(3,'10 ft.')
insert into UdtTestVb values(4,'1 ft. 23 in.')

select id, convert(nvarchar(25),distance) from UdtTestVb
drop table UdtTestVb

9.8.1 何时不使用UDT

9.8.2 何时使用UDT

代码

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _
Public Structure StringStuffVb
    Implements INullable

    Public Shared Function PadLeft(
                                  ByVal inputString As SqlString,
                                  ByVal totalWidth As SqlInt32) As SqlString
        Return New SqlString(
            inputString.Value.PadLeft(totalWidth.Value))
    End Function

    Public Shared Function PadRight(
                                  ByVal inputString As SqlString,
                                  ByVal totalWidth As SqlInt32) As SqlString
        Return New SqlString(
            inputString.Value.PadRight(totalWidth.Value))
    End Function

    Public Overrides Function ToString() As String
        ' Put your code here
        Return ""
    End Function

    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
        Get
            ' Put your code here
            Return m_Null
        End Get
    End Property

    Public Shared ReadOnly Property Null As StringStuffVb
        Get
            Dim h As StringStuffVb = New StringStuffVb
            h.m_Null = True
            Return h
        End Get
    End Property

    Public Shared Function Parse(ByVal s As SqlString) As StringStuffVb
        If s.IsNull Then
            Return Null
        End If

        Dim u As StringStuffVb = New StringStuffVb
        ' Put your code here
        Return u
    End Function

    ' Private member
    Private m_Null As Boolean
End Structure

测试代码，使用UDT中的静态方法：

select '<'+stringstuffvb::padleft('Hi',10)+'>'
select '<'+stringstuffvb::padright('Hi',10)+'>'

9.9 在客户端访问SqlClr特征

代码

Imports System.Data.SqlClient

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object,
                              ByVal e As System.EventArgs) Handles Button1.Click
        Using cn As New SqlConnection
            cn.ConnectionString =
                "server=.\sqlexpress;Initial Catalog=northwind;Integrated Security=True"
            Using cmd As SqlCommand = cn.CreateCommand
                cmd.CommandText =
                    "create table UdtTestVb(Id int not null,Distance distanceVb not null)"
                cn.Open()
                cmd.ExecuteNonQuery()
                cmd.CommandText =
                    "insert into UdtTestVb values(@Id,@distance)"
                Dim id As SqlParameter = cmd.CreateParameter
                id.ParameterName = "@id"
                id.DbType = DbType.Int32

                Dim distance As SqlParameter = cmd.CreateParameter
                distance.ParameterName = "@distance"
                distance.SqlDbType = SqlDbType.Udt
                distance.UdtTypeName = "DistanceVb"

                cmd.Parameters.Add(id)
                cmd.Parameters.Add(distance)

                id.Value = 1
                distance.Value = distanceVb.Parse("2 ft. 5 in.")
                cmd.ExecuteNonQuery()

                id.Value = 2
                distance.Value = DistanceVb.Parse("15 in.")
                cmd.ExecuteNonQuery()

                id.Value = 3
                distance.Value = DistanceVb.Parse("10 ft.")
                cmd.ExecuteNonQuery()

                id.Value = 4
                distance.Value = DistanceVb.Parse("1 ft. 23 in.")
                cmd.ExecuteNonQuery()

                cmd.CommandText = "SELECT id, convert(nvarchar(25),distance) FROM UdtTestVb"
                cmd.Parameters.Clear()
                Dim testTable As New DataTable
                testTable.Load(cmd.ExecuteReader)
                DataGridView1.DataSource = testTable

                cmd.CommandText = "drop table UdtTestVb"
                cmd.ExecuteNonQuery()
            End Using
        End Using
    End Sub
End Class

发表于 2010-02-21 20:49 xp44m 阅读(425) 评论(0) 编辑收藏举报