public void refreshTableInfo(string dbconnName, string dbName, DBConn engine)
{
lock (CodeRefresh._lock)
{
double diffSeconds = DateTime.Now.Subtract(CodeRefresh.LastRefreshTime).TotalSeconds;
if ((int)diffSeconds <= _refreshInterval)
{
GlobalClass.debugLog("CodeRefresh", "not exceed refresh time");
return;
}
CodeRefresh.LastRefreshTime = DateTime.Now;
// 檢查是否需要更新
string chkSql1 = string.Format(@"select TableName from TableInfo where DBConnName = '{0}' and DBName = '{1}' ",
dbconnName, dbName);
Reader r = _db.getDataReader(chkSql1);
// 有table資料，再檢查是否過期
if (r.Count > 0)
{
string chkSql2 = string.Format(@"SELECT distinct DBConnName,DBName FROM TableInfo
where DBConnName = '{0}' and DBName = '{1}'
and ModifiedDate < '{2}'", dbconnName, dbName, this.EffectiveDate);
r = _db.getDataReader(chkSql2);
if (r.Count == 0) // 沒有過期的
{
GlobalClass.debugLog("CodeRefresh", "no data expired");
return;
}
}
// 更新
if (CodeRefresh._tableThread != null)
{
GlobalClass.debugLog("CodeRefresh", "thread still running");
return; // running;
}
CodeRefresh._tableThread = new Thread(new ParameterizedThreadStart(threadRefreshTable));
CodeRefresh._tableThreadParam = new Hashtable();
CodeRefresh._tableThreadParam["engine"] = engine;
CodeRefresh._tableThreadParam["dbconnName"] = dbconnName;
CodeRefresh._tableThreadParam["dbName"] = dbName;
GlobalClass.debugLog("CodeRefresh", "invoke thread");
CodeRefresh._tableThread.Start(CodeRefresh._tableThreadParam);
} // end lock
}
/// <summary>
/// 取得table/view
/// </summary>
/// <param name="dbconnName"></param>
/// <returns></returns>
public DataTable getTables(string dbconnName, string dbName, DBConn engine)
{
GlobalClass.debugLog("TableInfoAssistant", string.Format("getTables, dbconnName={0}, dbName={1}, dbstr={2}", dbconnName, dbName, engine.Dbstr));
CodeRefresh cr = new CodeRefresh();
cr.refreshTableInfo(dbconnName, dbName, engine);
// 取得local儲存的table
string sql = string.Format(@"select TableName from TableInfo
where DBConnName = '{0}' and DBName = '{1}' order by Favorite desc, TableName asc ",
dbconnName, dbName);
Reader r = _db.getDataReader(sql);
DataTable dt = new DataTable();
dt.Columns.Add("TableName");
while (r.Read())
{
DataRow dr = dt.NewRow();
dr["TableName"] = r[0].ToString();
dt.Rows.Add(dr);
}
dt.AcceptChanges();
return(dt);
}
public DataTable getColumnInfo(string dbconnName, string dbName, string tableName, DBConn engine)
{
GlobalClass.debugLog("ColInfoAssistant", string.Format("getColumnInfo, dbconnName:{0},dbName:{1},tableName:{2}, dbstr:{3}",
dbconnName, dbName, tableName, engine.Dbstr));
_db.executeSQL(string.Format("delete from ColumnInfo where TableInfoSn in (select distinct TableInfoSn ColumnInfo where ModifiedDate < '{0}') ", this.EffectiveDate));
DataTable dt = new DataTable();
dt.Columns.Add("ColumnName");
// 取得 TableInfoSn
string sqlGetSn = string.Format(@"select sn from TableInfo
where DBConnName = '{0}' and DBName = '{1}' and TableName = '{2}' ",
dbconnName, dbName, tableName);
Reader r = _db.getDataReader(sqlGetSn);
if (r.Read() == false)
{
return(dt);
}
string tableSn = r[0].ToString();
// 找出columns
string sql = string.Format(@"select ColName from ColumnInfo where TableInfoSn = '{0}' ", tableSn);
r = _db.getDataReader(sql);
while (r.Read())
{
DataRow dr = dt.NewRow();
dr["ColumnName"] = r[0].ToString();
dt.Rows.Add(dr);
}
dt.AcceptChanges();
if (dt.Rows.Count == 0)
{
GlobalClass.debugLog("ColInfoAssistant", "getColumnInfo, invokeColumnInfoThread");
invokeColumnInfoThread(dbName, tableName, tableSn, engine);
}
return(dt);
}
public static string saveInq(string id, string parentId, string group, string name, string content, string shortKey)
{
SqliteConn acc = new SqliteConn();
try
{
Reader rCheck = acc.getDataReader(string.Format("select * from Inquiry where groupName = '{0}' and inqName = '{1}' ",
group, name));
if (rCheck.Read() == false) // new
{
int rows = acc.executeSQL(string.Format("insert into Inquiry(groupName, inqName, content, parentId, shortKey) values('{0}','{1}','{2}', '{3}','{4}'); ",
group, name, dbValue(content), parentId, shortKey));
if (rows != 0)
{
Reader r = acc.getDataReader("select max(id) as mid from Inquiry");
if (r.Read())
{
return(r[0].ToString());
}
}
}
else // update
{
DialogResult r = MessageBox.Show("覆蓋原有的內容?", "Confirm", MessageBoxButtons.YesNo);
if (r == DialogResult.Yes)
{
string uptSql = string.Format("update Inquiry set groupName='{0}', inqName='{1}', content='{2}', parentId='{3}', shortKey='{5}' where id={4} ",
group, name, dbValue(content), parentId, id, shortKey);
acc.executeSQL(uptSql);
}
return(id);
}
return("");
}
catch (Exception ex)
{
throw ex;
}
}
private void deleteInq(SqliteConn db, string id)
{
++_recursiveDepth;
if (_recursiveDepth > 100)
{
throw new Exception("recursive error");
}
Reader r = db.getDataReader("select id from Inquiry where parentId = '" + id + "'");
while (r.Read())
{
deleteInq(db, r[0].ToString());
}
db.executeSQL("delete from Inquiry where id = " + id);
}
private void loadList()
{
SqliteConn acc = new SqliteConn();
try
{
Reader r = acc.getDataReader(
"select id, groupName, inqName, content, shortKey from Inquiry where parentId = '' and (groupName='" + ddlGroup.Text + "') "); // or groupName='' or groupName is null
DataTable dt = new DataTable();
dt.Columns.Add("id");
dt.Columns.Add("Group");
dt.Columns.Add("Name");
dt.Columns.Add("SQL");
dt.Columns.Add("shortKey");
while (r.Read())
{
DataRow row = dt.NewRow();
row["id"] = r[0].ToString();
row["Group"] = r[1].ToString();
row["Name"] = r[2].ToString();
row["SQL"] = r[3].ToString();
row["shortKey"] = r[4].ToString();
dt.Rows.Add(row);
}
this.dgSavedInq.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
//acc.close();
}
}
public static ArrayList getGroups()
{
SqliteConn db = new SqliteConn();
try
{
Reader r = db.getDataReader("select distinct groupName from Inquiry where groupName <> '' ");
ArrayList list = new ArrayList();
while (r.Read())
{
list.Add(r[0]);
}
return(list);
}
catch (Exception e)
{
GlobalClass.errorLog(e.ToString());
}
return(null);
}
private void threadRefreshTable(object param)
{
Hashtable pParam = (Hashtable)param;
try
{
DBConn engine = pParam["engine"] as DBConn;
string dbconnName = pParam["dbconnName"] as string;
string dbName = pParam["dbName"] as string;
GlobalClass.debugLog("TableInfoAssistant", "threadGetTable start" + engine.Dbstr);
// 取得新table
string sql = "select * from " + dbName + ".INFORMATION_SCHEMA.TABLES with(nolock) where TABLE_NAME not like 'syncobj_%'";
DataTable result = engine.getData(sql);
SqliteConn lite = new SqliteConn();
string sqlCheckTable = "select TableName from TableInfo where DBConnName='" + dbconnName + "' and DBName = '" + dbName + "' and TableName = '{0}'";
string insSql = @"insert into TableInfo (DBConnName, DBName, TableName, ModifiedDate, TableType)
values('{0}','{1}','{2}','{3}','{4}')";
string uptSql = @"update TableInfo set ModifiedDate = '{0}' where DBConnName='" + dbconnName + "' and DBName = '" + dbName + "' and TableName = '{1}'";
string now = GlobalClass.now();
for (int i = 0; i < result.Rows.Count; i++)
{
// 逐筆更新 TableInfo
string tbname = result.Rows[i]["TABLE_NAME"].ToString();
string tbtype = result.Rows[i]["TABLE_TYPE"].ToString();
Reader r = lite.getDataReader(string.Format(sqlCheckTable, tbname));
if (r.Count > 0)
{
// update
lite.executeSQL(string.Format(uptSql, now, tbname));
}
else
{
// insert
lite.executeSQL(string.Format(insSql, dbconnName, dbName, tbname, now, tbtype
));
}
Thread.Sleep(300); // 不要造成負擔
}
// 移除不存在的table
string delSql = string.Format("delete FROM TableInfo where DBConnName='{0}' and DBName = '{1}' and ModifiedDate < '{2}'",
dbconnName, dbName, now);
lite.executeSQL(delSql);
}
catch (Exception e)
{
GlobalClass.debugLog("CodeRefresh", "threadRefreshTable, " + e.ToString());
pParam["message"] = e.ToString();
}
finally
{
CodeRefresh._tableThread = null;
}
}