/// <summary> /// 向数据库插入省份信息 /// </summary> /// <param name="obj"></param> /// <returns></returns> public void InsertProvinceObjToDB(Province obj, bool isCreateDBDates) { if (!isCreateDBDates) return; OleDbConnection connection = null; OleDbCommand mycmd = null; CreatConn(DBPath, ref connection, ref mycmd); List<string> cmdString = InsertSchoolCmdString(obj); try { connection.Open(); mycmd.Transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted); for (int i = 0; i < cmdString.Count; i++) { MsgEventHandle(string.Format(TipInfos.CurrentRecord, cmdString.Count, i, cmdString[i])); mycmd.CommandText = cmdString[i]; mycmd.ExecuteNonQuery(); } mycmd.Transaction.Commit(); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); if (mycmd.Transaction != null) mycmd.Transaction.Rollback(); System.Windows.Forms.MessageBox.Show(ex.Message); throw ex; } finally { //关闭连接 if (connection != null && connection.State == ConnectionState.Open) connection.Close(); } }
/// <summary> /// 获取插入学校命令字符串 /// </summary> /// <param name="province"></param> /// <returns></returns> private static List<string> InsertSchoolCmdString(Province province) { List<string> cmdList = new List<string>(); province.Citys.ForEach((c) => { c.Villages.ForEach((v) => { v.Schools.ForEach((s) => { cmdList.Add(string.Format("INSERT INTO [School]([schoolid],[villageid],[districtid],[schoolname],[schoolprop]) VALUES('{0}','{1}','{2}','{3}','{4}');", s.SchoolID, s.VilliageID, s.DistrictID, s.SchoolName.Replace('\'', ' '), s.SchoolProp1)); }); }); }); return cmdList; }
/// <summary> /// 创建省的实体 /// </summary> /// <param name="strProvinceData"></param> /// <param name="schoolType"></param> /// <param name="province"></param> /// <param name="isMuitCity"></param> private static void CreateProvinceModle(string strProvinceData, string schoolType, out Province province, out bool isMuitCity) { #region 省份处理 //解析txt所得到省份信息处理 string provinceName = UtilsHelper.GetProvinceName(strProvinceData); string provinceID = UtilsHelper.GetProvinceId(strProvinceData, provinceName); province = new Province() { LocationID = provinceID, LocationName = provinceName }; #endregion //分割内容 string[] contentsSplit = UtilsHelper.GetContents(strProvinceData, TipInfos.ThirdContent); //直辖市 isMuitCity = HandleMunicipalities(provinceName, provinceID, province, contentsSplit); for (int i = 1; i < contentsSplit.Length; i++) //三级目录的内容 : 广东;广州;荔湾区;<option value="440103">荔湾区 [学校列表]·廣州市荔灣區東沙小學·... { try { string[] strContents = UtilsHelper.GetContents(contentsSplit[i], TipInfos.School_List); //以学校列表分开 if (strContents.Length < 2) continue; string[] areaList = UtilsHelper.GetAreaInfo(strContents[0]); // strContents[0]:广东;广州;荔湾区;<option value="440103">荔湾区 if (areaList.Length < 2) continue; CreatVillageModle(strProvinceData, schoolType, province, isMuitCity, strContents, areaList); } catch (System.Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); throw ex; } } }
/// <summary> /// 过滤无效信息(如:省份下没有城市,城市下没有地区,地区下没有学校) /// </summary> /// <param name="province"></param> /// <param name="isMuitCity"></param> private static void RemoveInvalidDatas(Province province, bool isMuitCity) { try { //移除多余信息 for (int i = province.Citys.Count - 1; i >= 0; i--) { for (int j = province.Citys[i].Villages.Count - 1; j >= 0; j--) { if (province.Citys[i].Villages[j].Schools.Count == 0 || (!isMuitCity && province.Citys[i].Villages[j].VillageName == "市辖区")) //移除学校列表为空的 { province.Citys[i].Villages.Remove(province.Citys[i].Villages[j]); } } if (province.Citys[i].Villages.Count == 0) //移除地区列表为空的 { province.Citys.Remove(province.Citys[i]); } } } catch (System.Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); throw ex; } }
private static bool HandleMunicipalities(string provinceName, string provinceID, Province province, string[] contentsSplit) { bool isMuitCity = false; if (UtilsHelper.IsMuitCity(provinceName)) //直辖市处理 { isMuitCity = true; City city1 = new City() { DistrictID = string.Format("{0}0100", provinceID.Substring(0, 2)), DistrictName = "市辖区", LocationID = provinceID }; City city2 = new City() { DistrictID = string.Format("{0}0200", provinceID.Substring(0, 2)), DistrictName = "县", LocationID = provinceID }; province.Citys.Add(city1); province.Citys.Add(city2); } else //普通省市处理 { string cityOptions = UtilsHelper.GetContents(contentsSplit[0], TipInfos.SecondaryContent)[1]; string[] cityInfos = UtilsHelper.HandleCityInfos(cityOptions); //二级目录内容: <option value="1301">石家庄</option><option value="1302">唐山</option>... foreach (string cityInfo in cityInfos) { if (string.IsNullOrEmpty(cityInfo)) continue; #region 市级处理 string cityName = cityInfo.Split('>')[1]; //处理 <option value="1301">石家庄</option> string cityID = cityInfo.Split('"')[1].PadRight(6, '0'); // 如 : 合肥 340100 City city = new City() { DistrictID = cityID, DistrictName = cityName, LocationID = provinceID }; province.Citys.Add(city); //因为数据库中每个县都有冗余的"市辖区",判断是否是直辖市,如果不是直辖市,则删除 AcessDBUser.Instance.DeleteInvideVillageInfos(cityID); #endregion } } return isMuitCity; }
private static void CreatVillageModle(string strProvinceData, string schoolType, Province province, bool isMuitCity, string[] strContents, string[] areaList) { #region 区域处理 string cityNameByArea; string cityIDByArea; Village village; CreateVillageModle(strProvinceData, schoolType, isMuitCity, strContents, areaList, out cityNameByArea, out cityIDByArea, out village); #endregion City city = province.Citys.Find((c) => { return c.DistrictName == cityNameByArea && c.DistrictID == cityIDByArea; }); if (city != null) { city.Villages.Add(village); } }