[.NET] 使用C#開發SQL Function來提供資料 - 天氣預報

範例下載

範例程式碼：點此下載

問題情景

開發人員在設計一些資料彙整的系統服務時，可能會選擇WCF、WebAPI、SignalR...等等通訊框架，來開放API給客戶端開發人員使用。但在一些特殊的開發案例中，客戶會很婉轉的告知開發人員，上述這些技術太新，客戶端開發人員無法理解與使用這樣的技術。

為了滿足這類客戶的需求，開發人員可以選擇將系統服務封裝成為SQL Function並且佈署到SQL資料庫；後續客戶端開發人員透過SELECT語法來查詢SQL資料庫，就可以取得系統服務所提供的資料，大幅降低客戶端開發人員使用系統服務的技術門檻。

本篇文章介紹如何將系統服務封裝成為SQL Function來提供資料，為自己留個紀錄也希望能幫助到有需要的開發人員。

資料來源

為了降低範例的複雜度，後續範例使用CLK.OpenDataAPIs套件中的WeatherAPI類別做為系統服務，來示範如何將系統服務封裝成為SQL Function。

這個CLK.OpenDataAPIs套件可以由NuGet取得，套件中的WeatherAPI類別透過HTTP通訊協定從政府資料開發平台(http://data.gov.tw/)取得縣市天氣預報，用來提供天氣預報的相關資料給開發人員使用。

功能開發

01.建立DataBase

示範如何將系統服務封裝成為SQL Function，第一個步驟就是建立一個用來安裝SQL Function的範例資料庫：ClrSampleDB。

02.設定DataBase - 開啟SQLCLR

接著為了在SQL Server中執行C#所開發的SQL Function，必須要先透過下列的SQL指令，在SQL Server中開啟CLR的功能。

EXEC sp_configure 'clr enabled', 1  
GO  
RECONFIGURE  
GO

03.建立資料庫專案

完成了上列資料庫的基本設定，就可以開啟Visual Studio來動手建立資料庫類型專案：CLK.OpenDataAPIs.SqlExtension。

04.參考CLK.OpenDataAPIs

接著為CLK.OpenDataAPIs.SqlExtension專案，加入CLK.OpenDataAPIs套件來做為系統服務。這個CLK.OpenDataAPIs套件可以由NuGet取得，但是在Visual Studio中資料庫類型專案，並不支援使用NuGet來加入套件參考，所以開發人員在這個步驟要手動加入套件參考。

• 開啟NuGet Package Explorer來下載CLK.OpenDataAPIs的DLL：CLK.OpenDataAPIs.dll。

  

  

  

• 取得CLK.OpenDataAPIs.dll之後，就可以將這個套件DLL加入CLK.OpenDataAPIs.SqlExtension資料庫專案的參考。

  

05.設定CLK.OpenDataAPIs

接著還需要將CLK.OpenDataAPIs.dll的屬性變更為下圖的內容，其中最重要的就是「權限集合」這個屬性必須要設定為「外部」，這樣SQL Server在執行的時候，才會允許CLK.OpenDataAPIs.dll開啟HTTP通訊協定來取得資料。

06.設定資料庫專案 - 可信任 & 權限等級

設定完加入的套件參考之後，還要接著設定資料庫專案的屬性：可信任、權限等級，這兩個屬性必須要設定為下圖所示的內容，後續SQL Server在執行的時候，才會允許這個資料庫專案CLK.OpenDataAPIs.SqlExtension.dll，使用CLK.OpenDataAPIs.dll開啟HTTP通訊協定來取得資料。

07.封裝SQL Function

完成上述這個瑣碎的設定之後，就可以寫程式來將CLK.OpenDataAPIs套件中的WeatherAPI類別封裝成為SQL Function。

public partial class UserDefinedFunctions
{
    [SqlFunction(TableDefinition = @"LocationName nvarchar(MAX), 
                                     IssueTime datetime, 
                                     StartTime datetime, 
                                     EndTime datetime,
                                     MaxTemperature int,
                                     MaxTemperatureUnits nvarchar(MAX),
                                     MinTemperature int,
                                     MinTemperatureUnits nvarchar(MAX),
                                     ProbabilityOfPrecipitation int,
                                     ProbabilityOfPrecipitationUnits nvarchar(MAX),
                                     Weather int,
                                     WeatherText nvarchar(MAX),
                                     ComfortIndexText nvarchar(MAX)",
                FillRowMethodName = "WeatherAPI_GetAllForecast_FillRowMethod")]
    public static IEnumerable WeatherAPI_GetAllForecast()
    {
        return new WeatherAPI().GetAllForecast();
    }
}

public partial class UserDefinedFunctions
{
    // Methods
    public static void WeatherAPI_GetAllForecast_FillRowMethod(object row,
                                                               ref SqlString LocationName,
                                                               ref SqlDateTime IssueTime,
                                                               ref SqlDateTime StartTime,
                                                               ref SqlDateTime EndTime,
                                                               ref SqlInt32 MaxTemperature,
                                                               ref SqlString MaxTemperatureUnits,
                                                               ref SqlInt32 MinTemperature,
                                                               ref SqlString MinTemperatureUnits,
                                                               ref SqlInt32 ProbabilityOfPrecipitation,
                                                               ref SqlString ProbabilityOfPrecipitationUnits,
                                                               ref SqlInt32 Weather,
                                                               ref SqlString WeatherText,
                                                               ref SqlString ComfortIndexText)
    {
        // Require
        Forecast forecast = row as Forecast;
        if (forecast == null) throw new InvalidOperationException();

        // Fill
        LocationName = forecast.LocationName;
        IssueTime = forecast.IssueTime;
        StartTime = forecast.StartTime;
        EndTime = forecast.EndTime;
        MaxTemperature = forecast.MaxTemperature;
        MaxTemperatureUnits = forecast.MaxTemperatureUnits;
        MinTemperature = forecast.MinTemperature;
        MinTemperatureUnits = forecast.MinTemperatureUnits;
        ProbabilityOfPrecipitation = forecast.ProbabilityOfPrecipitation;
        ProbabilityOfPrecipitationUnits = forecast.ProbabilityOfPrecipitationUnits;
        Weather = forecast.Weather;
        WeatherText = forecast.WeatherText;
        ComfortIndexText = forecast.ComfortIndexText;
    }
}

這段封裝的步驟有點複雜，拆成幾塊會比較好理解。

首先在Function中將回傳資料格式定義為IEnumerable，這代表Function會回傳一個資料集合。

在SQL Server中是以Table來處理資料，沒有辦法處理IEnumerable類型回傳的資料集合。為了將IEnumerable類型轉換為Table讓SQL Server能夠處理，必須要先在SqlFunction特性的TableDefinition屬性中，定義IEnumerable類型的資料集合會被轉換為何種格式的Table的Schema。

SQL Server沒有提供自動依照Table Schema來轉換IEnumerable類型資料集合的功能，必須要開發人員寫程式將資料集合中的每個資料物件，轉換為Table中的每一行Row。而在SqlFunction特性的FillRowMethodName屬性中，可以指定用來將資料物件轉換為Table Row的轉換函式。

在這個將資料物件轉換為Table Row的轉換函式中，必須依照先前SqlFunction特性的TableDefinition屬性所定義的Table Schema，來定義做為函式輸出的ref參數，在這其中每個ref參數對應到一個Table Schema的欄位。

最後就是將每個資料物件屬性，填入對應的ref參數，用來輸出成為每個Table Row的欄位內容。至此就完成整個SQL Function的設計，這個SQL Function在執行的時候，就會取得資料集合並且依照程式定義將每個資料物件轉為Table Row來組成Table提供給SQL Server使用。

08.發行資料庫專案

將CLK.OpenDataAPIs套件中的WeatherAPI類別封裝成為SQL Function之後，還需要將包含這個SQL Function的資料庫專案，發行到先前建立的範例資料庫：ClrSampleDB裡面。

09.檢視DataBase

完成上列一連串的動作之後，就可以在資料庫看到資料庫專案中發行的組件以及函數。

功能使用

在SQL Server中，C#開發SQL Function與原生的SQL Function是同等級的存在，可以透過SQL語法來調用。

SELECT * FROM WeatherAPI_GetAllForecast()

當然也可以將SQL Function封裝成為View，讓使用者更方便的使用。

參考資料

張小呆的碎碎唸 - 使用 SQLCLR 來實作 SPLIT