---
# System prepended metadata

title: '[112]天方科技 ASP.net core 教育訓練 1120427'

---

# [112]天方科技 ASP.net core 教育訓練 1120427

### 建立up_setCmparea，新增修改資料
`Set NoCount On;`如果有設定，成功時回傳`-1`，沒有設定的情況下，回傳影響的**資料筆數**
```sql=
Create or ALTER PROCEDURE [dbo].[up_setCmparea] @cmp_area int, @cmp_name_abr nvarchar(24), @cmp_name_full nvarchar(45), @upd_name nvarchar(18)
AS
    --set nocount on; --回傳Insert update delete 影響筆數
    Declare @upd_dt nvarchar(13)
    Declare @now datetime = getdate()

    set @upd_dt = Format(year(@now) - 1911, '000' ) + Format( @now,'MMddHHmmss', 'en-US' )    --產生目前時間

    UPDATE s90_cmparea set cmp_area = @cmp_area, cmp_name_abr = @cmp_name_abr,
        cmp_name_full = @cmp_name_full, upd_name = @upd_name, upd_dt = @upd_dt
        WHERE cmp_area = @cmp_area

    --Insert to row if the Update statement faild
    IF ( @@ROWCOUNT = 0 ) 
    BEGIN
        INSERT INTO s90_cmparea (cmp_area,cmp_name_abr,cmp_name_full, upd_name, upd_dt )
        VALUES ( @cmp_area, @cmp_name_abr, @cmp_name_full, @upd_name, @upd_dt )

        --select * from s90_cmparea
        Return 100	--100代表是insert
    END

    Return 200	--200代表是update
```

### 建立up_deleteCmparea，刪除資料
成功會回傳`0`
```sql=
Create or ALTER  procedure [dbo].[up_deleteCmparea] @cmp_area int
as
	--set nocount on; --傳回insert, update, delete所影響的筆數

	delete s90_cmparea
	where cmp_area = @cmp_area
```

### 在Demo專案執行反向工程
### `IeduContextProcedures.cs`裡會出現目前建立的Stored Procedure
`returnValue`：在Stored Procedure內設定的回傳值(例如狀態碼)
`cancellationToken`：選擇是否取消非同步機制
```csharp=
public partial interface IeduContextProcedures
{
    Task<int> up_deleteCmpareaAsync(int? cmp_area, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default);
    Task<List<up_getCmpareaResult>> up_getCmpareaAsync(OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default);
    Task<List<up_getStudentResult>> up_getStudentAsync(decimal? std_key, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default);
    Task<int> up_setCmpareaAsync(int? cmp_area, string cmp_name_abr, string cmp_name_full, string upd_name, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default);
}
```

### `eduContextProcedures.cs`由程式產生Stored Procedure相關方法

使用`SqlParameter`建立各項參數，最後`_context.Database`由DB物件進行資料處裡

```csharp=
public virtual async Task<int> up_deleteCmpareaAsync(int? cmp_area, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default)
{
    var parameterreturnValue = new SqlParameter
    {
        ParameterName = "returnValue",
        Direction = System.Data.ParameterDirection.Output,
        SqlDbType = System.Data.SqlDbType.Int,
    };

    var sqlParameters = new []
    {
        new SqlParameter
        {
            ParameterName = "cmp_area",
            Value = cmp_area ?? Convert.DBNull,
            SqlDbType = System.Data.SqlDbType.Int,
        },
        parameterreturnValue,
    };
    //執行原始sql指令，程式產生時將寫入的字串參數化
    var _ = await _context.Database.ExecuteSqlRawAsync("EXEC @returnValue = [dbo].[up_deleteCmparea] @cmp_area", sqlParameters, cancellationToken);

    returnValue?.SetValue(parameterreturnValue.Value);

    return _;
}
```

### 執行SQL指令的方法
`DbSet.FromSql()`：在 EF Core 7.0 中引入的，以 `FormattableString`作為唯一參數，可防止`SQL injection`，因為所有參數數據都包裝在`DbParameter`中

`DbSet.FromSqlRaw()`：不能防止`SQL injection`， 因此在定義SQL命令時需要格外注意

`Database.ExecuteSql()`：如同`FromSql`，可防止`SQL injection`，透過`DbContext.Database`物件使用

`Database.ExecuteSqlRaw`()：如同`FromSqlRaw`，不能防止`SQL injection`，透過`DbContext.Database`物件使用

參考：
[**How to Execute Stored Procedures With EF Core 7**](https://code-maze.com/efcore-execute-stored-procedures/)

### 使用Controller，測試Stored Procedure
```csharp=
[HttpPost("sp/set")]
public async Task<ActionResult<int>> up_setCmparea(s90_cmparea s90_cmparea)
{

    var result = await _context.Procedures.up_setCmpareaAsync(s90_cmparea.cmp_area, s90_cmparea.cmp_name_abr, s90_cmparea.cmp_name_full, s90_cmparea.upd_name);
    var data = await _context.s90_cmparea.FindAsync(s90_cmparea.cmp_area);


    return CreatedAtAction("Gets90_cmparea", new { id = s90_cmparea.cmp_area }, data);  //並不會再次執行Action，需要最新資料，要透過手動再次查詢
}
```

### up_deleteCmparea 回傳0代表成功
```csharp=
 [HttpDelete("sp/del/{id}")]
public async Task<ActionResult<int>> up_deleteCmparea(int id)
{

    var result = await _context.Procedures.up_deleteCmpareaAsync(id);

    return result == 0 ? NotFound() : NoContent();

}
```

## 在Edu專案不經過EF Tool使用Stored Procedure
### 如果直接將Demo專案的`up_deleteCmparea()`移植到Edu專案，會發現有Error
```csharp=
 [HttpDelete("sp/del/{id}")]
public async Task<ActionResult<int>> up_deleteCmparea(int id)
{

    //error
    var result = await _context.Procedures.up_deleteCmpareaAsync(id);

    return result == 0 ? NotFound() : NoContent();

}
```

### 回到Demo專案的`eduContextProcedures.cs`，將`up_deleteCmpareaAsync()`內容移植到Edu專案的`up_deleteCmparea()`並修改

`ParameterName`和`SqlDbType`可以直接放入`SqlParameter`當參數

使用`Database.ExecuteSqlRaw()`執行SQL指令，`cancellationToken`沒有用到可以去掉

`returnValue`在當初設計Stored Procedure時，有定return value(例如狀態碼)的話可以使用到，沒有的話可以將`parameterreturnValue`相關程式碼刪除，不去抓取
```csharp=
[HttpDelete("sp/del/{id}")]
public async Task<ActionResult<int>> up_deleteCmparea(int id)
{
    
    var parameterreturnValue = new SqlParameter("returnValue", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output,
    };

    var sqlParameters = new[]
    {
        new SqlParameter("cmp_area", SqlDbType.Int)
        {
            Value = id
        },
        parameterreturnValue,
    };
    var result = await _context.Database.ExecuteSqlRawAsync("EXEC @returnValue = [dbo].[up_deleteCmparea] @cmp_area", sqlParameters);

    //int returnValue = (int)parameterreturnValue.Value;            

    return result == 0 ? NotFound() : NoContent();

}
```

### 建立第二支`up_deleteCmparea()`
由於沒有`returnValue`，可以將程式碼簡化，將`id`參數內插至SQL語法，並使用`Database.ExecuteSql()`執行SQL指令，可防止`SQL Injection`
```csharp=
[HttpDelete("sp/del2/{id}")]
public async Task<ActionResult<int>> up_deleteCmparea2(int id)
{
    //沒有使用ExecuteSqlRawAsync()可以直接使用內插語法，不會有sql injection風險
    var result = await _context.Database.ExecuteSqlAsync($"EXEC [dbo].[up_deleteCmparea] @cmp_area={id}");      

    return result == 0 ? NotFound() : NoContent();
}
```

### 以相同方式移植`up_setCmparea()`
將參數放入`SqlParameter`只留`Value`
`??`運算子是用來檢查是否為`null`，否的話傳回`??`左邊的值，是的話傳回右邊的值
```csharp=
[HttpPost("sp/set")]
public async Task<ActionResult<int>> up_setCmparea(s90_cmparea s90_cmparea)
{
    var parameterreturnValue = new SqlParameter("returnValue", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output,
    };

    var sqlParameters = new[]
    {
        new SqlParameter("cmp_area", SqlDbType.Int)
        {
            Value = s90_cmparea.cmp_area
        },
        new SqlParameter("cmp_name_abr", SqlDbType.NVarChar, 48)
        {
            Value = s90_cmparea.cmp_name_abr ?? Convert.DBNull
        },
        new SqlParameter("cmp_name_full", SqlDbType.NVarChar, 90)
        {
            Value = s90_cmparea.cmp_name_full ?? Convert.DBNull
        },
        new SqlParameter("upd_name", SqlDbType.NVarChar, 36)
        {
            Value = s90_cmparea.upd_name ?? Convert.DBNull
        },
        parameterreturnValue,
    };
    var _ = await _context.Database.ExecuteSqlRawAsync("EXEC @returnValue = [dbo].[up_setCmparea] @cmp_area, @cmp_name_abr, @cmp_name_full, @upd_name", sqlParameters);

    int returnValue = (int)parameterreturnValue.Value;

    var data = await _context.s90_cmparea.FindAsync(s90_cmparea.cmp_area);


    return CreatedAtAction("Gets90_cmparea", new { id = s90_cmparea.cmp_area }, data);
}
```

參考：
[**?? 和？？= 運算子 - null 聯合運算子**](https://learn.microsoft.com/zh-tw/dotnet/csharp/language-reference/operators/null-coalescing-operator)

### 建立第二支`up_setCmparea()`
不需要輸出參數的話，可以使用`Database.ExecuteSql()`內插語法
```csharp=
[HttpPost("sp/set2")]
public async Task<ActionResult<int>> up_setCmparea2(s90_cmparea s90_cmparea)
{
    var _ = await _context.Database
        .ExecuteSqlAsync($"EXEC [dbo].[up_setCmparea] 
                         {s90_cmparea.cmp_area},
                         {s90_cmparea.cmp_name_abr},
                         {s90_cmparea.cmp_name_full}, 
                         {s90_cmparea.upd_name}");

    var data = await _context.s90_cmparea.FindAsync(s90_cmparea.cmp_area);

    return CreatedAtAction("Gets90_cmparea", new { id = s90_cmparea.cmp_area }, data);
}
```