sueboy: 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
EExcel 丞燕快速查詢2

Datatable jquery
