/// <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);
}
}
