In 91

前言
雖然在layer concept與architect principle底下，應該盡可能地避免直接使用stored procedure來處理與business logic相關的事務，但現實生活畢竟還是現實生活，還是會有一些不得不使用stored procedure的前提，例如已存在的包袱、performance的考量、人員與環境的考量，甚至user的堅持與要求。

但即便是需要在AP直接呼叫DB的stored procedure，至少我們仍應遵守layer的concept，將呼叫DB相關的部分，置於DAL。並且想辦法避免AP呼叫stored procedure時，被DBMS的差異所影響。

這一篇文章，我們將介紹當呼叫stored procedure且需得到stored procedure的output parameters/return value，有兩種作法可以使用。

先來看我們的Sample stored procedure長怎樣：


擁有兩個input參數，分別是myFactorId與myValue，另外有個output參數@where，我們在這邊需要得到stored procedure執行完畢後，@where的值。

Solution

第一種作法，是使用Spring.AdoTemplate的作法，直接呼叫ExecuteNonQuery。

Sample code如下：

	public string MyStoredProcedure(string myFactorId, int myValue)
        {
            //sp name
            string spName = "MyStoredProcedure_Name";

            //input parameters
            IDbParameters parameters = CreateDbParameters();

            parameters.Add("MyFactorId", DbType.String).Value = myFactorId;
            parameters.Add("MyValue", DbType.Int16).Value = myValue;

            //output parameters
            parameters.AddOut("where", DbType.String, 4000);

            //execute
            AdoTemplate.ExecuteNonQuery(CommandType.StoredProcedure, spName, parameters);

            //get output parameter, 注意要加上@
            string result = CovertType.DBToString(parameters["@where"].Value);
            return result;
        }

參數如果是Output，則parameters應使用AddOut。如果要取得Return，則使用AddReturn。
而最後要取得output parameter，需加上@才取的到。

好處是對呼叫者來說，根本就不需要知道這個Dao的method內部是什麼，且可以follow原本Dao Class的相關屬性跟設定。

這種作法的壞處，就是比較多東西得手動設定，且較難看出其抽象意義。另外除非看到內部資訊，否則無法得知這是呼叫某一支stored procedure。

第二種作法，則是使用class對應stored procedure的作法，也是Spring.Net官方Document的作法。

Sample code如下：

	public class MyStoredProcedure : StoredProcedure
    {
        private static string procedureName = "MyStoredProcedure_Name";

        public MyStoredProcedure(IDbProvider dbProvider) : base(dbProvider, procedureName)
        { 
            DeriveParameters(true);           
            Compile();
        }

        public IDictionary GetResults(string myFactorId, int myValue)
        {
            return Query(myFactorId, myValue);
        }
    
    }

這邊有許多奇怪的method，不知道是在幹嘛，我們trace一下繼承鏈就知道了。

原來StoredProcedure這個Spring.Net幫我們定義好的abstract class，已經提供與封裝了許多方法，供我們不用重複開發。透過封裝過的方法，將執行stored procedure的過程抽象化出來，讓呼叫的時候可以更加comfortable。

建構式的參數，相當然而需要提供DBprovider，而其中DeriveParameters(true);裡面的true，指的是includeReturnPatameter，是否將return也包進去。
可以參考：

The 'DeriveParameters' method saves you the trouble of having to declare each parameter explicitly. When using DeriveParameters is it often common to use the Query method that takes a variable length list of arguments. This assumes additional knowledge on the order of the stored procedure arguments. If you do not want to follow this loose shorthand convention, you can call the method QueryByNamesParameters instead passing in a IDictionary of parameter key/value pairs.

If you would like to have the return value of the stored procedure included in the returned dictionary, pass in true as a method parameter to DeriveParameters().

Query的方法就更單純了，可以當作呼叫我們的stored procedure，並把需要的參數傳進去。

值得注意的是，這是non-Generic的作法，回傳的type是IDictionary，會將所有output parameter與return value放進此Dictionary中，需要取用時，只需要用key就可以得到value。

跟上述的第一種作法比較，這種方式已經漂亮的多了，將不必要看到的都封裝起來，且將需得到的結果都放在IDictionary中。

官方Document的Sample提供的Generic更是帥氣，多傳入一個Delegate的class或method，進行Lightweight的OR mapping，可以自行將執行結果以List<T>回傳。
有興趣的人可以參考一下：http://www.springframework.net/docs/1.3.0/reference/html/ado.html

驗證
我們先用測試專案來執行我們所撰寫呼叫stored procedure的方法，(這邊不是Unit Test，而是偏Integrate Test，因為是直接對測試DB做動作)

第一種作法的test:

	[Test]
        public void MyStoredProcedureTest()
        {
            //Arrange
            StoredProcedureDao instance = Core.WebUtility.Repository.DaoForTest("StoredProcedureDao") as StoredProcedureDao;

            string myFactorId = "Y01";
            int myValue = 1;

            //Act
            string result = instance.GetGroupSourceRule(myFactorId, myValue);

            //Assert
            Assert.AreEqual("Name=91", result);

        }

第二種作法的test:

	[TestFixture]
    public class MyStoredProcedureTest
    {
        
        [Test]
        public void MyStoredProcedureTest_Factor_Y01_Value_1()
        {                        
            //Arrange
            DbProvider dbProvider = Core.Utility.DBProvider.GetIASDbProviderForTest("SqlServer-2.0");
            MyStoredProcedure instance = new MyStoredProcedure(dbProvider);

            string groupFactorId = "Y01";
            int groupValue = 1;

            //Act
            IDictionary result = instance.GetResults(groupFactorId, groupValue);

            //Assert
            string output = Convert.ToString(result["@where"]);
            Assert.AreEqual("Name=91", output);

        }

我們來看一下驗證的結果：

看到回傳的資料是空字串，而非我們預期的Name=91，其實原因是stored procedure還沒寫完。
不過沒關係，我們先去手動設定一下@where的值，看看我們的程式是不是OK：

再跑一次看看結果：

這樣偷改正式程式的結果，是一件要不得的事…為什麼我這邊文章仍把這樣的作法貼上來呢？因為我要強調stub/mock object的重要性。

而在DB的stored procedure這邊，我還不知道怎麼進行stored procedure的單元測試，因為裡面實在有可能跟太多東西耦合了，且執行環境是直接與DBMS綁在一起。

只能再把stored procedure當作黑箱來測input/output/return有沒符合預期。

結論
雖說盡可能避免使用stored procedure，不過現實還是現實，因為Spring.Net的官網document實在寫得太簡短了，所以這邊在作個memo，希望對之後有需要的朋友有所幫助。
 

blog 與課程更新內容，請前往新站位置：http://tdd.best/