<asp:TemplateColumn HeaderText="操作"> <HeaderStyle HorizontalAlign="Center" Width="50px"></HeaderStyle> <ItemTemplate> <asp:CheckBox id=ckb_jf runat="server" Checked='<%# DataBinder.Eval(Container.DataItem,"JF") %>' OnCheckedChanged='radio_checkchange' AutoPostBack="true"> </asp:CheckBox> </ItemTemplate> </asp:TemplateColumn> <asp:BoundColumn DataField="id" ReadOnly="True" HeaderText="序号"> //一般情况下,此项同自动添加的. <HeaderStyle Width="50px"></HeaderStyle> </asp:BoundColumn>
二、DataGrid中的CheckBox的全选
private void cmdSelectAll_Click(object sender, System.EventArgs e) //一按扭事件 { System.Web.UI.WebControls.CheckBox chkExport ; if( cmdSelectAll.Text == "全部选中") { foreach(DataGridItem oDataGridItem in dgMain.Items) { chkExport = (CheckBox)oDataGridItem.FindControl("chkExport"); chkExport.Checked = true; } cmdSelectAll.Text = "全部不选"; } else { foreach(DataGridItem oDataGridItem in dgMain.Items) { chkExport = (CheckBox)oDataGridItem.FindControl("chkExport"); chkExport.Checked = false; } cmdSelectAll.Text = "全部选中"; }
}
三、DataGrid中的批量提交
例如：DataGrid中的CheckBox被选中后，进行批量删除操作。
private void cmd_delete_Click(object sender, System.EventArgs e) //一按扭事件 { System.Web.UI.WebControls.CheckBox chkExport ; foreach(DataGridItem oDataGridItem in dgMain.Items) { string strSql="delete from table where id="+DataGrid1.DataKeys[oDataGridItem.ItemIndex]; chkExport = (CheckBox)oDataGridItem.FindControl("chkExport"); if(chkExport.Checked) { doSql(strSql) ; //doSql(string sql) 数据库操作函数 } } } 四、DataGrid中的CheckBox项，选中后自动提交 1、先在于CheckBox中加上自动提交属性，AutoPostBack="true" 2、定义 OnCheckedChanged="CheckBox_checkchange" 事件； protected void CheckBox_checkchange(object sender , System.EventArgs e) { string strSql; CheckBox ckb=(CheckBox)sender; DataGridItem dgi=(DataGridItem)ckb.Parent.Parent; //获最选项的所在行的主键 strSql="update bmk set JF="+ckb.Checked+" where id="+this.dtg_bmk.DataKeys[dgi.ItemIndex]; database.doSql(strSql); // database.doSql(string sql)数据库操作方法； }
五、DataGrid中的自动分页 DataGrid中的确是带有自动分页功能，但为什么还要花时间来做手动分页呢？ 大家都知道，DataGrid中的数据绑定是一下子从数据库中全部取出，存放在服务器内存中。如果是少量的数据，服务还能承受得了，若是海量数据，恐怕是非得把服务器累垮不可!
说到这,应该明白我今天来谈DataGrid分页的目的了,是否有更方便快捷,而更节约资源,减小网络流量的分页方法呢? 下面我们来谈谈,使用存储过程实现DataGrid分页.原创--DataGrid自动分页例子，通过存储过程 通过存储过程来进行DataGrid自动分页，效率很高，可以进行百万和千万级的分页
自己通过50万条记录测试，翻至任何页，需时小于一秒
所需的存储过程如下：
CREATE PROCEDURE UP_GetRecordByPage @tblName varchar(255), -- 表名 @fldName varchar(255), -- 主键字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 1, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)AS
declare @strSQL varchar(6000) -- 主语句declare @strTmp varchar(100) -- 临时变量declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc'endelsebegin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc'end
set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder
if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1begin set @strTmp ='' if @strWhere != '' set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrderend
if @IsReCount != 0 set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere
exec (@strSQL)GO
Windows C# 页面代码
using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;using System.Data.SqlClient;
namespace _0921test{ /** <summary> /// DataGridMostDataDisplay 的摘要说明。 /// </summary> public class DataGridMostDataDisplay : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label2; protected System.Web.UI.WebControls.DataGrid dgShowC; protected System.Web.UI.HtmlControls.HtmlForm Form1; protected System.Web.UI.WebControls.TextBox TextBox1; protected System.Web.UI.WebControls.Button btnGOTO; protected System.Web.UI.WebControls.ImageButton ibtnFirstPage; protected System.Web.UI.WebControls.ImageButton ibtnPrevousPage; protected System.Web.UI.WebControls.ImageButton ibtnNextPage; protected System.Web.UI.WebControls.ImageButton ibtnLastPage; protected System.Web.UI.WebControls.Label Label1; protected System.Data.SqlClient.SqlConnection sqlConnection1;
static int Records = 0; //记录总数 int PageSize = 10; //页大小 static int PageIndex = 1; //当前页 static int PageCount = 0; protected System.Web.UI.WebControls.Label Label3; protected System.Web.UI.WebControls.Label Label4; protected System.Web.UI.WebControls.Label Label5; string strWhere = ""; protected System.Web.UI.WebControls.Button btnBindData; protected System.Web.UI.WebControls.TextBox txtShipAddress; protected System.Web.UI.WebControls.TextBox txtShipName; static string strWhereO = "";
private void BindGridC() { string connectionString = "workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind"; string sqlstr = "SELECT * FROM Test where TID < 1000"; try { using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet ds = new DataSet(); try { connection.Open(); SqlDataAdapter Dta= new SqlDataAdapter(sqlstr,connection); Dta.Fill(ds,"ds"); Dta.Dispose(); this.dgShowC.DataSource = ds; this.dgShowC.VirtualItemCount=5; this.dgShowC.DataBind(); Records = ds.Tables[0].Rows.Count; this.Label1.Text = "共有： "+Records.ToString()+" 记录"; this.Label2.Text = "页数： "+PageIndex+"/"+Records/20; } catch(System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } } } catch(SqlException SQLexc) { Response.Write("提取数据时出现错误：" + SQLexc.ToString()); } }
//分页获取数据列表 public DataSet GetList(int PageSize,int PageIndex,string strWhere) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@IsReCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1000), }; parameters[0].Value = "Test"; parameters[1].Value = "TID"; parameters[2].Value = PageSize; parameters[3].Value = PageIndex; parameters[4].Value = 0; parameters[5].Value = 0; parameters[6].Value = strWhere; return RunProcedure("UP_GetRecordByPage",parameters,"ds"); }
//分页获取数据列表 public DataSet GetListC(int PageSize,int PageIndex,string strWhere) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@IsReCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1000), }; parameters[0].Value = "Test"; parameters[1].Value = "TID"; parameters[2].Value = PageSize; parameters[3].Value = PageIndex; parameters[4].Value = 1; parameters[5].Value = 0; parameters[6].Value = strWhere; return RunProcedure("UP_GetRecordByPage",parameters,"ds"); }
// 创建 SqlCommand 对象实例(用来返回一个整数值) private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters) { SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters ); command.Parameters.Add( new SqlParameter ( "ReturnValue", SqlDbType.Int,4,ParameterDirection.ReturnValue, false,0,0,string.Empty,DataRowVersion.Default,null )); return command; }
/** 执行存储过程，返回影响的行数 /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <param name="rowsAffected">影响的行数</param> /// <returns></returns> public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected ) { string connectionString = "workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind"; using (SqlConnection connection = new SqlConnection(connectionString)) { int result; connection.Open(); SqlCommand command = BuildIntCommand(connection,storedProcName, parameters ); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; //Connection.Close(); return result; } }
// 执行存储过程 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName ) { string connectionString = "workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind"; using (SqlConnection connection = new SqlConnection(connectionString)) { DataSet dataSet = new DataSet(); connection.Open(); SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters ); sqlDA.Fill( dataSet, tableName ); connection.Close(); return dataSet; } }
// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand( storedProcName, connection ); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { command.Parameters.Add( parameter ); } return command; }
//可以得到@@RowCount public static object GetSingle(string SQLString) { string connectionString = "workstation id=WANGSZ;packet size=4096;integrated security=SSPI;data source=WANGSZ;persist security info=False;initial catalog=Northwind"; using (SqlConnection connection = new SqlConnection(connectionString)) { using(SqlCommand cmd = new SqlCommand(SQLString,connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value))) { return null; } else { return obj; } } catch(System.Data.SqlClient.SqlException e) { connection.Close(); throw new Exception(e.Message); } } } }
//根据存储过程绑定 private void BindGridStore() { strWhere = " ShipName like "+"'%"+ this.txtShipName.Text +"%' and ShipAddress like "+"'%"+ this.txtShipAddress.Text +"%'"; try { try { DataSet ds = GetList(PageSize,PageIndex,strWhere);
this.dgShowC.DataSource=ds.Tables[0].DefaultView; this.dgShowC.DataBind(); if(strWhere != strWhereO) { DataSet dsrc = GetListC(PageSize,PageIndex,strWhere); if(dsrc.Tables[0].Columns.Count==1) { Records = Convert.ToInt32(dsrc.Tables[0].Rows[0][0].ToString()); ; this.Label1.Text = "总记录数：" + Records.ToString(); } strWhereO = strWhere; } PageCount = Records/10; this.Label2.Text = "当前页数："+PageIndex+"/"+PageCount; } catch(System.Data.SqlClient.SqlException ex) { throw new Exception(ex.Message); } } catch(SqlException SQLexc) { Response.Write("提取数据时出现错误：" + SQLexc.ToString()); }
} private void dgShowC_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e) { this.dgShowC.CurrentPageIndex = e.NewPageIndex; PageIndex = e.NewPageIndex+1; this.BindGridC(); }
//控制四个翻页按钮的显示 private void ibtnVisible(bool first,bool previous,bool next,bool last) { this.ibtnFirstPage.Visible=first; this.ibtnPrevousPage.Visible=previous; this.ibtnNextPage.Visible=next; this.ibtnLastPage.Visible=last; } private void btnBindData_Click(object sender, System.EventArgs e) { //this.BindGridC(); this.BindGridStore(); PageIndex = 1; this.ibtnVisible(false,false,true,true); }
private void dgShowC_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { //鼠标移动到每项时颜色交替效果 if (e.Item.ItemType!=ListItemType.Header) { e.Item.Attributes.Add("OnMouseOut", "this.style.backgroundColor='Transparent';this.style.color='Black'"); e.Item.Attributes.Add("OnMouseOver", "this.style.backgroundColor='#cacee1';this.style.color='Blue'"); } //鼠标的形状为小手 e.Item.Attributes["style"] = "Cursor:hand"; }
private void ibtnFirstPage_Click(object sender, System.Web.UI.ImageClickEventArgs e) { this.ibtnVisible(false,false,true,true); PageIndex = 1; this.BindGridStore(); }
private void ibtnPrevousPage_Click(object sender, System.Web.UI.ImageClickEventArgs e) { if(PageIndex == 2) { this.ibtnVisible(false,false,true,true); PageIndex = 1; this.BindGridStore(); } else { this.ibtnVisible(true,true,true,true); PageIndex = PageIndex -1; this.BindGridStore(); } }
private void ibtnNextPage_Click(object sender, System.Web.UI.ImageClickEventArgs e) { if(PageIndex == Records/10-1) this.ibtnVisible(true,true,false,false); else this.ibtnVisible(true,true,true,true); PageIndex = PageIndex + 1; this.BindGridStore(); }
private void ibtnLastPage_Click(object sender, System.Web.UI.ImageClickEventArgs e) { this.ibtnVisible(true,true,false,false); PageIndex = PageCount; this.BindGridStore(); }
private void dgShowC_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { if(e.Item.ItemType == ListItemType.Pager) { foreach (Control c in e.Item.Cells[0].Controls) { if (c is Label) //当前页数 { Label lblpage=(Label)c; // lblpage.ForeColor= System.Drawing.ColorTranslator.FromHtml("#e78a29"); //#e78a29 ,#FF0000 // lblpage.Font.Bold=true; lblpage.Text="[<font color=#e78a29><b>"+lblpage.Text+"</b></font>]"; //((Label)c).ForeColor = System.Drawing.Color.Green; // break; } if(c is LinkButton) //链接的其他页数 { LinkButton linkButton = (LinkButton)c; linkButton.Text = "[" + linkButton.Text+"]"; } } } }
//跳转页面 private void btnGOTO_Click(object sender, System.EventArgs e) { PageIndex = System.Convert.ToInt32(this.TextBox1.Text); this.BindGridStore(); this.TextBox1.Text = ""; }
private void btnBindData_Click(object sender, System.EventArgs e) { }
转载于:https://www.cnblogs.com/feima-lxl/archive/2010/05/28/1746708.html
相关资源：数据结构—成绩单生成器