/// <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);
}
}