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