兩個DropDownList的聯動,選擇其中一個DropDownList,然後載入資料到另外的一個DropDownList上
這裡,我打算實現的需求是:有兩個DropDownList,一個預設載入所有的省份資料,然後,當我選擇省份的時候,把對應的市的資料,綁定到另外一個DropDownList上面,即實現了聯動。
好了,這裡不打算使用EF了,換用ADO.NET。首先建立好資料庫,表:
USE master GO IF EXISTS (SELECT * FROM sysdatabases WHERE name='MyAddressDB' )DROP DATABASE MyAddressDBGO CREATE DATABASE MyAddressDBGO USE MyAddressDBGO IF EXISTS (SELECT * FROM sysobjects WHERE name='Province')DROP TABLE ProvinceGO--省份表 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 CityGO--省份表 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.ProvinceSELECT ProvinceName FROM Temp.dbo.S_ProvinceINSERT INTO dbo.City ( CityName, ProvinceID ) SELECT CityName, ProvinceID FROM Temp.dbo.S_City--結束--測試插入成功與否--SELECT * FROM dbo.Province--SELECT * FROM dbo.City
然後建立一個空白的MVC項目,在Model檔案夾下,添加兩個實體:
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; } }}
然後在根目錄下,建立一個檔案夾DBOperator,在裡面建立一個AddressHelper類
AddRessHelper類中的代碼:
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 { /// <summary> /// 連接字串 /// </summary> public string ConnectionString { get { return ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; } } /// <summary> /// 擷取所有的省份 /// </summary> /// <returns></returns> public List<Province> GetAllProvince() { List<Province> lstProvince = new List<Province>(); 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; } /// <summary> /// 通過ProvinceID擷取市的資料 /// </summary> /// <param name="id"></param> /// <returns></returns> public List<City> 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<City>.ConvertToModel(ds.Tables[0]).ToList<City>(); } }}
DataTable轉List,我在網上找了一個協助類:
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<T> where T : new() { public static IList<T> ConvertToModel(DataTable dt) { //定義集合 IList<T> ts = new List<T>(); 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; } }}
建立Province控制器:
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<Province> lstProvince= db.GetAllProvince(); ViewBag.ListProvince = lstProvince; return View(); } }}
對應的Index視圖:
@using JsonDataInMVC.Models@{ ViewBag.Title = "Index"; List<Province> lstProvince = ViewBag.ListProvince as List<Province>;}<h2>ProvinceIndex</h2><label>省份:</label><select id="myProvince"> @foreach (var item in lstProvince) { <option value="@item.ProvinceID">@item.ProvinceName</option> }</select>
修改一下,預設的路由,
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<Province> lstProvince= db.GetAllProvince(); ViewBag.ListProvince = lstProvince; return View(); } public JsonResult GetAllCityByProvinceID(int id) { List<City> lstCity= db.GetCityListByProvinceID(id); return Json(lstCity, JsonRequestBehavior.AllowGet); } }}
Index視圖中:
@using JsonDataInMVC.Models@{ ViewBag.Title = "Index"; List<Province> lstProvince = ViewBag.ListProvince as List<Province>;}<h2>ProvinceIndex</h2><label>省份:</label><select id="myProvince"> @foreach (var item in lstProvince) { <option value="@item.ProvinceID">@item.ProvinceName</option> }</select><br/><hr /><label>城市:</label><select id="myCity"></select><script src="~/Scripts/jquery-1.10.2.js"></script><script type="text/javascript"> $(document).ready(function () { $("#myProvince").change(function () { //擷取省份的ID var provinceID = $("#myProvince").val(); //擷取城市 var myCity=$("#myCity"); //加入測試代碼 debugger; $.ajax({ url: "/Province/GetAllCityByProvinceID/" + provinceID, type: "post", dataType: "json", contentType: "application/json", success: function (result) { var myHTML = ""; myCity.html("");//賦值之前先清空 $.each(result, function (i, data) { myHTML += "<option value=" + data.CityID + ">" + data.CityName + "</option>"; }); myCity.append(myHTML); }, error: function (result) { alert(result.responseText); } }); }) })</script>
好了,弄好之後,運行程式:
選擇一個省份,對應的市的資訊就被我們查出來了,綁定到另外的市的下拉框中了。
總結:這篇文章,雖然基礎,但是很重要,平時開發中,遇到很多這樣的情境。
還有就是EF用多了,ADO.NET也不能忘記。
串連模式和非連結模式查詢資料庫6個步驟,牢記心中。
以上就是本文的全部內容,希望對大家的學習有所協助,也希望大家多多支援雲棲社區。