using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Students
{
public partial class Frm_EditScore : Form
{
public Frm_Score fs = null;
public Guid sid = Guid.Empty;
public string sname = string.Empty;
string connectionString = "server=localhost;database=Student;uid=sa;pwd=123123";
public Frm_EditScore()
{
InitializeComponent();
}
private void dgv_Students_CellClick(object sender, DataGridViewCellEventArgs e)
{
txt_Sname.Text = dgv_Students.Rows[dgv_Students.CurrentRow.Index].Cells["col_SName"].Value.ToString();
txt_Sid.Text = dgv_Students.Rows[dgv_Students.CurrentRow.Index].Cells["col_Sid"].Value.ToString();
p_dgv.Visible = false;
}
private void Frm_Add_Load(object sender, EventArgs e)
{
ComboxDataBind();
FindScoreBysid();
}
public void ComboxDataBind()
{
string queryName = txt_Sname.Text.Trim();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.Append(" select * from t_course");
SqlCommand command = new SqlCommand(sb.ToString(), conn);
SqlDataAdapter sda = new SqlDataAdapter(command);
DataSet ds = new DataSet();
sda.Fill(ds);
cmb_Course.DataSource = ds.Tables[0];
cmb_Course.ValueMember = "cid";
cmb_Course.DisplayMember = "cname";
}
}
public void FindScoreBysid()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
StringBuilder sb = new StringBuilder();
sb.Append("select * from t_score where sid = @sid");
SqlCommand command = new SqlCommand(sb.ToString(),conn);
command.Parameters.Add("@sid", sid);
SqlDataReader sdr = command.ExecuteReader();
if (sdr.HasRows)
{
if (sdr.Read())
{
txt_Sid.Text = sid.ToString();
txt_Sname.Text = sname;
cmb_Course.SelectedValue = sdr["cid"];
nup_Score.Value = decimal.Parse(sdr["score"].ToString());
txt_Sname.Enabled = false;
cmb_Course.Enabled = false;
btn_Select.Visible = false;
}
}
}
}
private void btn_Select_Click(object sender, EventArgs e)
{
p_dgv.Visible = true;
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
StringBuilder sb = new StringBuilder();
sb.Append("select sname,sage,sid,saddress,ssex from t_Students");
sb.Append(" where 1=1 ");
if (txt_Sname.Text.Trim().Length > 0)
{
sb.Append("and Sname like '%" + txt_Sname.Text.Trim() + "%'");
}
SqlDataAdapter sda = new SqlDataAdapter(sb.ToString(), con);
DataSet ds = new DataSet();
sda.Fill(ds);
DataTable dt = ds.Tables[0];
dgv_Students.DataSource = dt;
}
}
private void btn_Save_Click(object sender, EventArgs e)
{
string sql = string.Empty;
if(sid == Guid.Empty)
{
sql = "insert into t_score values(NEWID(),@cid,@uid,@score)";
}
else
{
sql = "update t_score set score = @score where sid = @sid";
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
if (sid == Guid.Empty)
{
command.Parameters.Add("@cid", cmb_Course.SelectedValue);
command.Parameters.Add("@uid", txt_Sid.Text.Trim());
}
else
{
command.Parameters.Add("@sid",sid);
}
command.Parameters.Add("@score", nup_Score.Value.ToString());
int index = command.ExecuteNonQuery();
if (index > 0)
{
MessageBox.Show("操作成功!!!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (fs != null)
{
fs.DataBind();
}
}
else
{
MessageBox.Show("操作成功!!!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
private void btn_Cancel_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Students
{
public partial class Frm_Score : Form
{
string connectionString = "server=localhost;database=Student;uid=sa;pwd=123123";
public Frm_Score()
{
InitializeComponent();
}
private void Frm_Score_Load(object sender, EventArgs e)
{
DataBind();
tv_Menu.ExpandAll();
}
private void btn_Query_Click(object sender, EventArgs e)
{
DataBind();
}
private void btn_Add_Click(object sender, EventArgs e)
{
Frm_EditScore fas = new Frm_EditScore();
fas.fs = this;
fas.ShowDialog();
}
private void btn_Edit_Click(object sender, EventArgs e)
{
if (dgv_Score.SelectedRows.Count > 0)
{
Frm_EditScore fe = new Frm_EditScore();
fe.Text = "编辑成绩";
fe.fs = this;
fe.sid = Guid.Parse(dgv_Score.Rows[dgv_Score.CurrentRow.Index].Cells["col_sid"].Value.ToString());
fe.sname = dgv_Score.Rows[dgv_Score.CurrentRow.Index].Cells["col_sname"].Value.ToString();
fe.ShowDialog();
}
else
{
MessageBox.Show("请选择需要编辑的数据行!!!", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Remove_Click(object sender, EventArgs e)
{
if (dgv_Score.SelectedRows.Count > 0)
{
DialogResult dr = MessageBox.Show("你确定要删除当前选中行吗?", "操作提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (dr == DialogResult.OK)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand("delete from t_score where sid = @sid",conn);
command.Parameters.Add("@sid", dgv_Score.Rows[dgv_Score.CurrentRow.Index].Cells["col_sid"].Value.ToString());
int index = command.ExecuteNonQuery();
if (index > 0)
{
MessageBox.Show("操作成功", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
DataBind();
}
else
{
MessageBox.Show("操作失败", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
}
}
else
{
MessageBox.Show("你确定要删除当前选中行吗?", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void dgv_Score_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
dgv_Score.ClearSelection();
for (int i = 0; i < dgv_Score.Rows.Count; i++)
{
if (i % 2 == 0)
{
dgv_Score.Rows[i].DefaultCellStyle.BackColor = Color.Pink;
}
}
}
private void tv_Menu_AfterSelect(object sender, TreeViewEventArgs e)
{
if (tv_Menu.SelectedNode.Level == 2)
{
DataView dv = null;
if(dgv_Score.DataSource is DataTable)
{
DataTable dt = dgv_Score.DataSource as DataTable;
dv = new DataView(dt);
}
else
{
dv = dgv_Score.DataSource as DataView;
}
string str = tv_Menu.SelectedNode.Text.Trim();
dv.RowFilter = "ssex='" +str+ "'";
dv.Sort = "sage desc";
dgv_Score.DataSource = dv;
}
}
#region 自定义的方法
public void DataBind()
{
string queryName = txt_QueryName.Text.Trim();
StringBuilder sb = new StringBuilder();
sb.Append(" select course.cname,score.score,score.sid,student.sname,student.sage,student.ssex from t_students student");
sb.Append(" inner join t_score score on student.sid=score.uid");
sb.Append(" inner join t_course course on course.cid = score.cid");
if (queryName.Length > 0)
{
sb.Append(" where student.sname like @sname");
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(sb.ToString(), conn);
if (queryName.Length > 0)
{
command.Parameters.Add("@sname","%" + queryName + "%");
}
SqlDataAdapter sda = new SqlDataAdapter(command);
DataSet ds = new DataSet();
sda.Fill(ds);
dgv_Score.DataSource = ds.Tables[0];
}
}
#endregion
}
}