public int deleteMainEventDelete(DateTime end)
{
int numDelete = 0;
string[] tablesName = getListOfTableInPeriod("event", end);
foreach (string table in tablesName)
{
try
{
numDelete += VsDbConnection.deleteMainData(table, end);
//¶éÒäÁèÁÕ¢éÍÁÙÅàËÅ×Íã¹µÒÃÒ§¡çãËéźµÒÃÒ§·Ôé§
if (VsDbConnection.getNumDataInTable(table) == 0 && !table.Equals(VsDbConnection.DateTimeToTableName("event", DateTime.Now)))
{
VsDbConnection.deleteTable(table);
}
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deleteMainEventDelete before:" + end + "table" + table + ex.Message + " " + ex.StackTrace);
}
}
}//ǹ·Ó·Ø¡ table ·ÕèÍÂÙè㹪èǧ firstTable ¶Ö§ endTable
return(numDelete);
}
public string[] getFiles(DateTime end)
{
List <string> files = new List <string>();
try
{
string[] tablesName = getListOfTableInPeriod("data", end);
foreach (string table in tablesName)
{
try
{
files.AddRange(VsDbConnection.getFiles(table, end));
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in getFiles before:" + end + ex.Message + " " + ex.StackTrace);
}
}
}//ǹ·Ó·Ø¡ table ·ÕèÍÂÙè㹪èǧ firstTable ¶Ö§ endTable
}
catch (Exception ex)
{
throw ex;
}
return(files.ToArray());
}
//·Ñé§ËÁ´¹Õéãªé¡Ñº Vs-main à·èÒ¹Ñé¹
//******************************************************************************************************
//ÊÓËÃѺ MainDB
public static int getNumDataInTable(string tableName)
{
string stringSQL = String.Format("SELECT count(*) from {0}", tableName);
DataTable data = new DataTable();
MySqlConnection conn = Connecting(mainDbConnStr);
MySqlDataAdapter da = new MySqlDataAdapter(stringSQL, conn);
//MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
try
{
da.Fill(data);
}
catch (Exception ex)
{
conn.Close();
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in getNumDataInTable " + " " + tableName + " " + ex.Message + " " + ex.StackTrace);
}
throw ex;
}
conn.Close();
int numOfData = int.Parse(data.Rows[0][0].ToString());
return(numOfData);
}
//á»Å§¤èÒ string connection ãËéà»ç¹ connection¨ÃÔ§
public static MySqlConnection Connecting(string connString)
{
MySqlConnection conn = null;
try
{
conn = new MySqlConnection("");
conn.ConnectionString = connString;
conn.Open();
}
catch (MySqlException ex)
{
if (conn != null)
{
conn.Close();
conn.Dispose();
conn = null;
}
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in Connecting to " + clientDbConnStr + " " + ex.Message + " " + ex.StackTrace);
}
throw ex;
// logger.Log(LogLevel.Error, err.Message + " " + err.Source + " " + err.StackTrace); ;
}
return(conn);
}
//*******************************************************************************
//ÃѺµÒÃÒ§·ÕèÁÕ·Ñé§ËÁ´ã¹ main DB
public static DataTable getAllTable()
{
MySqlConnection conn = Connecting(mainDbConnStr);
string stringSQL = "show tables;";
DataTable data = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(stringSQL, conn);
// MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
try
{
da.Fill(data);
}
catch (Exception ex)
{
conn.Close();
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in getAllTable " + " " + ex.Message + " " + ex.StackTrace);
}
throw ex;
}
conn.Close();
return(data);
}
public static DataTable getDataInMainDB(string tableName, DateTime TimeStart, DateTime TimeEnd)
{
string stringSQL = String.Format("SELECT * from {0} where `m_date_start`>='{1}' And `m_date_start`<'{2}'", tableName,
TimeStart.ToString("yyyy/M/d HH:mm:ss", new CultureInfo("en-US")), TimeEnd.ToString("yyyy/M/d HH:mm:ss", new CultureInfo("en-US")));
DataTable data = new DataTable();
MySqlConnection conn = Connecting(mainDbConnStr);
MySqlDataAdapter da = new MySqlDataAdapter(stringSQL, conn);
//MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
try
{
da.Fill(data);
}
catch (Exception ex)
{
conn.Close();
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in getDataInMainDB " + tableName + " " + TimeStart + " " + TimeEnd + " " + ex.Message + " " + ex.StackTrace);
}
throw ex;
}
conn.Close();
return(data);
}
//#########################################################################################################
public static DataTable getDataInMainDB(string tableName)
{
string stringSQL = String.Format("SELECT * FROM {0} ", tableName);
DataTable data = new DataTable();
MySqlConnection conn = Connecting(mainDbConnStr);
MySqlDataAdapter da = new MySqlDataAdapter(stringSQL, conn);
// MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
try
{
da.Fill(data);
}
catch (Exception ex)
{
conn.Close();
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in getDataInMainDB " + " " + ex.Message + " " + ex.StackTrace);
}
throw ex;
}
conn.Close();
return(data);
}
//ź array ¢Í§ file ·Õèµéͧ¡ÒÃ
public int deleteFile(string[] fileFullNames)
{
int falseNum = 0;;
foreach (string fileFullName in fileFullNames)
{
FileInfo file = new FileInfo(fileFullName);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deletefile " + file.Name + ex.Message + " " + ex.StackTrace);
}
tryDelete(tryDeleteFile, fileFullName);
continue;
}
if (!File.Exists(fileFullName))
{
falseNum++;
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + file.FullName);
}
}
else
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("err", " can not deleted: " + file.FullName);
}
tryDelete(tryDeleteFile, fileFullName);
}
}
else
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("err", " don't exist file:" + file.FullName);
}
}
deleteEmtyDir(file.Directory, "*" + file.Extension);
}
return(falseNum);
}
//ãªéä´é·Ñé§ data áÅÐ event
public static int deleteMainData(string tableName, DateTime TimeEnd)
{
MySqlConnection conn = null;
try
{
string stringSQL = string.Format(
"DELETE from {0} where `m_date_start` < '{1}' ", tableName
, TimeEnd.ToString("yyyy/M/d HH:mm:ss", new CultureInfo("en-US")));
conn = Connecting(mainDbConnStr);
MySqlCommand command = new MySqlCommand(stringSQL, conn);
// command.Parameters.AddWithValue("?tableName", tableName);
//command.Parameters.AddWithValue("?TimeEnd", TimeEnd.ToString("yyyy/M/d HH:mm:ss", new CultureInfo("en-US")));
int val = command.ExecuteNonQuery();
conn.Close();
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted from: " + tableName + " before: " + TimeEnd + " numDelete: " + val);
}
return(val);
}
catch (MySqlException ex)
{
if (conn != null)
{
try
{
conn.Close();
conn.Dispose();
conn = null;
}
catch { }
}
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in delete " + tableName + " before:" + TimeEnd + ex.Message + " " + ex.StackTrace);
}
return(0);
}
}
/* µÃǨÊͺÇèÒÁÕ directory ËÃ×ÍäÁè
* µÃǨÊͺÇèÒÁÕ file ÍÂÙèÀÒÂã¹ËÃ×ÍäÁè
* źfile ÀÒÂã¹·Ñé§ËÁ´
* ·Ó¡ÒÃź
*/
public bool deleteEmtyDir(DirectoryInfo dir, string filePatern)
{
if (dir.Exists && dir.GetFiles(filePatern).Length == 0)
{
try
{
dir.Delete(true);
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + dir.FullName);
}
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deletedir " + dir.Name + ex.Message + " " + ex.StackTrace);
}
tryDelete(tryDeleteDir, dir.FullName);
}
if (dir.Parent.Exists && dir.Parent.GetDirectories().Length == 0)
{
try
{
dir.Parent.Delete(true);
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + dir.Parent.FullName);
}
}
catch (Exception ex)
{
VsDbDelete.setmsg("ex", "in deletedir " + dir.Parent.Name + ex.Message + " " + ex.StackTrace);
tryDelete(tryDeleteDir, dir.Parent.FullName);
}
}
return(true);
}
return(false);
}
private int deleteMainDataAndFileEachTable(string table, DateTime end)
{
//´Ö§ª×èÍfile ÁÒ¨Ò¡ main DB
string[] filesName = VsDbConnection.getFiles(table, end);
//àÍÒ¤èÒ¨Ò¡ä¿Åì config
AppConfigData config = new AppConfigData();
config.LoadSettings("app.config");
string rootDir = config.localHost.Storage;//new FileInfo(filesName[0]).Directory.Parent.Parent.FullName;
//ź¢éÍÁÙÅã¹ main DB
int numMainDbDelte = VsDbConnection.deleteMainData(table, end);
//źfile
int numFileDelete = fileManage.deleteFile(filesName);
//¶éÒäÁèÁÕ¢éÍÁÙÅàËÅ×Íã¹µÒÃÒ§¡çãËéźµÒÃÒ§·Ôé§
if (VsDbConnection.getNumDataInTable(table) == 0)
{
if (!table.Equals(VsDbConnection.DateTimeToTableName("data", DateTime.Now)))
{
VsDbConnection.deleteTable(table);
}
deleteDirFileOfTable(table, rootDir);
}
//à»ÅÕºà·ÕèºÇèÒźä»à·èҡѹÁÑéÂ
if ((numMainDbDelte != numFileDelete))
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "number dedeted don't match "
+ " numMainDbDelte:" + numMainDbDelte + " numFileDelete:" + numFileDelete);
}
}
return(numMainDbDelte);
}
public int deleteMainDataAndFile(DateTime end)
{
int numDelete = 0;
string[] tablesName = getListOfTableInPeriod("data", end);
foreach (string table in tablesName)
{
try
{
numDelete += deleteMainDataAndFileEachTable(table, end);
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deleteMainDataAndFile before:" + end + "table" + table + ex.Message + " " + ex.StackTrace);
}
}
}//ǹ·Ó·Ø¡ table ·ÕèÍÂÙè㹪èǧ firstTable ¶Ö§ endTable
return(numDelete);
}
public static bool deleteTable(string tableName)
{
MySqlConnection conn = null;
try
{
string stringSQL = string.Format("DROP TABLE {0}", tableName);
conn = Connecting(mainDbConnStr);
MySqlCommand command = new MySqlCommand(stringSQL, conn);
bool res = false;
if (command.ExecuteNonQuery() > 0)
{
res = true;
}
conn.Close();
return(res);
}
catch (MySqlException ex)
{
if (conn != null)
{
conn.Close();
conn.Dispose();
conn = null;
}
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deleteTable " + " " + tableName + " " + ex.Message + " " + ex.StackTrace);
}
return(false);
}
}
// ǹź file à´ÔÁµÒÁ¨Ó¹Ç¹ÃͺáÅСÒÃ˹èǧàÇÅÒ·Õè¡Ó˹´
public void tryDeleteFile(Object obj)
{
string fileFullName = obj.ToString();
FileInfo file = new FileInfo(fileFullName);
for (int i = 0; i < roundDelete; i++)
{
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deletefile " + file.Name + ex.Message + " " + ex.StackTrace);
}
Thread.Sleep(timeWait);
continue;
}
if (!File.Exists(fileFullName))
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + file.FullName);
}
deleteEmtyDir(file.Directory, "*" + file.Extension);
Thread.Sleep(timeWait);
return;
}
else
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("err", " can not deleted: " + file.FullName);
}
Thread.Sleep(timeWait);
continue;
}
}
else
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + file.FullName);
}
}
}
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "cancel delete: " + file.FullName);
}
}
//ǹź dir à´ÔÁµÒÁ¨Ó¹Ç¹ÃͺáÅСÒÃ˹èǧàÇÅÒ·Õè¡Ó˹´
private void tryDeleteDir(Object obj)
{
string dirName = obj.ToString();
DirectoryInfo dir = new DirectoryInfo(dirName);
for (int i = 0; i < roundDelete; i++)
{
try
{
dir.Delete(true);
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + dirName);
}
//¶éÒÊÒÁÒöź dir ¢Í§¡Åéͧä´é¤Ãº·Ø¡¡Åéͧ
if (dir.Parent.Exists && dir.Parent.GetDirectories().Length == 0)
{
for (int j = 0; j < roundDelete; i++)
{
try
{
dir.Parent.Delete(true);
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "deleted: " + dir.Parent.FullName);
}
return;
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deletedir " + dir.Parent.Name + ex.Message + " " + ex.StackTrace);
}
Thread.Sleep(timeWait);
}
}
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "cancel delete: " + dir.Parent.Name);
}
}
Thread.Sleep(timeWait);
return;
}
catch (Exception ex)
{
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("ex", "in deletedir " + dirName + ex.Message + " " + ex.StackTrace);
}
}
}
if (VsDbDelete.setmsg != null)
{
VsDbDelete.setmsg("msg", "cancel delete: " + dirName);
}
}