Flexible use of the gridview control in asp.net, asp. netgridview

Flexible use of the gridview control in asp.net, asp. netgridview

Gridview is a commonly used data display control in asp.net. It should be very familiar to. net developers. The gridview has many functions, including paging and sorting. for net developers, it is very important to be proficient in using stored procedure pages or third-party custom pages. This is not only a project requirement, but also a prompt of our experience and capabilities, next, we will use stored procedure pagination to bind the gridview.

1. Execute the Stored Procedure

There are many examples of SQL paging stored procedures on the Internet, but you will find that many of them are not usable, for example, some use in or not in paging efficiency is very low, some sp can be paginated, but the extended type is very poor, some efficiency is relatively high, but the total number of query records cannot be returned,

For example, the following paging is relatively simple, but the paging efficiency with not in is relatively low, and the query table is fixed and cannot be expanded. It is actually a failed paging.

CREATE PROCEDURE GetProductsByPage @PageNumber int, @PageSize intASdeclare @sql nvarchar(4000) set @sql = 'select top ' + Convert(varchar, @PageSize) + ' * from test where id not in (select top ' + Convert(varchar, (@PageNumber - 1) * @PageSize) + ' id from test)'exec sp_executesql @sqlGO

In summary, I think this paging is very efficient in general, and only one sp is required for paging. Paging supports multi-Table multi-tag query.

Alter procedure [dbo]. [Proc_SqlPageByRownumber] (@ tbName VARCHAR (255), -- table name @ tbGetFields VARCHAR (1000) = '*', -- Return field @ OrderfldName VARCHAR (255 ), -- sorting field name @ PageSize INT = 20, -- page size @ PageIndex INT = 1, -- page number @ OrderType bit = 0, -- 0 in ascending order, non-0 descending order @ strWhere VARCHAR (1000) = '', -- Query condition @ TotalCount int output -- total number of records returned) AS -- =================================================== =======-- Author: allen (liyuxin) -- Create date: 2012-03-30 -- Description: Paging Stored Procedure (Multi-table join query supported) -- Modify [1]: 2012-03-30 -- ================================================= ======= begin declare @ strSql VARCHAR (5000) -- subject sentence DECLARE @ strSqlCount NVARCHAR (500) -- query the total number of records subject sentence DECLARE @ strOrder VARCHAR (300) -- sorting type -------------- total number of records ----------- if isnull (@ strWhere ,'') <> ''set @ strSqlCount = 'select @ TotalCout = count (*) from '+ @ tbName + 'where 1 = 1' + @ strWhere else set @ strSqlCount = 'select @ TotalCout = count (*) from' + @ tbName exec sp_executesql @ strSqlCount, n' @ TotalCout int output', @ TotalCount output -------------- paging ------------ IF @ PageIndex <= 0 SET @ PageIndex = 1 IF (@ OrderType <> 0) SET @ strOrder = 'ORDER BY' + @ OrderfldName + 'desc' else set @ strOrder = 'ORDER BY' + @ OrderfldName + 'asc 'SET @ strSql = 'select * FROM (SELECT ROW_NUMBER () OVER ('+ @ strOrder +') RowNo, '+ @ tbGetFields + 'from' + @ tbName + 'where 1 = 1' + @ strWhere +') tb WHERE tb. rowNo BETWEEN '+ str (@ PageIndex-1) * @ PageSize + 1) + 'and' + str (@ PageIndex * @ PageSize) exec (@ strSql) SELECT @ TotalCount END

2. encapsulate c # Call statements

We are always used to encapsulating the code, which can improve the reading efficiency and maintenance of the code, and develop a good habit of encapsulating the code. We have entered the intermediate level from the initial level, actually, this is just a habit.

Public static class PageHelper {// <summary> // paging data // </summary> /// <param name = "TableName"> indicates </param>/ // <param name = "RetureFields"> return field </param> // <param name = "strWhere"> condition </param> // <param name =" pageSize "> Number of records per page </param> /// <param name =" CurPage "> current page </param> /// <param name =" RowCount "> total number of records </param> /// <param name = "sortField"> sorting field </param> /// <returns> </returns> public static DataTable GetPageList (string tbName, string tbGetFields, string OrderFldName, int PageSize, int PageIndex, int OrderType, string strWhere, out int TotalCount) {SqlCommand cmd = new SqlCommand ("Proc_SqlPageByRownumber "); // name of the stored procedure cmd. commandType = CommandType. storedProcedure; cmd. parameters. addWithValue ("@ tbName", tbName); // table name cmd. parameters. addWithValue ("@ tbGetFields", tbGetFields); // field name to be displayed (do not add Select) cmd. parameters. addWithValue ("@ OrderfldName", OrderFldName); // name of the index field cmd. parameters. addWithValue ("@ PageIndex", PageIndex); // current page, page number cmd. parameters. addWithValue ("@ PageSize", PageSize); // number of data entries displayed per page cmd. parameters. addWithValue ("@ OrderType", OrderType); // sets the sorting type. If the value is not 0, the command is run in descending order. parameters. addWithValue ("@ strWhere", strWhere); // query condition. Do not add where cmd. parameters. add (new SqlParameter ("@ TotalCount", SqlDbType. int); cmd. parameters ["@ TotalCount"]. direction = ParameterDirection. output; DataTable dt = RunProcedureCmd (cmd); TotalCount = Convert. toInt32 (cmd. parameters ["@ TotalCount"]. value. toString (); // return dt;} // <summary> // execute the stored procedure, return DataTable // </summary> /// <param name = "cmd"> </param> /// <returns> </returns> public static DataTable RunProcedureCmd (SqlCommand cmd) {DataTable result = new DataTable (); string connectionString = ConfigurationManager. appSettings ["CONNSTRING"]. toString (); SqlConnection conn = new SqlConnection (connectionString); // your own link string try {if (conn. state = ConnectionState. closed) {conn. open ();} cmd. connection = conn; SqlDataAdapter da = new SqlDataAdapter (cmd); da. fill (result); da. dispose (); conn. close (); conn. dispose (); return result;} catch (Exception ex) {conn. close (); conn. dispose (); throw ex ;}}}

3. Using third-party plug-ins to implement pagination

Currently, the popular plug-in for paging is aspnetpager, which is a mature plug-in. There are also many examples on the Internet.

1) download the aspnetpager plug-in and right-click to reference it.

2) Open the toolbox, right-click the tab, and choose import plug-in from the shortcut menu.

3) drag the control to the page and set its properties.

Background code

Protected void Page_Load (object sender, EventArgs e) {if (! IsPostBack) {GridViewBind ("") ;}} private void GridViewBind (string sqlWhere) {int TotalCount; DataTable dt = bll. getList ("stu_score", "code, name, beginTime, endTime, score", "id", this. aspNetPager1.PageSize, this. aspNetPager1.CurrentPageIndex, 1, sqlWhere, out TotalCount); this. aspNetPager1.RecordCount = TotalCount; this. gridView1.DataSource = dt; this. gridView1.DataBind (); this. aspNetPager1.CustomInfoHTML = string. format ("Current page {0}/{1} contains {2} records per page {3}", new object [] {this. aspNetPager1.CurrentPageIndex, this. aspNetPager1.PageCount, this. aspNetPager1.RecordCount, this. aspNetPager1.PageSize});} // protectedvoid GridView1_RowDataBound1 (object sender, GridViewRowEventArgs e) {if (e. row. rowType = DataControlRowType. dataRow) {e. row. attributes. add ("onmouseover", "c = this. style. backgroundColor, this. style. backgroundColor = '# C7DEF3' "); e. row. attributes. add ("onmouseout", "this. style. backgroundColor = c ");}}

Front-end code

<Table width = "100%"> <tr> <td style = "width: 60%; float: left;"> beginTime: <asp: textBox ID = "txtBeginTime" runat = "server"> </asp: TextBox> endTime: <asp: textBox ID = "txtEndTime" name = "mydate" runat = "server"> </asp: TextBox> </td> <td style = "width: 30%; float: right; "> <asp: button ID = "btnSearch" runat = "server" Text = "Search" OnClick = "btnSearch_Click" class = "ui-button ui-widget ui-state-default ui-corner-all "> </asp: button> <asp: Button ID = "btnAdd" runat = "server" Text = "Create" OnClientClick = "javascript: return false; "/> </td> </tr> <td colspan =" 2 "style =" width: 100%; float: left; "> <asp: gridView ID = "GridView1" runat = "server" Width = "100%" CellPadding = "2" CssClass = "GridViewStyle" CellSpacing = "2" AutoGenerateColumns = "False"> <Columns> <asp: boundField DataField = "name" HeaderText = "name"/> <asp: BoundField DataField = "code" HeaderText = "code"/> <asp: boundField DataField = "beginTime" HeaderText = "beginTime"/> <asp: BoundField DataField = "endTime" HeaderText = "endTime"/> <asp: boundField DataField = "score" HeaderText = "score"/> </Columns> <FooterStyle CssClass = "regular"/> <RowStyle CssClass = "GridViewRowStyle"/> <SelectedRowStyle CssClass =" gridViewSelectedRowStyle "/> <PagerStyle CssClass =" GridViewPagerStyle "/> <AlternatingRowStyle CssClass =" Courier "/> <HeaderStyle CssClass =" GridViewHeaderStyle "/> </asp: gridView> </td> </tr> <td colspan = "2"> <webdiyer: aspNetPager ID = "AspNetPager1" runat = "server" CssClass = "paginator" CurrentPageButtonClass = "ECC" OnPageChanged = "feature" PageSize = "5" FirstPageText = "Homepage" LastPageText = "page "NextPageText =" next page "PrevPageText =" Previous Page "CustomInfoHTML =" % RecordCount % in total, page % CurrentPageIndex %/Total % PageCount % PAGE "minminfosectionwidth =" 30% "ShowCustomInfoSection =" Right "> </webdiyer: aspNetPager> </td> </tr> </table>

4. Of course, you can adjust your gridviewtype and create a new gridviewsy.css

. GridViewStyle {border-right: 2px solid # A7A6AA; border-bottom: 2px solid # A7A6AA; border-left: 2px solid white; border-top: 2px solid white; padding: 4px ;}. gridViewStyle a {color: # FFFFFF ;}. gridViewHeaderStyle th {border-left: 1px solid # EBE9ED; border-right: 1px solid # EBE9ED ;}. gridViewHeaderStyle {background-color: # 5D7B9D; font-weight: bold; color: White ;}. gridViewFooterStyle {background-color: # 5D7B9D; font-weight: bold; color: White ;}. gridViewRowStyle {background-color: # F7F6F3; color: #333333 ;}. gridViewAlternatingRowStyle {background-color: # FFFFFF; color: #284775 ;}. gridViewRowStyle td ,. gridViewAlternatingRowStyle td {border: 1px solid # EBE9ED ;}. gridViewSelectedRowStyle {background-color: # E2DED6; font-weight: bold; color: #333333 ;}. gridViewPagerStyle {background-color: #284775; color: # FFFFFF ;}. gridViewPagerStyle table/* to center the paging links */{margin: 0 auto 0 auto; the page control also adds a style. Of course, the gridview style and the page style are in the same css. paginator {font: 11px Arial, Helvetica, sans-serif; padding: 10px 20px 10px 0; margin: 0px ;}. paginator a {padding: 1px 6px; border: solid 1px # ddd; background: # fff; text-decoration: none; margin-right: 2px }. paginator a: visited {padding: 1px 6px; border: solid 1px # ddd; background: # fff; text-decoration: none ;}. paginator. ECC {padding: 1px 6px; font-weight: bold; font-size: 13px; border: none }. paginator a: hover {color: # fff; background: # ffa501; border-color: # ffa501; text-decoration: none ;}

Display style,

　

Next, we will add a style for the time. Generally, the datePicker plug-in is used to add a style for the time. js and css used to import the control

<script src="jquery-ui-1.9.2.custom/js/jquery-1.8.3.js" type="text/javascript"></script><script src="jquery-ui-1.9.2.custom/development-bundle/ui/jquery.ui.widget.js" type="text/javascript"></script><script src="jquery-ui-1.9.2.custom/development-bundle/ui/jquery.ui.core.js" type="text/javascript"></script><script src="jquery-ui-1.9.2.custom/development-bundle/ui/jquery.ui.datepicker.js" type="text/javascript"></script><link href="jquery-ui-1.9.2.custom/development-bundle/themes/ui-lightness/jquery.ui.all.css" rel="stylesheet" type="text/css" />

The default time plug-in is displayed in English.

Create initdatepicker_cn.js

Function initdatepicker_cn () {$. datepicker. regional ['zh-cn'] = {clearText: 'clear', clearStatus: 'clear selected date', closeText: 'close', closeStatus: 'Do not change current select ', prevText: '<', prevStatus: 'Show previous month', prevBigText: '<', prevBigStatus: 'Show previous year', nextText: 'Next month> ', nextStatus: 'Show next month', nextBigText: '>', nextBigStatus: 'Show next year', currentText: 'day', currentStatus: 'Show this month', monthNames: ['August 1 ', 'octoken ', 'August 1'], monthNamesShort: ['yi', '2', '3', '4', '5', '6', '7', '8 ', '9', '10', '11', '12'], monthStatus: 'select month', yearStatus: 'select year', weekHeader: 'Week', weekStatus: 'Weekly times in year', dayNames: ['sunday', 'monday', 'tuesday', 'wedday', 'thurday', 'saturday'], dayNamesShort: ['sunday', 'monday', 'tuesday', 'wedday', 'thurs', 'Friday'], dayNamesMin: ['day ', '1', '2', '3', '4', '5', '6'], dayStatus: 'dd is set to a week start', dateStatus: 'select m-month-D, dd', dateFormat: 'yy-mm-dd', firstDay: 1, initStatus: 'select date', isRTL: false}; $. datepicker. setDefaults ($. datepicker. regional ['zh-cn']);}

Add a jquery script to the front-end page. Of course, MainContent_txtBeginTime is the id of your time tag. Sometimes you may not be able to display it. Right-click the source file and you will find that the Control id is different from the html Tag id, we must select the tag id.

<script type="text/javascript">  jQuery(function () {   initdatepicker_cn();  $('#MainContent_txtBeginTime').datepicker({ changeMonth: true, changeYear: true }); });</script>

:

If you follow these four steps, a simple and practical page display page will show you in front of you. You are welcome to discuss it.