# [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);
}
```
Sign in with Wallet
Connect another wallet