DataView view = (DataView)SqlDataSource1.Select(new DataSourceSelectArguments());
DataTable dt = view.ToTable();
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
DataRow dr = dt.Rows[i];
if (...) dr.Delete();
}
dt.AcceptChanges();
2021年12月22日 星期三
SQL Server System-Versioned Temporal Tables & cdc
於 table 增加兩個欄位來啟用內建的table row log 機制 (僅限 2016 以上版本)
ALTER TABLE dbo.AWBuildVersion ADD
[TimeStart] DATETIME2(0) GENERATED ALWAYS AS ROW START NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeStart DEFAULT ('19000101'),
[TimeEnd] DATETIME2(0) GENERATED ALWAYS AS ROW END NOT NULL CONSTRAINT DFT_AWBuildVersion_TimeEnd DEFAULT ('99991231 23:59:59'),
PERIOD FOR SYSTEM_TIME ([TimeStart], [TimeEnd]);
ALTER TABLE dbo.AWBuildVersion
DROP CONSTRAINT DFT_AWBuildVersion_TimeStart, DFT_AWBuildVersion_TimeEnd;
ALTER TABLE dbo.AWBuildVersion SET ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.AWBuildVersion_History ) );
第一次修改資料後就會自動產生history table，裡面會有修改前的資料，若有新增欄位等行為也會自動同步
※後續若要調整table scheme 只能透過指令，已經不能再用設計功能處理
※若要知道每個版本是誰修改，要另外增加修改者的欄位，每次更新時記錄更新人員
若是 2016 以前版本，可以使用 cdc (異動資料擷取)
啟用資料庫cdc
sys.sp_cdc_enable_db
啟用資料表cdc
EXECUTE sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'table1',
@role_name = NULL;
之後異動會於系統資料表出現 dbo_table1_CT，每次異動產生兩筆row
__$operation=1 表示刪除
__$operation=2 表示新增
__$operation=3 表示異動前
__$operation=4 表示異動後
若有更改資料表結構，例如增加欄位，必須重新產生log table，否則不會記錄到新欄位的變化，使用以下指令 disable cdc (會自動 drop log table)
EXEC sys.sp_cdc_help_change_data_capture @source_schema = 'dbo', @source_name = 'student';
EXEC sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'student', @capture_instance = xxx;
取得異動時間
select cdc.lsn_time_mapping.tran_begin_time,...
from xxx
INNER JOIN cdc.lsn_time_mapping ON xxx.__$start_lsn = cdc.lsn_time_mapping.start_lsn
異動資料預設保存三天，若要調整天數使用以下指令 (每個資料庫各別設定，單位是分鐘，43200=30天)
EXEC sys.sp_cdc_change_job @job_type=N'Cleanup' ,@retention=43200
2021年12月17日 星期五
asp.net mvc api 學習心得
網址指定action name 呼叫對應action
WebApiConfig.cs routeTemplate 加入 {action}
public static void Register(HttpConfiguration config)
{
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{action}/{id}",
defaults: new { id = RouteParameter.Optional }
);
Authorize 導入JWT
●安裝 jwt.net
●新增 JwtAuthorizeAttribute class
using System.Net.Http; // to use GetRequestContext()
public class JwtAuthorizeAttribute : AuthorizeAttribute
{
protected override bool IsAuthorized(HttpActionContext actionContext)
{
var authorization = actionContext.Request.Headers.Authorization;
if (authorization != null && authorization.Scheme == "Bearer")
{
var token = authorization.Parameter;
var secret = System.Web.Configuration.WebConfigurationManager.AppSettings["JWTSecret"];
var claims = JwtBuilder.Create()
.WithAlgorithm(new HMACSHA256Algorithm()) // symmetric
.WithSecret(secret)
.MustVerifySignature()
.Decode<IDictionary<string, object>>(token).Select(a => new Claim(a.Key.ToLower() == "name" ? ClaimTypes.Name : a.Key.ToLower() == "role" ? ClaimTypes.Role : a.Key, a.Value.ToString()) { });
var identity = new ClaimsIdentity(claims, "JWT");
var principal = new ClaimsPrincipal(identity);
actionContext.Request.GetRequestContext().Principal = principal;
}
return base.IsAuthorized(actionContext);
}
}
●webapiconfig.cs 加入
config.Filters.Add(new JwtAuthorizeAttribute());
被呼叫端Controller
public class p
{
public string lineid { get; set; }
}
[JwtAuthorize]
public class xxxController : ApiController
{
[HttpPost]
public IHttpActionResult aaa([FromBody] p p1)
{
try {
...
return Ok(xxx);
}
catch (Exception ex)
{
return BadRequest(ex.Message);
}
產生token
var secret = System.Web.Configuration.WebConfigurationManager.AppSettings["JWTSecret"];
var token = JWT.Builder.JwtBuilder.Create()
.WithAlgorithm(new JWT.Algorithms.HMACSHA256Algorithm()) // symmetric
.WithSecret(secret)
.AddClaim("exp", DateTimeOffset.UtcNow.AddMinutes(1).ToUnixTimeSeconds())
.AddClaim("role", 123)
.Encode();
呼叫端
using (HttpClient httpClient = new HttpClient())
{
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
var stringContent = new StringContent(JsonConvert.SerializeObject(new { lineid = "12345" }), System.Text.Encoding.UTF8, "application/json");
using (var response = await httpClient.PostAsync("...", stringContent))
{
string responseBody = await response.Content.ReadAsStringAsync();
var jobj = JsonConvert.DeserializeObject<JObject>(responseBody);
}
}
asp.net core mvc api 學習心得 (使用JWT驗證權限)
被呼叫端
startup.cs
public void ConfigureServices(IServiceCollection services, IConfiguration configuration)
{
services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.IncludeErrorDetails = true;
options.TokenValidationParameters = new TokenValidationParameters {
// 透過這項宣告，就可以從 "sub" 取值並設定給 User.Identity.Name
NameClaimType = "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/nameidentifier",
// 透過這項宣告，就可以從 "roles" 取值，並可讓 [Authorize] 判斷角色
RoleClaimType = "http://schemas.microsoft.com/ws/2008/06/identity/claims/role",
ValidateIssuer = true,
ValidIssuer = Configuration.GetValue<string>("JwtSettings:Issuer"),
ValidateAudience = false,
ValidateLifetime = true,
ValidateIssuerSigningKey = true,
IssuerSigningKey = new SymmetricSecurityKey(Encoding.UTF8.GetBytes(Configuration.GetValue<string>("JwtSettings:SignKey")))
};
});
public void Configure(IApplicationBuilder app)
{
app.UseRouting()
app.UseAuthentication();
app.UseAuthorization();
app.UseEndpoints(...);
[Authorize(Roles="xxx,ooo")] // 指定可以存取的角色
[ApiController]
[Route("api/[controller]")]
public class xxxAPIController : Controller
{
[HttpGet("[action]")]
public async Task<IActionResult> 查詢(int courseid, bool? 尚未確認, bool? 推薦人聯絡未完成, int? sid) // 接收多個簡單變數(字串有預設長度限制，超過會出現 http 404)
{
public class 未出貨訂單參數
{
public string lineid { get; set; }
}
[HttpPost("[action]")]
public async Task<IActionResult> 未出貨訂單(未出貨訂單參數 p) // 預先定義class 接收物件參數
{
[HttpPost("[action]")]
public async Task<IActionResult> 查詢推薦人聯絡(Dictionary<string, dynamic> p) // 針對多個簡單變數及陣列直接轉換參數
{
var ids = (p["ids"] as Newtonsoft.Json.Linq.JArray).ToObject<List<int>>();
var id = int.Parse(p["id"] as string);
}
呼叫端 (C#)
using (HttpClient httpClient = new HttpClient())
{
//產生token
var claims = new List<Claim>() {
new Claim (JwtRegisteredClaimNames.NameId, 登入帳號),
};
roles.ForEach(a => claims.Add(new Claim(ClaimTypes.Role, a.ToString()))); // 加入角色以供被呼叫端驗證是否有權限
var tokenG = new JwtSecurityToken(
issuer: validIssuer,
audience: validAudience,
claims: claims,
expires: DateTime.UtcNow.AddDays(1), // 一天後過期
signingCredentials: new SigningCredentials(new SymmetricSecurityKey(Encoding.UTF8.GetBytes(issuerSigningKey)),
SecurityAlgorithms.HmacSha256)
);
var tokenHandler = new JwtSecurityTokenHandler();
string token = new JwtSecurityTokenHandler().WriteToken(tokenG);
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", token);
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
var stringContent = new StringContent(JsonConvert.SerializeObject(new { lineid= "12345" }), System.Text.Encoding.UTF8, "application/json");
using (var response = await httpClient.PostAsync("...", stringContent))
{
string responseBody = await response.Content.ReadAsStringAsync();
var jobj = JsonConvert.DeserializeObject<JObject>(responseBody);
}
}
呼叫端 (axios)
將取得的 jwt token 存放在 cookie，每次呼叫時帶入
axios.interceptors.request.use((config: AxiosRequestConfig) => {
const token = Cookies.get('...');
if (config.headers) {
config.headers.Authorization = `Bearer ${token}`;
}
return config;
})
axios.get('...', { params: { id:1 } }) // get帶入簡單變數
axios.post('...', null, { params: { id:1 } }) // post帶入簡單變數 (參數值有字數限制，超過會出現 "Request failed with status code 404")
axios.post('...', {id:1,ids:[1,2,3]}) // post帶入物件
※ 設定發布時同步上傳的資料夾及檔案
<ItemGroup>
<Content Remove="$(SpaRoot)**" />
<Content Include="資料夾\*" CopyToOutputDirectory="PreserveNewest" /> // 建置動作設為"內容"
</ItemGroup>
2021年12月8日 星期三
asp.net 網站無法同時處理多個請求
此為 session block 造成的，當 iis 執行一個請求時，若用到session，會把session lock，下一個請求會等待unlock 後才能存取session，之後才會執行後續程式
請關閉session，若有用到session 則改用其他方式做資料交換，例如用memorycache 記錄使用者權限
web.config
<system.web>
<sessionState mode="Off" />
public class 權限設定 {
class 使用者權限
{
public int userid { get; set; }
public string functionid { get; set; }
}
static ObjectCache cache = MemoryCache.Default;
static List<使用者權限> 使用者權限cache
{
get
{
if (cache["使用者權限"] == null) cache["使用者權限"] = new List<使用者權限>();
return cache["使用者權限"] as List<使用者權限>;
}
set
{
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTimeOffset.Now.AddDays(1);
cache.Add("使用者權限", value, policy);
}
}
public static bool 檢查權限(string ID, System.Web.UI.Page page)
{
var userid = page.User.Identity.Name.ToInt();
if (!使用者權限cache.Any(a=>a.userid==userid))
{
using (var db = new xxxEntities())
{
使用者權限cache.AddRange(db.xxx.Where(a=>a.UserID==userid).Select(a =>new 使用者權限() { userid = a.UserID, functionid = a.FunctionID }).ToList());
}
}
return 使用者權限cache.Any(a => a.userid == userid && a.functionid == ID);
}
public static void 清除使用者權限cache(Page page)
{
var userid = page.User.Identity.Name.ToInt();
使用者權限cache.RemoveAll(a => a.userid == userid);
}
}
2021年12月6日 星期一
asp.net webmethod async 寫法
c#
====
[WebMethod]
public async static Task<string> 查詢Async()
{
using (var db = new Entities1())
{
var xxx =(await db.xxx.Where(a => ...).ToListAsync().ConfigureAwait(false)).Select(a => ...);
※加入 .ConfigureAwait(false) 避免無窮等待，原因可參考這篇，簡短說明參考:
To avoid performance degradation and possible dead-locks in ASP.NET or WPF applications (or any SynchronizationContext-dependent environment), you should always put ConfigureAwait(false) in your await statements:
javascript
====
$http.post('xxx.aspx/查詢Async', { })
.then(function (result) {
var newlist = JSON.parse(result.data.d.Result);
※回傳資料會放在Result裡面
aspx
====
<%@ Page Async="true"
2021年12月3日 星期五
呼叫非同步方法不等待且visual studio 不要警告
安裝套件 Microsoft.VisualStudio.Threading
非同步方法最後加入.Forget()
ex. xxx.xxxAsync(...).Forget();
訂閱：
文章 (Atom)