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;
}
}
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);
}
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 threadGetColInfo(object param)
{
Hashtable pParam = (Hashtable)param;
try
{
GlobalClass.debugLog("ColInfoAssistant", "threadGetColInfo start");
DBConn engine = pParam["engine"] as DBConn;
string dbName = pParam["dbName"] as string;
string tableName = pParam["tableName"] as string;
string tableSn = pParam["tableSn"] as string;
string sql = string.Format("use [" + dbName + "]; " +
"select COLUMN_NAME " +
"from INFORMATION_SCHEMA.COLUMNS with(nolock) " +
"where TABLE_NAME = '{0}' and TABLE_CATALOG='{1}' "
, tableName, dbName);
DataTable result = engine.getData(sql);
SqliteConn lite = new SqliteConn();
string now = GlobalClass.now();
string insSql = @"insert into ColumnInfo (TableInfoSn, ColName, ModifiedDate)
values('{0}','{1}','{2}')";
for (int i = 0; i < result.Rows.Count; i++)
{
string colName = result.Rows[i]["COLUMN_NAME"].ToString();
lite.executeSQL(string.Format(insSql,
tableSn, colName, now));
}
GlobalClass.debugLog("ColInfoAssistant", "threadGetColInfo end, rows: " + result.Rows.Count.ToString());
}
catch (Exception e)
{
GlobalClass.debugLog("ColInfoAssistant", "threadGetColInfo " + e.ToString());
pParam["message"] = e.ToString();
}
finally
{
ColInfoAssistant._columnThread = null;
}
}
public static void deleteInq(string id)
{
SqliteConn acc = new SqliteConn();
try
{
if (id == "") // new
{
return;
}
else
{
string delSql = string.Format("delete from Inquiry where id={0} ", id);
acc.executeSQL(delSql);
}
}
catch (Exception ex)
{
throw ex;
}
}
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;
}
}