/// <summary> /// 插入新老师 /// </summary> /// <param name="dt"></param> /// <param name="frm"></param> /// <returns></returns> public bool InsertNewTeacher(DataTable dt, Form_Config frm = null) { try { if (dt.Columns.Count != 3) { return(false); } CONN.Open(); var trans = CONN.BeginTransaction(); for (int curRow = 0; curRow < dt.Rows.Count; curRow++) { string Name = dt.Rows[curRow][0].ToString(); string Major = dt.Rows[curRow][1].ToString(); string IncludeClass = dt.Rows[curRow][2].ToString(); //if (Name == "杨中建") // ; if (string.IsNullOrEmpty(Name)) { break; } if (frm != null) { frm.Invoke((EventHandler) delegate { frm.Text = "正在导入第 " + curRow + " 项中"; }); } string SQL_Insert = string.Format( @"INSERT INTO TeacherInfo ([Name], [Major], [IncludeClass]) VALUES('{0}', '{1}', '{2}')", Name, Major, IncludeClass ); SQLiteCommand cmd = new SQLiteCommand(SQL_Insert, CONN); cmd.ExecuteNonQuery(); cmd.Dispose(); } trans.Commit(); CONN.Close(); return(true); } catch { return(false); } }
/// <summary> /// 删除不存在照片的学生条目 /// </summary> /// <returns></returns> public List <string> DeleteStudentsWithoutNoImg(Form_Config frm = null, bool DeleteConfirm = false) { try { CONN.Open(); var trans = CONN.BeginTransaction(); List <string> res = new List <string>(); string SQL_SELECT_STUDENTS = @"SELECT * FROM StudentInfo"; SQLiteCommand cmd = new SQLiteCommand(SQL_SELECT_STUDENTS, CONN); SQLiteDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { string Name = sdr["Name"].ToString(); string Class = sdr["Class"].ToString(); string Major = sdr["Major"].ToString(); string PhotoPath = sdr["PhotoPath"].ToString(); string ID = sdr["ID"].ToString(); if (frm != null) { frm.Invoke((EventHandler) delegate { frm.Text = string.Format("正在检测 {0} - {1} - {2} ", Major, Class, Name); }); } if (File.Exists(PhotoPath) || File.Exists(PhotoPath + ".jpg") || File.Exists(PhotoPath + ".png") || File.Exists(PhotoPath + ".jpg.jpg")) //存在 { if (frm != null) { frm.Invoke((EventHandler) delegate { frm.Text += "- 照片存在"; }); } } else { if (frm != null) { frm.Invoke((EventHandler) delegate { frm.Text += "- 照片不存在 - 正在删除"; }); } res.Add(string.Format("{0} - {1} - {2}", Major, Class, Name)); if (DeleteConfirm) { string SQL_DELETE = string.Format(@"DELETE FROM StudentInfo WHERE ID = '{0}'", ID); SQLiteCommand del_cmd = new SQLiteCommand(SQL_DELETE, CONN); del_cmd.ExecuteNonQuery(); } } } trans.Commit(); sdr.Close(); CONN.Close(); return(res); } catch (Exception e) { throw e; } }
/// <summary> /// 插入新的学生信息 /// </summary> /// <param name="dt">数据表</param> /// <returns>操作是否成功</returns> public bool InsertNewStudent(DataTable dt, int current_input_success_total, ref int input_success_total, Form_Config frm = null) { try { input_success_total = 0; if (dt.Columns.Count != 17) { return(false); } CONN.Open(); var trans = CONN.BeginTransaction(); for (int curRow = 0; curRow < dt.Rows.Count; curRow++) { input_success_total = curRow; string ID = dt.Rows[curRow][0].ToString(), Name = dt.Rows[curRow][2].ToString(); if (string.IsNullOrEmpty(Name)) { break; } #region string Number = dt.Rows[curRow][1].ToString().Trim().Replace("\n", "").Replace("\r", ""), Gender = dt.Rows[curRow][3].ToString().Trim().Replace("\n", "").Replace("\r", ""), Class = dt.Rows[curRow][4].ToString().Trim().Replace("\n", "").Replace("\r", ""), Major = dt.Rows[curRow][5].ToString().Trim().Replace("\n", "").Replace("\r", ""), PoliticalStatus = dt.Rows[curRow][6].ToString().Trim().Replace("\n", "").Replace("\r", ""), Nation = dt.Rows[curRow][7].ToString().Trim().Replace("\n", "").Replace("\r", ""), Post = dt.Rows[curRow][8].ToString().Trim().Replace("\n", "").Replace("\r", ""), Address = dt.Rows[curRow][9].ToString().Trim().Replace("\n", "").Replace("\r", ""), Dorm = dt.Rows[curRow][10].ToString().Trim().Replace("\n", "").Replace("\r", ""), DormMember = dt.Rows[curRow][11].ToString().Trim().Replace("\n", "").Replace("\r", ""), Economic = dt.Rows[curRow][12].ToString().Trim().Replace("\n", "").Replace("\r", ""), BonusAndPenalty = dt.Rows[curRow][13].ToString().Trim().Replace("\n", "").Replace("\r", ""), Study = dt.Rows[curRow][14].ToString().Trim().Replace("\n", "").Replace("\r", ""), Habby = dt.Rows[curRow][15].ToString().Trim().Replace("\n", "").Replace("\r", ""), Job = dt.Rows[curRow][16].ToString().Trim().Replace("\n", "").Replace("\r", ""), PhotoPath = "Data/IMG/" + Major + "/" + Class + "/" + Name; #endregion #region /** * string Number = dt.Rows[curRow]["学号"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Gender = dt.Rows[curRow]["性别"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Class = dt.Rows[curRow]["班级"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Major = dt.Rows[curRow]["专业"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * PoliticalStatus = dt.Rows[curRow]["政治面貌"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Nation = dt.Rows[curRow]["民族"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Post = dt.Rows[curRow]["担任职务"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Address = dt.Rows[curRow]["家庭住址"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Dorm = dt.Rows[curRow]["宿舍"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * DormMember = dt.Rows[curRow]["宿舍成员"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Economic = dt.Rows[curRow]["家庭经济状况"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * BonusAndPenalty = dt.Rows[curRow]["奖惩情况"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Study = dt.Rows[curRow]["学习状况"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Habby = dt.Rows[curRow]["兴趣爱好"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * Job = dt.Rows[curRow]["职业倾向"].ToString().Trim().Replace("\n", "").Replace("\r", ""), * PhotoPath = "Data/IMG/" + Major + "/" + Class + "/" + Name; */ #endregion if (frm != null) { frm.Invoke((EventHandler) delegate { frm.Text = "[" + (current_input_success_total + curRow) + "]" + Major + " - " + Class + " - " + Name; }); } //Thread.Sleep(3); string SQL_Insert = string.Format( @"INSERT INTO StudentInfo ([Name], [Gender], [Class], [Major], [PoliticalStatus], [Nation], [Post], [Address], [Dorm], [DormMember], [Economic], [BonusAndPenalty], [Study], [Habby], [PhotoPath], [Job], [Number]) VALUES('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}')", Name, Gender, Class, Major, PoliticalStatus, Nation, Post, Address, Dorm, DormMember, Economic, BonusAndPenalty, Study, Habby, PhotoPath, Job, Number ); SQLiteCommand cmd = new SQLiteCommand(SQL_Insert, CONN); cmd.ExecuteNonQuery(); cmd.Dispose(); } trans.Commit(); CONN.Close(); return(true); } catch (Exception e) { input_success_total = 0; return(false); } }