public static long InsertWithTransaction(string table, List <Dictionary <string, object> > data)
{
try
{
using (var cmd = new SQLiteCommand(sql_con))
{
using (var transaction = sql_con.BeginTransaction())
{
//Add your query here.
foreach (Dictionary <string, object> nameValueData in data)
{
InsertReq(table, nameValueData);
}
transaction.Commit();
}
}
return(1);
}
catch (Exception ex)
{
Utils.ErrLog(ex);
return(-1);
}
}
public static void Dispose()
{
try {
wordApplication?.Quit();
} catch (Exception ex) {
Utils.ErrLog(ex);
}
}
public static string pdfText(string path)
{
PdfReader reader = new PdfReader(path);
string text = string.Empty;
for (int page = 1; page <= reader.NumberOfPages; page++)
{
try {
text += PdfTextExtractor.GetTextFromPage(reader, page);
} catch (Exception ex) {
Utils.ErrLog("Error processing page: " + page.ToString(), ex.Message);
}
}
reader.Close();
return(text);
}
public static List <xWordFrequencies> GetFrequencies(long fileId)
{
string query = string.Format("SELECT * FROM wf_frequencies WHERE file_id={0}", fileId);
List <xWordFrequencies> list = new List <xWordFrequencies>();
SQLiteCommand cmd = new SQLiteCommand();
cmd = sql_con.CreateCommand();
cmd.CommandText = query;
SQLiteDataReader Reader = cmd.ExecuteReader();
if (!Reader.HasRows)
{
return(null);
}
try
{
while (Reader.Read())
{
xWordFrequencies xwf = new xWordFrequencies()
{
id = Convert.ToInt64(GetDBInt64("id", Reader)),
fileId = Convert.ToInt64(GetDBInt64("file_id", Reader)),
word = GetDBString("word", Reader),
rank = GetDBInt("rank", Reader),
frequency = GetDBInt("frequency", Reader),
percentage = GetDBFloat("percentage", Reader),
};
list.Add(xwf);
}
}
catch (Exception ex)
{
Utils.ErrLog("Ошибка при чтении xwf из БД", ex.Message);
Utils.msgInformation("Ошибка при чтении БД, требуется обновление БД");
}
Reader.Close();
return(list);
}
public static long RemoveReq(string table, long id)
{
try
{
sql_cmd.CommandText = "DELETE from `" + table + "` WHERE `id`=" + id.ToString();
// Execution and getting result
try
{
return(sql_cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
Debug.WriteLine("ERR WHEN DELETING A ROW");
return(-1);
}
}
catch (Exception ex)
{
Utils.ErrLog(ex);
return(-1);
}
}
private void loadFiles()
{
this.Enabled = false;
lblStatus.Text = "Загружаю список файлов";
// Load all supported files from the chosen dretory
this.Enabled = true;
Utils.fList = new List <xTextFile>();
try {
foreach (string file in Directory.EnumerateFiles(Utils.WorkDirPath, "*.*", (chkSubdirectories.Checked) ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly)
.Where(s => s.EndsWith(".doc") || s.EndsWith(".docx") || s.EndsWith(".odt") || s.EndsWith(".pdf") || s.EndsWith(".txt") || s.EndsWith(".xlsx") || s.EndsWith(".rtf") || s.EndsWith(".htm") || s.EndsWith(".html")))
{
Utils.fList.Add(new xTextFile(file));
}
olvFiles.SetObjects(Utils.fList);
lblStatus.Text = "Готов";
} catch (UnauthorizedAccessException unaex) {
Utils.ErrLog(unaex);
Utils.msgCriticalError("Недостаточно прав для обработки данной директори, запустите программу с правами администратора, или выберите другую папку");
}
}
public static void Chistka()
{
ResetSQLiteConnection();
int iteration = -1;
string query = string.Format("SELECT * FROM `wf_frequencies` Where `id` > " + iteration);
List <xWordFrequencies> list = new List <xWordFrequencies>();
SQLiteCommand cmd = new SQLiteCommand();
cmd = sql_con.CreateCommand();
cmd.CommandText = query;
SQLiteDataReader Reader = cmd.ExecuteReader();
if (!Reader.HasRows)
{
return;
}
int count = 145000;
int removed = 0;
try
{
using (StreamWriter sw_debug = new StreamWriter("output.log", true, Encoding.UTF8))
{
using (StreamWriter sw_output = new StreamWriter("output.sql", true, Encoding.UTF8))
{
while (Reader.Read())
{
iteration++;
xWordFrequencies xwf = new xWordFrequencies()
{
id = Convert.ToInt64(GetDBInt64("id", Reader)),
fileId = Convert.ToInt64(GetDBInt64("file_id", Reader)),
word = GetDBString("word", Reader),
rank = GetDBInt("rank", Reader),
frequency = GetDBInt("frequency", Reader),
percentage = GetDBFloat("percentage", Reader),
};
String line = xwf.word;
line = line.Replace("1", "Ӏ");
line = line.Replace("I", "Ӏ");
line = line.Replace("l", "Ӏ");
line = line.Replace("ѐ", "ё");
line = line.Replace("e", "е");
line = line.Replace("a", "а");
line = line.Replace("p", "р");
line = line.Replace("o", "о");
line = line.Replace("i", "Ӏ");
line = line.Replace("l", "Ӏ");
line = line.Replace("k", "к");
line = line.Replace("x", "х");
line = line.Replace("y", "у");
line = line.Replace("n", "п");
line = line.Replace("m", "м");
line = line.Replace("c", "с");
line = line.Replace("r", "г");
line = line.Replace("u", "и");
line = line.Replace("Ѐ", "Ё");
line = line.Replace("E", "Е");
line = line.Replace("A", "А");
line = line.Replace("B", "В");
line = line.Replace("P", "Р");
line = line.Replace("O", "О");
line = line.Replace("I", "Ӏ");
line = line.Replace("K", "К");
line = line.Replace("X", "Х");
line = line.Replace("T", "Т");
line = line.Replace("M", "М");
line = line.Replace("C", "С");
xwf.word = line;
sw_debug.Write(string.Format("{0}/{1} {2} ", iteration, count, line));
Debug.Write(string.Format("{0}/{1} {2} ", iteration, count, line));
// IS IT CYRILLIC ?
if (!Regex.IsMatch(line, @"\A[\s\W\p{IsCyrillic}]*\z"))
{
// if not, remove
// Debug.Write("ILLEGAL.. REMOVING..." + (RemoveReq(TABLE_FREQUENCIES, xwf.id) > 0 ? "OK" : "FAIL"));
sw_debug.WriteLine("");
Debug.WriteLine("");
removed++;
continue;
}
Dictionary <string, object> nameValueData = new Dictionary <string, object>();
nameValueData.Add("word", line);
if ((UpdateReq("wf_frequencies", nameValueData, xwf.id) > 0))
{
// OK
sw_output.WriteLine("INSERT INTO `wf_frequencies` VALUES({0},{1},{2},'{3}',{4},{5})", xwf.id, xwf.fileId, xwf.rank, xwf.word, xwf.frequency, xwf.percentage);
sw_debug.Write("Ok");
Debug.Write("Ok");
}
else
{
// FAIL
// Exception must mean that such element is already existing in the database, so remove it
// Ideally I should have added their frequency but it's not that important so I am not doing it
// Debug.Write("FAIL, REMOVING ..." + (RemoveReq(TABLE_FREQUENCIES, xwf.id) > 0 ? "OK" : "FAIL"));
removed++;
}
sw_debug.WriteLine("");
Debug.WriteLine("");
}
}
sw_debug.WriteLine("END OF THE UNIVERSE!");
Debug.WriteLine("END OF THE UNIVERSE!");
sw_debug.WriteLine("REMOVED: " + removed);
Debug.WriteLine("REMOVED: " + removed);
}
}
catch (Exception ex)
{
Utils.ErrLog("Ошибка при чтении xwf из БД", ex.Message);
Utils.msgInformation("Ошибка при чтении БД, требуется обновление БД");
}
Reader.Close();
}