三级地址库
2018/1/18 21:16:54
数据来源:国家统计局
http://www.stats.gov.cn/tjsj/tjbz/xzqhdm/201608/t20160809_1386477.html
功能效果:
数据库:MySQL
脚本下载地址: https://pan.baidu.com/s/1pM32WER
效果:
编程语言:C# MVC
地址Model
public class Address { public int Id { get; set; } public string Name { get; set; } public string FullName { get; set; } public string RegionCode { get; set; } public string ParentRegionCode { get; set; } public string EnglishName { get; set; } public string FirstLetterGroup { get; set; } }
数据库访问层DAO
public class AddressDAO : IAddressDAO { public List<Address> GetAreas(string parentRegion) { List<Address> list = new List<Address>(); string sql = "SELECT name,fullname,regioncode FROM address where parentregioncode = '{0}' and regionlevel = 3"; DataTable dt = context.ExecuteSql(string.Format(sql, parentRegion), "mysql"); if (dt != null) { foreach (DataRow row in dt.Rows) { list.Add(new Address { Name = row[0].ToString(), FullName = row[1].ToString(), RegionCode = row[2].ToString() }); } } return list; } public List<Address> GetProvinceByFirstLetterGroup() { List<Address> list = new List<Address>(); string sql = "SELECT name,fullname,regioncode,EnglishName,FirstLetterGroup FROM address where RegionLevel = 1 and FirstLetterGroup!=''"; DataTable dt = context.ExecuteSql(sql, "mysql"); if (dt != null) { foreach (DataRow row in dt.Rows) { list.Add(new Address { Name = row[0].ToString(), FullName = row[1].ToString(), RegionCode = row[2].ToString(), EnglishName = row[3].ToString(), FirstLetterGroup = row[4].ToString() }); } } return list; } public Address GetRegionById(int id) { return context.Get<Address>(m => m.Id == id, null, "mysql").FirstOrDefault(); } public List<Address> GetCities(string parentRegion) { List<Address> list = new List<Address>(); string sql = "SELECT name,fullname,regioncode FROM address where parentregioncode = '{0}' and regionlevel = 2"; DataTable dt = context.ExecuteSql(string.Format(sql, parentRegion), "mysql"); if (dt != null) { foreach (DataRow row in dt.Rows) { list.Add(new Address { Name = row[0].ToString(), FullName = row[1].ToString(), RegionCode = row[2].ToString() }); } } return list; } public List<Address> GetProvinces(string regionCode) { List<Address> list = new List<Address>(); string sql = "SELECT name,fullname,regioncode FROM address where regionlevel = 1"; DataTable dt = context.ExecuteSql(sql, "mysql"); if (dt != null) { foreach (DataRow row in dt.Rows) { list.Add(new Address { Name = row[0].ToString(), FullName = row[1].ToString(), RegionCode = row[2].ToString() }); } } return list; } public Address GetRegionItem(string regionCode) { List<Address> list = new List<Address>(); string sql = string.Format("SELECT name,fullname,regioncode,ParentRegionCode FROM address where RegionCode = {0}", regionCode); DataTable dt = context.ExecuteSql(sql, "mysql"); if (dt != null) { foreach (DataRow row in dt.Rows) { list.Add(new Address { Name = row[0].ToString(), FullName = row[1].ToString(), RegionCode = row[2].ToString(), ParentRegionCode = row[3].ToString() }); } } return list.FirstOrDefault(); } }
数据库服务层Service
直接调用DAO,无逻辑,故略
控制器:
//新增信息时 ViewBag.Provinces = GetProvinces(""); ViewBag.Cities = GetCities("", ""); ViewBag.Areas = GetAreas("", ""); //编辑信息时 ViewBag.Provinces = GetProvinces(province); ViewBag.Cities = GetCities(province, city); ViewBag.Areas = GetAreas(city, area); public IEnumerable<SelectListItem> GetProvinces(string selectedRegionCode) { List<SelectListItem> result = new List<SelectListItem>(); List<Address> provinces = addressService.GetProvinces(""); foreach (var item in provinces) { result.Add(new SelectListItem { Value = item.RegionCode, Text = item.FullName, Selected = selectedRegionCode == item.RegionCode }); } return result; } public IEnumerable<SelectListItem> GetCities(string parentRegionCode, string selectedRegionCode) { List<SelectListItem> result = new List<SelectListItem>(); List<Address> cities = addressService.GetCities(parentRegionCode); foreach (var item in cities) { result.Add(new SelectListItem { Value = item.RegionCode, Text = item.FullName, Selected = selectedRegionCode == item.RegionCode }); } return result; } public IEnumerable<SelectListItem> GetAreas(string parentRegionCode, string selectedRegionCode) { List<SelectListItem> result = new List<SelectListItem>(); List<Address> areas = addressService.GetAreas(parentRegionCode); foreach (var item in areas) { result.Add(new SelectListItem { Value = item.RegionCode, Text = item.FullName, Selected = selectedRegionCode == item.RegionCode }); } return result; } //js刷新时用 public PartialViewResult GetCitiesView(string parentRegionCode) { ViewBag.Cities = GetCities(parentRegionCode, ""); return PartialView(); } //js刷新时用 public PartialViewResult GetAreasView(string parentRegionCode) { ViewBag.Areas = GetAreas(parentRegionCode, ""); return PartialView(); }
视图部分:
主页面视图:
@Html.DropDownList("province", ViewBag.Provinces as IEnumerable<SelectListItem>, "---请选择---", new { @class = "f-select span3", onchange = "getCity()" }) @Html.DropDownList("city", ViewBag.Cities as IEnumerable<SelectListItem>, "---请选择---", new { @class = "f-select span3", onchange = "getArea()" }) @Html.DropDownList("area", ViewBag.Areas as IEnumerable<SelectListItem>, "---请选择---", new { @class = "f-select span3" }) var GetCitiesUrl = "@Html.Raw(Url.Action("GetCitiesView", "DbSubmit"))"; var GetAreasUrl = "@Html.Raw(Url.Action("GetAreasView", "DbSubmit"))"; var getArea = function () { var url = GetAreasUrl + "?parentRegionCode=" + $("#city").val(); $.get(url, {}, function (result) { $("#area").replaceWith(result); }) } var getCity = function () { var url = GetCitiesUrl + "?parentRegionCode=" + $("#province").val(); $.get(url, {}, function (result) { $("#city").replaceWith(result); getArea(); }) }
局部视图:GetCitiesView
@Html.DropDownList("city", ViewBag.Cities as IEnumerable<SelectListItem>, "---请选择---", new { @class = "f-select span3", onchange = "getArea()" })
局部视图:GetAreasView
@Html.DropDownList("area", ViewBag.Areas as IEnumerable<SelectListItem>, "---请选择---", new { @class = "f-select span3" })
完
版权说明
作者:SQBER
本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
{0}
{5}
{1}
{2}回复
{4}
*昵称:
*邮箱:
个人站点:
*想说的话: