using CityJson;
using Dapper;
using Newtonsoft.Json;
{
using (var db = DbHelper.Db())
{
//数据格式
//code_prov code_city code_coun prov city coun
// 110000 110100 110101 北京市 北京市 东城区

//表结构
//name_prov code_prov name_city code_city name_coun code_coun name_town code_town
//河北省 130000 石家庄市 130100 新华区 130105 合作路街道 130105007

//查询省市县分组排序
var baiDuMaps = db.Query<BaiDuMap>(@"select code_prov,code_city,code_coun,max(name_prov) as prov,max(name_city) as city ,max(name_coun) as coun
from baidu_map_township_area
group by code_prov,code_city,code_coun");
//创建省List
var provs = new List<prov>();
//循环查询出的省市县
foreach (var item in baiDuMaps)
{
//判断省集合中是否已存在此省
if (provs.FirstOrDefault(p => p.code.Equals(item.code_prov)) == null)
{
//创建省对象
var prov = new prov();
//对象赋值
prov.code = item.code_prov;
prov.name = item.prov;
prov.layer = 1;
//创建市List
var citys = new List<city>();
//循环省市县集合对于市区进行添加
foreach (var baiDuMap in baiDuMaps)
{
//判断省id是否等于循环中省id
if (baiDuMap.code_prov == prov.code)
{
//判断市list中是否包含此市
if (citys.FirstOrDefault(p => p.code.Equals(baiDuMap.code_city)) == null)
{
//创建市对象
var city = new city();
//对象赋值
city.code = baiDuMap.code_city;
city.name = baiDuMap.city;
city.layer = 2;
//创建区县list
var couns = new List<coun>();
//循环省市区进行对于县区进行赋值
foreach (var biadu in baiDuMaps)
{
//判断市区与上一个list是否一致code码
if (biadu.code_city == city.code)
{
//判断区县中是否已经存在
if (couns.FirstOrDefault(p => p.code.Equals(biadu.code_coun)) == null)
{
//创建区县对象
var coun = new coun();
//对象赋值
coun.code = biadu.code_coun;
coun.name = biadu.coun;
coun.layer = 3;
//县区添加List
couns.Add(coun);
}
}
}
//向对应城市下添加区县
city.children = couns;
//添加到城市的子节点中
citys.Add(city);
}
}
}
//把市添加到相对应的省中
prov.children = citys;
//对应省进行赋值添加到List中
provs.Add(prov);
}
}
//打印控制台
Console.WriteLine(JsonConvert.SerializeObject(provs));
Console.ReadKey();
}

 

}

 

//数据库帮助类

 

using System.ComponentModel.DataAnnotations;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using Dapper;

namespace CityJson
{
public class DbHelper
{
//public DbHelper() {}

//连接字符串
private static readonly string ConnectionString = "数据库连接字符串";

public static SqlConnection Db()
{
var mysql = new SqlConnection(ConnectionString);
//mysql.Open();
return mysql;
}

 

/// <summary>
/// 添加
/// </summary>
/// <param name="conn"></param>
/// <param name="model">传过来的对象</param>
/// <returns></returns>
public static int Add<T>(IDbConnection conn, T model)
{
//获取类型
var type = typeof(T);
//定义变量
int i = 0;
//使用反射进行获取所有的属性
var prop = type.GetProperties().ToList();
//获取的些属性变成list集合
var propName = prop.Select(p => p.Name).ToList();
//删除主键
propName.Remove(GetPrimary(type));
//拼接sql
StringBuilder sql = new StringBuilder($"insert into {type.Name} ({string.Join(",", propName)}) values (@{string.Join(",@", propName)})");
//使用Daaper来执行命令
i = conn.Execute(sql.ToString(), model);
//返回一个i也就是成功否
return i;
}

/// <summary>
/// 不移除ID的批量添加
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <returns></returns>
public static int Adds<T>(IDbConnection conn, List<T> model)
{
var type = typeof(T);
int i = 0;
var prop = type.GetProperties().ToList();
var propName = prop.Select(p => p.Name).ToList();
propName.Remove(GetPrimary(type));
StringBuilder sql = new StringBuilder($"insert into {type.Name}({string.Join(",", propName)} ) values (@{string.Join(",@", propName)} ) ");
i = conn.Execute(sql.ToString(), model);
return i;
}

/// <summary>
/// 查询list
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="conn">数据库参数</param>
/// <param name="condition"></param>
/// <returns></returns>
public static List<T> GetList<T>(IDbConnection conn, string condition = "")
{
//获取类型
var type = typeof(T);
//拼接sql
var sql = new StringBuilder($"select * from {type.Name} where 1=1");
if (!string.IsNullOrWhiteSpace(condition))
{
sql.Append($" and {condition}");
}
DefaultTypeMap.MatchNamesWithUnderscores = true;
//使用Dapper来进行查询也就是查看 然后转换为list类型使用变量接收
var list = conn.Query<T>(sql.ToString()).ToList();
//返回这个变量
return list;
}

/// <summary>
/// 查询单个对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="conn"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static T FirstOrDefault<T>(IDbConnection conn, string condition = "")
{
//获取类型
var type = typeof(T);
//拼接sql
var sql = new StringBuilder($"select * from `{type.Name}` where 1=1");
if (!string.IsNullOrWhiteSpace(condition))
{
sql.Append($" and {condition}");
}
//使用Dapper来进行查询也就是查看 然后转换为list类型使用变量接收
//Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
var first = conn.QueryFirstOrDefault<T>(sql.ToString());
//返回这个变量
return first;
}

//查看
//public List<T> Show() {
// //实例化一个集合
// List<T> list = new List<T>();
// //获取类型
// var type = typeof(T);
// //拼接sql
// string sql = $"select * from {type.Name}";
// //使用Daaper来进行查询也就是查看 然后转换为list类型使用变量接收
// list = conn.Query<T>(sql).ToList();
// //返回这个变量
// return list;
//}
/// <summary>
/// 删除以及批量删除
/// </summary>
/// <param name="conn"></param>
/// <param name="ids">传过来的是一个id数组</param>
/// <returns></returns>
public static int Del<T>(IDbConnection conn, string ids)
{
//获取类型
var type = typeof(T);
//定义变量接收返回值
int i = 0;
//拼接sql
string primaryKey = GetPrimary(type);
if (string.IsNullOrEmpty(primaryKey))
{
throw new Exception($"{type.Name} 未找到主键");
}
string sql = $"delete from {type.Name} where {primaryKey} in ({ids})";
//使用daaper来执行命令
i = conn.Execute(sql, ids);
return i;
}

/// <summary>
/// 修改
/// </summary>
/// <param name="conn"></param>
/// <param name="model"></param>
/// <returns></returns>
public static int Update<T>(IDbConnection conn, T model)
{
//获取
Type type = typeof(T);

string primaryKey = GetPrimary(type);
if (string.IsNullOrEmpty(primaryKey))
{
throw new Exception($"{type.Name} 未找到主键");
}
//定义变量接收返回值
int i = 0;
//使用反射来查找所有的属性值 然后转化为list类型
var prop = type.GetProperties().ToList();
//然后筛选出来name转化为list集合
var propName = prop.Select(p => p.Name).ToList();
//删除主键
propName.Remove(primaryKey);
//拼接字符串
StringBuilder sql = new StringBuilder($"update `{type.Name}` set ");
//因为需要一个类型等于另外一个类型需要一一对应所以使用foreach便利
foreach (var variable in propName)
{
//每个属性等于@领个属性然后加上逗号
sql.Append($"{variable}=@{variable},");
}
//最后删除那个逗号
sql.Remove(sql.Length - 1, 1);
//因为修改需要条件当然咱们获取主键的方法使用上去了
sql.Append($" where {primaryKey}=@{primaryKey}");
//最后执行命令使用daaper
i = conn.Execute(sql.ToString(), model);
return i;
}

/// <summary>
/// 获取Id的 一个方法
/// </summary>
/// <returns></returns>
public static string GetPrimary(Type type)
{
//获取类型
//var type = typeof(T);
//使用反射查询出属性
var prop = type.GetProperties();
//定义空的字符串来接收主键
string primary = null;
//循环查找主键
foreach (var propertyInfo in prop)
{
//使用特性来查找主键
if (propertyInfo.GetCustomAttribute(typeof(KeyAttribute), true) != null)
{
//进行赋值
primary = propertyInfo.Name;
//找出来一个直接跳出
break;
}
}
//返回主键 字符串类型
return primary;
}
}
}

//下面是帮助类

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CityJson
{
internal class BaiDuMap
{
public int code_prov { get; set; }

public int code_city { get; set; }
public int code_coun { get; set; }
public string prov { get; set; }
public string city { get; set; }
public string coun { get; set; }

}
}

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CityJson
{

//省
internal class prov
{

public string name { get; set; }

public int code { get; set; }

public int layer { get; set; }

public List<city> children { get; set; }
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CityJson
{

//市
internal class city
{

public string name { get; set; }

public int code { get; set; }

public int layer { get; set; }

public List<coun> children { get; set; }
}
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CityJson
{

//区县
internal class coun
{
public string name { get; set; }

public int code { get; set; }

public int layer { get; set; }
}
}