//取出当前所有资源分类
public DataTable selectAll()
{
DataTable dt = new DataTable();
string sql = "select * from category";
dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
return(dt);
}
/// <summary>
/// 查询正在上机学生
/// </summary>
/// <param name="studentOnline">正在上机实体</param>
/// <returns>以表形式返回</returns>
public DataTable SelectOnlineStuent()
{
//定义查询语句
string sql = @"select * from OnLineStudent";
//接受查询返回值
DataTable SelectOnlineStudnet = sqlHelper.ExecuteQuery(sql, CommandType.Text);
return(SelectOnlineStudnet);
}
/// <summary>
/// 查询基础表
/// </summary>
/// <returns>整个基础表信息</returns>
public DataTable SelectBast()
{
//定义SQL语句
string sql = @"select * from Basis";
//接受返回值
DataTable SelectBast = sqlHelper.ExecuteQuery(sql, CommandType.Text);
return(SelectBast);//返回结果
}
/// <summary>
/// 查询基础信息
/// </summary>
/// <param name="basitInf">基础信息实体</param>
/// <returns>基础信息临时表</returns>
public DataTable SelectBasit()
{
//定义SQL语句
string sql = @"select * from Basis";
//获取查询结果
DataTable BasiTable = sqlHelper.ExecuteQuery(sql, CommandType.Text);
//返回查询结果
return(BasiTable);
}
/// <summary>
/// 查询管理者信息
/// </summary>
/// <param name="manager">管理者实体</param>
/// <returns>以表形式返回，管理者信息。</returns>
public DataTable SelectManagerial(Managerial manager)
{
//定义参数
SqlParameter[] sqlams = { new SqlParameter("@ManId", manager.ManId) };
//定义SQL语句
string sql = @"select * from Managerial where ManId=@ManId";
//接受查询结果
DataTable SelectUserLogingTable = sqlHelper.ExecuteQuery(sql, sqlams, CommandType.Text);
return(SelectUserLogingTable);
}
/// <summary>
/// 判断用户是否存在
/// </summary>
/// <param name="username">用户名</param>
/// <returns></returns>
public bool SelectUser(string username)
{
bool flag = false;
string sql = "select UserName from Login where UserName='******'";
DataTable dt = sqlhelper.ExecuteQuery(sql);
if (dt.Rows.Count > 0)
{
flag = true;
}
return(flag);
}
/// <summary>
/// 查询退卡记录
/// </summary>
/// <param name="cancecard">退卡实体</param>
/// <returns></returns>
public DataTable SelectCancelCard(CanceCard cancecard)
{
//定义参数
SqlParameter[] sqlpams = { new SqlParameter("@OperationID", cancecard.OperationID),
new SqlParameter("@State", cancecard.State) };
//定义SQL语句
string sql = @"select * from CancelCard where OperationID=@OperationID and State=@State";
//接受查询的返回值
DataTable SelectCancelCardnTable = sqlHelper.ExecuteQuery(sql, sqlpams, CommandType.Text);
return(SelectCancelCardnTable);
}
/// <summary>
/// 更新公告信息
/// </summary>
/// <param name="n">公告信息实体类</param>
public void Update(notices n)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@content", n.Content),
new SqlParameter("@issuer", n.Issuer),
new SqlParameter("@issueTime", n.IssueTime),
new SqlParameter("@noticeId", n.NoticeId),
new SqlParameter("@noticeName", n.NoticeName),
new SqlParameter("@modifier", n.Modifier),
};
sqlhelper.ExecuteQuery("UPDATE notices SET noticeName=@noticeName, modifier=@modifier,[content] = @content,issuer=@issuer, issueTime=@issueTime ,lastmodify = getdate() where noticeId=@noticeId ", paras, CommandType.Text);
}
/// <summary>
/// 学生更新问题
/// </summary>
/// <param name="n">答疑实体类</param>
public void UpdateByStu(dayi n)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@classId", n.ClassId),
new SqlParameter("@modifier", n.Modifier),
new SqlParameter("@dayiName", n.DayiName),
new SqlParameter("@publishTime", n.PublishTime),
new SqlParameter("@questionContent", n.QuestionContent),
new SqlParameter("@dayiId", n.DayiId),
};
sqlhelper.ExecuteQuery("UPDATE dayi SET modifier=@modifier,dayiName = @dayiName,publishTime=@publishTime, questionContent=@questionContent ,lastmodify = getdate() where dayiId=@dayiId ", paras, CommandType.Text);
}
/// <summary>
/// 取出当前所有新闻分类
/// </summary>
/// <returns></returns>
public static DataTable SelectAll()
{
string sql = "select * from category";
DataTable dt = SQLHelper.ExecuteQuery(sql, CommandType.Text);
return(dt);
}
/// <summary>
/// 更新学生作业
/// </summary>
/// <param name="n">作业列表实体类</param>
public void Update(homeworkList n)
{
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@classId", n.ClassId),
new SqlParameter("@closeTime", n.CloseTime),
new SqlParameter("@content", n.Content),
new SqlParameter("@name", n.Name),
new SqlParameter("@publishTime", n.PublishTime),
//new SqlParameter("@referenceAnswer",n.ReferenceAnswer ),
new SqlParameter("@modifier", n.Modifier),
new SqlParameter("@remarks", n.Remarks),
new SqlParameter("@times", n.Times),
};
sqlhelper.ExecuteQuery("UPDATE homeworkList SET closeTime = @closeTime,[content]=@content,name=@name,publishTime = @publishTime,modifier = @modifier,remarks=@remarks,lastmodify = getdate() where classId=@classId AND times=@times", paras, CommandType.Text);
}
/// <summary>
/// 查询学生数据
/// </summary>
/// <returns>返回datatable</returns>
public DataTable SelectStu()
{
string sql = "select StuNo as 学生学号,StuName as 学生姓名,StuNianji as 学生年级,Academy as 学生学院,Profession as 学生专业,StuClass as 学生班级,Grade as 学生综测,StuPwd as 初始密码 from Student";
DataTable studentTable = SQLHelper.ExecuteQuery(sql);
return(studentTable);
}
public DataTable SelectTea()
{
string sql = "select TeaNo as 教师工号,TeaName as 教师姓名,Academy as 所属学院,GroupNumber as 所带组数,TeaPwd as 初始密码 from Teacher";
DataTable teaTable = SQLHelper.ExecuteQuery(sql);
return(teaTable);
}
public DataTable selectNote()
{
string sql = "select infono as 序号,infotitle as 标题,(select Adminname from Admin b where a.Adminno =b.AdminNo)as 发布人,infotime as 发布时间 from information a";
DataTable notetable = SQLHelper.ExecuteQuery(sql);
return(notetable);
}
public DataTable SelectFromResuit()
{
string sql = @"select distinct(a.groupid) as 学生组号,(select stuname from student d where d.stuno=(select leaderno from grouptable c where c.groupid=a.groupid)) as 组长姓名,b.teaname as 教师姓名,a.topic as 论文课题 from result a,teacher b,student c where a.teacherno=b.teano and a.groupid=c.groupid";
DataTable dataTable = SQLHelper.ExecuteQuery(sql);
return(dataTable);
}
/// <summary>
/// 选择全部新闻
/// </summary>
/// <returns></returns>
public static DataTable SelectAll()
{
DataTable dt = new DataTable();
string sql = "select * from news";
dt = SQLHelper.ExecuteQuery(sql, CommandType.Text);
return(dt);
}
/// <summary>
/// 查询基础数据信息
/// </summary>
/// <returns></returns>
public DataTable SelectBasitInfo()
{
//定义SQL语句
string sql = @"select * from Basis";
//接受查询结果
DataTable SelectBasitTable = sqlHelper.ExecuteQuery(sql, CommandType.Text);
return(SelectBasitTable);
}
/// <summary>
/// 根据类别ID取出该类别下的所有新闻
/// </summary>
/// <param name="caId">类别ID</param>
/// <returns></returns>
public static DataTable SelectByCaId(string caId)
{
//TODO:根据类别ID取出该类别下的所有新闻
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@caId", caId)
};
return(SQLHelper.ExecuteQuery("[procNewsSelectByCaId]", paras, CommandType.StoredProcedure));
}
/// <summary>
/// 根据标题搜索新闻
/// </summary>
/// <param name="title">标题</param>
/// <returns></returns>
public static DataTable SelectByTitle(string title)
{
//TODO:根据标题搜索新闻
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@title", title)
};
return(SQLHelper.ExecuteQuery("[procNewsSelectByTitle]", paras, CommandType.StoredProcedure));
}
public DataTable GetInfoDetail(string infono)
{
string sql = "select InfoTitle,InfoContent,InfoTime from Information where InfoNo=@infono";
SqlParameter[] paras = { new SqlParameter("@infono", infono) };
DataTable infode = SQLHelper.ExecuteQuery(sql, paras);
return(infode);
}
/// <summary>
/// 根据内容搜索新闻
/// </summary>
/// <param name="content">新闻内容</param>
/// <returns></returns>
public static DataTable SelectByContent(string content)
{
//TODO:根根据内容搜索新闻
SqlParameter[] paras = new SqlParameter[]
{
new SqlParameter("@content", content)
};
return(SQLHelper.ExecuteQuery("[procNewsSelectByContent]", paras, CommandType.StoredProcedure));
}
/// <summary>
/// 查询卡号信息
/// </summary>
/// <param name="registarCardno">卡号实体</param>
/// <returns>卡号信息：以DataTable形式返回</returns>
public DataTable SelectRegistartCardnoInfo(RegistrationCardno registarCardno)
{
//定义参数
SqlParameter[] sqlpams = { new SqlParameter("@StudentCardno", registarCardno.StudentCardno) };
//定义SQL语句
string sql = @"select * from RegistrationCardno where StudentCardno=@StudentCardno";
DataTable CardnoInfoTable = sqlHelper.ExecuteQuery(sql, sqlpams, CommandType.Text); //接受查询结果
return(CardnoInfoTable); //返回查询结果
}
/// <summary>
/// 查询所有的部门。
/// </summary>
/// <returns>返回所有部门信息的数据集对象</returns>
public DataSet QueryDepartmentList()
{
DataSet ds = new DataSet();
string sql = "select * from Department";
ds = SQLHelper.ExecuteQuery(sql);
return(ds);
}
/// <summary>
/// 查询正在值班教师
/// </summary>
/// <param name="userOnWork">管理者正在工作实体</param>
/// <returns>正在值班管理信息</returns>
public DataTable SelectOnManWork(UserOnWork userOnWork)
{
//定义参数
SqlParameter[] sqlpams = { new SqlParameter("@ManLevel", userOnWork.Level) };
//定义查询参数
string sql = @"select ManID, ManLevel,OpertionName from OnWorkMan where ManLevel=@ManLevel";
//接受查询返回值
DataTable SelectOnWorkTable = sqlHelper.ExecuteQuery(sql, sqlpams, CommandType.Text);
return(SelectOnWorkTable);
}
/// <summary>
/// 根据新闻ID取出该新闻的所有评论
/// </summary>
/// <param name="newsId">新闻Id</param>
/// <returns></returns>
public DataTable SelectByNewsId(string newsId)
{
DataTable dt = new DataTable();
string sql = "select *from comment where newsId=@newsId order by createTime desc";//按照时间排序
SqlParameter[] paras = new SqlParameter[] {
new SqlParameter("@newsId", newsId)
};
dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
return(dt);
}
/// <summary>
/// 查询余额
/// </summary>
/// <param name="regiCardnoCash">余额</param>
/// <returns>卡号信息</returns>
public DataTable SelectCash(RegistrationCardno regiCardnoCash)
{
//定义参数
SqlParameter[] sqlams = { new SqlParameter("@StudentCardno", regiCardnoCash.StudentCardno) };
//定义SQL语句
string sql = @"select Studentbalance from RegistrationCardno where StudentCardno=@StudentCardno";
//获取查询结果
DataTable SelectCash = sqlHelper.ExecuteQuery(sql, sqlams, CommandType.Text);
return(SelectCash);//返回查询结果
}
/// <summary>
/// 查询管理者信息
/// </summary>
/// <param name="manidCardnoInfo">管理者信息实体</param>
/// <returns>整个管理者信息实体</returns>
public DataTable SelectManIDCardnoInfo(ManIDcardno manidCardnoInfo)
{
//定义查询参数
SqlParameter[] sqlapms = { new SqlParameter("@ManId", manidCardnoInfo.Manid) };
//定义SQL语句
string sql = @"select * from ManIDcardno where ManId=@ManId";
//接受查询结果
DataTable ManIDInfoTable = sqlHelper.ExecuteQuery(sql, sqlapms, CommandType.Text);
return(ManIDInfoTable);//返回查询结果
}
/// <summary>
/// 查询学生上机状态
/// </summary>
/// <param name="StuCardno">正在上机实体</param>
/// <returns></returns>
public DataTable StudnetOnLineState(StudentOnLine StuCardno)
{
//定义参数
SqlParameter[] sqlparms = { new SqlParameter("@StudentCardno", StuCardno.StudentCardno) };
//定义SQL语句
string sql = @"select * from OnLineStudent where StudentCardno=@StudentCardno";
//接受查询结果
DataTable SelectOnleDatable = sqlHelper.ExecuteQuery(sql, sqlparms, CommandType.Text);
return(SelectOnleDatable);
}
/// <summary>
/// 查询卡号是否存在
/// </summary>
/// <param name="regisCardno">卡号实体</param>
/// <returns>返回卡号信息</returns>
public DataTable SelectRegisCardnoInfo(RegistrationCardno regisCardno)
{
//定义参数
SqlParameter[] sqlamps = { new SqlParameter("@StudentCardno", regisCardno.StudentCardno) };
//定义SQL语句
string sql = @"select * from RegistrationCardno where StudentCardno=@StudentCardno";
//接受查询结果
DataTable RegisCardnoInfo = sqlHelper.ExecuteQuery(sql, sqlamps, CommandType.Text);
return(RegisCardnoInfo);
}
/// <summary>
/// 查询学生信息
/// </summary>
/// <param name="studentInfo">学生信息实体</param>
/// <returns></returns>
public DataTable SelectStudentInfo(StudentInfo studentInfo)
{
//定义参数
SqlParameter[] sqlparms = { new SqlParameter("@StudentCardno", studentInfo.StudentCardno) };
//定义SQL语句
string sql = @"select * from StudentInfo where StudentCardno=@StudentCardno";
//接受查询结果
DataTable StudentInfoTable = sqlHelper.ExecuteQuery(sql, sqlparms, CommandType.Text);
return(StudentInfoTable);//返回结果
}
