//
目前功能:编辑、排序、单选项、复选项、合并单元格、产生序列号、联动下拉框、删除、超连接、鼠标移动改变颜色、奇偶项颜色区分、排序、分页
1.html代码
<
HTML
>
<
HEAD
>
<
title
>
DatagridTest
</
title
>
<
meta
content
="Microsoft Visual Studio .NET 7.1"
name
="GENERATOR"
>
<
meta
content
="C#"
name
="CODE_LANGUAGE"
>
<
meta
content
="JavaScript"
name
="vs_defaultClientScript"
>
<
meta
content
="http://schemas.microsoft.com/intellisense/ie5"
name
="vs_targetSchema"
>
<
script
language
="javascript"
>
function fun_option(obj) { document.Form1.hiduserid.value = obj.value; } function chkAll_true() { var chkall = document.all[ " chkAll " ]; var chkother = document.getElementsByTagName( " input " ); for ( var i = 0 ;i < chkother.length;i ++ ) { if ( chkother[i].type == 'checkbox') { if (chkother[i].id.indexOf('chkExport') >- 1 ) { if (chkall.checked == true ) { chkother[i].checked = true ; } else { chkother[i].checked = false ; } } } } } function Delete() { var al = new Array(); var chkother = document.getElementsByTagName( " input " ); for ( var i = 0 ,j = 0 ;i < chkother.length;i ++ ) { if ( chkother[i].type == 'checkbox') { if (chkother[i].id.indexOf('chkExport') >- 1 ) { if (chkother[i].checked == true ) { al[j] = chkother[i].value; j ++ ; } } } } if (al == "" ) { alert('You need to select a row in the list before selecting Delete./r/nPlease select a row and try again.'); } else if (window.confirm( " Are you sure to delete all these? " )) { var deleteID = ' <%= btndelete.ClientID %> '; window.document.all(deleteID).click(); } else { return false; } }
</
script
>
</
HEAD
>
<
body
>
<
form
id
="Form1"
method
="post"
runat
="server"
>
<
TABLE
id
="Table1"
height
="100%"
cellSpacing
="1"
cellPadding
="1"
width
="100%"
border
="1"
>
<
tr
>
<
td
><
asp:dropdownlist
id
="DropDownList1"
runat
="server"
AutoPostBack
="True"
></
asp:dropdownlist
><
asp:dropdownlist
id
="DropDownList2"
runat
="server"
AutoPostBack
="True"
></
asp:dropdownlist
><
asp:dropdownlist
id
="DropDownList3"
runat
="server"
></
asp:dropdownlist
><
asp:textbox
id
="TextBox1"
runat
="server"
></
asp:textbox
><
asp:checkbox
id
="CheckBox1"
runat
="server"
Text
="可见否"
></
asp:checkbox
><
asp:button
id
="Button1"
runat
="server"
Text
="保存"
></
asp:button
><
asp:button
id
="Button2"
runat
="server"
Text
="导出Excel"
></
asp:button
></
td
>
</
tr
>
<
tr
height
="30"
>
<
td
><
INPUT
id
="chkAll"
onclick
="chkAll_true()"
type
="checkbox"
>
全选/取消
<
asp:button
id
="btndelete"
runat
="server"
Text
="删除"
></
asp:button
></
td
>
</
tr
>
<
TR
vAlign
="top"
>
<
TD
width
="100%"
colSpan
="5"
><
asp:datagrid
id
="DataGrid1"
runat
="server"
OnDeleteCommand
="Delete"
ForeColor
="Black"
GridLines
="None"
CellPadding
="2"
BackColor
="LightGoldenrodYellow"
BorderWidth
="1px"
BorderColor
="Tan"
AllowSorting
="True"
AutoGenerateColumns
="False"
DataKeyField
="UserID"
OnUpdateCommand
="Update"
OnCancelCommand
="Cancel"
OnEditCommand
="Edit"
PageSize
="5"
AllowPaging
="True"
>
<
FooterStyle
BackColor
="Tan"
></
FooterStyle
>
<
SelectedItemStyle
ForeColor
="GhostWhite"
BackColor
="DarkSlateBlue"
></
SelectedItemStyle
>
<
AlternatingItemStyle
BackColor
="PaleGoldenrod"
></
AlternatingItemStyle
>
<
HeaderStyle
Font-Bold
="True"
BackColor
="Tan"
></
HeaderStyle
>
<
Columns
>
<
asp:TemplateColumn
HeaderText
="序号"
>
<
HeaderStyle
Width
="10px"
></
HeaderStyle
>
<
ItemTemplate
>
<
INPUT
id
=radio
onclick
=fun_option(this)
type
=radio
value
='<%#
DataBinder.Eval(Container.DataItem,"UserID") %
>
' name=radio>
</
ItemTemplate
>
<
FooterTemplate
>
</
FooterTemplate
>
</
asp:TemplateColumn
>
<
asp:TemplateColumn
HeaderText
="序号"
>
<
HeaderStyle
Width
="10px"
></
HeaderStyle
>
<
FooterTemplate
>
</
FooterTemplate
>
</
asp:TemplateColumn
>
<
asp:TemplateColumn
>
<
HeaderStyle
Width
="10px"
></
HeaderStyle
>
<
ItemTemplate
>
<
asp:CheckBox
id
="chkExport"
Runat
="server"
></
asp:CheckBox
>
</
ItemTemplate
>
</
asp:TemplateColumn
>
<
asp:BoundColumn
DataField
="UserID"
SortExpression
="UserID"
ReadOnly
="True"
HeaderText
="UserID"
>
<
HeaderStyle
Width
="70px"
></
HeaderStyle
>
</
asp:BoundColumn
>
<
asp:TemplateColumn
SortExpression
="UserName"
HeaderText
="姓名"
>
<
HeaderStyle
Width
="70px"
></
HeaderStyle
>
<
ItemTemplate
>
<
a
href
='form1.aspx?id=<%#
DataBinder.Eval(Container.DataItem,"UserID") %
>
&name
=
<%
# DataBinder.Eval(Container.DataItem, " UserName " )
%>
' target="_blank">
<%
# DataBinder.Eval(Container.DataItem, " UserName " )
%>
</
a
>
</
ItemTemplate
>
<
EditItemTemplate
>
<
asp:TextBox
id
=UserName
Text
='<%#
DataBinder.Eval(Container.DataItem,"UserName") %
>
' Width="100%" Runat="server">
</
asp:TextBox
>
</
EditItemTemplate
>
</
asp:TemplateColumn
>
<
asp:TemplateColumn
SortExpression
="province"
HeaderText
="省市"
>
<
HeaderStyle
Width
="70px"
></
HeaderStyle
>
<
ItemTemplate
>
<%
# DataBinder.Eval(Container.DataItem, " province " )
%>
</
ItemTemplate
>
<
EditItemTemplate
>
<
asp:DropDownList
id
="province"
Width
="100%"
Runat
="server"
AutoPostBack
="True"
OnSelectedIndexChanged
="ddlprovince_SelectedIndexChanged"
></
asp:DropDownList
>
</
EditItemTemplate
>
</
asp:TemplateColumn
>
<
asp:TemplateColumn
SortExpression
="city"
HeaderText
="县市"
>
<
HeaderStyle
Width
="70px"
></
HeaderStyle
>
<
ItemTemplate
>
<%
# DataBinder.Eval(Container.DataItem, " city " )
%>
</
ItemTemplate
>
<
EditItemTemplate
>
<
asp:DropDownList
id
="city"
Runat
="server"
Width
="100%"
AutoPostBack
="True"
OnSelectedIndexChanged
="ddlcity_SelectedIndexChanged"
></
asp:DropDownList
>
</
EditItemTemplate
>
</
asp:TemplateColumn
>
<
asp:TemplateColumn
SortExpression
="area"
HeaderText
="乡镇"
>
<
HeaderStyle
Width
="70px"
></
HeaderStyle
>
<
ItemTemplate
>
<%
# DataBinder.Eval(Container.DataItem, " area " )
%>
</
ItemTemplate
>
<
EditItemTemplate
>
<
asp:DropDownList
id
="area"
Runat
="server"
Width
="100%"
></
asp:DropDownList
>
</
EditItemTemplate
>
</
asp:TemplateColumn
>
<
asp:TemplateColumn
SortExpression
="Enabled"
HeaderText
="可见否"
>
<
HeaderStyle
Width
="70px"
></
HeaderStyle
>
<
ItemTemplate
>
<%
# DataBinder.Eval(Container.DataItem, " Enabled " )
%>
</
ItemTemplate
>
<
EditItemTemplate
>
<
asp:CheckBox
id
=chkenabled
Runat
="server"
Checked
='<%#
DataBinder.Eval(Container.DataItem,"Enabled") %
>
'>
</
asp:CheckBox
>
</
EditItemTemplate
>
</
asp:TemplateColumn
>
<
asp:EditCommandColumn
ButtonType
="PushButton"
UpdateText
="更新"
HeaderText
="编辑"
CancelText
="取消"
EditText
="编辑"
></
asp:EditCommandColumn
>
<
asp:ButtonColumn
Text
="删除"
ButtonType
="PushButton"
HeaderText
="删除"
CommandName
="Delete"
></
asp:ButtonColumn
>
</
Columns
>
<
PagerStyle
NextPageText
="下一页"
PrevPageText
="上一页"
HorizontalAlign
="Right"
ForeColor
="DarkSlateBlue"
BackColor
="PaleGoldenrod"
Mode
="NumericPages"
></
PagerStyle
>
</
asp:datagrid
></
TD
>
</
TR
>
<
tr
height
="30"
>
<
td
></
td
>
</
tr
>
</
TABLE
>
<
INPUT
id
="hiduserid"
type
="hidden"
runat
="server"
>
</
form
>
</
body
>
</
HTML
>
2.cs代码
using
System.Data.SqlClient;
using
System.Text;
public
class
DatagridTest : System.Web.UI.Page
{ protected System.Web.UI.WebControls.DataGrid DataGrid1; protected System.Web.UI.HtmlControls.HtmlInputHidden hiduserid; protected System.Web.UI.WebControls.Button btndelete; protected System.Web.UI.WebControls.DropDownList DropDownList1; protected System.Web.UI.WebControls.DropDownList DropDownList2; protected System.Web.UI.WebControls.DropDownList DropDownList3; protected System.Web.UI.WebControls.CheckBox CheckBox1; protected System.Web.UI.WebControls.TextBox TextBox1; protected System.Web.UI.WebControls.Button Button1; protected System.Web.UI.WebControls.Button Button2; private string constring = "" ; private void Page_Load( object sender, System.EventArgs e) { if ( ! Page.IsPostBack) { ListProvince(); ListCity(); ListArea(); btndelete.Attributes.Add( " onclick " , "return Delete(); " ); SortExpression = "" ; sort = " desc " ; DataBind(); } } DataBind #region DataBind private void DataBind() { string sql = " select a.UserID,a.UserName,b.province,c.city,d.area,a.Enabled,b.provinceID,c.cityID,d.areaID from testgrid a " ; sql += " left join province b on a.provinceID=b.provinceID " ; sql += " left join city c on a.cityID=c.cityID " ; sql += " left join area d on a.areaID=d.areaID " ; DataSet ds = GetDataSet(sql); dt = ds.Tables[ 0 ]; dt.DefaultView.Sort = " UserID asc " ; dt.AcceptChanges(); this .DataGrid1.DataSource = dt; this .DataGrid1.DataBind(); } #endregion Web Form Designer generated code #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base .OnInit(e); this .DataGrid1.ItemDataBound += new DataGridItemEventHandler(DataGrid1_ItemDataBound); this .DataGrid1.SortCommand += new DataGridSortCommandEventHandler(DataGrid1_SortCommand); this .DataGrid1.ItemCreated += new DataGridItemEventHandler(DataGrid1_ItemCreated); this .DataGrid1.PageIndexChanged += new DataGridPageChangedEventHandler(DataGrid1_PageIndexChanged); this .btndelete.Click += new System.EventHandler( this .btndelete_Click); this .DropDownList1.SelectedIndexChanged += new System.EventHandler( this .DropDownList1_SelectedIndexChanged); this .DropDownList2.SelectedIndexChanged += new System.EventHandler( this .DropDownList2_SelectedIndexChanged); this .Button1.Click += new System.EventHandler( this .Button1_Click); this .Button2.Click += new System.EventHandler( this .Button2_Click); } /**/ /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this .DropDownList1.SelectedIndexChanged += new System.EventHandler( this .DropDownList1_SelectedIndexChanged); this .DropDownList2.SelectedIndexChanged += new System.EventHandler( this .DropDownList2_SelectedIndexChanged); this .Button1.Click += new System.EventHandler( this .Button1_Click); this .Button2.Click += new System.EventHandler( this .Button2_Click); this .Load += new System.EventHandler( this .Page_Load); } #endregion GetDataSet #region GetDataSet private DataSet GetDataSet( string sql) { constring = System.Configuration.ConfigurationSettings.AppSettings[ " ConnectionString " ]; SqlDataAdapter sda = new SqlDataAdapter(sql,constring); DataSet ds = new DataSet(); sda.Fill(ds); return ds; } #endregion DataGrid1_ItemDataBound #region DataGrid1_ItemDataBound private void DataGrid1_ItemDataBound( object sender, DataGridItemEventArgs e) { e.Item.Cells[ 1 ].Text = Convert.ToString(e.Item.ItemIndex + 1 ); // 产生序号 if (e.Item.ItemType != ListItemType.Header) { if (e.Item.ItemIndex % 2 == 0 ) { e.Item.Attributes.Add( " onmouseout " , " this.style.backgroundColor='PaleGoldenrod' " ); } else { e.Item.Attributes.Add( " onmouseout " , " this.style.backgroundColor='LightGoldenrodYellow' " ); } e.Item.Attributes.Add( " onmouseover " , " this.style.backgroundColor='#C1D2EE' " ); } switch (e.Item.ItemType) { case ListItemType.Item: case ListItemType.AlternatingItem: e.Item.Attributes.Add( " ondblclick " , " alert(' " + e.Item.ItemIndex + " '); " ); break ; case ListItemType.Header: e.Item.Cells[ 0 ].ColumnSpan = 2 ; // 合并单元格 e.Item.Cells[ 1 ].Visible = false ; break ; case ListItemType.EditItem: for ( int i = 4 ; i < DataGrid1.Columns.Count - 2 ; i ++ ) // 只调整被编辑的列 { if (e.Item.ItemType == ListItemType.EditItem) { e.Item.Cells[i].Attributes.Add( " Width " , " 70px " ); } } string sqlprovince = " select * from province " ; DataSet dsprovince = GetDataSet(sqlprovince); // 一级 if (e.Item.ItemType == ListItemType.EditItem) { DropDownList ddlprovince = (DropDownList)e.Item.FindControl( " province " ); ddlprovince.DataSource = dsprovince; ddlprovince.DataTextField = " province " ; ddlprovince.DataValueField = " provinceID " ; ddlprovince.DataBind(); ddlprovince.Items.FindByValue(Convert.ToString(DataBinder.Eval(e.Item.DataItem, " provinceID " ))).Selected = true ; // 二级 if (e.Item.ItemType == ListItemType.EditItem) { if (ddlprovince.SelectedIndex !=- 1 ) { string sqlcity = " select * from city where father=' " + ddlprovince.SelectedValue + " ' " ; DropDownList ddlcity = (DropDownList)e.Item.FindControl( " city " ); DataSet dscity = GetDataSet(sqlcity); ddlcity.DataSource = dscity; ddlcity.DataTextField = " city " ; ddlcity.DataValueField = " cityID " ; ddlcity.DataBind(); ddlcity.Items.FindByValue(Convert.ToString(DataBinder.Eval(e.Item.DataItem, " cityID " ))).Selected = true ; // 三级 if (e.Item.ItemType == ListItemType.EditItem) { if (ddlcity.SelectedIndex !=- 1 ) { string sqlarea = " select * from area where father=' " + ddlcity.SelectedValue + " ' " ; DropDownList ddlarea = (DropDownList)e.Item.FindControl( " area " ); DataSet dsarea = GetDataSet(sqlarea); ddlarea.DataSource = dsarea; ddlarea.DataTextField = " area " ; ddlarea.DataValueField = " areaID " ; ddlarea.DataBind(); ddlarea.Items.FindByValue(Convert.ToString(DataBinder.Eval(e.Item.DataItem, " areaID " ))).Selected = true ; } } } } } break ; default : break ; } } #endregion DataGrid1_SortCommand #region DataGrid1_SortCommand private void DataGrid1_SortCommand( object source, DataGridSortCommandEventArgs e) { if (SortExpression == e.SortExpression.ToString()) { if (sort == " asc " ) { sort = " desc " ; } else { sort = " asc " ; } } else { if (sort == " asc " ) { sort = " desc " ; } else { sort = " asc " ; } } SortExpression= e.SortExpression; dt.DefaultView.Sort = e.SortExpression + " " + sort; DataGrid1.DataSource = dt; DataGrid1.DataBind(); } #endregion DataGrid1_ItemCreated #region DataGrid1_ItemCreated private void DataGrid1_ItemCreated( object sender, DataGridItemEventArgs e) { for ( int i = 3 ; i < DataGrid1.Columns.Count - 2 ; i ++ ) // -2的意思是两个button列不用排序 { if (i == 0 ) continue ; if (e.Item.ItemIndex ==- 1 ) { if (DataGrid1.Columns[i].SortExpression == SortExpression) { try { TableCell tableCell = e.Item.Cells[i]; Label label = new Label(); label.Font.Name = " webdings " ; if (sort == " asc " ) { label.Text = " 6 " ; } else { label.Text = " 5 " ; } label.Width = 20 ; tableCell.Controls.Add(label); } catch {} } } } foreach (DataGridItem di in this .DataGrid1.Items) { if (di.ItemType == ListItemType.Item || di.ItemType == ListItemType.AlternatingItem) { ((Button)di.Cells[ 10 ].Controls[ 0 ]).Attributes.Add( " onclick " , " return confirm('确认删除此项吗?'); " ); } } } #endregion property #region property private DataTable dt { get { if (ViewState[ " dt " ] != null ) { return (DataTable)ViewState[ " dt " ]; } else { return null ; } } set { ViewState[ " dt " ] = value; } } private string SortExpression { get { if (ViewState[ " SortExpression " ] != null && ViewState[ " SortExpression " ].ToString() != string .Empty) { return ViewState[ " SortExpression " ].ToString(); } else { return "" ; } } set { ViewState[ " SortExpression " ] = value; } } private string sort { get { if (ViewState[ " sort " ] != null && ViewState[ " sort " ].ToString() != string .Empty) { return ViewState[ " sort " ].ToString(); } else { return "" ; } } set { ViewState[ " sort " ] = value; } } #endregion Edit #region Edit protected void Edit( object sender,DataGridCommandEventArgs e) { this .DataGrid1.EditItemIndex = e.Item.ItemIndex; DataBind(); } #endregion Cancel #region Cancel protected void Cancel( object sender,DataGridCommandEventArgs e) { this .DataGrid1.EditItemIndex =- 1 ; DataBind(); } #endregion Update #region Update protected void Update( object sender,DataGridCommandEventArgs e) { if (e.Item.ItemType == ListItemType.EditItem) { SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[ " ConnectionString " ]); SqlCommand comm = new SqlCommand( " update testgrid set UserName=@UserName,provinceID=@province,cityID=@city,areaID=@area,Enabled=@Enabled where UserID=@UserID " ,conn); SqlParameter parm1 = new SqlParameter( " @UserName " ,SqlDbType.NVarChar, 50 ); parm1.Value = ((TextBox)e.Item.FindControl( " UserName " )).Text; SqlParameter parm2 = new SqlParameter( " @province " ,SqlDbType.NVarChar, 50 ); parm2.Value = ((DropDownList)e.Item.FindControl( " province " )).SelectedValue; SqlParameter parm3 = new SqlParameter( " @city " ,SqlDbType.NVarChar, 50 ); parm3.Value = ((DropDownList)e.Item.FindControl( " city " )).SelectedValue; SqlParameter parm4 = new SqlParameter( " @area " ,SqlDbType.NVarChar, 50 ); parm4.Value = ((DropDownList)e.Item.FindControl( " area " )).SelectedValue; SqlParameter parm5 = new SqlParameter( " @Enabled " ,SqlDbType.Bit); parm5.Value = ((CheckBox)e.Item.FindControl( " chkenabled " )).Checked; SqlParameter parm6 = new SqlParameter( " @UserID " ,SqlDbType.Int); parm6.Value = this .DataGrid1.DataKeys[e.Item.ItemIndex]; comm.Parameters.Add(parm1); comm.Parameters.Add(parm2); comm.Parameters.Add(parm3); comm.Parameters.Add(parm4); comm.Parameters.Add(parm5); comm.Parameters.Add(parm6); conn.Open(); comm.ExecuteNonQuery(); conn.Close(); this .DataGrid1.EditItemIndex =- 1 ; DataBind(); } } #endregion Delete event #region Delete event protected void Delete( object sender,DataGridCommandEventArgs e) { delete( this .DataGrid1.DataKeys[e.Item.ItemIndex].ToString()); } #endregion ddlprovince_SelectedIndexChanged #region ddlprovince_SelectedIndexChanged public void ddlprovince_SelectedIndexChanged( object sender, EventArgs e) { DropDownList ddlprovince = sender as DropDownList; if (ddlprovince.SelectedIndex !=- 1 ) { DropDownList ddlcity = ((Control)(((Control)sender)).Parent).FindControl( " city " ) as DropDownList; string sqlcity = " select * from city where father=' " + ddlprovince.SelectedValue + " ' " ; DataSet dscity = GetDataSet(sqlcity); ddlcity.DataSource = dscity; ddlcity.DataTextField = " city " ; ddlcity.DataValueField = " cityID " ; ddlcity.DataBind(); } } #endregion ddlcity_SelectedIndexChanged #region ddlcity_SelectedIndexChanged public void ddlcity_SelectedIndexChanged( object sender, EventArgs e) { DropDownList ddlcity = sender as DropDownList; if (ddlcity.SelectedIndex !=- 1 ) { DropDownList ddlarea = ((Control)(((Control)sender)).Parent).FindControl( " city " ) as DropDownList; string sqlarea = " select * from area where father=' " + ddlcity.SelectedValue + " ' " ; DataSet dsarea = GetDataSet(sqlarea); ddlarea.DataSource = dsarea; ddlarea.DataTextField = " area " ; ddlarea.DataValueField = " areaID " ; ddlarea.DataBind(); } } #endregion btndelete_Click #region btndelete_Click private void btndelete_Click( object sender, System.EventArgs e) { StringBuilder s = new StringBuilder(); foreach (DataGridItem di in DataGrid1.Items) { if (((CheckBox)(di.FindControl( " chkExport " ))).Checked == true ) { s.Append( " , " ).Append(DataGrid1.DataKeys[di.ItemIndex].ToString()); } } if (s.ToString().StartsWith( " , " ) == true ) { delete(s.ToString().Substring( 1 ,s.ToString().Length - 1 )); } } #endregion delete #region delete private void delete( string id) { SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[ " ConnectionString " ]); string sql = @" declare @sql nvarchar(400) set @sql = 'delete from testgrid where UserID in('+@UserID+')' exec( @sql) " ; SqlCommand comm = new SqlCommand(sql,conn); SqlParameter parm1 = new SqlParameter( " @UserID " ,SqlDbType.VarChar, 200 ); parm1.Value = id; comm.Parameters.Add(parm1); conn.Open(); comm.ExecuteNonQuery(); conn.Close(); DataBind(); } #endregion ListProvince #region ListProvince private void ListProvince() { string sql = " select * from province " ; DataSet ds = GetDataSet(sql); DropDownList1.DataSource = ds; DropDownList1.DataTextField = " province " ; DropDownList1.DataValueField = " provinceID " ; DropDownList1.DataBind(); } #endregion ListCity #region ListCity private void ListCity() { if (DropDownList1.SelectedIndex !=- 1 ) { string sql = " select * from city where father=' " + DropDownList1.SelectedValue + " ' " ; DataSet ds = GetDataSet(sql); DropDownList2.DataSource = ds; DropDownList2.DataTextField = " city " ; DropDownList2.DataValueField = " cityID " ; DropDownList2.DataBind(); } } #endregion ListArea #region ListArea private void ListArea() { if (DropDownList2.SelectedIndex !=- 1 ) { string sql = " select * from area where father=' " + DropDownList2.SelectedValue + " ' " ; DataSet ds = GetDataSet(sql); DropDownList3.DataSource = ds; DropDownList3.DataTextField = " area " ; DropDownList3.DataValueField = " areaID " ; DropDownList3.DataBind(); } } #endregion save #region save private void Button1_Click( object sender, System.EventArgs e) { SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[ " ConnectionString " ]); SqlCommand comm = new SqlCommand( " insert into testgrid (UserName,provinceID,cityID,areaID,Enabled) values(@UserName,@provinceID,@cityID,@areaID,@Enabled) " ,conn); SqlParameter parm1 = new SqlParameter( " @UserName " ,SqlDbType.NVarChar, 50 ); parm1.Value = this .TextBox1.Text; SqlParameter parm2 = new SqlParameter( " @provinceID " ,SqlDbType.NVarChar, 50 ); parm2.Value = this .DropDownList1.SelectedValue; SqlParameter parm3 = new SqlParameter( " @cityID " ,SqlDbType.NVarChar, 50 ); parm3.Value = this .DropDownList2.SelectedValue; SqlParameter parm4 = new SqlParameter( " @areaID " ,SqlDbType.NVarChar, 50 ); parm4.Value = this .DropDownList3.SelectedValue; SqlParameter parm5 = new SqlParameter( " @Enabled " ,SqlDbType.Bit); parm5.Value = this .CheckBox1.Checked; comm.Parameters.Add(parm1); comm.Parameters.Add(parm2); comm.Parameters.Add(parm3); comm.Parameters.Add(parm4); comm.Parameters.Add(parm5); conn.Open(); comm.ExecuteNonQuery(); conn.Close(); DataBind(); } #endregion private void DropDownList1_SelectedIndexChanged( object sender, System.EventArgs e) { ListCity(); } private void DropDownList2_SelectedIndexChanged( object sender, System.EventArgs e) { ListArea(); } private void Button2_Click( object sender, System.EventArgs e) { ToExcel( this .DataGrid1, " meng " ); } ToExcel #region ToExcel public static void ToExcel(System.Web.UI.Control ctl, string FileName) { HttpContext.Current.Response.Charset = " UTF-8 " ; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = " application/ms-excel " ; HttpContext.Current.Response.AppendHeader( " Content-Disposition " , " attachment;filename= " + "" + FileName + " .xls " ); ctl.Page.EnableViewState = false ; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); } #endregion private void DataGrid1_PageIndexChanged( object source, DataGridPageChangedEventArgs e) { this .DataGrid1.CurrentPageIndex = e.NewPageIndex; DataBind(); } }
3. 数据库教本(或下载真实数据/Files/singlepine/area.rar )
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[TestGrid]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
drop
table
[
dbo
]
.
[
TestGrid
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[province]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
drop
table
[
dbo
]
.
[
province
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[city]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
drop
table
[
dbo
]
.
[
city
]
GO
if
exists
(
select
*
from
dbo.sysobjects
where
id
=
object_id
(N
'
[dbo].[area]
'
)
and
OBJECTPROPERTY
(id, N
'
IsUserTable
'
)
=
1
)
drop
table
[
dbo
]
.
[
area
]
GO
CREATE
TABLE
[
dbo
]
.
[
TestGrid
]
(
[
UserID
]
[
int
]
IDENTITY
(
1
,
1
)
NOT
NULL
,
[
UserName
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
provinceID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
cityID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
areaID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
Enabled
]
[
bit
]
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
province
]
(
[
id
]
[
int
]
NOT
NULL
,
[
provinceID
]
[
nvarchar
]
(
6
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
province
]
[
nvarchar
]
(
40
) COLLATE Chinese_PRC_CI_AS
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
city
]
(
[
id
]
[
int
]
NOT
NULL
,
[
cityID
]
[
nvarchar
]
(
6
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
city
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
father
]
[
nvarchar
]
(
6
) COLLATE Chinese_PRC_CI_AS
NULL
)
ON
[
PRIMARY
]
GO
CREATE
TABLE
[
dbo
]
.
[
area
]
(
[
id
]
[
int
]
NOT
NULL
,
[
areaID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
area
]
[
nvarchar
]
(
60
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
father
]
[
nvarchar
]
(
6
) COLLATE Chinese_PRC_CI_AS
NULL
)
ON
[
PRIMARY
]
GO
4.源代码下载/Files/singlepine/datagrid.rar