private void btnDel_Click(object sender, EventArgs e)
{
DialogResult dr = MessageBox.Show("是否确认删除", "删除提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2);
if (dr == System.Windows.Forms.DialogResult.Yes)
{
StuScore s = new StuScore();
if (true)
{
s.SNO = dgvScoreManage.SelectedRows[0].Cells["SNO"].Value.ToString();
}
bool result = bll.ScEdit(s, 0, out msg);
if (msg != "")
{
MessageBox.Show(msg);
}
if (result)
{
MessageBox.Show("删除成功");
BindData();
}
MessageBox.Show("删除失败");
}
}
/// <summary>
/// 提交执行增删改的数据
/// </summary>
/// <param name="sc"></param>
/// <param name="op">0-删除，1-添加，2-修改</param>
/// <param name="msg"></param>
/// <returns></returns>
public int ScEdit(StuScore sc, int op, out string msg)
{
StringBuilder sb = new StringBuilder();
switch (op)
{
case 0:
sb.AppendFormat("delete from Score where sno={0} and subjectid = {1}", sc.SNO, sc.SubjectID);
break;
case 1:
sb.AppendFormat("insert into Score(sno,score,score,subjectID) values('{0}','{1}','{2}','{3}') ", sc.SNO, sc.Score, sc.Score, sc.SubjectID);
sb.AppendFormat("insert into Stuinfo(sno,stuname) values('{0}','{1}')", sc.SNO, sc.StuName);
break;
case 2:
sb.AppendFormat("update Score set Score ='{0}' where subjectid={1} and sno = {2}", sc.Score, sc.SubjectID, sc.SNO);
break;
default:
msg = "输入的操作无法识别";
return(0);
}
return(sh.ExecuteNonquery(sb.ToString(), out msg));
}
/// <summary>
/// 执行变更
/// </summary>
/// <param name="sc"></param>
/// <param name="op">0-删除，1-添加，2-修改</param>
/// <param name="msg"></param>
/// <returns></returns>
public bool ScEdit(StuScore sc, int op, out string msg)
{
int result = dal.ScEdit(sc, op, out msg);
if (result > 0)
{
return(true);
}
return(false);
}
/// 获取成绩表
/// </summary>
/// <param name="sc"></param>
/// <param name="IsLike">判断模糊查询</param>
/// <param name="msg"></param>
/// <returns></returns>
public StuScore[] GetScore(StuScore sc, bool IsLike, out string msg)
{
StringBuilder sb = new StringBuilder();
sb.Append("select * from V_Scores where 1=1 ");
if (IsLike)
{
if (!string.IsNullOrEmpty(sc.SNO))
{
sb.AppendFormat(" and sno like '%{0}%' ", sc.SNO);
}
if (!string.IsNullOrEmpty(sc.SubjectName))
{
sb.AppendFormat(" and SubjectName like '%{0}%'", sc.SubjectName);
}
}
else
if (!string.IsNullOrEmpty(sc.SNO))
{
sb.AppendFormat(" and sno = '{0}'", sc.SNO);
}
if (!string.IsNullOrEmpty(sc.SubjectName))
{
sb.AppendFormat(" and SubjectName = '{0}'", sc.SubjectName);
}
DataTable dt = sh.ExecuteDataAdapter(sb.ToString(), out msg);
if (dt != null && dt.Rows.Count > 0)
{
StuScore[] list = new StuScore[dt.Rows.Count + 1];
for (int i = 0; i < dt.Rows.Count; i++)
{
StuScore s = new StuScore();
s.ID = Convert.ToInt32(dt.Rows[i]["ID"]);
s.SubjectID = Convert.ToInt32(dt.Rows[i]["SubjectID"]);
s.SNO = dt.Rows[i]["SNO"].ToString();
s.StuName = dt.Rows[i]["StuName"].ToString();
s.SubjectName = dt.Rows[i]["SubjectName"].ToString();
s.Score = dt.Rows[i]["Score"] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i]["Score"]);
list[i] = s;
}
StuScore ss = new StuScore();
ss.ID = 0;
list[list.Length - 1] = ss;
return(list);
}
return(null);
}
public void BindData()
{
StuScore sc = new StuScore();
sc.SNO = txtSNO.Text;
sc.SubjectName = txtSubjectName.Text;
StuScore[] list = bll.GetScore(sc, out msg);
if (msg != "")
{
MessageBox.Show(msg);
}
dgvScoreManage.DataSource = list;
}
protected void Button1_Click(object sender, EventArgs e)
{
string constr = "Data Source=154.8.141.37;Initial Catalog=Judgement;User ID=admin;Password=123456";
//string constr = "Data Source=.; Persist Security Info=False;Initial Catalog=Judgement;Integrated Security=SSPI";
try
{
string sqlstr = "Select StuID,StuName,PresentScore,ProgramScore,ReportScore,SummaryScore,StuAve,TeaAve,FinalScore From StuFinalScore";
using (SqlConnection con = new SqlConnection(constr))
{
DataSet ds = new DataSet();
con.Open();
SqlDataAdapter ada = new SqlDataAdapter(sqlstr, con);
ada.Fill(ds);
StuScore.DataSource = ds;
StuScore.DataBind();
con.Close();
}
}
catch (Exception)
{
Response.Write("<script>alert('加载失败')</script>");
}
}
/// <summary>
/// 获取成绩数组数据
/// </summary>
/// <param name="sc"></param>
/// <param name="msg"></param>
/// <returns></returns>
public StuScore[] GetScore(StuScore sc, out string msg)
{
return(dal.GetScore(sc, true, out msg));
}
private void dgvScoreManage_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
bool isUpdateOP = true;//默认是【修改】操作
int id = Convert.ToInt32(dgvScoreManage.Rows[e.RowIndex].Cells["ID"].Value);
string opStr = "修改";
if (id == 0)
{
opStr = "添加";
isUpdateOP = false;
dgvScoreManage.Rows[e.RowIndex].Cells["SNO"].Value = Convert.ToString((Convert.ToInt32(dgvScoreManage.Rows[e.RowIndex - 1].Cells["SNO"].Value) + 1));
}
//自增学号
#region 能修改已存在的列
string sno = Convert.ToString(dgvScoreManage.Rows[e.RowIndex].Cells["SNO"].Value);
string newStuName = Convert.ToString(dgvScoreManage.Rows[e.RowIndex].Cells["StuName"].Value);
string NewSubjectName = Convert.ToString(dgvScoreManage.Rows[e.RowIndex].Cells["SubjectName"].Value);
//学号不能修改
if (NonEdit(isUpdateOP, sno, OldSNO, "学号", "SNO", e))
{
return;
}
//姓名不能修改
if (NonEdit(isUpdateOP, newStuName, OldStuName, "姓名", "StuName", e))
{
return;
}
// 学科名
if (NonEdit(isUpdateOP, NewSubjectName, OldSubjectName, "学科名", "SubjectName", e))
{
return;
}
#endregion
int newScore = Convert.ToInt32(dgvScoreManage.Rows[e.RowIndex].Cells["Score"].Value);
if (newScore != OldScore)
{
if (newScore > 100 || newScore < 0)
{
MessageBox.Show("你输入的成绩有误，请重新输入");
dgvScoreManage.Rows[e.RowIndex].Cells["Score"].Value = OldScore;
return;
}
if (isUpdateOP)
{
if (MessageBox.Show("请确认是否要修改该成绩", "修改提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.No)
{
dgvScoreManage.Rows[e.RowIndex].Cells["Score"].Value = OldScore;
return;
}
}
}
StuScore ss = new StuScore();
ss.SubjectName = NewSubjectName;
ss.Score = newScore;
ss.StuName = newStuName;
ss.SNO = sno;
ss.SubjectID = Convert.ToInt32(dgvScoreManage.Rows[e.RowIndex].Cells["SubjectID"].Value);
ss.ID = Convert.ToInt32(dgvScoreManage.Rows[e.RowIndex].Cells["ID"].Value);
bool isOK;
if (!isUpdateOP)
{
//新增
isOK = bll.ScEdit(ss, 1, out msg);
}
else
{
//修改
isOK = bll.ScEdit(ss, 2, out msg);
}
if (msg != "")
{
MessageBox.Show(msg);
}
if (isOK)
{
MessageBox.Show(opStr + "成功");
t_Fresh.Enabled = true;
}
else
{
//将原来的值还原，并提示错误
dgvScoreManage.Rows[e.RowIndex].Cells["SubjectName"].Value = OldSubjectName;
dgvScoreManage.Rows[e.RowIndex].Cells["Score"].Value = OldScore;
dgvScoreManage.Rows[e.RowIndex].Cells["StuName"].Value = OldStuName;
dgvScoreManage.Rows[e.RowIndex].Cells["SNO"].Value = OldSNO;
MessageBox.Show("输入的数据有误，请重新确认");
}
}
