public Students()
{
InitializeComponent();
this.currentStudent = new Student();
this.db = new DB();
this.show_list();
LoadData();
}
public int updateStudent(Student student)
{
if (student == null)
return 0;
SQLiteParameter[] parameters = new SQLiteParameter[12];
//string sql = "UPDATE USER SET(USERNAME, PASSWORD, NAME, AGE, SEX, PHONE, EMAIL, ADDRESS, REMARK, PHOTO, IS_ADMIN) VALUES(@mUSERNAME, @mPASSWORD, @mNAME, @mAGE, @mSEX, @mPHONE, @mEMAIL, @mADDRESS, @mREMARK, @mPHOTO, @mIS_ADMIN) WHERE _id=" + student.id;
string sql = "UPDATE USER SET USERNAME = @mUSERNAME, PASSWORD = @mPASSWORD, NAME = @mNAME, AGE = @mAGE, SEX = @mSEX, PHONE = @mPHONE, EMAIL = @mEMAIL, ADDRESS = @mADDRESS , REMARK = @mREMARK, PHOTO = @mPHOTO, IS_ADMIN = @mIS_ADMIN WHERE _id = @m_id";
//VALUES(@mUSERNAME, @mPASSWORD, @mNAME, @mAGE, @mSEX, @mPHONE, @mEMAIL, @mADDRESS, @mREMARK, @mPHOTO, @mIS_ADMIN) WHERE _id=" + student.id;
parameters[11] = (new SQLiteParameter("m_id", student.id));
parameters[0] = (new SQLiteParameter("mUSERNAME", student.USERNAME));
parameters[1] = (new SQLiteParameter("mPASSWORD", student.password));
parameters[2] = (new SQLiteParameter("mNAME", student.NAME));
parameters[3] = (new SQLiteParameter("mAGE", student.age));
parameters[4] = (new SQLiteParameter("mSEX", student.sex));
parameters[5] = (new SQLiteParameter("mPHONE", student.phone));
parameters[6] = (new SQLiteParameter("mEMAIL", student.email));
parameters[7] = (new SQLiteParameter("mADDRESS", student.address));
parameters[8] = (new SQLiteParameter("mREMARK", student.remark));
//parameters[8] = (new SQLiteParameter("mPHOTO", student.photo));
SQLiteParameter p = new SQLiteParameter("mPHOTO", DbType.Binary);
p.Value = student.photo;
parameters[9] = p;
parameters[10] = (new SQLiteParameter("mIS_ADMIN", student.isAdmin));
return this.ExecuteNonQuery(sql, parameters);
}
public int insertStudent(Student student)
{
if (student == null)
return 0;
SQLiteParameter[] parameters = new SQLiteParameter[11];
string sql = "INSERT INTO USER(USERNAME, PASSWORD, NAME, AGE, SEX, PHONE, EMAIL, ADDRESS, REMARK, PHOTO, IS_ADMIN) VALUES(@mUSERNAME, @mPASSWORD, @mNAME, @mAGE, @mSEX, @mPHONE, @mEMAIL, @mADDRESS, @mREMARK, @mPHOTO, @mIS_ADMIN)";
//parameters[0]=(new SQLiteParameter("m_id", student.id));
parameters[0] = (new SQLiteParameter("mUSERNAME", student.USERNAME));
parameters[1] = (new SQLiteParameter("mPASSWORD", student.password));
parameters[2] = (new SQLiteParameter("mNAME", student.NAME));
parameters[3] = (new SQLiteParameter("mAGE", student.age));
parameters[4] = (new SQLiteParameter("mSEX", student.sex));
parameters[5] = (new SQLiteParameter("mPHONE", student.phone));
parameters[6] = (new SQLiteParameter("mEMAIL", student.email));
parameters[7] = (new SQLiteParameter("mADDRESS", student.address));
parameters[8] = (new SQLiteParameter("mREMARK", student.remark));
SQLiteParameter p = new SQLiteParameter("mPHOTO", DbType.Binary);
p.Value = student.photo;
parameters[9] = p;
parameters[10] = (new SQLiteParameter("mIS_ADMIN", student.isAdmin));
return this.ExecuteNonQuery(sql, parameters);
}
public Student getStudentByName(string name)
{
Student student = new Student();
SQLiteCommand cmd = this.connection.CreateCommand();
cmd.CommandText = "SELECT * FROM USER WHERE USERNAME = \"" + name + "\"";
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
try { student.id = reader.GetInt16(0); }
catch (Exception e) { }
try { student.USERNAME = reader.GetString(1); }
catch (Exception e) { }
try { student.password = reader.GetString(2); }
catch (Exception e) { }
try { student.NAME = reader.GetString(3); }
catch (Exception e) { }
try { student.age = reader.GetInt16(4); }
catch (Exception e) { }
try { student.sex = reader.GetString(5); }
catch (Exception e) { }
try { student.phone = reader.GetString(6); }
catch (Exception e) { }
try { student.email = reader.GetString(7); }
catch (Exception e) { }
try { student.address = reader.GetString(8); }
catch (Exception e) { }
try { student.remark = reader.GetString(9); }
catch (Exception e) { }
try { student.isAdmin = reader.GetInt16(11); }
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();
student.photo = desBytes;
streamImage.Close(); // 关闭流
}
catch (Exception e) { }
}
else
{
return null;
}
reader.Close();
return student;
}
/**
* 添加用户
*/
private void Add_Save_Click(object sender, RoutedEventArgs e)
{
int age;
if (this.aUserName.Text == null || this.aUserName.Text.Trim().Equals(""))
{
Message ms = new Message("错误提示", "用户名称不能为空！");
ms.ShowDialog();
return;
}
if (this.db.getStudentByName(this.aUserName.Text)!=null)
{
Message ms = new Message("错误提示", "该名称已经被占用！");
ms.ShowDialog();
return;
}
try
{
age = Int16.Parse(this.aAge.Text);
}
catch (Exception age_e)
{
Message ms = new Message("错误提示", "数据类型不符！年龄必须为数字。");
ms.ShowDialog();
return;
}
Student student = new Student();
student.USERNAME = this.aUserName.Text;
student.password = this.aPassword.Text;
student.NAME = this.aName.Text;
student.age = age;
student.sex = this.aSex_nan.IsChecked == true ? "男" : "女";//this.aSex.Text;
student.phone = this.aPhone.Text;
student.email = this.aEmail.Text;
student.address = this.aAddress.Text;
student.remark = this.aRemark.Text;
student.photo = Utils.Utils.BitmapImageToByteArray((BitmapImage)(this.aPhoto.Source));//Utils.Utils.BitmapImageToByteArray((BitmapImage)(this.aPhoto.Source));
student.isAdmin = this.aIsadmin.IsChecked == true ? 1 : 0;
DB db = new DB();
int res = db.insertStudent(student);
if (res > 0)
{
Message msg = new Message("添加客户", "成功添加客户" + student.USERNAME + "。");
msg.ShowDialog();
this.LoadData();
// 自动跳转
List_Click(sender, e);
}
else
{
Message msg = new Message("添加客户", "添加客户" + student.USERNAME + "失败！");
msg.ShowDialog();
}
}