public bool ConfigExists(int group)
{
//DataSet ds = null;
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_SpecifiedRoomConfig, new object[1] {
group
});
if (dt.Rows.Count > 0)
{
return(true);
}
//ds = SQLiteHelper.ExecuteDataSet(
// SQLiteHelper.connectString,
// sqlSelect_SpecifiedRoomConfig, new object[1] { group });
//if (ds != null)
//{
// if (ds.Tables.Count > 0)
// {
// if (ds.Tables[0].Rows.Count > 0)
// {
// return true;
// }
// }
//}
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(false);
}
public static bool insert_record(string id, string info, string create_time)
{
bool bR = false;
try
{
int result = 0;
object[] pars = new object[]
{
id, info, create_time
};
result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(sql_insert_record, pars).ToString());
if (result >= 1)
{
bR = true;
}
else
{
bR = false;
}
}
catch
{
//MessageBox.Show("出现错误：" + ex.Message);
}
return(bR);
}
public static bool QuestionExists(string content)
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_getSpecifiedQuestion, new object[1] {
content
});
if (dt.Rows.Count > 0)
{
return(true);
}
//ds = SQLiteHelper.ExecuteDataSet(
// SQLiteHelper.connectString,
// sqlSelect_getSpecifiedQuestion, new object[1] { content });
//if (ds != null)
//{
// if (ds.Tables.Count > 0)
// {
// if (ds.Tables[0].Rows.Count > 0)
// {
// return true;
// }
// }
//}
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(false);
}
public static DataTable GetClassCheckInfo(string className, string start, string end)
{
DataSet ds = null;
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(
sqlSelect_GetClassCheckInfo, new object[3] {
className, start, end
});
return(dt);
//ds = SQLiteHelper.ExecuteDataSet(
// SQLiteHelper.connectString,
// sqlSelect_GetClassCheckInfo, new object[3] { className, start, end });
//if (ds != null)
//{
// if (ds.Tables.Count > 0)
// {
// return ds.Tables[0];
// }
//}
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public static List <string> GetnjList()
{
List <string> list = new List <string>();
DataSet ds = null;
DataTable dt = null;
try
{
if (InitialDB())
{
dt = CsharpSQLiteHelper.ExecuteTable(SqlGetnj, null);
for (int i = 0; i < dt.Rows.Count; i++)
{
list.Add(dt.Rows[i][0].ToString());
}
//ds = SQLiteHelper.ExecuteDataSet(dbPath, SqlGetnj, null);
}
//if (ds != null && ds.Tables.Count > 0)
//{
// DataTable dt = ds.Tables[0];
// for (int i = 0; i < dt.Rows.Count; i++)
// {
// list.Add(dt.Rows[i][0].ToString());
// }
//}
}
catch (System.Exception ex)
{
MessageBox.Show("读取学生数据库时出现错误！" + ex.Message);
return(list);
}
return(list);
}
public static bool add_question_record(string question_id, string student_id, string answer)
{
string sql = sql_Insert_question_answer_info;
try
{
int result = 0;
result = CsharpSQLiteHelper.ExecuteNonQuery(
sql
, new object[3]
{
question_id
, student_id
, answer
});
if (result > 0)
{
return(true);
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新数据时出现错误：" + ex.Message);
}
return(false);
}
public static bool deleteQuestion(string question_id)
{
string sql = sqlDelete_delete_specified_question;
try
{
int result = 0;
result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(
sql
, new object[1]
{
question_id
}).ToString());
//result = int.Parse(SQLiteHelper.ExecuteNonQuery(SQLiteHelper.connectString,
// sql
// , new object[1]
// {
// question_id
// }).ToString());
if (result > 0)
{
return(true);
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新数据时出现错误：" + ex.Message);
}
return(false);
}
/// <summary>
/// 将学号和epc相关联
/// </summary>
/// <param name="xh"></param>
/// <param name="epc"></param>
/// <returns></returns>
public static bool BindPersonEPC(string xh, string epc)
{
bool bR = false;
try
{
int result = 0;
object[] pars = new object[2] {
xh, epc
};
result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(SqlBindPerson2EPC, pars).ToString());
if (result >= 1)
{
bR = true;
}
else
{
bR = false;
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新记录出现错误：" + ex.Message);
}
return(bR);
}
public DataTable getStudentInfo(string id)
{
DataSet ds = null;
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_GetSpecifiedStudentInfo, new object[1] {
id
});
return(dt);
//ds = SQLiteHelper.ExecuteDataSet(
// SQLiteHelper.connectString,
// sqlSelect_GetSpecifiedStudentInfo, new object[1] { id });
//if (ds != null)
//{
// if (ds.Tables.Count > 0)
// {
// return ds.Tables[0];
// }
//}
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public static bool PersonExist(Person p)
{
bool bR = false;
if (InitialDB())
{
int result = 0;
DataTable dt = CsharpSQLiteHelper.ExecuteTable(SqlCheckSpecialPersonExist, new object[1] {
p.id_num
});
if (dt.Rows.Count >= 1)
{
bR = true;
}
}
/*
* List<Person> lp = rfidCheck_CheckOn.GetPersonList();
* if (lp != null)
* {
*
* for (int i = 0; i < lp.Count; i++)
* {
* if (p.id_num == lp[i].id_num)
* {
* bR = true;
* break;
* }
* }
* }
*/
return(bR);
}
public bool DeleteNotExistedGroup(List <int> groups)
{
string str_not_in = string.Empty;
for (int i = 0; i < groups.Count; i++)
{
if (str_not_in == string.Empty)
{
str_not_in = groups[i].ToString();
}
else
{
str_not_in = str_not_in + "," + groups[i].ToString();
}
}
try
{
int result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(
sqlDelete_allNotExistedGroups
, new object[1]
{
str_not_in
}).ToString());
if (result > 0)
{
return(true);
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新数据时出现错误：" + ex.Message);
}
return(false);
}
public static bool AddQuestion(string question_id, string caption, string answer, string question_index)
{
try
{
int result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(
sqlInsert_addQuestion
, new object[4]
{
question_id
, caption
, answer
, question_index
}).ToString());
//int result = int.Parse(SQLiteHelper.ExecuteNonQuery(SQLiteHelper.connectString,
// sqlInsert_addQuestion
// , new object[4]
// {
// question_id
// ,caption
// ,answer
// ,question_index
// }).ToString());
if (result > 0)
{
return(true);
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新数据时出现错误：" + ex.Message);
}
return(false);
}
public bool AddMapConfig(string equipID, int group, int row, int column)
{
try
{
int result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(
sqlInsert_SaveConfig
, new object[4]
{
equipID
, group
, row
, column
}).ToString());
//int result = int.Parse(SQLiteHelper.ExecuteNonQuery(SQLiteHelper.connectString,
// sqlInsert_SaveConfig
// , new object[4]
// {
// equipID
// ,group
// ,row
// ,column
// }).ToString());
if (result > 0)
{
return(true);
}
}
catch (System.Exception ex)
{
MessageBox.Show("添加数据时出现错误：" + ex.Message);
}
return(false);
}
public bool AddClassCheckInfo(string time, string name, string percentage)
{
try
{
int result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(
sqlInsert_AddClassCheckInfo
, new object[3]
{
time
, name
, percentage
}).ToString());
//int result = int.Parse(SQLiteHelper.ExecuteNonQuery(SQLiteHelper.connectString,
// sqlInsert_AddClassCheckInfo
// , new object[3]
// {
// time
// ,name
// ,percentage
// }).ToString());
if (result > 0)
{
return(true);
}
}
catch (System.Exception ex)
{
MessageBox.Show("添加数据时出现错误：" + ex.Message);
}
return(false);
}
public static DataTable getAllQuestion()
{
DataSet ds = null;
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_getAllQuestion, null);
return(dt);
//ds = SQLiteHelper.ExecuteDataSet(
// SQLiteHelper.connectString,
// sqlSelect_getAllQuestion, null);
//if (ds != null)
//{
// if (ds.Tables.Count > 0)
// {
// return ds.Tables[0];
// }
//}
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
/// <summary>
/// 获取指定时间考勤过的人员列表
/// </summary>
/// <param name="strCheckDate"></param>
/// <returns></returns>
//public static List<CheckPerson> GetCheckedPersonList(string strCheckDate)
//{
// List<CheckPerson> personList = new List<CheckPerson>();
// ///*
// //if (null == strCheckDate)
// //{
// // return null;
// //}
// //DataSet myDataSetPerson = rfidCheck_CheckOn.GetPersonDataSet();
// //DataSet myDataSetRecords = rfidCheck_CheckOn.GetCheckDataSet();
// //DataTable tbPerson = myDataSetPerson.Tables["student"];
// //if (tbPerson == null)
// //{
// // rfidCheck_CheckOn.AddStudentTableToPersonDS(ref myDataSetPerson);
// // tbPerson = myDataSetPerson.Tables["student"];
// //}
// //DataTable tbRecords = myDataSetRecords.Tables["CheckRecords"];
// //if (null == tbRecords)
// //{
// // rfidCheck_CheckOn.AddCheckRecordTableToCheckDS(ref myDataSetRecords);
// // tbRecords = myDataSetRecords.Tables["CheckRecords"];
// //}
// //string strDate = null;
// //strDate = rfidCheck_CheckOn.GetDateSubString(strCheckDate);
// //if (null != strDate)
// //{
// // for (int i = 0; i < tbRecords.Rows.Count; i++)
// // {
// // string strDateTemp = rfidCheck_CheckOn.GetDateSubString(tbRecords.Rows[i]["Date"].ToString()); ;
// // if (null != strDateTemp && strDateTemp == strDate)// 保证获得的记录的时间和要求的时间一致
// // {
// // string tempID = tbRecords.Rows[i]["id_no"].ToString();
// // string tempDate = tbRecords.Rows[i]["Date"].ToString();
// // for (int j = 0; j < tbPerson.Rows.Count; j++)
// // {
// // if (tbPerson.Rows[j]["id_no"].ToString() == tempID)
// // {
// // CheckRecord cr = new CheckRecord(tempID, tbPerson.Rows[j]["name"].ToString(), tbRecords.Rows[i]["Date"].ToString());
// // Person p = new Person(tbPerson.Rows[j]["id_no"].ToString(), tbPerson.Rows[j]["name"].ToString(), tbPerson.Rows[j]["tel"].ToString(), tbPerson.Rows[j]["mail"].ToString());
// // CheckPerson cp = new CheckPerson(tempDate, p);
// // personList.Add(cp);
// // break;
// // }
// // }
// // }
// // }
// //}
// return personList;
//}
//public static List<CheckRecord> GetCheckRecords(string startTime, string endTime)
//{
// List<CheckRecord> records = new List<CheckRecord>();
// return records;
//}
/// <summary>
/// 获取考勤记录
/// </summary>
/// <param name="strCheckDate">需要获取的考勤记录的时间</param>
/// <returns>考勤记录的集合</returns>
//public static List<CheckRecord> GetCheckRecords(string strCheckDate)
//{
// if (null == strCheckDate)
// {
// return null;
// }
// List<CheckRecord> records = new List<CheckRecord>();
// DataSet myDataSetRecords = rfidCheck_CheckOn.GetCheckDataSet();
// DataTable tbRecords = myDataSetRecords.Tables[0];
// if (null != tbRecords)
// {
// }
// /*
// if (null == tbRecords)
// {
// rfidCheck_CheckOn.AddCheckRecordTableToCheckDS(ref myDataSetRecords);
// tbRecords = myDataSetRecords.Tables["CheckRecords"];
// }
// string strDate = null;
// strDate = rfidCheck_CheckOn.GetDateSubString(strCheckDate);
// if (null!=strDate)
// {
// for (int i = 0; i < tbRecords.Rows.Count; i++)
// {
// string strDateTemp = rfidCheck_CheckOn.GetDateSubString(tbRecords.Rows[i]["Date"].ToString()); ;
// if (null != strDateTemp && strDateTemp == strDate)// 保证获得的记录的时间和要求的时间一致
// {
// string tempID = tbRecords.Rows[i]["id_no"].ToString();
// string tempDate=tbRecords.Rows[i]["Date"].ToString();
// CheckRecord cr = new CheckRecord(tempID, tempDate);
// records.Add(cr);
// }
// }
// }
// */
// return records;
//}
public static bool CheckEPCUsed(string epc)
{
bool bR = false;
try
{
if (InitialDB())
{
int result = 0;
object[] pars = new object[1]
{
epc
};
DataTable dt = CsharpSQLiteHelper.ExecuteTable(SqlCheckEPCUsed, pars);
if (dt.Rows.Count > 0)
{
bR = true;
}
//result = int.Parse(SQLiteHelper.ExecuteScalar(dbPath, SqlCheckEPCUsed, pars).ToString());
//if (result >= 1)
//{
// bR = true;
//}
//else
//{
// bR = false;
//}
}
}
catch (System.Exception ex)
{
MessageBox.Show("更新记录出现错误：" + ex.Message);
}
return(bR);
}
public static DataTable GetCheckedPersonDataSet(string dateStart, string dateEnd)
{
//DataSet ds = null;
DataTable dt = null;
try
{
if (InitialDB())
{
dt = CsharpSQLiteHelper.ExecuteTable(
SqlSelectCheckedPersonInPeriod
, new object[2] {
dateStart, dateEnd
});
//ds = SQLiteHelper.ExecuteDataSet(dbPath,
// SqlSelectCheckedPersonInPeriod
// , new object[2] { dateStart, dateEnd });
}
}
catch (System.Exception ex)
{
MessageBox.Show("读取学生数据库时出现错误！" + ex.Message);
return(null);
}
return(dt);
}
public static DataTable GetCheckDataSet()
{
DataSet dsCheckRecords = null;
DataTable dtCheckRecords = null;
try
{
if (InitialDB())
{
dtCheckRecords = CsharpSQLiteHelper.ExecuteTable(SqlSelectAllRecords, null);
//dsCheckRecords = SQLiteHelper.ExecuteDataSet(dbPath, SqlSelectAllRecords, null);
}
/*
*
* if (!File.Exists(FileCheckName))
* {
* rfidCheck_CheckOn.InitialCheckDB();
* }
* dsCheckRecords = new DataSet();
* dsCheckRecords.ReadXml(FileCheckName);
*/
}
catch (System.Exception ex)
{
MessageBox.Show("读取学生数据库时出现错误！" + ex.Message);
return(null);
}
return(dtCheckRecords);
}
public static DataTable GetStatisticCheckInfoDataSet(string startDate, string endDate
, string nj, string bj)
{
DataSet ds = null;
DataTable dt = null;
if (null == startDate || null == endDate)
{
return(dt);
}
int paraCount = 2;
string sql = SqlSelectCheckedPersonWithPara_head;
if (null != nj)
{
paraCount++;
sql += SqlSelectCheckedPersonWithPara_nj;
}
if (null != bj)
{
paraCount++;
sql += SqlSelectCheckedPersonWithPara_bj;
}
sql += SqlSelectCheckedPersonWithPara_tail;
Object[] paras = new Object[paraCount];
paras[0] = startDate;
paras[1] = endDate;
if (null != nj)
{
paras[2] = nj;
}
if (null != bj)
{
if (paras[2] == null)
{
paras[2] = bj;
}
else
{
paras[3] = bj;
}
}
try
{
if (InitialDB())
{
dt = CsharpSQLiteHelper.ExecuteTable(sql, paras);
//ds = SQLiteHelper.ExecuteDataSet(dbPath, sql, paras);
}
}
catch (System.Exception ex)
{
MessageBox.Show("读取学生数据库时出现错误！" + ex.Message);
return(null);
}
return(dt);
}
public static bool PersonDelete(Person p)
{
bool bR = false;
try
{
if (InitialDB())
{
int result = 0;
object[] pars = new object[1]
{
p.id_num
};
result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(SqlDeletePerson, pars).ToString());
if (result >= 1)
{
bR = true;
}
else
{
bR = false;
}
}
/*
*
* DataSet myDataSet = GetPersonDataSet();
* DataTable dt = myDataSet.Tables["student"];
* if (dt == null)
* {
* rfidCheck_CheckOn.AddStudentTableToPersonDS(ref myDataSet);
* dt = myDataSet.Tables["student"];
* }
* if (null != dt)
* {
* for (int i = 0; i < dt.Rows.Count; i++)
* {
* if (dt.Rows[i]["id_no"].ToString() == p.id_num)
* {
* dt.Rows[i].Delete();
* myDataSet.WriteXml(FilePersonName);
* bR = true;
* break;
* }
* }
* }
*
*/
}
catch (System.Exception ex)
{
MessageBox.Show("删除记录出现错误：" + ex.Message);
}
return(bR);
}
public static bool PersonAdd(Person p)
{
bool bR = false;
try
{
if (InitialDB())
{
int result = 0;
object[] pars = new object[7];
pars[0] = p.id_num;
pars[1] = p.name;
pars[2] = p.sex;
pars[3] = p.age;
pars[4] = p.bj;
pars[5] = p.email;
pars[6] = p.deviceID;
result = int.Parse(CsharpSQLiteHelper.ExecuteNonQuery(SqlInsertPerson, pars).ToString());
if (result >= 1)
{
bR = true;
}
else
{
bR = false;
}
}
/*
* DataSet myDataSet = GetPersonDataSet();
* DataTable dt = myDataSet.Tables["student"];
* if (dt == null)
* {
* rfidCheck_CheckOn.AddStudentTableToPersonDS(ref myDataSet);
* dt = myDataSet.Tables["student"];
* }
* if (null != dt)
* {
* DataRow myRow = dt.NewRow();
* myRow["id_no"] = p.id_num;
* myRow["name"] = p.name;
* myRow["tel"] = p.telephone;
* myRow["mail"] = p.email;
* dt.Rows.Add(myRow);
* myDataSet.WriteXml(FilePersonName);
* bR = true;
* }
*/
}
catch (System.Exception ex)
{
MessageBox.Show("添加新记录出现错误：" + ex.Message);
}
return(bR);
}
public static void clearStudentInfo()
{
try
{
CsharpSQLiteHelper.ExecuteNonQuery(sqlDelete_clearStudentInfo, null);
CsharpSQLiteHelper.ExecuteNonQuery(sqlDelete_clearStudentEpcLink, null);
}
catch (System.Exception ex)
{
MessageBox.Show("更新数据时出现错误：" + ex.Message);
}
}
public DataTable getAllRoomConfigs()
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sql_select_room_configs, null);
return(dt);
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public static bool AddCheckInfo(string record_id, string STUDENTID, string CHECK_TIME)
{
bool bR = true;
int result = CsharpSQLiteHelper.ExecuteNonQuery(sqlInsert_AddCheckInfo, new object[3] {
record_id, STUDENTID, CHECK_TIME
});
if (result <= 0)
{
bR = false;
}
return(bR);
}
public static DataTable getQuestion(string question_id)
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_getQuestion, new object[] { question_id });
return(dt);
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public static DataTable getAllStudentInfo()
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_allGetStudentInfo, null);
return(dt);
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public static DataTable getAllRightAnswer(string question_id, string answer)
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sql_select_right_answer, new object[] { question_id, answer });
return(dt);
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public DataTable DeleteRoomConfig(string name)
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sql_delete_room_configs, new object[] { name });
return(dt);
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}
public int GetGroupCount()
{
int count = 0;
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_allRoomConfig, null);
count = dt.Rows.Count;
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(count);
}
public DataTable getStudentInfo(string id)
{
try
{
DataTable dt = CsharpSQLiteHelper.ExecuteTable(sqlSelect_GetSpecifiedStudentInfo, new object[1] {
id
});
return(dt);
}
catch (System.Exception ex)
{
MessageBox.Show("查询数据库时出现错误：" + ex.Message);
}
return(null);
}