ASP.NET MVC下拉框联动实例解析_实用技巧_

 USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name='MyAddressDB' ) DROP DATABASE MyAddressDB GO CREATE DATABASE MyAddressDB GO USE MyAddressDB GO IF EXISTS (SELECT * FROM sysobjects WHERE name='Province') DROP TABLE Province GO --省份表 CREATE TABLE Province ( ProvinceID INT IDENTITY(1,1) PRIMARY KEY, ProvinceName NVARCHAR(50) NOT NULL ) IF EXISTS (SELECT * FROM sysobjects WHERE name='City') DROP TABLE City GO --省份表 CREATE TABLE City ( CityID INT IDENTITY(1,1) PRIMARY KEY, CityName NVARCHAR(50) NOT NULL, ProvinceID INT REFERENCES dbo.Province(ProvinceID) NOT NULL ) --插入测试语句：【在网上找了一个省市数据库，把里面的数据导入我当前数据库中】 --开始 INSERT INTO dbo.Province SELECT ProvinceName FROM Temp.dbo.S_Province INSERT INTO dbo.City ( CityName, ProvinceID ) SELECT CityName, ProvinceID FROM Temp.dbo.S_City --结束 --测试插入成功与否 --SELECT * FROM dbo.Province --SELECT * FROM dbo.City 

 using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace JsonDataInMVC.Models { public class Province { public int ProvinceID { get; set; } public string ProvinceName { get; set; } } } 

 using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace JsonDataInMVC.Models { public class City { public int CityID { get; set; } public string CityName { get; set; } public int ProvinceID { get; set; } } } 

 using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Configuration; using JsonDataInMVC.Models; using System.Data; using System.Data.SqlClient; namespace JsonDataInMVC.DBOperator { public class AddressHelper { /// 
 /// 连接字符串 /// 
 public string ConnectionString { get { return ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; } } /// 
 /// 获取所有的省份 /// 
 ///  public List GetAllProvince() { List lstProvince = new List(); string sql = @"SELECT * FROM dbo.Province"; //ADO.NET连接方式访问数据库 //1.创建连接对象[连接字符串] SqlConnection conn = new SqlConnection(ConnectionString); //2.创建命令对象 SqlCommand cmd = new SqlCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.Connection = conn; //3.打开连接 conn.Open(); //4.发送命令 SqlDataReader reader= cmd.ExecuteReader(); //5.处理数据 while (reader.Read()) { lstProvince.Add(new Province() { ProvinceID =Convert.ToInt32( reader["ProvinceID"]), ProvinceName = reader["ProvinceName"].ToString() }); } //6.关闭连接 conn.Close(); reader.Close(); return lstProvince; } /// 
 /// 通过ProvinceID获取市的数据 /// 
 ///  ///  public List GetCityListByProvinceID(int id) { DataSet ds = new DataSet(); string sql = @"SELECT CityID,CityName FROM dbo.City WHERE ProvinceID=@ProvinceID"; //ADO.NET非连接方式访问数据库 //1.创建连接对象 SqlConnection conn = new SqlConnection(ConnectionString); 　　　　　　　　//2.创建数据适配器对象 　　　　　　　　 SqlDataAdapter sda = new SqlDataAdapter(sql,conn);//这里还真必须这样写。不能像下面的两条注释语句那样写。 　　　　　　　　//sda.SelectCommand.Connection = conn; 　　　　　　　　//sda.SelectCommand.CommandText = sql; 　　　　　　　　 sda.SelectCommand.CommandType = CommandType.Text; sda.SelectCommand.Parameters.AddWithValue("@ProvinceID", id);//参数设置别忘了 //3.打开连接【注意，非链接模式下，连接的打开关闭，无所谓，不过还是打开好点。规范化】 conn.Open(); //4.发送命令 sda.Fill(ds); //5.处理数据 //6关闭连接【【注意，非链接模式下，连接的打开关闭，无所谓，不过还是打开好点。规范化】】 conn.Close(); return DataTableToList.ConvertToModel(ds.Tables[0]).ToList(); } } } 

 using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Reflection; using System.Web; namespace JsonDataInMVC.DBOperator { public static class DataTableToList where T : new() { public static IList ConvertToModel(DataTable dt) { //定义集合 IList ts = new List(); T t = new T(); string tempName = ""; //获取此模型的公共属性 PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (DataRow row in dt.Rows) { t = new T(); foreach (PropertyInfo pi in propertys) { tempName = pi.Name; //检查DataTable是否包含此列 if (dt.Columns.Contains(tempName)) { //判断此属性是否有set if (!pi.CanWrite) continue; object value = row[tempName]; if (value != DBNull.Value) pi.SetValue(t, value, null); } } ts.Add(t); } return ts; } } } 

 using JsonDataInMVC.DBOperator; using JsonDataInMVC.Models; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; namespace JsonDataInMVC.Controllers { public class ProvinceController : Controller { private AddressHelper db; public ProvinceController() { db = new AddressHelper(); } // GET: Province public ActionResult Index() { List lstProvince= db.GetAllProvince(); ViewBag.ListProvince = lstProvince; return View(); } } } 

 @using JsonDataInMVC.Models @{ ViewBag.Title = "Index"; List lstProvince = ViewBag.ListProvince as List; } 
ProvinceIndex
省份: @foreach (var item in lstProvince) { @item.ProvinceName } 

 public static void RegisterRoutes(RouteCollection routes) { routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); routes.MapRoute( name: "Default", url: "{controller}/{action}/{id}", defaults: new { controller = "Province", action = "Index", id = UrlParameter.Optional } ); } 

 using JsonDataInMVC.DBOperator; using JsonDataInMVC.Models; using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; namespace JsonDataInMVC.Controllers { public class ProvinceController : Controller { private AddressHelper db; public ProvinceController() { db = new AddressHelper(); } // GET: Province public ActionResult Index() { List lstProvince= db.GetAllProvince(); ViewBag.ListProvince = lstProvince; return View(); } public JsonResult GetAllCityByProvinceID(int id) { List lstCity= db.GetCityListByProvinceID(id); return Json(lstCity, JsonRequestBehavior.AllowGet); } } } 

 @using JsonDataInMVC.Models @{ ViewBag.Title = "Index"; List lstProvince = ViewBag.ListProvince as List; } 
ProvinceIndex
省份: @foreach (var item in lstProvince) { @item.ProvinceName } 
城市: