public async Task <bool> SaveCourse()
{
NewCourse.Start = StartDate;
NewCourse.End = EndDate;
if (await ValidateCourse(NewCourse))
{
if (isUpdate)
{
await SqliteConn.UpdateAsync(NewCourse);
}
else
{
var courseCount = await SqliteConn.Table <Course>().Where(c => c.TermId == Term.Id).CountAsync();
if (courseCount > 5)
{
ErrorText = $"* Term '{Term.Title}' has already reached the maximum number of courses of 6.";
return(false);
}
await SqliteConn.InsertAsync(NewCourse);
}
return(true);
}
return(false);
}
async Task LoadTerm()
{
IsBusy = true;
try
{
Term = await SqliteConn.Table <Term>().FirstOrDefaultAsync(t => t.Id == Term.Id);
Title = $"{Term.Title}'s courses";
var courses = await SqliteConn.Table <Course>().Where(c => c.TermId == Term.Id).OrderBy(term => term.Start).ToListAsync();
// Loading the data causes the refresh to trigger
lock (coursesLock)
{
CanAddCourse = courses.Count < 6;
Courses.Clear();
foreach (var course in courses)
{
Courses.Add(course);
}
}
}
catch (Exception ex)
{
Debug.Write(ex);
}
finally
{
IsBusy = false;
}
}
async Task LoadTerms()
{
IsBusy = true;
try
{
var terms = await SqliteConn.Table <Term>().OrderBy(term => term.Start).ToListAsync();
lock (termsLock)
{
Terms.Clear();
foreach (var term in terms)
{
Terms.Add(term);
}
}
}
catch (Exception ex)
{
Debug.Write(ex);
}
finally
{
IsBusy = false;
}
}
public CodeRefresh()
{
_db = new SqliteConn();
if (CodeRefresh.LastRefreshTime.Equals(DateTime.MinValue))
{
CodeRefresh.LastRefreshTime = DateTime.Now.AddSeconds(-1 * (_refreshInterval + 1));
}
}
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);
}
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 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 async Task <bool> SaveAssessment()
{
NewAssessment.Start = StartDate;
NewAssessment.End = EndDate;
if (!ValidateAssessment())
{
return(false);
}
if (isUpdate)
{
await SqliteConn.UpdateAsync(NewAssessment);
}
else
{
await SqliteConn.InsertAsync(NewAssessment);
}
return(true);
}
public async Task <bool> SaveTerm()
{
NewTerm.Start = StartDate;
NewTerm.End = EndDate;
if (ValidateTerm())
{
if (isUpdate)
{
await SqliteConn.UpdateAsync(NewTerm);
}
else
{
await SqliteConn.InsertAsync(NewTerm);
}
return(true);
}
return(false);
}
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 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 deleteToolStripMenuItem_Click(object sender, EventArgs e)
{
//DataTable dt = this.dgSavedInq.DataSource as DataTable;
string name = this.dgSavedInq.Rows[selectedRows].Cells["Name"].Value.ToString();
string id = this.dgSavedInq.Rows[selectedRows].Cells["id"].Value.ToString();
DialogResult r = MessageBox.Show("Delete \"" + name + "\" ?", "Confirm", MessageBoxButtons.YesNo);
SqliteConn db = null;
try
{
if (r == DialogResult.Yes)
{
db = new SqliteConn();
_recursiveDepth = 0;
deleteInq(db, id);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw ex;
}
finally
{
//if (db != null)
//{
// db.close();
//}
}
loadList();
}
private async Task LoadCourse()
{
IsBusy = true;
HasObjectiveAssessment = false;
HasPerformanceAssessment = false;
try
{
Course = await SqliteConn.Table <Course>().FirstOrDefaultAsync(c => c.Id == Course.Id);
var assessments = await SqliteConn.Table <Assessment>().Where(a => a.CourseId == Course.Id).ToListAsync();
lock (assessmentsLock)
{
foreach (var assessment in assessments)
{
if (assessment.Type == AssessmentType.Objective)
{
ObjectiveAssessment = assessment;
HasObjectiveAssessment = true;
}
else
{
PerformanceAssessment = assessment;
HasPerformanceAssessment = true;
}
}
}
}
catch (Exception ex)
{
Debug.Write(ex);
}
finally
{
IsBusy = false;
}
}
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);
}
public async Task DeleteCourses(Course course)
{
await SqliteConn.DeleteAsync(course);
await LoadTerm();
}
public async Task DeleteAssessment(Assessment assessment)
{
await SqliteConn.DeleteAsync(assessment);
await LoadCourse();
}
public async Task UpdateCourseNotifications()
{
await SqliteConn.UpdateAsync(Course);
}
private static object _obj = new object(); // critical section obj
public ColInfoAssistant()
{
_db = new SqliteConn();
}
public async Task UpdateAssessmentNotifications(Assessment assessment)
{
await SqliteConn.UpdateAsync(assessment);
}
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;
}
}
private static object _obj = new object(); // critical section obj
public TableInfoAssistant()
{
_db = new SqliteConn();
}
public async Task DeleteTerm(Term term)
{
await SqliteConn.DeleteAsync(term);
await LoadTerms();
}