/// <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);
}
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;
}
}
private void invokeColumnInfoThread(string dbName, string tableName, string tableSn, DBConn engine)
{
lock (ColInfoAssistant._obj)
{
if (ColInfoAssistant._columnThread != null)
{
GlobalClass.debugLog("ColInfoAssistant", "invokeColumnInfoThread, running");
return; // running;
}
ColInfoAssistant._columnThread = new Thread(new ParameterizedThreadStart(threadGetColInfo));
ColInfoAssistant._columnThreadParam = new Hashtable();
ColInfoAssistant._columnThreadParam["engine"] = engine;
ColInfoAssistant._columnThreadParam["dbName"] = dbName;
ColInfoAssistant._columnThreadParam["tableName"] = tableName;
ColInfoAssistant._columnThreadParam["tableSn"] = tableSn;
ColInfoAssistant._columnThread.Start(ColInfoAssistant._columnThreadParam);
}
}
