I was working on a website and needed a way to display my log information in a gridview control but allow paging through a certain number of articles at a time so that if the user was viewing the complete archive, the page wouldn't be too long.
The default paging in the ASP.NET gridview control does not scale well as it always retrieves all the records from the database and then only displays the ones you want. I wanted to rather fetch only the records that I needed for the current page and let SQL so all the work so that the impact on my web application was less.
After some searching on the web I found various articles but nothing that really worked for me so I decided to write my own. Incidentally, I started by drawing the logic out on a piece of paper before just diving into the code and this really helped with my focus and made sure that I didn't waste time going in the wrong direction. Now, let's get to the code...
Firstly, the structure of my SQL News Article table is as follows:
The logID is an auto-incrementing identity column .
So in addition to the paging, I needed to be able to filter my records by logTable/logOper/logTime as well as search word(s) found in the Title or Body fields. To do this, I created a stored procedure that looks like this:
IF OBJECT_ID (N'sp_GetLogs',N'P') IS NOT NULL
DROP PROC sp_GetLogs
go
CREATE PROC sp_GetLogs
@iPageIndex INT,
@iMaxRows INT,
@search varchar(1000)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @iStart INT
SELECT @iStart = (@iPageIndex - 1) * @iMaxRows
DECLARE @iEnd INT
SELECT @iEnd = @iStart + @iMaxRows
IF OBJECT_ID (N'#TempLogs',N'U') IS NOT NULL
DROP TABLE #TempLogs
CREATE TABLE #TempLogs(
intUniqueID INT PRIMARY KEY IDENTITY(1, 1),
userCName NVARCHAR(100),
logTable VARCHAR(50),
logOper VARCHAR (50),
logTime VARCHAR(50),
logContent NVARCHAR(300))
DECLARE @SQLSTR VARCHAR(1000)
SET @SQLSTR='INSERT #TempLogs SELECT U.userCName,S.logTable,S.logOper,S.logTime,S.logContent FROM Sys_Log S
INNER JOIN JC_Userinfo U ON U.userID = S.logUser
WHERE '+@search;
PRINT(@SQLSTR)
EXEC (@SQLSTR)
SELECT * FROM #TempLogs
WHERE intUniqueID > @iStart
AND intUniqueID <= @iEnd
END
GO
EXEC sp_GetLogs 2,5,'LOGTABLE=''JC_DEPARTMENT'''
there are some eggache question here
1. in sysobjects
| S | SYSTEM TABLE |
| V | VIEW |
| U | USER TABLE(包括临时表) |
| TR | TRIGGER |
so, drop a temp table is
IF OBJECT_ID (N'#TempLogs',N'U') IS NOT NULL
DROP TABLE #TempLogs
2.exec a variable is like this , don’t forget the brackets.
DECLARE @SQLSTR VARCHAR(1000)
SET @SQLSTR='INSERT #TempLogs SELECT U.userCName,S.logTable,S.logOper,S.logTime,S.logContent FROM Sys_Log S
INNER JOIN JC_Userinfo U ON U.userID = S.logUser
WHERE '+@search;
PRINT(@SQLSTR)
EXEC (@SQLSTR)
The paramters I pass into the procdure are:
- @iPageIndex
- @iMaxRows
- @search
The @PageIndex refers to the current page of results that we are viewing.
The @iMaxRows refers to the number of records displayed on each page - note that it is called max rows because the last page may contain less than the others depending on the total number of records.
The @search refers to any search word(s) used to filter the results and is also optional.
The first thing we do is set the start and end values for our recordset based on the current page and the number of records to fetch. We will use these later to select only the relevant records to pass back to our web application.
Next we create a temprorary table with an auto-incrementing identity column - this is important as if records are deleted from our original news article table, the id's will no longer be sequential - if, however, we insert our records into our temporary table, the id's will be sequential and we can select between our start and end values.
We then select the appropriate records from our log table and insert these into our temporary table and finally, we select our results from our temporary table where the ids are between our start and end values. After selecting them, we delete our temporary table from the memory.
So, now we have a SQL stored procedure that will return a set of results based on some filtering and paging parameters. How do we use this in our web application?
Here's what the source for my News.aspx page looks like:
------------------------------------------------
------------------------------------------------
Note that I have two "divs," one for the gridview control and another one called "Pager" that will display the page numbers for paging. (As an aside - check the dtPosted column, you will see that I have specified a date format AND HtmlEncode - if the HtmlEncode is not set to false, your formatting will not be applied)
Now that we have our grid, let's bind the data to it in our code behind page.
In the Page_Load event I call the following routine:
FillNewsGrid("News.aspx", gvNews, Pager, iMaxRows, iCurrentPage, iCategory, txtSearchText.Value)
This routine looks like this:
------------------------------------------------
------------------------------------------------
Lastly, I compile the link to view the complete article in the RowDataBound event:
There we go, that should get you started, please feel free to contact me if you need any assistance getting this up and running on your site.
提示:
写存储过程中, 可以使用print(@sql)来看下最后的sql是否正确