[MVC]Stored Procedure + Transaction 使用範例 (Entity Framework 6)

資料庫是使用 "北風"，首先，在資料庫建立 Stored Procedure : spInsertProduct, spSelectProduct

Stored Procedure (spInsertProduct)

CREATE PROCEDURE [dbo].[spInsertProduct] 
 @ProductName nvarchar(40),
 @SupplierID int,
 @CategoryID int,
 @QuantityPerUnit nvarchar(20),
 @UnitPrice money,
 @UnitsInStock smallint,
 @UnitsOnOrder smallint,
 @ReorderLevel smallint,
 @Discontinued bit 

AS
BEGIN

 insert into Products (
  ProductName,
  SupplierID,
  CategoryID,
  QuantityPerUnit,
  UnitPrice,
  UnitsInStock,
  UnitsOnOrder,
  ReorderLevel,
  Discontinued
 ) values (
  @ProductName,
  @SupplierID,
  @CategoryID,
  @QuantityPerUnit,
  @UnitPrice,
  @UnitsInStock,
  @UnitsOnOrder,
  @ReorderLevel,
  @Discontinued
 )   


END

Stored Procedure (spSelectProduct)

create PROCEDURE [dbo].[spSelectProduct] 
    @ProductID int
AS
BEGIN

 select * from Products 
 where ProductID=@ProductID
 order by ProductName

END

在 MVC 的控制器中可以如下使用: (以 Transaction 方式執行資料庫的 Stored Procedure)

Stored Procedure + Transcation 範例(Create)

MvcTestDbModel db = new MvcTestDbModel();
public ActionResult Create()
{
 try {
  List sp = new List();
  sp.Add(new SqlParameter("@ProductName", "test 2"));
  sp.Add(new SqlParameter("@SupplierID", 1));
  sp.Add(new SqlParameter("@CategoryID", 1));
  sp.Add(new SqlParameter("@QuantityPerUnit", "Set"));
  sp.Add(new SqlParameter("@UnitPrice", 10.5));
  sp.Add(new SqlParameter("@UnitsInStock", 1));
  sp.Add(new SqlParameter("@UnitsOnOrder", 1));
  sp.Add(new SqlParameter("@ReorderLevel", 1));
  sp.Add(new SqlParameter("@Discontinued", true));
  using(System.Data.Entity.DbContextTransaction ct = db.Database.BeginTransaction()) {
   try {
    db.Database.ExecuteSqlCommand(@"exec spInsertProduct 
       @ProductName, 
       @SupplierID, 
       @CategoryID, 
       @QuantityPerUnit, 
       @UnitPrice, 
       @UnitsInStock, 
       @UnitsOnOrder, 
       @ReorderLevel, 
       @Discontinued", sp.ToArray());
    ct.Commit();
   }
   catch(Exception ex) {
    ct.Rollback();
    Response.Write(ex.Message);
   }
  }
  return RedirectToAction("Index");
 }
 catch(Exception ex) {
  Response.Write(ex.Message);
 }
 return RedirectToAction("Create");
}

取得產品資料的範例

GetProductInfo method

private void GetProductInfo(int ProductID)
{
    try {
        List<SqlParameter> sp = new List<SqlParameter>();
        sp.Add(new SqlParameter("@ProductID", 1));
        try {
            IEnumerable<Products> p = db.Database.SqlQuery<Products>(@"exec spSelectProduct @ProductID", sp.ToArray());
            foreach(Products pp in p) {
                Response.Write("<br>" + pp.ProductID + " " + pp.ProductName);
            }
        }
        catch(Exception ex) {
            Response.Write(ex.Message);
        }
    }
    catch(Exception ex) {
        Response.Write("<br>" + ex.Message);
    }
}

Model 中的配置

Models\MvcTestDbModel.cs

using System.Data.Entity;

namespace JQGrid20180810Example.Models
{
    public partial class MvcTestDbModel : DbContext
    {
        public MvcTestDbModel()
            : base("name=MvcTestDbModelConnection")
        {
        }

        public virtual DbSet Roles { get; set; }
        public virtual DbSet Users { get; set; }
        public virtual DbSet Products { get; set; }
        
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Types().Configure(t => t.MapToStoredProcedures());
        }
    }
}