省市县树形结构打印-.netCore控制台程序
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 | 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 counfrom baidu_map_township_areagroup by code_prov,code_city,code_coun");//创建省Listvar 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;//创建市Listvar citys = new List<city>();//循环省市县集合对于市区进行添加foreach (var baiDuMap in baiDuMaps){//判断省id是否等于循环中省idif (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;//创建区县listvar 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;//县区添加Listcouns.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));//拼接sqlStringBuilder 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);//拼接sqlvar 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);//拼接sqlvar 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;//拼接sqlstring 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}");//最后执行命令使用daaperi = 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; }}} |
