9.1 SQLCLR能否取代T-SQL
SQL CLR是实现专门执行计算的程序的最佳选择
9.2 不使用Visual Studio创建存储过程
9.2.1 启用SqlClr
在management studio中运行如下查询代码启用服务器设置:
--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 创建源代码
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 加载程序集
WITH PERMISSION_SET=SAFE
GO
9.2.6 修改执行权限
启用数据库Lake的trustWorthy属性
ALTER DATABASE Lake SET TRUSTWORTHY ON
为CSL赋予EXTERNAL_ACCESS权限
GRANT EXTERNAL ACCESS ASSEMBLY TO CSL
为CSL赋予UNSAFE权限
GRANT UNSAFE ASSEMBLY TO CSL
9.2.7 注册存储过程
AS EXTERNAL NAME VbProcs. [ VbTestNamespace.VbTestClass ] .SayHi
9.2.8 执行存储过程
9.2.9 刷新程序集
GO
9.2.10 查看已经安装的程序集及其权限
9.2.11 使用参数传输数据
vb函数如下:
greeting = String .Format( " Hello from VB, {0} " , name)
End Sub
注册包含参数的vb存储过程
@name NVARCHAR ( 50 ),
@greeting NVARCHAR ( 100 ) OUTPUT
AS EXTERNAL NAME VbProcs. [ VbTestNamespace.VbTestClass ] .GetGreeting
执行存储过程
EXEC GetVbGreeting ' Glenn ' , @result OUTPUT
PRINT @result
9.3 使用Visual Studio创建存储过程
存储过程模板生成的代码:
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 StoredProcedures ()
' 在此处添加您的代码
End Sub
End Class
修改自动生成的代码得到以下代码:
Public Shared Sub HelloVb()
SqlContext.Pipe.Send( " Hello from VB! " )
End Sub
执行存储过程:
9.4 传递行集数据
9.4.1 传递代码中生成的行集数据
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class StoredProcedures
< Microsoft.SqlServer.Server.SqlProcedure(Name: = " GetVbWords " ) > _
Public Shared Sub GetWords( ByVal sentence As String )
Dim rec As New SqlDataRecord( New SqlMetaData( " Index " , SqlDbType.Int), _
New SqlMetaData( " Word " , SqlDbType.NVarChar, 50 ))
SqlContext.Pipe.SendResultsStart(rec)
Dim i = 0
For Each word As String In sentence.Split( " " c)
rec.SetInt32( 0 , i)
i += 1
rec.SetString( 1 , word)
SqlContext.Pipe.SendResultsRow(rec)
Next
SqlContext.Pipe.SendResultsEnd()
End Sub
End Class
执行存储过程:
9.4.1.1 在SQLCLR中使用SqlConnection对象
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 传递数据库行集中的数据
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 GetCustomersVb()
Using cn As New SqlConnection()
cn.ConnectionString = " context connection=true "
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = " Select * from Customers "
cn.Open()
Using rdr As SqlDataReader = cmd.ExecuteReader
SqlContext.Pipe.Send(rdr)
End Using
End Using
End Using
End Sub
End Class
使用SqlContext.Pipe.ExecuteAndSend方法简化代码
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 GetCustomersVb()
Using cn As New SqlConnection()
cn.ConnectionString = " context connection=true "
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = " Select * from Customers "
cn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
End Using
End Using
End Sub
End Class
9.5 创建用户自定义函数
9.5.1 使用标量函数
标量函数的定义:
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
测试:
9.5.2 使用流表值函数(TVF)
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
测试:
9.6 处理用户自定义聚集
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
测试:
另一个用户自定义聚集示例:
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.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.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:
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.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::padright( ' Hi ' , 10 ) + ' > '
9.9 在客户端访问SqlClr特征
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