public int insertTeacher(Teacher teacher)
{
if (teacher == null)
return 0;
SQLiteParameter[] parameters = new SQLiteParameter[11];
string sql = "INSERT INTO TEACHER(TEACHERNUMBER, NAME, AGE, SEX, PHONE, EMAIL, POSITION, DIRECTION, DETAILS, REMARK, PHOTO) VALUES(@mTEACHERNUMBER, @mNAME, @mAGE, @mSEX, @mPHONE, @mEMAIL, @mPOSITION, @mDIRECTION, @mDETAILS, @mREMARK, @mPHOTO)";
//parameters[0]=(new SQLiteParameter("m_id", student.id));
parameters[0] = (new SQLiteParameter("mTEACHERNUMBER", teacher.TEACHERNUMBER));
parameters[1] = (new SQLiteParameter("mNAME", teacher.NAME));
parameters[2] = (new SQLiteParameter("mAGE", teacher.AGE));
parameters[3] = (new SQLiteParameter("mSEX", teacher.SEX));
parameters[4] = (new SQLiteParameter("mPHONE", teacher.PHONE));
parameters[5] = (new SQLiteParameter("mEMAIL", teacher.EMAIL));
parameters[6] = (new SQLiteParameter("mPOSITION", teacher.POSITION));
parameters[7] = (new SQLiteParameter("mDIRECTION", teacher.DIRECTION));
parameters[8] = (new SQLiteParameter("mDETAILS", teacher.DETAILS));
parameters[9] = (new SQLiteParameter("mREMARK", teacher.REMARK));
SQLiteParameter p = new SQLiteParameter("mPHOTO", DbType.Binary);
p.Value = teacher.PHOTO;
parameters[10] = p;
return this.ExecuteNonQuery(sql, parameters);
}
public int updateTeacher(Teacher teacher)
{
if (teacher == null)
return 0;
SQLiteParameter[] parameters = new SQLiteParameter[12];
string sql = "UPDATE TEACHER SET TEACHERNUMBER = @mTEACHERNUMBER, NAME = @mNAME, SEX = @mSEX, PHONE = @mPHONE, REMARK = @mREMARK, AGE = @mAGE,POSITION = @mPOSITION, EMAIL = @mEMAIL , DETAILS = @mDETAILS, PHOTO = @mPHOTO, DIRECTION = @mDIRECTION WHERE _id = @m_id";
parameters[11] = (new SQLiteParameter("m_id", teacher._id));
parameters[0] = (new SQLiteParameter("mTEACHERNUMBER", teacher.TEACHERNUMBER));
parameters[1] = (new SQLiteParameter("mNAME", teacher.NAME));
parameters[2] = (new SQLiteParameter("mSEX", teacher.SEX));
parameters[3] = (new SQLiteParameter("mAGE", teacher.AGE));
parameters[4] = (new SQLiteParameter("mPHONE", teacher.PHONE));
parameters[5] = (new SQLiteParameter("mEMAIL", teacher.EMAIL));
parameters[6] = (new SQLiteParameter("mPOSITION", teacher.POSITION));
parameters[7] = (new SQLiteParameter("mDIRECTION", teacher.DIRECTION));
parameters[8] = (new SQLiteParameter("mDETAILS", teacher.DETAILS));
parameters[9] = (new SQLiteParameter("mREMARK", teacher.REMARK));
SQLiteParameter p = new SQLiteParameter("mPHOTO", DbType.Binary);
p.Value = teacher.PHOTO;
parameters[10] = p;
return this.ExecuteNonQuery(sql, parameters);
}
// 老师操作
public Teacher getTeacherById(int id)
{
Teacher teacher = new Teacher();
SQLiteCommand cmd = this.connection.CreateCommand();
cmd.CommandText = "SELECT * FROM TEACHER WHERE _id = " + id;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
try { teacher._id = reader.GetInt16(0); }
catch (Exception e) { }
try { teacher.TEACHERNUMBER = reader.GetString(1); }
catch (Exception e) { }
try { teacher.NAME = reader.GetString(2); }
catch (Exception e) { }
try { teacher.AGE = reader.GetInt16(3); }
catch (Exception e) { }
try { teacher.SEX = reader.GetString(4); }
catch (Exception e) { }
try { teacher.PHONE = reader.GetString(5); }
catch (Exception e) { }
try { teacher.EMAIL = reader.GetString(6); }
catch (Exception e) { }
try { teacher.POSITION = reader.GetString(7); }
catch (Exception e) { }
try { teacher.DIRECTION = reader.GetString(8); }
catch (Exception e) { }
try { teacher.DETAILS = reader.GetString(9); }
catch (Exception e) { }
try { teacher.REMARK = reader.GetString(10); }
catch (Exception e) { }
try
{
MemoryStream streamImage = new MemoryStream(reader["PHOTO"] as byte[]);
byte[] desBytes = new byte[streamImage.Length];
streamImage.Read(desBytes, 0, desBytes.Length);
streamImage.Close();
teacher.PHOTO = desBytes;
streamImage.Close(); // 关闭流
}
catch (Exception e) { }
}
else
{
return null;
}
reader.Close();
return teacher;
}