[.NET] 使用C#開發SQL Function來提供服務 - 簡訊發送

範例下載

範例程式碼：點此下載

問題情景

在「使用C#開發SQL Function來提供資料 - 天氣預報」這篇文章中，介紹如何將系統服務封裝成為SQL Function來提供資料。而在另外一些特殊的開發案例中，將系統服務封裝成為SQL Function不是用來提供資料、而是單單用來提供服務，也是一種很常見的設計。只不過這種設計所服務的目標使用者，不是負責開發系統的RD、而會是偏向負責維運系統的MIS與FAE。

在目前的職場環境中，MIS與FAE已經可以稱做是全能的工作人員，像是系統運轉維護、資料庫備份管理...等等維運工作，都是交由MIS與FAE來一肩扛下。而這兩者在維運的工作流程中常常會需要各種系統服務的支援，例如：系統發生問題時發送簡訊告知維護人員、每個月定時發送報表到主管信箱...等等。

為了滿足這類使用者的需求，開發人員可以選擇將系統服務(簡訊發送、報表發送)封裝成為SQL Function並且佈署到SQL資料庫；後續MIS與FAE只要透過SQL語法來定義Trigger、Job，就可以在這些Trigger、Job中使用系統服務所提供的服務，大幅降低MIS與FAE使用系統服務的技術門檻。

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

資料來源

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

這個CLK.KotsmsAPIs套件可以由NuGet取得，套件中的KotsmsAPI類別透過HTTP通訊協定來使用簡訊王(http://www.kotsms.com.tw/)所提供的服務，用來提供簡訊發送相關功能給開發人員使用。

而在使用CLK.KotsmsAPIs套件之前，記得要先到簡訊王(http://www.kotsms.com.tw/)申請測試帳號，取得相關的帳號、密碼、點數，才能實際使用簡訊發送的相關功能。

功能開發

接著依照「使用C#開發SQL Function來提供資料 - 天氣預報」這篇文章的開發步驟，就可以將CLK.KotsmsAPIs套件中的KotsmsAPI類別封裝成為下列的SQL Function，並且發行到資料庫。

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlInt32 KotsmsAPI_SendMessage(string username, string password, string phoneNumber, string message)
    {
        return new KotsmsAPI(username, password).SendMessage(phoneNumber, message);
    }
}

功能使用

01.SQL語法使用

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

EXEC dbo.KotsmsAPI_SendMessage @username = '***********',
                               @password = '***********',
                               @phoneNumber = '28825252',
                               @message = '測試發送:許蓋功Ab123'

02.Trigger使用

一些既有系統在運作中，會將系統中所發生的事件、警報...等等資料寫入資料庫。

在某些無法修改系統原始碼的維護情景中，負責維護系統的MIS或FAE，可以為這些紀錄事件、警報...等等資料的表格定義Trigger，並在Trigger中使用封裝成為SQL Function的系統服務。透過這樣的方式，就能在系統發生事件、警報的同時，同步透過系統服務來通知相關的負責人員。例如下列範例：就是透過警報紀錄表格(AlarmHistory)的Trigger，在發生警報的時候，同步發送簡訊來通知相關的負責人員。

-- 建立Trigger
CREATE TRIGGER [dbo].[AlarmHistory_Transmit] on [dbo].[AlarmHistory] AFTER INSERT
AS
BEGIN  
    DECLARE @AlarmMessage as nvarchar(MAX)

    SELECT @AlarmMessage = AlarmMessage FROM Inserted

    EXEC dbo.KotsmsAPI_SendMessage @username = '***********',
                                   @password = '***********',
                                   @phoneNumber = '28825252',
                                   @message = @AlarmMessage
END

03.Stored Procedure使用

也有一些既有系統在開發時，會特別將SQL語法全部封裝成為SQL內的Stored Procedure，系統內所有資料操作都是透過這些Stored Procedure來讀寫資料庫。

在某些無法修改系統原始碼的維護情景中，負責維護系統的MIS或FAE，可以在這些Stored Procedure中插入封裝成為SQL Function的系統服務。透過這樣的方式，就能在系統操作資料庫的同時，同步透過系統服務來通知相關的負責人員。例如下列範例：就是透過修改記錄溫度的Stored Procedure，在溫度過高的時候，同步發送簡訊來通知相關的負責人員。

-- 建立Procedure
CREATE PROCEDURE Insert_TemperatureHistory @Temperature as int
AS
BEGIN

    -- 既有Stored Procedure
    INSERT INTO dbo.TemperatureHistory (Temperature)
                                VALUES (@Temperature);

    -- 插入SQL Function
    IF(@Temperature>50)
    BEGIN
        EXEC dbo.KotsmsAPI_SendMessage @username = '***********',
                                       @password = '***********',
                                       @phoneNumber = '28825252',
                                       @message = '溫度過高'
    END

END