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