private const string SQL_SEARCH_COUNT = "SELECT COUNT(*) AS COUNT FROM [WSE_CompanyCertificate] WHERE ( 1 = 1 ) ";
private const string SQL_SEARCH_IN = @"
SELECT [ID]
,( ROW_NUMBER() OVER (ORDER BY [UserID] , [Type] , [OrderNumber] ) -1 ) AS RowNumber
FROM [WSE_CompanyCertificate]
WHERE (1=1)";
private const string SQL_SEARCH_OUT = @"
WITH OrderedTable AS ({0})
SELECT n.*
FROM OrderedTable o LEFT OUTER JOIN
[WSE_CompanyCertificate] n ON o.[ID] = n.[ID]
WHERE o.[RowNumber] BETWEEN @PageLower AND @PageUpper
ORDER BY o.RowNumber
";
private const string SQL_SEARCH_USER_ID = " AND ( [UserID] = @UserID ) ";
private const string SQL_SEARCH_Type = " AND ( [Type] = @Type ) ";
private const string SQL_SEARCH_VALIDATE = " AND ( [validate] = @validate) ";
[Microsoft.SqlServer.Server.SqlProcedure(Name = "WSE_COMPANY_CERTIFICATE_SEARCH_3")]
public static void Search(out int AllCount, int PageIndex, int PageSize,
int UserID, int Type, int validate)
{
DateTime dtStart = DateTime.Now;
using (SqlConnection cn = new SqlConnection(SqlConn.SQL_CON))
{
cn.Open();
SqlCommand cmdCount = new SqlCommand();
SqlCommand cmdSearch = new SqlCommand();
cmdCount.Connection = cn;
cmdSearch.Connection = cn;
string strWhere = "";
if (UserID > 0)
{
strWhere += SQL_SEARCH_USER_ID;
cmdSearch.Parameters.AddWithValue(PARM_USER_ID, UserID);
cmdCount.Parameters.AddWithValue(PARM_USER_ID, UserID);
}
if (Type == 0 || Type == 1)
{
strWhere += SQL_SEARCH_Type;
cmdSearch.Parameters.AddWithValue(PARM_TYPE,Type);
cmdCount.Parameters.AddWithValue(PARM_TYPE,Type);
}
if (validate == -1 || validate == 0 || validate == 1)
{
strWhere += SQL_SEARCH_VALIDATE;
cmdSearch.Parameters.AddWithValue(PARM_VALIDATE, validate);
cmdCount.Parameters.AddWithValue(PARM_VALIDATE, validate);
}
string strSQL = SQL_SEARCH_COUNT + strWhere;
cmdCount.CommandText = strSQL;
DBUtility.WriteSQL(cmdCount);
AllCount = Convert.ToInt32(cmdCount.ExecuteScalar());
strSQL = SQL_SEARCH_IN + strWhere;
strSQL = string.Format(SQL_SEARCH_OUT, strSQL);
cmdSearch.CommandText = strSQL;
int intPageLower = PageSize * PageIndex;
int intPageUpper = intPageLower + PageSize - 1;
cmdSearch.Parameters.AddWithValue(Constant.PARM_PAGE_UPPER, intPageUpper);
cmdSearch.Parameters.AddWithValue(Constant.PARM_PAGE_LOWER, intPageLower);
DBUtility.WriteSQL(cmdSearch);
SqlContext.Pipe.ExecuteAndSend(cmdSearch);
}
TimeSpan ts = DateTime.Now - dtStart;
SqlContext.Pipe.Send(string.Format("{0}:{1}", ts.Seconds, ts.Milliseconds));
}