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

前回からの続きです。

№5．Create部分のストアドプロシジャ化。

さて、今回は、Create部分についてストアドプロシジャ化します。

Createで使用するストアドプロシジャは以下のスクリプトを参照ください。

CREATE PROCEDURE [dbo].[SP_CreateCarrier1]
    @p_CompanyName   [nvarchar](40)  --パラメータ名は日本語ダメ
   ,@p_Tel           [nvarchar](24)  -- 
   ,@po_DataCount    int OUTPUT
 AS
--OUTPUT PARA を初期化
	SET @po_DataCount    =0

	INSERT INTO 運送会社(運送会社,電話番号)
	SELECT @p_CompanyName,@p_Tel
    
	SET    @po_DataCount = @@rowcount

上記ストアドプロシジャがデータベースに登録できたら、次は EDM (Entity Data Model)へ登録します。手順はList用を登録したときと同じ要領です。

1.NorthwindJ.edmxを選択。NorthwindJ.edmxのダイアグラムを表示し、ダイアグラムの白地部分で右クリック。リストより[データベースよりモデルを更新]を選択

2.更新ウイザードが表示されたら、[追加]タブで[ストアドプロシジャと関数]のツリーを開き、[SP_CreateCarrier1]をチェックして[完了]

3.モデルブラウザーで念のため[関数インポート]ツリーに[SP_CreateCarrier1]が追加されていることを確認。ちなみに、[編集]で開くと「次の要素のコレクションを返します」は[無し]が選択されています。

ここで[NorthwindJ.edmx]を保存します。

4.ソリューションエクスプローラで[NorthwindJ.edmx]ツリー下の[NorthwindJ.Context.tt]を選択、右クリックしリストより[カスタムツールの実行]を選択。

[NorthwindJ.Context.tt]の配下[NorthwindJ.Context.cs]に[SP_CreateCarrier1]のメソッドが追加されていることを確認。

これで、Model関連の作業は完了です。

次は、[CarrierRepository.cs]を変更しましょう。

追加するCreate用のメソッドはこんな感じです。

 //Create用 ストアドプロシジャ呼び出し
 public bool CreateCarrierSP(運送会社ViewModel model, out int DataCount)
 {
     DataCount = 0;
     ObjectParameter dataCount = new ObjectParameter("po_DataCount", typeof(int));
     int result = db.SP_CreateCarrier1
         (model.運送会社1,
          model.電話番号,
          dataCount);
     DataCount = (int)dataCount.Value;
     return true;
 }

あと、リポジトリインターフェースに

 //Create用 
 bool CreateCarrierSP(運送会社ViewModel model, out int DataCount);

を追加しました。

次はコントローラーを変更します。

追加するCreate用のメソッドはこんな感じです。

 // POST: /運送会社/Create
 [HttpPost]
 public ActionResult Create(運送会社ViewModel model)
 {
     if (ModelState.IsValid)
     {
         //ストアドプロシジャのOUTPUTパラメータ用変数。
         int dataCount = 0;
         //ストアドプロシジャを呼び出してmodelを作成
         if (this.repository.CreateCarrierSP(model, out dataCount) == true)
          {
             return RedirectToAction("Index");
         }           
     }
     return View(model);
}

これでCreate処理の完了となります。動かしてみてください。

上記以外の「Edit」「Delete」処理も上記と同様の方法で対応が可能かと思います。

今回は、「MVC４でストアドプロシジャを使ってみる」って課題でした。なんとかストアドプロシジャを呼び出す方法がわかってきたように思います。

尚、上記のサンプルではまだまだ業務システムで使えるレベルにはなってないなあって思ってます。今後の課題としては

１．例外発生時の処理が実装されていない。

　　コマンドタイムアウトの発生や、例えば０除算などのバグ等。
２．運送会社名の重複チェックがされていない。
３．同時実行制御が実装されていない。

　　timestamp型を利用した楽観的並行性制御をやっときたい。
４．トランザクション処理が実装されていない。

　　今回のサンプルではなくても良さそうですが、下のサンプルではやってます。

などなどまだ先は長いようですね。

せっかくですのでこの段階でのスクリプトを掲示しておきます。

[ストアドプロシジャ]

-- List -----------------------------------------------------------------------
CREATE 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 --レコード件数を返します。
GO
-- Create ----------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SP_CreateCarrier1]
	@p_CompanyName   [nvarchar](40)  --パラメータ名は日本語ダメ
   ,@p_Tel           [nvarchar](24)  -- 
   ,@po_DataCount    int OUTPUT
 AS
--OUTPUT PARA を初期化
	SET @po_DataCount    =0

	INSERT INTO 運送会社(運送会社,電話番号)
	SELECT @p_CompanyName,@p_Tel
    
	SET    @po_DataCount = @@rowcount
GO
-- Update  ---------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SP_UpdateCarrier1]
    @p_ID            int
   ,@p_CompanyName   nvarchar(40)  --パラメータ名は日本語ダメ
   ,@p_Tel           nvarchar(24)  -- 
   ,@po_DataCount    int OUTPUT
 AS
--OUTPUT PARA を初期化
	SET @po_DataCount    =0

	UPDATE [運送会社]
	SET    [運送会社]    = @p_CompanyName
	      ,[電話番号]    = @p_Tel
    WHERE  [運送コード]  = @p_ID
	SET    @po_DataCount = @@rowcount
GO

-- Delete ----------------------------------------------------------------------
CREATE PROCEDURE [dbo].[SP_DeleteCarrier1]
	@p_ID            int       --パラメータ名は日本語ダメ
   ,@po_DataCount    int OUTPUT
 AS
--OUTPUT PARA を初期化
	SET    @po_DataCount    =0

	DELETE [運送会社]
	WHERE  [運送コード] = @p_ID
   
	SET    @po_DataCount = @@rowcount
GO

[CarrierRepository.cs]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data.Objects; //ストアドプロシジャのパラメータで必要
using System.Transactions; //トランザクション制御


namespace Mvc4ApplicationD.Models.Repositories
{
    // リポジトリ インターフェース
    public interface ICarrierRepository
    {
        //List取得
        IEnumerable GetCarriersList(int? CarrierCDMIN, int? CarrierCDMAX, out int DataCount);
        //単一レコード取得
        運送会社ViewModel GetCarrier(int? id, out int DataCount);
        //Create用 ストアドプロシジャ呼び出し
        bool CreateCarrier(運送会社ViewModel model, out int DataCount);
        //Update用 ストアドプロシジャ呼び出し
        bool UpdateCarrier(運送会社ViewModel model, out int DataCount);
        //Delete用 ストアドプロシジャ呼び出し
        bool DeleteCarrier(int? id, out int DataCount);

    } // end of interace

    public class CarrierRepository : ICarrierRepository
    {
        //Northwind データベースコンテキストを用意する 
        private NorthwindJEntities db = new NorthwindJEntities();

        // 表示用運送会社一覧を作成
        public IEnumerable GetCarriersList(int? CarrierCDMIN, int? CarrierCDMAX, out int DataCount)
        {
            //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));

            //IListのインスタンスを生成
            IList model = new List();

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

            //出来上がったmodelを返しておしまい。
            return model;
        }
        //詳細表示用 
        public 運送会社ViewModel GetCarrier(int? id, out int DataCount)
        {
            DataCount = 0;
            var item = new 運送会社ViewModel();
            if (id == null) { return null; }
            ObjectParameter dataCount = new ObjectParameter("po_DataCount", typeof(int));
            IList model = new List();
            foreach (SP_GetCarriers1_Result 運送会社 in db.SP_GetCarriers1(id, id, dataCount))
             {
                item.運送コード = 運送会社.運送コード;
                item.運送会社1 = 運送会社.運送会社;
                item.電話番号 = 運送会社.電話番号;
                break;
            }
            DataCount = (int)dataCount.Value;
            return item;
        }
        //Create用 ストアドプロシジャ呼び出し
        public bool CreateCarrier(運送会社ViewModel model, out int DataCount)
        {
            // --- 運送会社の追加～その他の更新処理までをトランザクション化 ---
            using (var scope = new TransactionScope())
            {
                DataCount = 0;
                // SP_CreateCarrier1でレコードを追加
                ObjectParameter dataCount = new ObjectParameter("po_DataCount", typeof(int));
                int result = db.SP_CreateCarrier1
                    (model.運送会社1,
                     model.電話番号,
                     dataCount);
                DataCount = (int)dataCount.Value;
                //その他の更新処理があったらここに追加 
                //
                // トランザクションをコミット
                scope.Complete();
            }
            return true;

        }
        //Update用 ストアドプロシジャ呼び出し
        public bool UpdateCarrier(運送会社ViewModel model, out int DataCount)
        {
            // --- 運送会社の更新～その他の更新処理までをトランザクション化 ---
            using (var scope = new TransactionScope())
            {
                DataCount = 0;
                ObjectParameter dataCount = new ObjectParameter("po_DataCount", typeof(int));
                int result = db.SP_UpdateCarrier1
                    (model.運送コード,
                     model.運送会社1,
                     model.電話番号,
                     dataCount);
                DataCount = (int)dataCount.Value;
                //その他の更新処理があったらここに追加 
                //
                // トランザクションをコミット
                scope.Complete();
            }
            return true;
        }
        //Delete用 ストアドプロシジャ呼び出し
        public bool DeleteCarrier(int? id, out int DataCount)
        {
            // --- 運送会社の削除～その他の更新処理までをトランザクション化 ---
            using (var scope = new TransactionScope())
            {
                DataCount = 0;
                if (id == null) { return false; }
                ObjectParameter dataCount = new ObjectParameter("po_DataCount", typeof(int));
                int result = db.SP_DeleteCarrier1(id, dataCount);
                DataCount = (int)dataCount.Value;
                //その他の更新処理があったらここに追加 
                //
                // トランザクションをコミット
                scope.Complete();
            }
           return true;
        }
    }
}

[運送会社Controller.cs]

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Mvc4ApplicationD.Models;

using Mvc4ApplicationD.Models.Repositories;

namespace Mvc4ApplicationD.Controllers
{
    public class 運送会社Controller : Controller
    {
        //データアクセスリポジトリである[Repository]を保持するフィールドを用意
        private ICarrierRepository repository;
        //コンストラクタ　
        public 運送会社Controller() : this(null) { }   //コンストラクタまずは引数無し

        //コンストラクタでCarrierRepositoryを生成
        public 運送会社Controller(ICarrierRepository repository)
        {
            this.repository = (repository ?? new CarrierRepository());
        }
        //
        // GET: /運送会社/

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

            //ストアドプロシジャを呼び出してmodelを作成
            IEnumerable model = this.repository.GetCarriersList(運送コードMIN, 運送コードMAX, out dataCount);
            //ストアドプロシジャのOUTPUTパラメータには取得レコード件数がセットされている。
            //ViewBagに格納してビューで表示します。
            ViewBag.DataCount = dataCount;
            return View(model);
        }

        //
        // GET: /運送会社/Details/5
        public ActionResult Details(int id = 0)
        {
            //ストアドプロシジャのOUTPUTパラメータ用変数。
            int dataCount = 0;
            //ストアドプロシジャを呼び出してmodelを作成
            運送会社ViewModel model = this.repository.GetCarrier(id, out dataCount);
            if (dataCount == 0)
            {  return HttpNotFound(); }
            return View(model);
        }

        //
        // GET: /運送会社/Create
        public ActionResult Create()
        {
            return View();
        }
        //
        // POST: /運送会社/Create
        [HttpPost]
        public ActionResult Create(運送会社ViewModel model)
        {
            if (ModelState.IsValid)
            {
                //ストアドプロシジャのOUTPUTパラメータ用変数。
                int dataCount = 0;
                //ストアドプロシジャを呼び出してmodelを作成
                if (this.repository.CreateCarrier(model, out dataCount) == true)
                {
                    return RedirectToAction("Index");
                }           
            }
            return View(model);
        }
        //
        // GET: /運送会社/Edit/5
        public ActionResult Edit(int id = 0)
        {
            //ストアドプロシジャのOUTPUTパラメータ用変数。
            int dataCount = 0;
            //ストアドプロシジャを呼び出してmodelを作成
            運送会社ViewModel model = this.repository.GetCarrier(id, out dataCount);
            if (dataCount == 0)
            { return HttpNotFound(); }
            return View(model);
        }
        //
        // POST: /運送会社/Edit/5
        [HttpPost]
        public ActionResult Edit(運送会社ViewModel model)
        {
            if (ModelState.IsValid)
            {
                //ストアドプロシジャのOUTPUTパラメータ用変数。
                int dataCount = 0;
                //ストアドプロシジャを呼び出してmodelを作成
                if (this.repository.UpdateCarrier(model, out dataCount) == true)
                {
                    return RedirectToAction("Index");
                }
            }
            return View(model);
        }

        //
        // GET: /運送会社/Delete/5
        public ActionResult Delete(int id = 0)
        {
            //ストアドプロシジャのOUTPUTパラメータ用変数。
            int dataCount = 0;
            //ストアドプロシジャを呼び出してmodelを作成
            運送会社ViewModel model = this.repository.GetCarrier(id, out dataCount);
            if (dataCount == 0)
            { return HttpNotFound(); }
            return View(model);
        }
        //
        // POST: /運送会社/Delete/5

        [HttpPost, ActionName("Delete")]
        public ActionResult DeleteConfirmed(int id)
        {
            //ストアドプロシジャのOUTPUTパラメータ用変数。
            int dataCount = 0;
            //ストアドプロシジャを呼び出してレコードを削除
            if (this.repository.DeleteCarrier(id, out dataCount) == true)
            {
                return RedirectToAction("Index");
            }
            return View();
        }

        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
        }
    }
}

喜帳面の日記

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

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