你的位置:首页 > ASP.net教程

[ASP.net教程]MVC学习系列7


         【使用场景:两个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();      List<City> lstCity = new List<City>();      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个步骤,牢记心中。