/// <summary>
/// 添加一个学生用户。
/// </summary>
/// <param name="student">学生实体信息</param>
/// <returns></returns>
public static bool insertStudent(Student student)
{
try
{
string sql = "insert into users(username, password, sex, grade, age, major, IP, regtime) values(?0,?1,?2,?3,?4,?5,?6,?7);";
MySqlParameter[] para = new MySqlParameter[8];
para[0] = new MySqlParameter("?0", student.username);
para[1] = new MySqlParameter("?1", student.password);
para[2] = new MySqlParameter("?2", student.sex);
para[3] = new MySqlParameter("?3", student.grade);
para[4] = new MySqlParameter("?4", student.age);
para[5] = new MySqlParameter("?5", student.major);
para[6] = new MySqlParameter("?6", student.IP);
para[7] = new MySqlParameter("?7", student.regtime);
int res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
LogWriter.writeLine(@"C:\WEB_ASP\logs\DAL.log", "insertStudent:" + e.Message);
return(false);
}
}
public static bool modifyTeam(Team team)
{
try
{
string sql = "update team set AuditMode=?0,ScoreType=?1,AnswerStatus=?3,CourceName=?4,CourceTerm=?5,StuClass=?6,ScoreType=?7,Introduce=?8 where TeamID=?9";
MySqlParameter[] para = new MySqlParameter[9];
para[0] = new MySqlParameter("?0", team.AuditMode);
para[1] = new MySqlParameter("?1", team.ScoreType);
para[2] = new MySqlParameter("?3", team.AnswerStatus);
para[3] = new MySqlParameter("?4", team.CourceName);
para[4] = new MySqlParameter("?5", team.CourceTerm);
para[5] = new MySqlParameter("?6", team.StuClass);
para[6] = new MySqlParameter("?7", team.ScoreType);
para[7] = new MySqlParameter("?8", team.Introduce);
para[8] = new MySqlParameter("?9", team.TeamID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
/// <summary>
/// 添加订单
/// </summary>
/// <param name="name"></param>
/// <param name="goods"></param>
/// <param name="gcnt"></param>
/// <returns></returns>
public static bool AddOrder(string name, List <Goods> goods, List <int> gcnt)
{
try
{
if (goods.Count != gcnt.Count)
{
return(false);
}
decimal totalPrice = 0.0M;
for (int i = 0; i < goods.Count; i++)
{
totalPrice += goods.ElementAt(i).UnitPrice *gcnt.ElementAt(i);
}
string sql = "insert into tb_order(customerName,totalMoney,orderDate) values(?name,?money,now());";
MySqlParameter[] para = { new MySqlParameter("?name", name), new MySqlParameter("?money", totalPrice) };
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(AddOrderDetail(name, goods, gcnt));
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 更新个人信息
/// </summary>
/// <param name="name">Name</param>
/// <param name="birthday">Birthday</param>
/// <param name="phone">Phone</param>
/// <param name="email">Email</param>
/// <returns>bool</returns>
public static bool UpdatePersonalInfo(string name, string birthday, string phone, string email)
{
try
{
string sql = "update tb_customer set " + (birthday == ""?"": "customerBirthday=?birthday,") + "customerPhone=?phone,customerEmail=?email where customerName=?name;";
List <MySqlParameter> paras = new List <MySqlParameter>();
if (birthday != "")
{
paras.Add(new MySqlParameter("?birthday", birthday));
}
paras.Add(new MySqlParameter("?phone", phone));
paras.Add(new MySqlParameter("?email", email));
paras.Add(new MySqlParameter("?name", name));
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, paras.ToArray());
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 修改二/三级类别的父类
/// </summary>
/// <param name="level">类别等级（2,3）</param>
/// <param name="typeID">TypeID</param>
/// <param name="parentTypeID">ParentTypeID</param>
/// <returns>bool</returns>
public static bool UpdateParentTypeID(int level, string typeID, string parentTypeID)
{
try
{
string tb_name = "tb_goodstype";
if (level == 2)
{
tb_name += "_second";
}
else if (level == 3)
{
tb_name += "_third";
}
string sql = "update " + tb_name + " set goodsParentTypeID=?parentTypeID where goodsTypeID=?typeID;";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?parentTypeID", parentTypeID);
para[1] = new MySqlParameter("?typeID", typeID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
return(false);
}
catch (Exception e)
{
throw e;
}
}
public static bool joinTeam(string teamID, string stuID, bool update = false)
{
string AuditMode = queryTeamAuditMode(teamID);
string status = AuditMode == "自动审核" ? "已加入" : "待审核";
string sql = "";
MySqlParameter[] para = new MySqlParameter[2];
try
{
if (!update)
{
sql = "insert into team_member values(?0,?1,'" + status + "')";
}
else
{
sql = "update team_member set AuditStatus='" + status + "' where TeamID=?0 and StuID=?1";
}
para[0] = new MySqlParameter("?0", teamID);
para[1] = new MySqlParameter("?1", stuID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
/// <summary>
/// 更新收货信息
/// </summary>
/// <param name="name">Name</param>
/// <param name="trueName">TrueName</param>
/// <param name="address">Address</param>
/// <param name="postCode">PostCode</param>
/// <returns>bool</returns>
public static bool UpdatePostInfo(string name, string trueName, string address, string postCode)
{
try
{
string sql = "update tb_customer set customerTrueName=?trueName,customerAddress=?address,customerPostCode=?postCode where customerName=?name;";
MySqlParameter[] para = new MySqlParameter[4];
para[0] = new MySqlParameter("?trueName", trueName);
para[1] = new MySqlParameter("?address", address);
para[2] = new MySqlParameter("?postCode", postCode);
para[3] = new MySqlParameter("?name", name);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 添加一个商品
/// </summary>
/// <param name="name">Name</param>
/// <param name="typeID">TypeID</param>
/// <param name="unitPrice">UnitPrice</param>
/// <param name="descript">Descript</param>
/// <param name="imageName">ImageName</param>
/// <returns>bool</returns>
public static bool AddGood(string name, string typeID, decimal unitPrice, string descript = "", string imageName = "")
{
try
{
string sql = "insert into tb_goods(goodsName,goodsTypeID,goodsUnitPrice,goodsDescript,goodsImageName,goodsDate) values(?name,?typeID,?unitPrice,?descript,?imageName,now());";
MySqlParameter[] para = new MySqlParameter[5];
para[0] = new MySqlParameter("?name", name);
para[1] = new MySqlParameter("?typeID", typeID);
para[2] = new MySqlParameter("?unitPrice", unitPrice);
para[3] = new MySqlParameter("?descript", descript);
para[4] = new MySqlParameter("?imageName", imageName);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
public static bool quitTeam(string teamID, string stuID)
{
string AuditMode = queryTeamAuditMode(teamID);
string sql = "";
MySqlParameter[] para = new MySqlParameter[2];
try
{
sql = "update team_member set AuditStatus='未加入' where TeamID=?0 and StuID=?1";
para[0] = new MySqlParameter("?0", teamID);
para[1] = new MySqlParameter("?1", stuID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
/// <summary>
/// 更新商品信息
/// </summary>
/// <param name="goods"></param>
/// <returns></returns>
public static bool UpdateGood(Goods goods)
{
try
{
string sql = "update tb_goods set goodsName=?name,goodsTypeID=?typeID,goodsDescript=?descript,goodsUnitPrice=?unitPrice,goodsImageName=?imageName where goodsID=?id;";
MySqlParameter[] para = new MySqlParameter[6];
para[0] = new MySqlParameter("?name", goods.Name);
para[1] = new MySqlParameter("?typeID", goods.TypeID);
para[2] = new MySqlParameter("?descript", goods.Desctipt);
para[3] = new MySqlParameter("?unitPrice", goods.UnitPrice);
para[4] = new MySqlParameter("?imageName", goods.ImageName);
para[5] = new MySqlParameter("?id", goods.Id);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 添加一个教师用户。
/// </summary>
/// <param name="teacher">教师实体信息</param>
/// <returns></returns>
public static bool insertTeacher(Teacher teacher)
{
try
{
string sql = "insert into teacher(username, password, sex, age) values(?0,?1,?2,?3);";
MySqlParameter[] para = new MySqlParameter[4];
para[0] = new MySqlParameter("?0", teacher.username);
para[1] = new MySqlParameter("?1", teacher.password);
para[2] = new MySqlParameter("?2", teacher.sex);
para[3] = new MySqlParameter("?3", teacher.age);
int res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
LogWriter.writeLine(@"C:\WEB_ASP\logs\DAL.log", "insertTeacher:" + e.Message);
return(false);
}
}
/// <summary>
/// 添加用户
/// </summary>
/// <param name="name">Name</param>
/// <param name="pass">Pass</param>
/// <param name="question">Question</param>
/// <param name="answer">Answer</param>
/// <returns>bool</returns>
public static bool AddCustomer(string name, string pass, string question, string answer)
{
try
{
string sql = "insert into tb_customer(customerName,customerPass,customerRegDate,customerQuestion,customerAnswer) values(?name,?pass,now(),?question,?answer);";
MySqlParameter[] para = new MySqlParameter[4];
para[0] = new MySqlParameter("?name", name);
para[1] = new MySqlParameter("?pass", DAL_Safety.GetMD5(pass));
para[2] = new MySqlParameter("?question", question);
para[3] = new MySqlParameter("?answer", DAL_Safety.EncodeBase64(answer));
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 修改一个学生信息（包括密码、年龄、年纪、性别、专业）。
/// </summary>
/// <param name="student">修改后的学生信息</param>
/// <returns></returns>
public static bool modifyStudent(Student student)
{
string username = student.username;
string password = student.password;
if (password.Length != 0)
{
password = DAL_Safety.getMD5(password);
}
string age = student.age;
string grade = student.grade;
string sex = student.sex;
string major = student.major;
try
{
string sql = "";
int res = 0;
if (password.Length != 0)
{
sql = "update users set password=?1, grade=?2, age=?3, sex=?4, major=?5 where username=?6;";
MySqlParameter[] para = new MySqlParameter[6];
para[0] = new MySqlParameter("?1", password);
para[1] = new MySqlParameter("?2", grade);
para[2] = new MySqlParameter("?3", age);
para[3] = new MySqlParameter("?4", sex);
para[4] = new MySqlParameter("?5", major);
para[5] = new MySqlParameter("?6", username);
res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
}
else
{
sql = "update users set grade=?1, age=?2, sex=?3, major=?4 where username=?5;";
MySqlParameter[] para = new MySqlParameter[5];
para[0] = new MySqlParameter("?1", grade);
para[1] = new MySqlParameter("?2", age);
para[2] = new MySqlParameter("?3", sex);
para[3] = new MySqlParameter("?4", major);
para[4] = new MySqlParameter("?5", username);
res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
}
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
LogWriter.writeLine("DAL.log", "modifyStudent:" + e.Message);
return(false);
}
}
/// <summary>
/// 修改教师信息。
/// </summary>
/// <param name="teacher">修改后的教师实体信息</param>
/// <returns></returns>
public static bool modifyTeacher(Teacher teacher)
{
string username = teacher.username;
string password = teacher.password;
if (password.Length != 0)
{
password = DAL_Safety.getMD5(password);
}
string age = teacher.age;
string sex = teacher.sex;
try
{
string sql = "";
int res = 0;
if (password.Length != 0)
{
sql = "update teacher set password=?1, age=?2, sex=?3 where username=?4;";
MySqlParameter[] para = new MySqlParameter[4];
para[0] = new MySqlParameter("?1", password);
para[1] = new MySqlParameter("?2", age);
para[2] = new MySqlParameter("?3", username);
para[3] = new MySqlParameter("?4", username);
res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
}
else
{
sql = "update teacher set age=?1, sex=?2 where username=?3;";
MySqlParameter[] para = new MySqlParameter[3];
para[0] = new MySqlParameter("?1", age);
para[1] = new MySqlParameter("?2", sex);
para[2] = new MySqlParameter("?3", username);
res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
}
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
LogWriter.writeLine("DAL.log", "modifyTeacher:" + e.Message);
return(false);
}
}
/// <summary>
/// 根据ID删除一个学生。
/// </summary>
/// <param name="ID">要删除的学生ID</param>
/// <returns></returns>
public static bool deleteStudentByID(string ID)
{
string sql = "delete from users where id=?1";
MySqlParameter[] para = new MySqlParameter[1];
para[0] = new MySqlParameter("?1", ID);
int res = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (res > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除二/三级中父类别为指定父类别的所有类别（删除二级类别时，需要先删除这些二级类别对应的所有三级类别）
/// </summary>
/// <param name="level">类别等级（2或3）</param>
/// <param name="parentTypeID">ParentTypeID</param>
/// <returns>bool</returns>
public static bool DeleteType(int level, string parentTypeID)
{
try
{
string sql = "delete from " + (level == 2 ? "tb_goodstype_second" : "tb_goodstype_third") + " where goodsParentTypeID=?typeID;";
MySqlParameter para = new MySqlParameter("?typeID", parentTypeID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
return(false);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 删除一个一级类别（需要先删除其二级类别）
/// </summary>
/// <param name="typeID">TypeID</param>
/// <returns>bool</returns>
public static bool DeleteType(string typeID)
{
try
{
string sql = "delete from tb_goodstype where goodsTypeID=?typeID;";
MySqlParameter para = new MySqlParameter("?typeID", typeID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
return(false);
}
catch (Exception e)
{
throw e;
}
}
public static bool deleteTeamByID(string id)
{
try
{
string sql = "delete from team where TeamID=?1";
MySqlParameter para = new MySqlParameter("?1", id);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch
{
return(false);
}
}
/// <summary>
/// 删除一个商品
/// </summary>
/// <param name="id">ID</param>
/// <returns>bool</returns>
public static bool DeleteGood(string id)
{
try
{
string sql = "delete from tb_goods where goodsID=?id;";
MySqlParameter para = new MySqlParameter("?id", id);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 添加一级类别
/// </summary>
/// <param name="typeName">TypeName</param>
/// <returns>bool</returns>
public static bool AddType(string typeName)
{
try
{
string sql = "insert into tb_goodstype(goodsTypeName) values(?name);";
MySqlParameter para = new MySqlParameter("?name", typeName);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 删除用户
/// </summary>
/// <param name="name">Name</param>
/// <returns>bool</returns>
public static bool DeleteCustomer(string name)
{
try
{
string sql = "delete from tb_customer where customerName=?name;";
MySqlParameter para = new MySqlParameter("?name", name);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 修改商品图片名称
/// </summary>
/// <param name="id"></param>
/// <param name="imageName"></param>
/// <returns></returns>
public static bool UpdateGoodsImageName(string id, string imageName)
{
try
{
string sql = "update tb_goods set goodsImageName=?imageName where goodsID=?id;";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?imageName", imageName);
para[1] = new MySqlParameter("?id", id);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 添加管理员
/// </summary>
/// <param name="name"></param>
/// <param name="pass"></param>
/// <returns>bool</returns>
public static bool AddManage(string name, string pass)
{
try
{
string sql = "insert into tb_manage(managerName,managerPWD) values(?name,?pass);";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?name", name);
para[1] = new MySqlParameter("?pass", DAL_Safety.GetMD5(pass));
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="name">Name</param>
/// <param name="pass">Pass（新）</param>
/// <returns>bool</returns>
public static bool UpdatePassword(string name, string pass)
{
try
{
string sql = "update tb_customer set customerPass=?pass where customerName=?name;";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?pass", DAL_Safety.GetMD5(pass));
para[1] = new MySqlParameter("?name", name);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 更新密保问题
/// </summary>
/// <param name="name">Name</param>
/// <param name="question">Question</param>
/// <param name="answer">Answer</param>
/// <returns>bool</returns>
public static bool UpdateQuestion(string name, string question, string answer)
{
try
{
string sql = "update tb_customer set customerQuestion=?question,customerAnswer=?answer where customerName=?name;";
MySqlParameter[] para = new MySqlParameter[3];
para[0] = new MySqlParameter("?question", question);
para[1] = new MySqlParameter("?answer", DAL_Safety.EncodeBase64(answer));
para[2] = new MySqlParameter("?name", name);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 添加一个二/三级类别
/// </summary>
/// <param name="typeName">TypeName</param>
/// <param name="level">级别（2或3）</param>
/// <param name="parentTypeID">ParentTypeID</param>
/// <returns>bool</returns>
public static bool AddType(string typeName, int level, string parentTypeID)
{
try
{
string typeLevel = level == 2 ? "tb_goodstype_second" : "tb_goodstype_third";
string sql = "insert into " + typeLevel + "(goodsTypeName,goodsParentTypeID) values(?name,?parentTypeID);";
MySqlParameter[] para = new MySqlParameter[2];
para[0] = new MySqlParameter("?name", typeName);
para[1] = new MySqlParameter("?parentTypeID", parentTypeID);
int ret = DAL_MysqlHelper.ExecuteNonQuery(sql, para);
if (ret == 1)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception e)
{
throw e;
}
}
