public static void UpdateStory() { string filePath = Environment.CurrentDirectory + "\\sat单词助记to陈 0226.xlsx"; DataTable dtSource = DataHelper.ImportData(filePath).Tables[0]; string message = ""; MySqlOperator sqlOperator = new MySqlOperator(); if (dtSource != null) { for (int i = 0; i < dtSource.Rows.Count; i++) { string word = dtSource.Rows[i][0].ToString().Trim(); string story = dtSource.Rows[i][3].ToString().Trim(); // 构建删除sql string practiceSql = string.Format("select id from elibpractices where name = '{0}'", word); DataTable dt = sqlOperator.QueryDataTable(practiceSql); if (dt != null) { for (int index = 0; index < dt.Rows.Count; index++) { string id = dt.Rows[index][0].ToString(); string updateHint = string.Format("update elibpracticehints set story = '{0}' where practiceId = '{1}'", story, id); bool success = sqlOperator.UpdateDataTable(updateHint); if (!success) { message += "word:" + word; } } } } } }
/// <summary> /// 撤销填充词频 /// </summary> public static void ReverseUpdateIndex() { try { string filePath = Environment.CurrentDirectory + "\\sat - reverse.txt"; List <string> wordsList = DataHelper.ReadWords(filePath); MySqlOperator sqlOperator = new MySqlOperator(); foreach (string word in wordsList) { string sql = string.Format("select * from elibenglishwords where id = '{0}' ", word); DataTable dt = sqlOperator.QueryDataTable(sql); if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; sql = string.Format("update elibenglishwords set cocaIndex = null where id = '{0}' and PolySemyIndex = {1} ", dr["id"].ToString(), dr["PolySemyIndex"].ToString() ); sqlOperator.UpdateDataTable(sql); } } } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 填充词频 /// </summary> /// <param name="notNullColumn"></param> /// <param name="nullColumn"></param> public static void UpdateIndex(string notNullColumn, string nullColumn) { try { string sql = string.Format(@" select id , PolySemyIndex, {0} , {1} FROM elibenglishwords where issat = 1 and {0} is null and {1} is not null", nullColumn, notNullColumn); MySqlOperator sqlOperator = new MySqlOperator(); DataTable dt = sqlOperator.QueryDataTable(sql); for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; sql = string.Format("update elibenglishwords set {2} = {3} where id = '{0}' and PolySemyIndex = {1} ", dr["id"].ToString(), dr["PolySemyIndex"].ToString(), nullColumn, dr[notNullColumn].ToString()); sqlOperator.UpdateDataTable(sql); } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 更新单词例句 /// </summary> public static void UpdateSentence() { try { string filePath = Environment.CurrentDirectory + "\\词汇frombook - 例句 - 20190121.xlsx"; DataSet ds = DataHelper.ImportData(filePath); DataTable dtUpdate = new DataTable(); dtUpdate.Columns.Add("word"); dtUpdate.Columns.Add("enSentence"); dtUpdate.Columns.Add("chSentence"); foreach (DataTable data in ds.Tables) { for (int i = 0; i < data.Rows.Count; i++) { DataRow drNew = dtUpdate.NewRow(); drNew[0] = data.Rows[i][0].ToString(); drNew[1] = data.Rows[i][4].ToString(); drNew[2] = data.Rows[i][5].ToString(); dtUpdate.Rows.Add(drNew); } } string errorMessage = ""; MySqlOperator sqlOperator = new MySqlOperator(); for (int i = 0; i < dtUpdate.Rows.Count; i++) { string word = dtUpdate.Rows[i][0].ToString().ToLower().Trim(); string enSentence = dtUpdate.Rows[i][1].ToString().ToLower().Trim().Replace("'", "\\'"); string chSentence = dtUpdate.Rows[i][2].ToString().ToLower().Trim().Replace("'", "\\'"); string sentence = "[{\"en\":\"" + enSentence + "\",\"ch\":\"" + chSentence + "\"}]"; if (!string.IsNullOrEmpty(word)) { string sql = string.Format(@"update elibenglishwords set examples = '{0}' where id = '{1}' ", sentence, word); bool success = sqlOperator.UpdateDataTable(sql); if (!success) { errorMessage += word + "\r\n"; //DataHelper.WriteLog(word); } } } } catch (Exception ex) { } }
/// <summary> /// 填充elibIndex,elibIndex = !string.isnullorempty(cocaindex)? cocaindex : nbcindex /// </summary> public static void UpdateElibIndex() { try { MySqlOperator sqlOperator = new MySqlOperator(); string sql = "select * from elibenglishwords "; DataTable dt = sqlOperator.QueryDataTable(sql); int failCount = 0; if (dt != null) { for (int i = 0; i < dt.Rows.Count; i++) { string cocaindex = dt.Rows[i]["cocaindex"].ToString(); string bncindex = dt.Rows[i]["bncindex"].ToString(); string updateValue = ""; // 更新值 if (!string.IsNullOrEmpty(cocaindex)) //优先使用cocaindex { updateValue = cocaindex; } else if (!string.IsNullOrEmpty(bncindex))// 再使用bncindex { updateValue = bncindex; } else { updateValue = "null"; //continue; } string word = dt.Rows[i]["id"].ToString().Replace("'", "\\'"); string updateSql = string.Format("update elibenglishwords set elibindex = {0} where id = '{1}' and PolySemyIndex = {2}", updateValue, word, dt.Rows[i]["polySemyIndex"].ToString()); bool success = sqlOperator.UpdateDataTable(updateSql); if (!success) { failCount++; } } } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 更新单词库词频为COCA60000 /// </summary> public static void UpdateCOCA60000() { try { string filePath = Environment.CurrentDirectory + "\\COCA60000.xlsx"; DataTable dtSource = DataHelper.ImportData(filePath).Tables[0]; string sql = string.Empty; MySqlOperator sqlOperator = new MySqlOperator(); List <string> replaceList = new List <string>() { "(", ")", "(", ")", "[", "]" }; for (int index = 0; index < dtSource.Rows.Count; index++) { string[] words = dtSource.Rows[index][2].ToString().ToLower().Trim().Split( new string[] { "/" }, StringSplitOptions.RemoveEmptyEntries); string frequency = dtSource.Rows[index][0].ToString(); foreach (string temp in words) { string word = temp.Replace("'", "\\'"); foreach (string rep in replaceList) { word = word.Replace(rep, ""); } sql = string.Format(@"update elibenglishwords set cocaIndex = {0} , elibIndex = {0} where id = '{1}' ", frequency, word); bool success = sqlOperator.UpdateDataTable(sql); if (!success) { DataHelper.WriteLog(dtSource.Rows[index][2].ToString().ToLower()); } } } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// <summary> /// 标注SAT /// </summary> public static void MarkSat(MySqlOperator sqlOperator, string word) { try { string sql = string.Format("select * from elibenglishwords where id = '{0}'", word); DataTable dt = sqlOperator.QueryDataTable(sql); if (dt != null)// 标注一词多义 { for (int i = 0; i < dt.Rows.Count; i++) { sql = string.Format("update elibenglishwords set issat = 1 where id = '{0}' and PolySemyIndex = {1}", dt.Rows[i]["id"].ToString(), dt.Rows[i]["polySemyIndex"].ToString()); sqlOperator.UpdateDataTable(sql); } } } catch (Exception ex) { throw new Exception(ex.Message); } }
public static void UpdateWordDefinition() { string filePath = Environment.CurrentDirectory + "\\sat多义final.xls"; DataTable dtSource = DataHelper.ImportData(filePath).Tables[0]; MySqlOperator sqlOperator = new MySqlOperator(); Dictionary <string, List <DataRow> > wordDic = new Dictionary <string, List <DataRow> >(); // 获取源数据并进行分组 for (int i = 0; i < dtSource.Rows.Count; i++) { string word = dtSource.Rows[i]["Id"].ToString().ToLower().Trim(); if (string.IsNullOrEmpty(word)) { continue; } if (wordDic.ContainsKey(word)) { wordDic[word].Add(dtSource.Rows[i]); } else { wordDic.Add(word, new List <DataRow>() { dtSource.Rows[i] }); } int index = wordDic[word].Count - 1; wordDic[word][index]["PolysemyIndex"] = index; } string message = ""; List <string> wordList = new List <string>(wordDic.Keys); for (int i = 0; i < wordList.Count; i++) { string word = wordList[i]; string sql = string.Format("select * from elibenglishwords where id = '{0}' ", word); DataTable dt = sqlOperator.QueryDataTable(sql); if (dt != null && dt.Rows.Count > 0) { // 构建插入数据SQL string updateSql = @"Insert into elibenglishwords ( Id,Definition,Translation,PolysemyIndex,Phonetic, IsSAT,Examples,Audio,BNCIndex,COCAIndex,Collins, IsGRE,IsOxford,IsTOEFL,ElibIndex,IsForVocabularTest ) values "; List <DataRow> rows = wordDic[word]; for (int index = 0; index < rows.Count; index++) { string COCAIndexString = string.IsNullOrEmpty(dt.Rows[0]["COCAIndex"].ToString()) ? "null" : dt.Rows[0]["COCAIndex"].ToString(); string BNCIndexString = string.IsNullOrEmpty(dt.Rows[0]["BNCIndex"].ToString()) ? "null" : dt.Rows[0]["BNCIndex"].ToString(); string CollinsString = string.IsNullOrEmpty(dt.Rows[0]["Collins"].ToString()) ? "null" : dt.Rows[0]["Collins"].ToString(); string IsGREString = string.IsNullOrEmpty(dt.Rows[0]["IsGRE"].ToString()) ? "null" : dt.Rows[0]["IsGRE"].ToString(); string IsOxfordString = string.IsNullOrEmpty(dt.Rows[0]["IsOxford"].ToString()) ? "null" : dt.Rows[0]["IsOxford"].ToString(); string IsTOEFLString = string.IsNullOrEmpty(dt.Rows[0]["IsTOEFL"].ToString()) ? "null" : dt.Rows[0]["IsTOEFL"].ToString(); string ElibIndexString = string.IsNullOrEmpty(dt.Rows[0]["ElibIndex"].ToString()) ? "null" : dt.Rows[0]["ElibIndex"].ToString(); string IsForVocabularTestString = string.IsNullOrEmpty(dt.Rows[0]["IsForVocabularTest"].ToString()) ? "null" : dt.Rows[0]["IsForVocabularTest"].ToString(); updateSql += "\r\n( '" + word + "','" + Replace(rows[index]["Definition"].ToString()) + "','" + Replace(rows[index]["Translation"].ToString()) + "'," + rows[index]["PolysemyIndex"].ToString() + ",'" + Replace(dt.Rows[0]["Phonetic"].ToString()) + "'," + dt.Rows[0]["IsSAT"].ToString() + ",'" + Replace(dt.Rows[0]["Examples"].ToString()) + "','" + dt.Rows[0]["Audio"].ToString() + "'," + BNCIndexString + "," + COCAIndexString + "," + CollinsString + "," + IsGREString + "," + IsOxfordString + "," + IsTOEFLString + "," + ElibIndexString + "," + IsForVocabularTestString + "),"; } updateSql = updateSql.TrimEnd(',') + ";"; // 构建删除sql string deleteSql = string.Format("delete from elibenglishwords where id = '{0}'", word); bool delete = sqlOperator.UpdateDataTable(deleteSql); if (delete) { bool update = sqlOperator.UpdateDataTable(updateSql); if (!update) { message += "更新出错:" + word; } } else { message += "删除出错:" + word; } } } }