//修改
public int ModifyStudent(student objStudent)
{
//编写sql语句
StringBuilder sqlB = new StringBuilder();
sqlB.Append("update students set studentName='{0}',Gender='{1}',Birthday='{2}',");
sqlB.Append("studentIdNo='{3}',PhoneNumber='{4}',studentAddress='{5}',classId={6} ");
sqlB.Append(" where studentId={7}");
//解析对象
string sql = string.Format(sqlB.ToString(), objStudent.StudentName, objStudent.Gender, objStudent.Birthday,
objStudent.StudentIdNo, objStudent.PhoneNumber, objStudent.StudentAddress,
objStudent.ClassId, objStudent.StudentId);
try
{
return(Convert.ToInt32(MysqlHelper.Update(sql)));
}
catch (MySqlException ex)
{
throw new Exception("数据库操作异常:" + ex.Message);
}
catch (Exception ex) {
throw ex;
}
}
//单一查询--详细信息
public student GetStudentById(string studentId)
{
string sql = "select * from students where studentId=" + studentId;
MySqlDataReader objReader = MysqlHelper.GetReader(sql);
student objStudent = null;
if (objReader.Read())
{
objStudent = new student()
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
Gender = objReader["Gender"].ToString(),
Birthday = Convert.ToDateTime(objReader["Birthday"]),
CardNo = objReader["CardNo"].ToString(),
StudentIdNo = objReader["StudentIdNo"].ToString(),
PhoneNumber = objReader["PhoneNumber"].ToString(),
StudentAddress = objReader["StudentAddress"].ToString(),
Age = Convert.ToInt16(objReader["Age"]),
ClassId = Convert.ToInt32(objReader["ClassId"])
};
}
objReader.Close();
return(objStudent);
}
public List <student> GetStudentByClass(string className)
{
string sql = "select a.* from students a,studentclass b where a.ClassId=b.ClassId and b.ClassName like '%{0}%'";
sql = string.Format(sql, className);
MySqlDataReader objReader = MysqlHelper.GetReader(sql);
List <student> list = new List <student>();
while (objReader.Read())
{
list.Add(new student
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
Gender = objReader["Gender"].ToString(),
//Birthday = Convert.ToDateTime(objReader["Birthday"]),
//CardNo = objReader["CardNo"].ToString(),
//StudentIdNo=objReader["StudentIdNo"].ToString(),
//PhoneNumber=objReader["PhoneNumber"].ToString(),
//StudentAddress=objReader["StudentAddress"].ToString()
});
}
objReader.Close();
return(list);
}
public static MysqlHelper GetMysqlInstance()
{
if (mysqlInstance == null)
{
mysqlInstance = new MysqlHelper();
}
return(mysqlInstance);
}
/// <summary>
/// 添加用户
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public int AddUser(UserInfo userInfo)
{
//对数据库进添加一个用户操作
string commandText = "insert into UserInfo(userName, passWord, permision) values(?userName, ?passWord, ?permision)";
MySqlParameter[] paras = new MySqlParameter[]
{
new MySqlParameter("?userName", userInfo.Username),
new MySqlParameter("?passWord", userInfo.Password),
new MySqlParameter("?permision", userInfo.Permision)
};
return(MysqlHelper.ExecuteNonQuery(connectStr, CommandType.Text, commandText, paras));
}
//删除
public int DeleteStudent(string studentId)
{
String sql = "delete from students where studentId='{0}'";
sql = string.Format(sql, studentId);
try
{
return(Convert.ToInt32(MysqlHelper.Update(sql)));
}
catch (MySqlException ex)
{
throw new Exception("数据库操作异常:" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
public Boolean Chcek(LoginUser u) //用户名时候的sql语句
{
string query = "SELECT * FROM `auth_user` WHERE username = @username and password = @password";
MySqlParameter[] commandParameter = new MySqlParameter[] {
new MySqlParameter("@username", u.UserName),
new MySqlParameter("@password", u.PassWord)
};
MySqlDataReader searchResult = MysqlHelper.ExecuteDefaultReader(query, commandParameter);
if (searchResult.HasRows)
{
//如果存在结果说明该登录用户存在
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 查找数据库中是否有相同的用户名
/// </summary>
/// <param name="userInfo"></param>
/// <returns></returns>
public bool IsEqual(UserInfo userInfo)
{
bool res = false;
string commandText = "select * from UserInfo where userName=?userName;";
MySqlParameter paras = new MySqlParameter("?userName", userInfo.Username);
MySqlDataReader mysqlread = MysqlHelper.ExecuteReader(connectStr, CommandType.Text, commandText, paras);//mysqlcom.ExecuteReader(CommandBehavior.CloseConnection);
while (mysqlread.HasRows)
{
if (mysqlread.Read())
{
int feildNum = mysqlread.FieldCount;
res = true;//mysqlread["username"].ToString();
}
else
{
break;
}
}
return(res);
}
public SysAdmin AdminLogin(SysAdmin objAdmin)
{
string sql = "select AdminName from Admins where LoginId='{0}' and LoginPwd='{1}'";
sql = string.Format(sql, objAdmin.LoginId, objAdmin.LoginPwd);
try
{
MySqlDataReader objReader = MysqlHelper.GetReader(sql);
if (objReader.Read())
{
objAdmin.AdminName = objReader["AdminName"].ToString();
objReader.Close();
}
else
{
objAdmin = null;
}
}
catch (Exception ex) {
throw new Exception("应用程序和数据库出现问题" + ex.Message);
}
return(objAdmin);
}
//添加
public int AddStudent(student objStudent)
{
//编写语句
StringBuilder sqlB = new StringBuilder();
sqlB.Append("INSERT INTO students(StudentId, StudentName, Gender, Birthday, StudentIdNo, Age,PhoneNumber,StudentAddress,CardNo,ClassId) VALUES");
sqlB.Append("('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')");
string sql = string.Format(sqlB.ToString(), objStudent.StudentId, objStudent.StudentName,
objStudent.Gender, objStudent.Birthday, objStudent.StudentIdNo, objStudent.Age, objStudent.PhoneNumber,
objStudent.StudentAddress, objStudent.CardNo, objStudent.ClassId);
try {
return(Convert.ToInt32(MysqlHelper.Update(sql)));
}
catch (MySqlException ex)
{
throw new Exception("数据库操作异常:" + ex.Message);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 插入数据库
/// </summary>
/// <param name="picName"></param>
/// <param name="mysqlPass"></param>
/// <returns></returns>
public bool DalInsertPic(string picName, MysqlHelper mh)
{
return(mh.ExecuteOperateMysql(picName));
}
/// <summary>
/// 查询图片是否记录在数据库
/// </summary>
/// <param name="picName">文件名</param>
/// <param name="mysqlPass">数据库密码</param>
/// <returns></returns>
public bool DalSearchPic(string picName, MysqlHelper mh)
{
return(mh.ExecuteQueryMysql(picName));
}