#眉標=SQL Server 2008、.NET CLR、LINQ
#副標=實用SQL Server 2008（6）
#大標=SQL Server 2008 CLR程式寫作(上)
#作者=文／圖 胡百敬


===<反灰>=============
Dim i As Integer?
==<end>==============




===<反灰>=============
Dim j As Integer
==<end>==============




==<box>===========
程式1
Dim i As Integer?
Dim j As Integer
i = Nothing
j = Nothing
Try
    MessageBox.Show(j) '呈現 0
    MessageBox.Show(i.HasValue) '呈現 False
    MessageBox.Show(i) '丟出例外 「可為 Null 的物件必須具有值。」
Catch ex As Exception
    Debug.Print(ex.Message)
End Try
==<end>==============





==<box>===========
程式2
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function AddNullable(ByVal intA As Integer?, ByVal intB As Integer?) As Integer?
    '直接相加可能 Null 的資料
    Return intA + intB
End Function
==<end>==============




===<反灰>=============
無法尋找類型'Nullable`1'，因為它不存在或您沒有權限。
==<end>==============





==<box>===========
程式3
use tempdb
go
--將組件註冊到SQL Server內
create assembly SqlCLR from 'C:\SqlServer2008CLRNew\\bin\SqlServer2008CLRNew.dll'
go

--設定使用者自訂函數實際參照的.NET函數
create function AddTwo (@a int, @b int)
returns int
as
external name SqlCLR.[SqlServer2008CLRNew.UserDefinedFunctions].AddNullable
go

--設定SQL Server可以啟動執行.NET所撰寫的物件
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'clr enabled',1
reconfigure

--測試參數值有null的狀況
select dbo.addtwo(1,1)  --傳回 2
select dbo.addtwo(1,null)  --傳回 NULL

--移除物件
DROP FUNCTION AddTwo
DROP ASSEMBLY SqlCLR
==<end>==============





==<box>===========
程式4
Imports System
Imports System.Linq
Imports System.Xml.Linq
Imports System.Data.SqlTypes

Public Class clsXMLLinq
 '需要手動加上 Attribute 定義，
 '告知函數用途
 <Microsoft.SqlServer.Server.SqlFunction()> _
 Public Shared Function FilterAge(ByVal intAge As Integer) As SqlXml
  '自行定義 XML 資料
  Dim elmPersons As New XElement("Persons", _
　　New XElement("John", New XAttribute("Age", 17)), _
　　New XElement("Mary", New XAttribute("Age", 35)), _
　　New XElement("Carl", New XAttribute("Age", 28)), _
　　New XElement("Bob", New XAttribute("Age", 57)), _
　　New XElement("Nelly", New XAttribute("Age", 13)) _
  )
  '透過 LINQ to XML 查詢 XML 資料
  Dim filterQuery = From el In elmPersons.Descendants() _
  Where Convert.ToInt32(el.Attribute("Age").Value) > intAge _
  Order By el.Attribute("Age").Value _
  Select el

  Dim TreeResult As New XElement("Persons", filterQuery)
  '直接傳回 XML 資料
  Dim TreeFiltered As New SqlXml(TreeResult.CreateReader())
      Return TreeFiltered
 End Function
==<end>==============




==<box>===========
程式5
USE tempdb
CREATE ASSEMBLY XMLLinq 
FROM 'C:\SqlServer2008CLRNew\XMLLinq\bin\Debug\XMLLinq.dll'
WITH PERMISSION_SET = SAFE 
GO

CREATE FUNCTION dbo.FilterAge(@age AS int)
RETURNS xml
AS EXTERNAL NAME XMLLinq.[XMLLinq.clsXMLLinq].FilterAge
GO

SELECT dbo.FilterAge(25) AS [大於歲者];
==<end>==============





==<box>===========
程式6
Imports System.Collections
Imports System.Text
Imports Microsoft.SqlServer.Server
Imports Microsoft.SqlServer.Types
Imports System.Runtime.InteropServices

Public Class UseHierarchyID
 '要傳回實作 IEnumerable 介面的物件
 '並指定解釋迭代運算所傳回物件的資料意義之函數是 FillRow_ListAncestors
    <SqlFunction(fillrowmethodname:="FillRow_ListAncestors", tabledefinition:="Ancestor HierarchyID")> _
    Public Shared Function ListAncestors(ByVal hir As SqlHierarchyId) As IEnumerable
      Return New hirCurrent(hir)
    End Function

    Public Shared Sub FillRow_ListAncestors(ByVal obj As Object, <Out()> ByRef Ancestor As SqlHierarchyId)
      Ancestor = CType(obj, SqlHierarchyId)
    End Sub
End Class

Public Class hirCurrent : Implements IEnumerator, IEnumerable
    Private position As SqlHierarchyId = SqlHierarchyId.Null
    Public Sub New(ByVal hir As SqlHierarchyId)
      position = hir
    End Sub

    Public Function GetEnumerator() As IEnumerator Implements IEnumerable.GetEnumerator
      Return CType(Me, IEnumerator)
    End Function

 '迭代運算以 SQL Server 2008 HierarchyID 類型本身提供的 GetAncestor 函數，
 '移到原節點的父節點
    Public Function MoveNext() As Boolean Implements IEnumerator.MoveNext
	 If Not position.GetAncestor(1).IsNull Then
	  position = position.GetAncestor(1)
		Return True
	 Else
		Return False
	 End If
    End Function

    Public Sub Reset() Implements IEnumerator.Reset
      position = SqlHierarchyId.Null
    End Sub

    Public ReadOnly Property Current() As Object Implements IEnumerator.Current
      Get
        Return position
      End Get
    End Property
End Class
==<end>==============





==<box>===========
程式7
CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (Ancestor HierarchyID)
AS
EXTERNAL NAME SqlCLR.[SqlServer2008CLRNew.UseHierarchyID].ListAncestors
GO

--驗證函數功能
--Employee 的OrganizationNode 是HierarchyID 資料類型
DECLARE @h HIERARCHYID
select @h=OrganizationNode from AdventureWorks2008.HumanResources.Employee
where BusinessEntityID=8
SELECT e.LoginID,e.OrganizationNode.ToString(),e.OrganizationLevel FROM dbo.ListAncestors(@h) h JOIN AdventureWorks2008.HumanResources.Employee e
ON h.Ancestor=e.OrganizationNode

--直接以HierarchyID 資料類型提供的函數驗證
SELECT P.LoginID,P.OrganizationNode.ToString(),P.OrganizationLevel
FROM AdventureWorks2008.HumanResources.Employee AS P
  JOIN AdventureWorks2008.HumanResources.Employee AS C
    ON C.BusinessEntityID=8
    AND C.OrganizationNode.IsDescendantOf(P.OrganizationNode) = 1
WHERE P.BusinessEntityID<> 8  --節點本身不要出現
==<end>==============





==<box>===========
參考資源

本文的構想來自微軟的範例程式，以及SQL Server大師Bob Beauchemin的網誌。網址：
●http://www.microsoft.com/downloadS/details.aspx?familyid=E9C68E1B-1E0E-4299-B498-6AB3CA72A6D7&displaylang=en 
●http://www.sqlskills.com/BLOGS/BOBB/category/SQLCLR.aspx

相關程式碼下載：●http://cid-bf14192bd27975cb.skydrive.live.com/self.aspx/Source/SQL%202008/SqlServer2008CLRNew.zip
●http://www.runpc.com.tw/download/index.htm
==<end>==============