山丘突歐

很久沒碰 .Net 程式了，這幾天為了做PPolis 後台和資料庫的特殊功能，再把曾經稍稍玩過 VS2005 的介面拿出來用，只是不是很順利的遇到微軟出包，半個下午斷送在小小範例中。

這次要談的主角CLR 確實是SQL 2005 資料庫一項非常厲害的武器，他可讓整個資料庫可以用 .Net 所提供的強大程式庫，這樣寫起Trigger 、Store Procedure、User-Defined Function ，不論運算需求多困難都可以輕易的完成。最重要的是他減少了在AP Server 與 SQL Server 之間的資料傳遞，所以不論怎麼看，這都會是殺手級的資料庫功能。(山丘還沒辦法使用Tools 來Debug CLR 這是現在使用CLR最大的痛)

只是這樣的功能市場上的程式設計們捧場嗎？如果真的捧場，應該不會讓線上的說明錯誤維持這麼久吧？看看那個下午我找到了幾個錯誤：

Err 1
同樣的標題 Create and Run a CLR SQL Server Trigger 中文版卻比英文版的少了程式原碼，這樣下去中文版的讀者可能百思不得其解，『我該怎麼開始寫這些程式呢？』。

Err 2
按照原程式執行是不會有任何結果的，但是也不會有任何錯誤產生，我從來沒想過要幫範例程式Debug，但這回我真的這麼做了。
原程式當然不會有動靜，因為 userName.ToString() 永遠都只會傳回 “@username”，所以

if (IsEMailAddress(userName.Value.ToString()))  //判斷內容需改成山丘寫的這樣
{
    sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(userName)";
    sqlP.Send(sqlComm.CommandText);
    sqlP.ExecuteAndSend(sqlComm);
}

改寫完後…

Err 3
又一個錯誤產生，主要錯誤訊息如下：

The name "userName" is not permitted in this context. 
Valid expressions are constants, constant expressions, and (in some contexts) variables. 
Column names are not permitted.

所以..

if (IsEMailAddress(userName.Value.ToString()))  
{
    sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(@userName)"; //參數該這樣引用
    sqlP.Send(sqlComm.CommandText);
    sqlP.ExecuteAndSend(sqlComm);
}

但是，之前的參數建立方式也錯了，所以完整程式碼應該長成這樣。

[SqlTrigger(Name = "UserNameAudit", Target = "Users", Event = "FOR INSERT")]
public static void UserNameAudit()
{
    SqlTriggerContext triggContext = SqlContext.TriggerContext;
    SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);

    if (triggContext.TriggerAction == TriggerAction.Insert)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
            SqlCommand sqlComm = new SqlCommand();
            SqlPipe sqlP = SqlContext.Pipe;

            sqlComm.Connection = conn;
            sqlComm.CommandText = "SELECT UserName from INSERTED";

            userName.Value = sqlComm.ExecuteScalar().ToString();
            sqlComm.Parameters.Add(userName);

            if (IsEMailAddress(userName.Value.ToString()))
            {
                sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(@userName)";
                sqlP.Send(sqlComm.CommandText);
                sqlP.ExecuteAndSend(sqlComm);
            }
        }
    }
}

不只C#的出現錯誤，我看了一下其他語言的也有同樣的錯誤，甚至Store Procedure 的範例也是錯的，不曉得微軟怎麼會把這麼重要的範例，沒有經過測試的情況下放到官方文件上，也不曉得這樣的錯誤放上網路多久，但這給了山丘一個啟示，下次看微軟範例得要有幫微軟Debug 的準備。

貼出這整個過程，也避免有人遇到同樣錯誤，也跟山丘一樣繞了一大圈才找到解答。

其它範例：
(範例1)(範例2)(範例3)(範例4)