public class StudentDAL
{
/// <summary>
/// 分页显示
/// </summary>
/// <param name="sname"></param>
/// <param name="cId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List<StudentInfo> ShowStudentInfo(string sname,int cId,int pageIndex,int pageSize)
{
string strwhere = "";
if (!string.IsNullOrEmpty(sname))
{
strwhere += $"and s.SName like '%{sname}%'";
}
if (cId>0)
{
strwhere += "and c.CId=" + cId;
}
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@strwhere",strwhere),
new SqlParameter("@pageIndex",pageIndex),
new SqlParameter("@pageSize",pageSize),
new SqlParameter("@totalCount",SqlDbType.Int),
new SqlParameter("@pageCount",SqlDbType.Int)
};
paras[3].Direction = ParameterDirection.Output;
paras[4].Direction = ParameterDirection.Output;
var dt = DBHelper.GetDataTableProc("pr_ShowStudent", paras);
var strlist = JsonConvert.SerializeObject(dt);
var list = JsonConvert.DeserializeObject<List<StudentInfo>>(strlist);
if (list.Count > 0)
{
list.FirstOrDefault().TotalCount = Convert.ToInt32(paras[3].Value);
list.FirstOrDefault().PageCount = Convert.ToInt32(paras[4].Value);
}
return list;
}
/// <summary>
/// 班级下拉
/// </summary>
/// <returns></returns>
public List<ClassInfo> ShowClass()
{
string sql = "select * from ClassInfo";
var dt = DBHelper.GetDataTable(sql);
var strlist = JsonConvert.SerializeObject(dt);
var list = JsonConvert.DeserializeObject<List<ClassInfo>>(strlist);
return list;
}
/// <summary>
/// 导入学生信息
/// </summary>
/// <param name="mod"></param>
/// <returns></returns>
public int ImportStudent(StudentInfo mod)
{
string sql = $"insert into StudentInfo values ({mod.SNo},'{mod.SName}','{mod.SSex}','{mod.Sqq}','{mod.SPhone}',{mod.CId})";
return DBHelper.ExecuteNonQuery(sql);
}
/// <summary>
/// 删除学生
/// </summary>
/// <param name="sId"></param>
/// <returns></returns>
public int DelStudent(int sId)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@sId",sId)
};
int n= DBHelper.ExecuteNonQueryProc("pr_DelStudent", paras);
return n;
}
/// <summary>
/// 添加学生
/// </summary>
/// <param name="mod"></param>
/// <returns></returns>
public int AddStudent(StudentInfo mod)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@sNo",mod.SNo),
new SqlParameter("@sName",mod.SName),
new SqlParameter("@sSex",mod.SSex),
new SqlParameter("@sqq",mod.Sqq),
new SqlParameter("@sPhone",mod.SPhone),
new SqlParameter("@cId",mod.CId),
new SqlParameter("@success",SqlDbType.Int),
};
paras[6].Direction = ParameterDirection.Output;
DBHelper.ExecuteNonQueryProc("pr_AddStudent", paras);
return Convert.ToInt32(paras[6].Value);
}
/// <summary>
/// 反填学生
/// </summary>
/// <param name="sId"></param>
/// <returns></returns>
public StudentInfo GetStudentInfo(int sId)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@sId",sId)
};
var dt=DBHelper.GetDataTableProc("pr_GetStudent", paras);
var strlist = JsonConvert.SerializeObject(dt);
var mod = JsonConvert.DeserializeObject<List<StudentInfo>>(strlist).FirstOrDefault();
return mod;
}
/// <summary>
/// 修改学生
/// </summary>
/// <param name="mod"></param>
/// <returns></returns>
public int UpdStudent(StudentInfo mod)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@sNo",mod.SNo),
new SqlParameter("@sName",mod.SName),
new SqlParameter("@sSex",mod.SSex),
new SqlParameter("@sqq",mod.Sqq),
new SqlParameter("@sPhone",mod.SPhone),
new SqlParameter("@cId",mod.CId)
};
return DBHelper.ExecuteNonQueryProc("pr_UpdStudent", paras);
}