string EndDate = Request["EndDate"];
DataTable qtyControls = U2.SQL.DTFromSQL("Select YA00, PD00 from QtyControl Where EndDate > '" + EndDate + "' and SoldQty >= InitQty");
var values = qtyControls.AsEnumerable().Select(r => "('" + r.Field<string>("YA00") + "','" + r.Field<string>("PD00") + "')").ToList();
var sqls = new List<string>();
sqls.Add("Delete StopSaleYAP;");
int start = 0;
while(start < values.Count)
{
var end = start + 999;
if(end > values.Count - 1)
{
end = values.Count;
}
sqls.Add("insert into StopSaleYAP(YA00, PD00) Values" + string.Join(",", values.GetRange(start, end)) + ";");
start = end + 1;
}
U2.SQL.ExecuteSQL(string.Join("\r\n", sqls));
public static bool IsErrorOrder(Order.Input.CheckValidOrder dto)
{
if (dto.OrderNos == null || dto.OrderNos.Count == 0)
{
return false;
}
var orderCount = dto.OrderNos.Count();
var orders = NpreoOrderMain.GetList(dto.OrderNos);
if (orders.Count != orderCount || !dto.OrderNos.Any(x => orders.Select(o => o.Order_No).Contains(x)))
{
return true;
}
return false;
}
var fu = Request.Files[0];
fu.SaveAs(Server.MapPath("UploadFiles/") + fu.FileName);
--
基本題:
1. 對 Linq 熟嗎.
2. 對 ASP.Net 的 Cache 熟悉嗎.
3. 用過什麼 ORM, 試說明優缺點.
4. 試說明 MVC 的架構.
資安相關問題:
1. 試說明 SQL Injection
2. 試說明 Cross Site Injection.
3. 上傳檔案要注意的事項.
4. 試說明 cookie 的安全設定 ? same site, secure, http only.
前端相關加分題:
1. jQuery 或 Vue 熟悉嗎 ?
2. 試說明 RWD
3. 試說明 bootstrap
進階問題:
1. 試說明 Reflection
2. 試說明 Dependency Injection
3. 試說明 singleton vs static
4. 試單有兩個欄位 Id, Status (付款待確認: 1.1; 已付款: 2, 訂單已出貨: 3; 訂單取消中: 5; )
狀態 1.1 和 狀態 2 的訂單可取消，取消後改為狀態 5
客人要取消訂單，訂單編號為 123, 試說明程式執行的過程。
GetAllDataFromBaseTableWithCache(string OrderFields = "_Default", bool IsCopy = true )
以及
Get物件名稱FromCachedDT(string Key)
-----------------------------------------------------------
設計上是希望將所有資料Cache在記憶體，然後前端直接抓出資料，不須到資料庫抓資料
理論上網頁速度應該比較佳，實際上卻發現是效能殺手
原因在於 GetAllDataFromBaseTableWithCache 是抓出整個table的資料放到 Cache
然後又做了一件事，當有人要資料時，會把整個 DataTable 做 Copy() 再 return
也就是說，當DataTable有數千筆資料列時
每叫用一次 "Get物件名稱FromCachedDT(string Key)" 就會把數千筆資料整個複製一次
再從複製出來的DataTable Select 出一列 return
------------------------------------------------------------
解決方法:
Get物件名稱FromCachedDT(string Key) --> 改成一筆一筆資料做 Cache，這樣 Copy() 只會Copy一筆
實測上，原本頁面要跑1.8秒，調整後可以 0.4 秒就完成
-----------------------------------------------------------
結論:
PG產生器就把 GetAllDataFromBaseTableWithCache 拿掉吧
舊的程式碼:
public static DataTable DTFromSQL(string Sql, string DBC = null, Int32 timeout = 0)
{
SqlDataAdapter DA = new SqlDataAdapter(Sql, GetDBC(DBC));
DataTable DT = new DataTable();
try
{
DA = new SqlDataAdapter(Sql, DBC);
if (timeout > 0)
{
DA.SelectCommand.CommandTimeout = timeout;
}
DA.Fill(DT);
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
DA.Dispose();
}
return DT;
}
新的程式碼:
public static DataTable DTFromSQL(string Sql, string DBC = null, Int32 timeout = 30)
{
using (var DA = new SqlDataAdapter(Sql, GetDBC(DBC)))
{
//不可為 null
DataTable DT = new DataTable();
DA.SelectCommand.CommandTimeout = timeout;
DA.Fill(DT);
return DT;
}
}
可以參考:
https://dotblogs.com.tw/yc421206/archive/2012/01/03/64154.aspx
前兩天遇到一個需求, 輸出的 Excel 要加上表頭, 如下圖
於是乎把 UW.ExcelPOI.DTToExcelAndWriteToClient 做了一些擴充, (其實應該說是幫 DTToWorkSheet 做了擴充), 過程如下.
1. 需求: 一個可以快速填入欄位的 Sub (method or function)
A. 每一個 Cell 可以設定內容(文字), 字型大小, 跨欄數, 對齊方式. (其它的未來再來擴充, 例如顔色).
B. 每一個 Row 由 Cell 組成, 由左到右.
C. 一次可以填多個 Row
2. 實作:
A. 先定義 Cell
Public Class Cell
Public Content As String
Public Colspan As Int32 = 1
Public Alignment As NPOI.SS.UserModel.HorizontalAlignment
Public FontHeightInPoints As Int32 = 0
Sub New(Content As String, Optional Colspan As Int32 = 1,
Optional Alignment As NPOI.SS.UserModel.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.General,
Optional FontHeightInPoints As Int32 = 0)
Me.Content = Content
Me.Colspan = Colspan
Me.Alignment = Alignment
Me.FontHeightInPoints = FontHeightInPoints
End Sub
End Class
B. Row 的格式: 我想最直的覺的就是 List(of Cell) 了吧.
C. 多個 Row 的表示法: List(Of List(Of Cell))
D. 來把 Cell 填入 WorkSheet 吧,
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
共有三個參數: WS 和 ltRows 應該不用解釋了. 最後一個 StartRow 用來指定插入資料的開始 Row.
E. 完整程式碼: (程式碼不看沒關係, 但要跳到 F. 重點講解哦)
Public Shared Sub AddRows(WS As HSSFSheet, ltRows As List(Of List(Of Cell)), ByRef StartRow As Int32)
Dim WR As HSSFRow
If ltRows IsNot Nothing Then
For Each ltRow As List(Of Cell) In ltRows
WR = WS.CreateRow(StartRow)
Dim C As Int32 = 0
For Each cell As Cell In ltRow
Dim ic As NPOI.SS.UserModel.ICell = WR.CreateCell(C)
ic.SetCellValue(cell.Content)
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
If cell.Colspan > 1 Then
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
C += cell.Colspan - 1
End If
C += 1
Next
StartRow += 1
Next
End If
End Sub
F. 重點講解:
這個 function 在實作時有兩個卡點:
1. 如何合併欄:
WS.AddMergedRegion(New CellRangeAddress(StartRow, StartRow, C, C + cell.Colspan - 1))
2. 如何設定字型大小和對齊方式:
Dim cs As NPOI.SS.UserModel.ICellStyle = WS.Workbook.CreateCellStyle()
cs.Alignment = cell.Alignment
If cell.FontHeightInPoints > 0 Then
Dim oFont As NPOI.SS.UserModel.IFont = WS.Workbook.CreateFont()
oFont.FontHeightInPoints = cell.FontHeightInPoints
cs.SetFont(oFont)
End If
ic.CellStyle = cs
這裡有件有有趣的事, 我一開始是這樣寫的.
ic.CellStyle.Alignment = cell.Alignment
結果是整個 WorkSheet 的對齊方式都被改了. 我猜當 WorkSheet 初建立時, CellStyle 都是用同一個. 所以改任一個 cell 的 CellStyle 會同時改到所有 cell 的.
G. 使用方式:
Dim ltHeader As New List(Of List(Of UW.ExcelPOI.Cell))
Dim ltLine As New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell(DB.SysConfig.SYSTEM_NAME & "應收明細表", 16,
NPOI.SS.UserModel.HorizontalAlignment.Center, 28))
ltHeader.Add(ltLine)
'第二行
ltLine = New List(Of UW.ExcelPOI.Cell)
ltLine.Add(New UW.ExcelPOI.Cell("期間: " & Me.txtbl_date_s.Text & " ~ " & Me.txtbl_date_e.Text, 10,
NPOI.SS.UserModel.HorizontalAlignment.Left, 20))
ltLine.Add(New UW.ExcelPOI.Cell("製表日期: " & Now.ToString("yyyy-MM-dd"), 6,
NPOI.SS.UserModel.HorizontalAlignment.Right, 20))
ltHeader.Add(ltLine)
UW.ExcelPOI.DTToExcelAndWriteToClient(newdt, ltHeader:=ltHeader)
網站開發，善用 Cached DataTable 可以使網站效能倍增，不必一直去資料庫抓資料。
但是使用 Cached DataTable 有一個地方要注意
就是他是 Shared 物件，表示同時有好幾個頁面都可以存取他
例如A網頁將資料抓出，然後變更裡面的DataRow資料，
另一個B網頁也會跟著變更
由於 Cached DataTable 在 Set DataRow Value 時
可能 Critical Section 沒有處理好，若真的同時多個thread 操作
會出現 Exception
(System.ArgumentOutOfRangeException: 索引超出範圍。必須為非負數且小於集合的大小。)
建議:
- Cached DataTable 最好是只能用來讀，不要Set Value
- 若需要Set Value，可以使用 DataTable Copy()功能，先複製出來再操作，就不會影響到原始資料
'這是 UNT FixPage 物件抓取單一資料的範例
Shared Function GetSpecialPage2FromCachedDT(ByVal Key As Int32) As DB.SpecialPage2
Dim DT As DataTable = GetAllDataFromBaseTableWithCache()
Dim Row As DataRow = DT.Rows.Find(Key)
If Row IsNot Nothing Then
'避免每個thread都共用table 寫入資料會出現問題
Dim newDT As DataTable = DT.Clone()
newDT.ImportRow(Row)
Return New SpecialPage2(newDT.Rows(0))
Else
Return Nothing
End If
End Function
例如檢查產品是否停賣
原本的寫法是用以下方法尋找
DT.Select("Id=" & ProductId)
後來改寫 DataTable 加上 PrimaryKey
'寫入Cache前先設定好
DT.PrimaryKey = New DataColumn() {obj.Columns("Id")}
Dim row As DataRow = DT.Rows.Find(ProductId)
共1085筆 找出其中一筆
Select方法 - 00:00:00.0000559
Rows.Find - 00:00:00.0000064
兩者速度差了約10倍
Sample 如下:
Imports System.Drawing.Printing
Namespace X
Partial Public Class PrintSomething
Dim PD As PrintDocumentDim ppc As New Printing.PreviewPrintController()
Dim TotalPage As Int32 = 0
''' <summary>
''' 第幾次列印, 用來控制寫出的圖檔
''' </summary>
''' <remarks></remarks>
Dim PrintCount As Int32 = 1Dim PageCount As Int32 = 1
:
:
:
Sub New(ByVal oPM As DB.PackingListMain, ByVal dtDetail As DataTable, dtUsedCoupon As DataTable)
'初始化資料
:
:
:
End SubSub InitPrintDocument()
'在這裡設定 PrintDocument 的紙張, 印表機, 邊界, 橫印或直印PD = New PrintDocument
:
:
:End Sub
Sub print()
InitPrintDocument()PD.PrintController = ppc
' 第一次先把列印結果存在 Printing.PreviewPrintController 中
AddHandler PD.PrintPage, AddressOf GeneratePreview'再來儲存圖片
AddHandler PD.EndPrint, AddressOf SaveImagePD.Print()
End Sub
Dim ppi() As Printing.PreviewPageInfo
Dim PrintPage As Int32 = 0Sub SaveImage(ByVal sender As Object, ByVal ev As PrintEventArgs)
PrintDebug("Save Image Start, " & Now.ToString("yyyy-MM-dd HH:mm:ss.fff"))
'把圖檔存起來
Dim TF As String = Now.ToString("yyyyMMddHHmmss") ' DB.SysConfig.Path.LocalDataRoot
Dim PathLocal As String = "C:\PrintLog\" & TF.Substring(0, 4) & "-" & TF.Substring(4, 2) & "\"
Dim PathPackingListSheetImage As String = DB.SysConfig.PackingListSheetImage & TF.Substring(0, 4) & "-" & TF.Substring(4, 2) & "\"System.IO.Directory.CreateDirectory(PathLocal)
System.IO.Directory.CreateDirectory(PathPackingListSheetImage)Dim LatestImages As String = ""
ppi = ppc.GetPreviewPageInfo()For x As Integer = 0 To ppi.Length - 1
Dim Filename As String = TF & "_" & Me.oPM.Id & "_" & x & ".png"ppi(x).Image.Save(PathLocal & Filename, System.Drawing.Imaging.ImageFormat.Png)
Next
'把圖檔印出來
For x As Integer = 0 To ppi.Length - 1
'建立新的 PrintDocument
InitPrintDocument()If CST.WebConfig.Server_NAME = "測試主機" Then
UW.WU.DebugWriteLine("PrinterName: " & Me.PD.PrinterSettings.PrinterName)
UW.WU.DebugWriteLine("PaperSize: " & PD.DefaultPageSettings.PaperSize.PaperName)PD.DocumentName = "PackingList"
PD.PrinterSettings.PrintFileName = "C:\PDF\PackingList" & Now.ToString("yyyyMMddHHmmss") & "_" & PrintPage & ".prn"
PD.PrinterSettings.PrintToFile = True'PD.PrinterSettings.
End If'很奇怪, 這裡不會分頁, 所以要一張一張印
AddHandler PD.PrintPage, AddressOf pd_PrintPage2
PD.Print()
PrintPage += 1Next
End SubPrivate Sub pd_PrintPage2(ByVal sender As Object, ByVal ev As PrintPageEventArgs)
'很奇怪, 這裡不會分頁, 所以要一張一張印
ev.Graphics.DrawImage(ppi(PrintPage).Image, 0, 0)
ev.HasMorePages = False
End Sub
Private Sub GeneratePreview(ByVal sender As Object, ByVal ev As PrintPageEventArgs)
' 列印內容
End SubEnd Class
End ClassEnd Namespace