Datatable jquery
Datatables jquery 最近剛好在利用，因為server-side用php，改寫成asp 底下是參考的code '*** 底下為server-side SQL處理 ***** 'sql變數儲存的為原本sql語法 sql = "select xxx from xxx" if not isnull( Request.QueryString("iSortCol_0")) then sOrder = "ORDER BY " for i=0 to Request.QueryString("iSortingCols" ) - 1 sOrder = sOrder & fnColumnToField(Request.QueryString("iSortCol_" & i) ) & " " & Request.QueryString("iSortDir_" & i)) & ", " next sOrder = left( sOrder, len(sOrder) - 2 ) end if '/* Filtering - NOTE this does not match the built-in DataTables filtering which does it ' * word by word on any field. It's possible to do here, but concerned about efficiency ' * on very large tables, and MySQL's regex functionality is very limited '*/ sWhere = "" if Request.QueryString("sSearch") <> "" then sWhere = "WHERE search_column_name LIKE '%" & replace(trim(Request.QueryString("sSearch")), "'", "''") & "%' OR " &_ "search_column_name LIKE '%" & replace(trim(Request.QueryString("sSearch")), "'", "''") & "%' " end if sQuery = "SELECT column_name " &_ " FROM (" & sql & ") a " &_ sWhere &_ sOrder '*** 這段是分頁處理 ***** 開始 pagesize = Request.QueryString("iDisplayLength") '每頁大小 page = fix(Request.QueryString("iDisplayStart") / pagesize) + 1 '這段比較有問題，可能有錯 set cmd = server.CreateObject("ADODB.Command") cmd.ActiveConnection = Conn cmd.CommandType = 4 'adCmdText 1; adCmdTable 2; adCmdStoreProc 3; adCmdUnknown 4; adStroeProducure cmd.CommandText = "sp_xxx_store_procedure" 'cmd.Parameters 欄位, 類型, 回傳|輸入|輸出, 長度, 值 'adChar adInteger '1:adParamInput 2:adParamOutput輸出參數 3:adParamReturnValue返回值 cmd.Parameters.Append cmd.CreateParameter("@SqlCommand", 8, 1, 4000, sQuery) cmd.Parameters.Append cmd.CreateParameter("@CurrentPageIndex", 4, 1, 4, page) cmd.Parameters.Append cmd.CreateParameter("@PageSize", 4, 1, 4, pagesize) cmd.Parameters.Append cmd.CreateParameter("@PageCount", 4, 2, 4, pageCount) cmd.Parameters.Append cmd.CreateParameter("@recordCount", 4, 2, 4, recordCount) set rs = cmd.Execute rs.close pageCount = cmd.Parameters("@PageCount").value recordCount = cmd.Parameters("@recordCount").value if pageCount = 0 then pageCount = 1 if page > pageCount then response.Redirect("?page="&pageCount) end if rs.open set rs = rs.NextRecordSet if not rs.eof then '有值才取出來 arr_mn = rs.GetRows '(欄位,第幾列) StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr end if rs.close set rs=nothing '*** 這段是分頁處理 ***** 結束 '/* Paging */ '使用上面的分頁 iFilteredTotal = recordCount '這個也可能有問題 iTotal = recordCount sOutput = "{" sOutput = sOutput & """sEcho"": " & Request.QueryString("sEcho") & ", " sOutput = sOutput & """iTotalRecords"": " & iTotal & ", " sOutput = sOutput & """iTotalDisplayRecords"": " & iFilteredTotal & ", " sOutput = sOutput & """aaData"": [" sOutput = sOutput & datatable_sdata(arr_mn) & "]}" response.write sOutput function fnColumnToField( i ) '這是放datatables的column，要order欄位 if i = 0 then fnColumnToField = "sort_column_name_1" elseif i = 1 then fnColumnToField = "csort_column_name_2" end if end function 'sp_xxx_store_procedure 為底下程式碼 CREATE PROCEDURE [dbo].[sp_xxx_store_procedure] @SqlCommand NVARCHAR(4000), @CurrentPageIndex INT, @PageSize INT, @PageCount int=0 out, @recordCount int=0 out AS SET nocount ON DECLARE @curl INT, @rowcount INT EXEC sp_cursoropen @curl output, @SqlCommand, @scrollopt=1, @ccopt=1, @rowcount=@rowcount output set @recordCount = @rowcount set @PageCount = ceiling(1.0*@rowcount/@PageSize) --SELECT ceiling(1.0 * @rowcount/@PageSize) as 總頁數, @rowcount as 總筆數, @CurrentPageIndex as 目前頁 --SELECT ceiling(1.0 * @rowcount/@PageSize) as totalpages, @rowcount as totalrecords, @CurrentPageIndex as nowpage SET @CurrentPageIndex = (@CurrentPageIndex - 1) * @PageSize + 1 EXEC sp_cursorfetch @curl, 16, @CurrentPageIndex, @PageSize EXEC sp_cursorclose @curl SET nocount OFF GO 'asp內的function function datatable_sdata(arr) '串dataTable用的data if isarray(arr) then rows_mn = ubound(arr, 2) '取得recordset 的記錄行數' 2代表第2維陣例-幾例 for i=0 to rows_mn arr(0,i) = replace(arr(0,i), chr(13)&chr(10), "") '去掉換行 coumn_1 arr(1,i) = replace(arr(1,i), chr(13)&chr(10), "") '去掉換行 coumn_2 str = str & "[""" & arr(0,i) & """, """ & arr(1,i) & """]," & chr(13) & chr(10) '有沒有chr(13)或chr(10)都行 next datatable_sdata = left(str, len(str)-3) 'chr(13)+chr(10)+, end if end function