喜帳面の日記

50歳越えおやじのASP.NET　MVC　への挑戦日記です。

Visual Studio Express 2012 for Web でいってみる 14.ストアドプロシジャを使ってみる(6/6)

Visual Studio Express 2012 for Web MVC４ Entity Framework ストアドプロシジャ 例外処理

前回からの続きです。

№6．ストアドプロシジャと例外処理。

さて、今回は、ストアドプロシジャと例外処理について考えてみます。

サンプルのストアドプロシジャ。前回使用していたストアドプロシジャを変更して、0除算が発生するようにします。

ALTER PROCEDURE [dbo].[SP_GetCarriers1]
     @CarrierCDMIN  int        --コード最少
	,@CarrierCDMAX  int        --コード最大
	,@po_DataCount  int OUTPUT --レコード件数
    AS
	-- 指定範囲のレコードを返します。
	
	SET    @po_DataCount = 100 / 0 --0除算が発生

	SELECT 運送コード,運送会社,電話番号  
	FROM   dbo.運送会社
	WHERE  運送コード BETWEEN @CarrierCDMIN AND @CarrierCDMAX
	ORDER  BY  運送コード
	SET	@po_DataCount = @@rowcount --レコード件数を返します。

GO

このストアドプロシジャを呼び出すと現段階では、

f:id:SannomiyaNotes:20121203200213p:plain

こんなページが表示されます。こんなページをユーザーに見せるのはちょと恥ずかしいし、よく見ないと、どこで例外が発生してるのか把握しづらいですよね。

そこで、例外をtry/catchで制御してみます。

では、コントローラーの変更です。

        public ActionResult Index()
        {
            try
            {

                //範囲指定情報用変数
                int? 運送コードMIN = null;
                int? 運送コードMAX = null; // 
                //ストアドプロシジャのOUTPUTパラメータ用変数。
                int dataCount = 0;

                //ストアドプロシジャを呼び出してmodelを作成
                IEnumerable<運送会社ViewModel> model = this.repository.GetCarriersList
                    (運送コードMIN, 運送コードMAX, out dataCount);
                //ストアドプロシジャのOUTPUTパラメータには取得レコード件数がセットされている。
                //ViewBagに格納してビューで表示します。
                ViewBag.DataCount = dataCount;
                return View(model);
            }
            catch (Exception ex)                    //例外処理
            {
                string dispmessage;
                if (ex.InnerException == null)
                {   //内部例外が存在しない場合
                    dispmessage = ex.Message;       
                }
                else
                {   //内部例外が存在する場合
                    dispmessage = ex.Message + "内部例外：" + ex.InnerException.Message;
                }
                // フォームでエラーメッセージを表示。
                ModelState.AddModelError("", dispmessage); 
                IList<運送会社ViewModel> model = new List<運送会社ViewModel>();
                return View(model);                        
            }
        }

あと、ビュー[Index.cshtml]に@Html.ValidationSummary(true)を追加します

@model IEnumerable<Mvc4ApplicationD.Models.運送会社ViewModel>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<h3>

     @Html.ValidationSummary(true) 
     データ件数　@ViewBag.DataCount
</h3>

<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.運送会社1)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.電話番号)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.運送会社1)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.電話番号)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.運送コード }) |
            @Html.ActionLink("Details", "Details", new { id=item.運送コード }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.運送コード })
        </td>
    </tr>
}
</table>

実行してみると

f:id:SannomiyaNotes:20121203204315p:plain

例外処理に制御が移り、エラー情報を取得できていることがわかります。

[Index]のページでエラー内容を表示するか否かはおいといて、ハンドルはできてますね。

尚、上記のサンプルでは、catch (Exception ex)　としてエラー全般をcatchしてますが、細かくわけたい場合、catch (EntityException ex)でcatch可能です。

さて、もう一つの事例を見てください。

今度のストアドプロシジャは、

ALTER PROCEDURE [dbo].[SP_GetCarriers1]
ALTER PROCEDURE [dbo].[SP_GetCarriers1]
     @CarrierCDMIN  int        --コード最少
	,@CarrierCDMAX  int        --コード最大
	,@po_DataCount  int OUTPUT --レコード件数
	AS
	-- 指定範囲のレコードを返します。
	SELECT 運送コード,運送会社,電話番号  
	FROM   dbo.運送会社
	WHERE  運送コード BETWEEN @CarrierCDMIN AND @CarrierCDMAX
	ORDER  BY  運送コード
	SET	@po_DataCount = @@rowcount --レコード件数をセットします。

	SET @po_DataCount = @po_DataCount / 0 --0除算が発生

０除算の発生位置を、SELECT文の後に移動しています。

この状態で実行すると、

f:id:SannomiyaNotes:20121203212003p:plain

ストアドプロシジャ内で発生している0除算はcatchされず、正常に処理がされたかのように動作してしまいます。今回のサンプルでは重要な意味を持たないかもしれませんが、もし演算の結果を使って別のテーブルに更新するようなケースだと問題になりそうです。

 こんな事態に対応する方法として、ストアドプロシジャ内でもtry/catchを使う方法を試してみます。

こんな風にストアドプロシジャを変更してみました。

ALTER PROCEDURE [dbo].[SP_GetCarriers1]
     @CarrierCDMIN  int        --コード最少
	,@CarrierCDMAX  int        --コード最大
	,@po_DataCount  int OUTPUT --レコード件数
    ,@po_ErrorMessage   nvarchar(max)  OUTPUT --ストアドプロシジャのERROR_MESSAGE
	AS
    BEGIN TRY
		-- アウトプットパラメータの初期化
		SET @po_DataCount = 0
		SET @po_ErrorMessage =''

		-- 指定範囲のレコードを返します。
		SELECT 運送コード,運送会社,電話番号  
		FROM   dbo.運送会社
		WHERE  運送コード BETWEEN @CarrierCDMIN AND @CarrierCDMAX
		ORDER  BY  運送コード
		SET	@po_DataCount = @@rowcount --レコード件数を返します。

		SET @po_DataCount = @po_DataCount / 0 --0除算が発生

    END TRY

    BEGIN CATCH
	   --エラーメッセージを編集
	 SET @po_ErrorMessage = 
	   N'ErrorNumber = '+ CONVERT(nvarchar(10),ERROR_NUMBER())  + CHAR(13)
	 + N'ErrorMessage = '+ CONVERT(nvarchar(4000),ERROR_MESSAGE())  + CHAR(13)
	 + N'ErrorSeverity = '+ CONVERT(nvarchar(10),ERROR_SEVERITY())  + CHAR(13)
	 + N'ErrorState = '+ CONVERT(nvarchar(10),ERROR_STATE())  + CHAR(13)
	 + N'ErrorProcedure = '+ CONVERT(nvarchar(128),ERROR_PROCEDURE())  + CHAR(13)
	 + N'ErrorLine = '+ CONVERT(nvarchar(10),ERROR_LINE())  + CHAR(13)

    END CATCH

アウトプットパラメータを１つ(@po_ErrorMessage)追加。ストアドプロシジャで発生した例外をCATCHしエラーの各種情報をセットするようにしました。

このストアドプロシジャをEDM (Entity Data Model)で更新。

[CarrierRepository.cs]は以下の通りです。（抜粋）

    // リポジトリ インターフェース
    public interface ICarrierRepository
    {
        //List取得
        IEnumerable GetCarriersList
            (int? CarrierCDMIN, int? CarrierCDMAX, out int DataCount, out string ErrorMessage);

public IEnumerable GetCarriersList
             (int? CarrierCDMIN, int? CarrierCDMAX, out int DataCount, out string ErrorMessage)
{
    //input parameter 運送コードの範囲指定用
    CarrierCDMIN = CarrierCDMIN != null ? CarrierCDMIN : 0;  //nullだったら   0をセット
    CarrierCDMAX = CarrierCDMAX != null ? CarrierCDMAX : 999;//nullだったら 999をセット

    //output parameter の指定
    //ここのパラメータ名はストアドプロシジャと一致させること。日本語はつかえないみたい。
    ObjectParameter dataCount = new ObjectParameter("po_DataCount", typeof(int));
    ObjectParameter errorMessage = new ObjectParameter("po_ErrorMessage", typeof(string)); 
    //IListのインスタンスを生成
    IList model = new List();

    //ストアドプロシジャ SP_GetCarriers1を呼び出し受け取ったデータより model にセットして行く
    //NorthwindJ.Context.cs の メソッドSP_GetCarriers1を参照
    foreach (SP_GetCarriers1_Result 運送会社 in db.SP_GetCarriers1(CarrierCDMIN, CarrierCDMAX, dataCount, errorMessage))
    {
        //明示的に変換しmodelに追加します。
        var item = new 運送会社ViewModel();
        item.運送コード = 運送会社.運送コード;
        item.運送会社1 = 運送会社.運送会社;
        item.電話番号 = 運送会社.電話番号;
        model.Add(item);
    }
    //データセットを返すストアドプロシジャのOUTPUTパラメータの値は
    //データセットの読み取り終了後でないと取得できない。
    DataCount = (int)dataCount.Value;
    ErrorMessage = (string)errorMessage.Value;
    //出来上がったmodelを返しておしまい。
    return model;
}

 

[C[運送会社Controller]（抜粋）

public ActionResult Index()
{
    try
    {
        //範囲指定情報用変数
        int? 運送コードMIN = null;
        int? 運送コードMAX = null; // 
        //ストアドプロシジャのOUTPUTパラメータ用変数。
        int dataCount = 0;
        string errorMessage = "";

        //ストアドプロシジャを呼び出してmodelを作成
        IEnumerable model = this.repository.GetCarriersList
            (運送コードMIN, 運送コードMAX, out dataCount, out errorMessage);
        //ストアドプロシジャ内でエラーが発生？ 
        if (errorMessage != "")  
          {    ModelState.AddModelError("", errorMessage);   }
        
        //ストアドプロシジャのOUTPUTパラメータには取得レコード件数がセットされている。
        //ViewBagに格納してビューで表示します。
        ViewBag.DataCount = dataCount;
        return View(model);
    }
    catch (Exception ex)                    //例外処理
    {
        string dispmessage;
        if (ex.InnerException == null)
        {   //内部例外が存在しない場合
            dispmessage = ex.Message;       
        }
        else
        {   //内部例外が存在する場合
            dispmessage = ex.Message + "内部例外：" + ex.InnerException.Message;
        }
        // フォームでエラーメッセージを表示。
        ModelState.AddModelError("", dispmessage); 
        IList model = new List();
        return View(model);                        
    }
}

 

この変更を行って実行すると

f:id:SannomiyaNotes:20121204143410p:plain

ストアドプロシジャ内でCATCHしたエラー情報を取得し表示することができました。

しかし、０除算の発生する場所をselectの前に移動すると

ALTER PROCEDURE [dbo].[SP_GetCarriers1]
     @CarrierCDMIN  int        --コード最少
    ,@CarrierCDMAX  int        --コード最大
    ,@po_DataCount  int OUTPUT --レコード件数
    ,@po_ErrorMessage   nvarchar(max)  OUTPUT --ストアドプロシジャのERROR_MESSAGE
 AS
    BEGIN TRY
		-- アウトプットパラメータの初期化
		SET @po_DataCount = 0
		SET @po_ErrorMessage =''

		SET @po_DataCount = @po_DataCount / 0 --0除算が発生

		-- 指定範囲のレコードを返します。
		SELECT 運送コード,運送会社,電話番号  
		FROM   dbo.運送会社
		WHERE  運送コード BETWEEN @CarrierCDMIN AND @CarrierCDMAX
		ORDER  BY  運送コード
		SET	@po_DataCount = @@rowcount --レコード件数を返します。


    END TRY

    BEGIN CATCH
	    --エラーメッセージを編集
		SET @po_ErrorMessage = 
		  N'ErrorNumber = '+ CONVERT(nvarchar(10),ERROR_NUMBER())  + CHAR(13)
		+ N'ErrorMessage = '+ CONVERT(nvarchar(4000),ERROR_MESSAGE())  + CHAR(13)
		+ N'ErrorSeverity = '+ CONVERT(nvarchar(10),ERROR_SEVERITY())  + CHAR(13)
		+ N'ErrorState = '+ CONVERT(nvarchar(10),ERROR_STATE())  + CHAR(13)
		+ N'ErrorProcedure = '+ CONVERT(nvarchar(128),ERROR_PROCEDURE())  + CHAR(13)
		+ N'ErrorLine = '+ CONVERT(nvarchar(10),ERROR_LINE())  + CHAR(13)

    END CATCH

こんなエラーが発生してしまいます。

f:id:SannomiyaNotes:20121204144246p:plain

どうも、C#側に戻ってきた時点で、List型処理でレコードセットが戻ってくるはずなのに、何もかえってこない為に発生しているようです。

もうちょっとストアドプロシジャ側のエラー処理に工夫が必要のようです。

で、こんな風に、エラー発生時に空のレコードを返すようにしました。

ALTER PROCEDURE [dbo].[SP_GetCarriers1]
     @CarrierCDMIN  int        --コード最少
	,@CarrierCDMAX  int        --コード最大
	,@po_DataCount  int OUTPUT --レコード件数
    ,@po_ErrorMessage   nvarchar(max)  OUTPUT --ストアドプロシジャのERROR_MESSAGE
	AS
    BEGIN TRY
		-- アウトプットパラメータの初期化
		SET @po_DataCount = 0
		SET @po_ErrorMessage =''

		SET @po_DataCount = @po_DataCount / 0 --0除算が発生

		-- 指定範囲のレコードを返します。
		SELECT 運送コード,運送会社,電話番号  
		FROM   dbo.運送会社
		WHERE  運送コード BETWEEN @CarrierCDMIN AND @CarrierCDMAX
		ORDER  BY  運送コード
		SET	@po_DataCount = @@rowcount --レコード件数を返します。


    END TRY

    BEGIN CATCH
	    --エラーメッセージを編集
		SET @po_ErrorMessage = 
		  N'ErrorNumber = '+ CONVERT(nvarchar(10),ERROR_NUMBER())  + CHAR(13)
		+ N'ErrorMessage = '+ CONVERT(nvarchar(4000),ERROR_MESSAGE())  + CHAR(13)
		+ N'ErrorSeverity = '+ CONVERT(nvarchar(10),ERROR_SEVERITY())  + CHAR(13)
		+ N'ErrorState = '+ CONVERT(nvarchar(10),ERROR_STATE())  + CHAR(13)
		+ N'ErrorProcedure = '+ CONVERT(nvarchar(128),ERROR_PROCEDURE())  + CHAR(13)
		+ N'ErrorLine = '+ CONVERT(nvarchar(10),ERROR_LINE())  + CHAR(13)

		SELECT 運送コード,運送会社,電話番号  
		FROM dbo.運送会社
		WHERE 運送コード =NULL

    END CATCH

これでストアドプロシジャのエラーメッセージを表示できるようになりました。

実際の運用時、エラーログテーブルやファイルにストアドプロシジャからのエラー情報を格納しておけばエラー原因の調査にも役立ちそうです。

ちなみに、タイムアウトやデッドロックはストアドプロシジャ側ではCATCHできず、c#側でcatchすることになります。

デッドロックが発生して負けた方に表示されるメッセージは

『コマンド定義を実行中にエラーが発生しました。詳細については、内部例外を参照してください。

内部例外：コミットできないトランザクションはバッチの終了時に検出されます。このトランザクションはロールバックされます。』

です。エンドユーザーにはきっと何のことだかわからないですね。

やっぱり、エラーメッセージはログに格納して、『ごめんなさい、エラーです。サポートに連絡してください。』とだけ表示して連絡もらった方がいいかも。

 

以上で『ストアドプロシジャを使ってみる』シリーズは終了させてもらいます。