ASP.NET

ASP.NET  Tips

1. 善用Using

---> 自動Close跟Dispose

using (Sqlconnection conn = new Sqlconnection("xxx"))
{
     conn.Open();
     .....

}

 

2. 資料庫交易  (Transaction)   (using System.Transactions)

->交易無全部完成，自動rollback,

using (TransactionScope scope = new TransactionScope())
{
    .....
    .....
    scope.Complete();

}

 

3. string.Join

string[] values = {"1","2","3"};

string.Join(",",values);     //1,2,3

values.Aggregate( (i,j) => i + "," + j );    //1,2,3

 

4. SQL衝突

Update name = @Name where id=@id and name=@origin_name
//除了取id再與要修改欄位,做判斷

 

5. 取Date Time 指定該月的最後一天

DateTime.DaysInMonth(year, month);

new DateTime(yesr, month, 1).AddMonths(1).AddDays(-1);

 

6.SQL多筆(multi row)變成單行(single string)    

--->FOR XML PATH('')  合併欄位

1. -->SELECT
SELECT PRODUCTID,
(
  SELECT ',' + PO_A.[OWNER]
  FROM PRODUCTOWNER PO_A
  WHERE PO_A.PRODUCTID = PO_B.PRODUCTID 
  FOR XML PATH('')
)AS OWNERS
FROM PRODUCTOWNER PO_B


2. -->DISTINCT
SELECT DISTINCT PRODUCTID,
(
  SELECT ',' + PO_A.[OWNER]
  FROM PRODUCTOWNER PO_A
  WHERE PO_A.PRODUCTID = PO_B.PRODUCTID 
  FOR XML PATH('')
)AS OWNERS
FROM PRODUCTOWNER PO_B 

3. -->STUFF("原字串","起始位置","移除長度","替換字串")
SELECT DISTINCT PRODUCTID,
(
STUFF(
(SELECT ',' + PO_A.[OWNER]
  FROM PRODUCTOWNER PO_A
  WHERE PO_A.PRODUCTID = PO_B.PRODUCTID 
  FOR XML PATH('')),   //原字串
  1,                   //起始位置
  1,                   //移除長度
  ''                   //替換字串
)AS OWNERS
FROM PRODUCTOWNER PO_B 

全文>參考網址: https://dotblogs.com.tw/supershowwei/2016/01/26/145353

參考網址: pixnet