Ajax省市区无刷新单表联动查询

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

方法一：

在很多时候都需要用到无刷新级联查询，本文将以省市区的级联查询作为例子。注：此为单表三级联动

环境：Vistual Studio 2015 、MSSQL

1、首先下载AjaxControlToolkit控件，附件下载地址附文章末尾。

2、创建Asp.net网站，将AjaxControlToolkit.dll和AjaxControlToolkit.pdb拷至项目的Bin文件夹下，并在项目中引用。

3、在Web.config配置文件中<system.web> 中添加如下节点

4、配置完成后前台页面写代码（写在form标签内）

5、在项目中新建一个Web服务（本例命名为：CityService.asmx）

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient; //引用
using System.Data;
using AjaxControlToolkit; //引用AjaxContrlToolkit控件
using System.Collections.Specialized;//引用
/// <summary>
///CityService 的摘要说明
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
//若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务，请取消对下行的注释。
[System.Web.Script.Services.ScriptService] //需要把些标记加上，不然调不到方法
public class CityService : System.Web.Services.WebService
{ public CityService()
{
//如果使用设计的组件，请取消注释以下行
//InitializeComponent();
}
static string ConnStr = "Server=10.72.25.12;User ID=zzcp;Password=gwbnsh@zzcp;database=zzcp;Connection Reset=FALSE";
/// <summary>
/// 获取省份
/// </summary>
/// <param name="knownCategoryValues"></param>
/// <param name="category"></param>
/// <returns></returns>
[WebMethod]
public CascadingDropDownNameValue[] GetProvince(string knownCategoryValues, string category)
{
List<CascadingDropDownNameValue> list = new List<CascadingDropDownNameValue>(); SqlConnection conn = new SqlConnection(ConnStr);
string sql = "SELECT * FROM tb_regions WHERE LevelType=1";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CascadingDropDownNameValue t = new CascadingDropDownNameValue(dr["Name"].ToString(), dr["id"].ToString());
list.Add(t);
}
dr.Close();
conn.Close();
return list.ToArray();
}
/// <summary>
/// 获取城市
/// </summary>
/// <param name="knownCategoryValues"></param>
/// <param name="category"></param>
/// <returns></returns>
[WebMethod]
public CascadingDropDownNameValue[] GetCity(string knownCategoryValues, string category)
{
SqlConnection conn = new SqlConnection(ConnStr);
List<CascadingDropDownNameValue> list = new List<CascadingDropDownNameValue>();
StringDictionary sd = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
string sql = string.Format("SELECT * FROM tb_regions WHERE LevelType=2 AND ParentId='{0}' ", sd["ProvinceID"]);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CascadingDropDownNameValue t = new CascadingDropDownNameValue(dr["Name"].ToString(), dr["id"].ToString());
list.Add(t);
}
dr.Close();
conn.Close();
return list.ToArray();
}
/// <summary>
/// 获取区
/// </summary>
/// <param name="knownCategoryValues"></param>
/// <param name="category"></param>
/// <returns></returns>
[WebMethod]
public CascadingDropDownNameValue[] GetDistrict(string knownCategoryValues, string category)
{
SqlConnection conn = new SqlConnection(ConnStr);
List<CascadingDropDownNameValue> list = new List<CascadingDropDownNameValue>();
StringDictionary sd = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
string sql = string.Format("SELECT * FROM tb_regions WHERE LevelType=3 AND ParentId='{0}' ", sd["CityID"]);
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
CascadingDropDownNameValue t = new CascadingDropDownNameValue(dr["Name"].ToString(), dr["id"].ToString());
list.Add(t);
}
dr.Close();
conn.Close();
return list.ToArray();
}
}

CascadingDropDown与DropDownList控件组合，下面是对其各个属性的一个简单说明：

TargetControlID：与之协作的DropDownList控件的ID。
ParentControlID：父级下拉框的ID，显然顶级下拉框ddlProvince不含有父级下拉框，因此不含有ParentControlID。
Category：此下拉框的“类别”，也可以称为名称，具体使用到后面再说，这里说了也不好理解。
PromptText：进行选择之前显示的文本。
LoadingText：加载时显示的文本，因为需要与服务端进行通信，所以难免会产生延迟，这里的文本便是在延迟时显示的文本。
ServicePath：获取数据时的Web服务。此处即为我们的WCF服务。
ServiceMethod：获取数据的方法。这些方法由WCF服务实现，显然我们暂时并没有实现它。

方法二：

前台代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Douyu.Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
15 <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
<ContentTemplate>
<asp:DropDownList ID="ddlProvice" runat="server" AppendDataBoundItems="true" AutoPostBack="true"
OnSelectedIndexChanged="ddlProvice_SelectedIndexChanged">
<asp:ListItem Text="-请选择省份-" Value=""></asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="true"
onselectedindexchanged="ddlCity_SelectedIndexChanged">
<asp:ListItem Text="-请选择城市-" Value=""></asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlArea" runat="server">
<asp:ListItem Text="-请选择县区-" Value=""></asp:ListItem>
</asp:DropDownList>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>

后台代码：

using GwbnshHD;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls; namespace Douyu
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindProvince();
BindCity();
BindDistrict();
}
}
//加载城市
protected void ddlProvice_SelectedIndexChanged(object sender, EventArgs e)
{
BindCity();
BindDistrict();
}
//加载县区
protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)
{
BindDistrict();
}
//加载省份
protected void BindProvince()
{
DataTable dt = DbHelperSQL.Query("SELECT * FROM tb_regions WHERE LevelType=1").Tables[];
ddlProvice.DataTextField = "Name";
ddlProvice.DataValueField = "id";
ddlProvice.DataSource = dt;
ddlProvice.DataBind();
}
/// <summary>
/// 绑定市区
/// </summary>
/// <param name="code"></param>
public void BindCity()
{
string SelectPro = ddlProvice.SelectedValue;
if (!string.IsNullOrEmpty(SelectPro))
{
ddlCity.Items.Clear();
ddlCity.AppendDataBoundItems = true;
ddlCity.Items.Insert(, new ListItem("-请选择城市-", ""));
DataTable dt1 = DbHelperSQL.Query("SELECT * FROM tb_regions WHERE LevelType=2 AND ParentId=" + SelectPro + "").Tables[];
ddlCity.DataTextField = "Name";
ddlCity.DataValueField = "id";
ddlCity.DataSource = dt1;
ddlCity.DataBind();
}
else
{
ddlCity.Items.Clear();
ddlCity.AppendDataBoundItems = true;
ddlCity.Items.Insert(, new ListItem("-请选择城市-", ""));
}
}
/// <summary>
/// 绑定县区
/// </summary>
/// <param name="code"></param>
public void BindDistrict()
{
string SelectCity = ddlCity.SelectedValue;
if (!string.IsNullOrEmpty(SelectCity))
{
ddlArea.Items.Clear();
ddlArea.AppendDataBoundItems = true;
ddlArea.Items.Insert(, new ListItem("-请选择县区-", ""));
DataTable dt2 = DbHelperSQL.Query("SELECT * FROM tb_regions WHERE LevelType=3 AND ParentId=" + SelectCity + "").Tables[];
ddlArea.DataTextField = "Name";
ddlArea.DataValueField = "id";
ddlArea.DataSource = dt2;
ddlArea.DataBind();
}
else
{
ddlArea.Items.Clear();
ddlArea.AppendDataBoundItems = true;
ddlArea.Items.Insert(, new ListItem("-请选择县区-", ""));
}
}
}
}