tijian_tieying/web/dccdc.DAL/AreaBll.cs

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;

using Dapper;
using dccdc.Models;

namespace dccdc.DAL
{
    public class AreaBll
    {
        /// <summary>
        /// 获取地区
        /// </summary>
        /// <returns></returns>
        public List<AreaModel> GetList()
        {
            using (SqlConnection con = CommHelper.GetSqlConnection())
            {
                return con.Query<AreaModel>("select * from area1").ToList();
            }
        }
        
        /// <summary>
        /// 根据当前区域CODE查询下级区域
        /// </summary>
        /// <param name="ProvinceID"></param>
        /// <returns></returns>
        public List<AreaModel> GetSubordinate(string code)
        {
            using (SqlConnection con = CommHelper.GetSqlConnection())
            {
                if (string.IsNullOrWhiteSpace(code) || code == "0")
                {
                    return con.Query<AreaModel>("select * from area1 where pcode='0'").ToList();
                }
                return con.Query<AreaModel>("select * from area1 where pcode=@code", new { code }).ToList();
            }
        }

        /// <summary>
        /// 获取省份列表
        /// </summary>
        /// <returns></returns>
        public List<AreaModel> GetProvince()
        {
            using (SqlConnection con = CommHelper.GetSqlConnection())
            {
                return con.Query<AreaModel>("select * from area1 where pcode='0'").ToList();
            }
        }
        /// <summary>
        /// 获取单独省份
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public List<AreaModel> GetProvince(string code)
        {
            using (SqlConnection con = CommHelper.GetSqlConnection())
            {
                return con.Query<AreaModel>("select * from area1 where code=@code", new { code }).ToList();
            }
        }
        /// <summary>
        /// 根据省份获取市
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<AreaModel> GetProvinceByPid(string code)
        {
            using (SqlConnection conn = CommHelper.GetSqlConnection())
            {
                AreaModel m = GetTopLevel(code);
                List<AreaModel> list = new List<AreaModel> { m };
                string pcode = m.pCode;
                while (pcode != "0")
                {
                    AreaModel m1 = GetTopLevel(pcode);
                    list.Add(m1);
                    pcode = m1.pCode;
                }
                return list;
            }
        }
        /// <summary>
        /// 根据当前区域code查询上级区域
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public AreaModel GetTopLevel(string code)
        {
            using (SqlConnection conn = CommHelper.GetSqlConnection())
            {
                return conn.Query<AreaModel>("select * from area1 where code=@code", new { code }).FirstOrDefault();
            }

        }
        /// <summary>
        /// 根据code查找所有父级
        /// </summary>
        /// <param name="code"></param>
        /// <returns></returns>
        public List<AreaModel> GetTopListByCode(string code)
        {
            List<AreaModel> list = new List<AreaModel>();
            using (SqlConnection conn = CommHelper.GetSqlConnection())
            {
                while (true)
                {
                    AreaModel area = conn.Query<AreaModel>("select * from area1 where code=@code", new { code }).FirstOrDefault();
                    if (null == area)
                    {
                        break;
                    }
                    list.Add(area);
                    code = area.pCode;
                }


                //AreaModel am = conn.Query<AreaModel>("select * from area where code=@code", new { code })
                //    .FirstOrDefault();
                //AreaModel topam = conn.Query<AreaModel>("select * from area where code=@pcode", new { @pcode = am.pCode }).FirstOrDefault();
                //AreaModel p = conn.Query<AreaModel>("select * from area where code=@pcode",
                //    new { @pcode = topam.pCode }).FirstOrDefault();
                //list.Add(p);
                //list.Add(topam);
                //list.Add(am);
                return list;
            }
        }


    }
}