数据库操作类：
来自周公的： 文件取自
这个图标是从文件中提取出来的，后面会介绍这个方法
根据理论考试题目分布：
道路交通安全法规和规章 25 (违法行为综合判断与案例分析)
地方性法规 5%
道路交通信号 20% （常见交通标志标线和交警手势信号辨识）
安全行车，文明驾驶知识 20% （驾驶职业道德和文明驾驶常识）（安全行车常识）
复杂条件道路下的安全驾驶知识 10% （恶劣气候和复杂道路条件下驾驶常识）
紧急情况处理10% （紧急情况下避险常识）
常见故障判断，车辆维护5%
自救和危险品 5% （交通事故救护及常见危化品处置常识）
根据我的题库改为
违法行为综合判断与案例分析 15% 单选7
安全行车常识 25% 判断10 单选 15
常见交通标志标线和交警手势信号辨识 20% 判断10 单选8
驾驶职业道德和文明驾驶常识 10% 判断5 单选5
恶劣气候和复杂道路条件下驾驶常识 10% 判断 5 单选5
紧急情况下避险常识 10% 判断 5 单选5
交通事故救护及常见危化品处置常识 10% 判断5 单选5
多选题10个从总题库中抽取10个
说明：
在先前数据库设计中我在题库中设计了两个属性，Rate和Random， 其中Rate是用来提高错题的出现次数，Random则用来随机抽取题目，从下面的select语句中可看出：
ORDER BY Rate DESC,Random ，按照先Rate降序排列，再按随机数Random降序排列，Limit使用来限定一次去多少个的！IN则是集合操作，表示在这里面取数据
选择
- 取50个选择题
SELECT TestID,BelongTo,Type,Question,Answer,Image,Video FROM COMPREHENSIVE_EXAM
WHERE TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1001')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 7)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1002')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 15)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1003')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 8)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1004')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 5)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1005')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 5)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1006')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 5)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1007')
AND Type = '单选'
ORDER BY Rate DESC,Random
LIMIT 5)
- 取40个判断题
SELECT TestID,BelongTo,Type,Question,Answer,Image,Video FROM COMPREHENSIVE_EXAM
WHERE TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1002')
AND Type = '判断'
ORDER BY Rate DESC,Random
LIMIT 10)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1003')
AND Type = '判断'
ORDER BY Rate DESC,Random
LIMIT 10)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1004')
AND Type = '判断'
ORDER BY Rate DESC,Random
LIMIT 5)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1005')
AND Type = '判断'
ORDER BY Rate DESC,Random
LIMIT 5)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1006')
AND Type = '判断'
ORDER BY Rate DESC,Random
LIMIT 5)
OR
TestID IN (SELECT TestID FROM COMPREHENSIVE_EXAM
WHERE BelongTo IN (SELECT NodeID FROM NODE WHERE ParNodeID = 'N1007')
AND Type = '判断'
ORDER BY Rate DESC,Random
LIMIT 5)
- 取10个多选题
SELECT TestID,BelongTo,Type,Question,Answer,Image,Video
FROM COMPREHENSIVE_EXAM
WHERE Type = '多选'
ORDER BY Rate DESC,Random
LIMIT 10
插入
//向表ERRORLIST插入数据
private void InsertERRORLIST(string TableName, string TestID)
{
string sql = "INSERT INTO ERRORLIST(UID, TableName, TestID, DateTime)" + "values(@UID, @TableName, @TestID, @DateTime)";
SQLiteParameter[] parameters = new SQLiteParameter[]{
new SQLiteParameter("@UID","UI1001"),
new SQLiteParameter("@TableName", TableName),
new SQLiteParameter("@TestID", TestID),
new SQLiteParameter("@DateTime", DateTime.Now.ToString("yyyy-MM-dd")),
};
db.ExecuteNonQuery(sql, parameters);
}
更新
//错题比率清零
private void SetZero()
{
string sql = "UPDATE COMPREHENSIVE_EXAM SET Rate = 500 ";
db.ExecuteNonQuery(sql, null);
MessageBox.Show("清零成功");
}
//更新数据库中题目排名rate，答对减一，答错加一,并且将错误的答案写到表ErrorList中
private void UpadateSqlRate()
{
using (SQLiteConnection connection = new SQLiteConnection("Data Source= CarTraining.db"))
{
connection.Open();
using (DbTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand command = new SQLiteCommand(connection))
{
int toAdd = 0;
string sqlsel;
string sqlupd;
for (int i = 0; i < 100; i++)
{
int rightAnswer = Convert.ToInt32(dt.Rows[i][4]);
if (allAnswer[i] == rightAnswer)
{
toAdd = -1;
}
else
{
toAdd = 1;
}
//根据testID获取表COMPREHENSIVE_EXAM中的Rate排名值
sqlsel = "SELECT Rate FROM COMPREHENSIVE_EXAM WHERE TestID = '"
+ dt.Rows[i][0].ToString() +"'";
command.CommandText = sqlsel;
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
//更新表COMPREHENSIVE_EXAM中的排名值
toAdd = Convert.ToInt32(data.Rows[0][0]) + toAdd;
sqlupd = "UPDATE COMPREHENSIVE_EXAM SET Rate = " + toAdd
+ " WHERE TestID = '" + dt.Rows[i][0].ToString() + "'";
command.CommandText = sqlupd;
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
}
}
删除
//错题集清空
private void EmptyErrorList()
{
string sql = "DELETE FROM ERRORLIST";
db.ExecuteNonQuery(sql, null);
MessageBox.Show("错题集清空成功");
}