需求：设计这样一个页面，在页面上可以自由选择和展示各省份下城市？
思路：一次性查询出所需的记录（查询数据库的操作不宜写到 C# 代码的循环语句中），并保存到全局变量中，之后根据条件过滤出需要的。可以在 WebForm 页面中，添加相应的 ASP.NET 服务器控件：GridView 和 CheckBoxList 来实现，只需绑定相应的数据即可。
前台页面：
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ProvinceCityConfig.aspx.cs" Inherits="ProvinceCityConfig"%> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>选择各省份下的城市</title> <link rel="stylesheet" type="text/css" href="../css/basic.css" /> <script src="../jQuery/jquery-1.5.1.js" type="text/javascript"></script> <script language="javascript" type="text/javascript"> $(function () { //表格隔行变色 $("table.queryList_font12 th").css("background", "#f2f2f2"); $("table.queryList_font12 .item_td_center:even").css("background", "#f0fafa"); $("table.queryList_font12 .item_td:even").css("background", "#f0fafa"); }); </script> </head> <body> <form id="form1" runat="server"> <dl class="si_info"> <dd class="marginleft24"> <dl> <dt>请选择各省份下的城市</dt> </dl> </dd> </dl> <div class="content"> <div style="border: 1px solid silver; max-height: 400px; overflow-y: auto; overflow-x: hidden;" id="divList" runat="server"> <asp:GridView ID="gvList" runat="server" Width="100%" Height="100%" DataKeyNames="PROVINCE_SEQ"
OnRowDataBound="gvItem_RowCommand" CssClass="queryList_font12 mytable" BorderWidth="1px" AutoGenerateColumns="False"> <HeaderStyle CssClass="gv_header" /> <Columns> <asp:TemplateField HeaderText="省份"> <HeaderStyle CssClass="header_th_center" Wrap="False" Width="12%" Font-Size="12px" /> <ItemStyle CssClass="item_td_center" Width="15%" /> <ItemTemplate> <asp:Label ID="province" runat="server" ToolTip='<%#Eval("PROVINCE_SEQ") %>'
Text='<%# Eval("PROVINCE_CODE") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="城市"> <HeaderStyle CssClass="header_th_center" Wrap="False" Width="85%" Font-Size="12px" /> <ItemStyle CssClass="item_td" Wrap="False" Width="85%" /> <ItemTemplate> <asp:CheckBoxList ID="city" runat="server" RepeatLayout="Table" RepeatDirection="Horizontal" RepeatColumns="10" ></asp:CheckBoxList> </ItemTemplate> </asp:TemplateField> </Columns> <EmptyDataTemplate /> <PagerSettings Visible="False" /> <EmptyDataRowStyle HorizontalAlign="Center" /> </asp:GridView> </div> <table class="inputlist_table" border="0" cellspacing="0" cellpadding="0" style=" 100%;"> <tr> <td align="center"> <asp:Button ID="btnSave" runat="server" Text="保存"OnClick="btnSave_Click" CssClass="ok" /> </td> </tr> </table> </div> </form> </body> </html>
后台代码文件：
using System; using System.Collections.Generic; using System.Web.UI.WebControls; using System.Data;
using System.Data.Common; public partial class ProvinceCityConfig : PageBase { #region Fields private ProvinceCityRelation provinceCitySevice = new ProvinceCityRelation(); protected log4net.ILog log = log4net.LogManager.GetLogger("ExceptionLogger"); private DataSet allCityCache = new DataSet(); private DataSet selectCityCache = new DataSet(); #endregion Fields #region Events protected void Page_Load(object sender, EventArgs e) { Response.Expires = -1; try { OpUserEntity opUser = new OpUserEntity(); opUser.userID = this.CurrentUser.Username; opUser.compSEQ = this.CurrentUser.CompanySeq; buscity.opUser = opUser; if (!IsPostBack) { ShowData(); } } catch (Exception ex) { log.Error("[ProvinceCityConfig::Page_Load]" + ex); MessageBox.Show(this, "页面加载失败，请重试或联系客服人员！"); } } /// <summary> /// 保存省份与城市的对应关系 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnSave_Click(object sender, EventArgs e) { DbTransaction tran = SqlHelper.OpenTransaction(); List<ProvinceCityRelationDto> list = new List<ProvinceCityRelationDto>(); //依次获取DataGridView中各控件的值，并添加到list中 for (int i = 0; i < gvList.Rows.Count; i++) { string Province = (gvList.Rows[i].Cells[0].Controls[1] as Label).ToolTip; CheckBoxList cityList = gvList.Rows[i].Cells[1].Controls[1] as CheckBoxList; //每家省份是否选中了一个城市 bool hasOne = false; for (int j = 0; j < cityList.Items.Count; j++) { if (cityList.Items[j].Selected) { hasOne = true; ProvinceCityRelationDto dto = new ProvinceCityRelationDto(); decimal configSeq = 0; decimal citySeq = 0; decimal.TryParse(Province, out configSeq); decimal.TryParse(cityList.Items[j].Value, out citySeq); dto.BusinessConfigSeq = configSeq; dto.cityDictSeq = citySeq; list.Add(dto); } } if (!hasOne) { MessageBox.Show(this, "每个省份至少选择一个城市才能保存"); return; } } //提交保存 SuperResult result = provinceCityRelation.Save(list, tran);
if (result.opResult == ResultValue.Succ) { MessageBox.Show(this, "保存成功"); tran.Commit(); ShowData(); } else { MessageBox.Show(this, "系统异常，请联系客服人员"); tran.Rollback(); ShowData(); } } protected void gvItem_RowCommand(object sender, GridViewRowEventArgs e) { if (e.Row.RowType == DataControlRowType.DataRow) { CheckBoxList cityList = (CheckBoxList)e.Row.FindControl("city"); if (cityList != null) { string ProvinceSeq = gvList.DataKeys[e.Row.RowIndex].Value.ToString(); cityList.DataSource = allcityCache.Tables[0].DefaultView; cityList.DataValueField = "CITY_SEQ"; cityList.DataTextField = "CITY_CODE"; cityList.DataBind(); if (!DataHelper.IsEmpty(selectCityCache)) { //用DataSet一次性查询出所有记录，然后根据条件来过滤出所需要的数据
DataRow[] correctRows = selectCityCache.Tables[0].Select("PROVINCE_SEQ = " + ProvinceSeq, "CITY_CODE ASC"); foreach (DataRow row in correctRows) { //checkbox显示为“已选中” if (cityList.Items.FindByValue(row["CITY_SEQ"].ToString()) != null) { cityList.Items.FindByValue(row["CITY_SEQ"].ToString()).Selected = true; } } } } } } #endregion #region Methods /// <summary> /// 查询相关的数据
/// 思路：先查出所有数据，然后根据条件筛选出所需信息 /// </summary> private void ShowData() {//查询所有的城市 string allCitySql = @"SELECT P.CITY_SEQ,P.CITY_CODE FROM CITY P WHERE P.STATUS =1 ORDER BY P.CITY_CODE ASC"; //结果保存到全局变量中 allCityCache = SqlHelper.ExecuteDataSet(allCitySql); //查询所有的省份和城市的匹配信息 if (!DataHelper.IsEmpty(allCityCache)) { string selectCitySql = @"SELECT T.PROVINCE_SEQ,T.CITY_SEQ,P.CITY_CODE,P.STATUS FROM PROVINCE_CITY_REF T LEFT JOIN CITYP P ON P.CITY_SEQ = T.CITY_SEQ WHERE P.STATUS = 1"; DataSet selectCity = SqlHelper.ExecuteDataSet(selectCitySql); //结果保存到全局变量中 selectCityCache.Merge(selectCity); //selectcityCache.Tables.Add(selectCity); } //查询所有的省份，并绑定到gridview string sqlProvince = @"SELECT B.PROVINCE_SEQ, B.PROVINCE_CODE FROM PROVINCE B WHERE B.STATUS = 1 ORDER BY B.PROVINCE_CODE ASC"; DataSet allProvince = SqlHelper.ExecuteDataSet(sqlProvince); //绑定省份到gridview的第一列 gvList.DataSource = allProvince.Tables[0]; gvList.DataBind(); //数据为空时，显示默认的表头 if (dsProvince.Tables[0].Rows.Count <= 0) { BaseDataTool.AddTableTop(this.gvList); } } #endregion }
最终效果如下：
