界面外观
先附上数据库中某一张表的数据
开始运行
先根据学生信息,抽象出相应的对象
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Model
{
public class StudentInfo
{
public string Sno { get; set; }
public string Sname { get; set; }
public string Ssex { get; set; }
public int Sage { get; set; }
public string Sdept { get; set; }
}
}
明确功能后,开始写数据访问层
1.先添加一个类,封装好访问数据层的方法
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Student.DAL
{
public static class SqlHelper
{
//从配置文件里读取连接字符串
private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssql"].ConnectionString;
public static int ExecuteNonquery(string sql, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandType = cmdType;
if (pms != null)
{
com.Parameters.AddRange(pms);
}
con.Open();
return com.ExecuteNonQuery();
}
}
}
public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandType = cmdType;
if (pms != null)
{
com.Parameters.AddRange(pms);
}
con.Open();
return com.ExecuteScalar();
}
}
}
public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandType = cmdType;
if (pms != null)
{
com.Parameters.AddRange(pms);
}
try
{
con.Open();
return com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
}
public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
}
return dt;
}
}
}
2.开始写数据访问层的代码
using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Student.DAL
{
public class StudentDAL
{
/// <summary>
/// 获取学生信息
/// </summary>
/// <returns></returns>
public List<StudentInfo> GetAllStudents()
{
string sql = "select * from Student";
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
List<StudentInfo> list = new List<StudentInfo>();
if (dt.Rows.Count>0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToStudentInfo(dr));
}
}
return list;
}
private StudentInfo RowToStudentInfo(DataRow dr)
{
StudentInfo student = new StudentInfo();
student.Sno = dr["Sno"].ToString();
student.Sname = dr["Sname"].ToString();
student.Ssex = dr["Ssex"].ToString();
student.Sage = Convert.ToInt32(dr["Sage"]);
student.Sdept = dr["Sdept"].ToString();
return student;
}
/// <summary>
/// 增加学生
/// </summary>
/// <param name="student">学生对象</param>
/// <returns></returns>
public int AddStudentInfo(StudentInfo student)
{
string sql = "insert into Student values(@Sno,@Sname,@Ssex,@Sage,@Sdept)";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@Sno",student.Sno),
new SqlParameter("@Sname",student.Sname),
new SqlParameter("@Ssex",student.Ssex),
new SqlParameter("@Sage",student.Sage),
new SqlParameter("@Sdept",student.Sdept)
};
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
/// <summary>
/// 更新学生信息
/// </summary>
/// <param name="student">学生对象</param>
/// <returns></returns>
public int UpdateStudentInfo(StudentInfo student)
{
string sql = "update Student set Sname=@Sname,Ssex=@Ssex,Sage=@Sage,Sdept=@Sdept where Sno=@Sno";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@Sname",student.Sname),
new SqlParameter("@Ssex",student.Ssex),
new SqlParameter("@Sage",student.Sage),
new SqlParameter("@Sdept",student.Sdept),
new SqlParameter("@Sno",student.Sno)
};
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
/// <summary>
/// 根据学生编号删除学生的数据
/// </summary>
/// <param name="Sno">学生编号</param>
/// <returns></returns>
public int DeleteStudentInfo(string Sno)
{
string sql = "delete from Student where Sno=" + Sno;
return SqlHelper.ExecuteNonquery(sql, CommandType.Text);
}
}
}
数据访问层写好后,开始写逻辑处理层
这层在这写的很简单。就先这么简单点吧
using Model;
using Student.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Student.BLL
{
public class StudentBLL
{
StudentDAL dal = new StudentDAL();
/// <summary>
/// 返回全部学生的数据
/// </summary>
/// <returns></returns>
public List<StudentInfo> GetAllStudents()
{
return dal.GetAllStudents();
}
/// <summary>
/// 增加学生信息
/// </summary>
/// <param name="student">学生对象</param>
/// <returns>成功与否</returns>
public bool AddStudentInfo(StudentInfo student)
{
return dal.AddStudentInfo(student) > 0;
}
/// <summary>
/// 更新学生信息
/// </summary>
/// <param name="student">学生对象</param>
/// <returns>成功与否</returns>
public bool UpdateStudentInfo(StudentInfo student)
{
return dal.UpdateStudentInfo(student)>0;
}
/// <summary>
/// 删除学生信息
/// </summary>
/// <param name="Sno">学号</param>
/// <returns>成功与否</returns>
public bool DeleteStudentInfo(string Sno)
{
return dal.DeleteStudentInfo(Sno)>0;
}
}
}
在写UI层的代码
应该是先设计界面布局,界面布局在一开始展示了。可回到顶部查看。
接下来添加一些事件
using HZH_Controls.Controls;
using Model;
using Student.BLL;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace 数据库作业十八
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
LoadStudentInfo();
}
StudentBLL bll = new StudentBLL();
/// <summary>
///加载学生信息
/// </summary>
private void LoadStudentInfo()
{
dataGridView1.DataSource = bll.GetAllStudents();
if (dataGridView1.Rows.Count>0)
{
dataGridView1.Rows[0].Selected = false;
}
dataGridView1.Font = new Font("微软雅黑", 10);
}
private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
{
DataGridViewRow currentRow = this.dataGridView1.Rows[e.RowIndex];
StudentInfo student = currentRow.DataBoundItem as StudentInfo;
if (student!=null)
{
txtSno.Text = student.Sno;
txtSname.Text = student.Sname;
txtSex.Text = student.Ssex;
txtSage.Text = student.Sage.ToString();
txtSdept.Text = student.Sdept;
}
}
//关闭窗体
private void ucBtnExt4_BtnClick(object sender, EventArgs e)
{
this.Close();
}
//增加学生信息
private void ucBtnExt1_BtnClick(object sender, EventArgs e)
{
if (Check())
{
StudentInfo student = new StudentInfo();
student.Sno = txtSno.Text.Trim();
student.Sname = txtSname.Text.Trim();
student.Sage = Convert.ToInt32(txtSage.Text.Trim());
student.Ssex = txtSex.Text.Trim();
student.Sdept = txtSdept.Text.Trim();
string msg = bll.AddStudentInfo(student) ? "操作成功" : "操作失败";
MessageBox.Show(msg);
LoadStudentInfo();
}
else
{
MessageBox.Show("请将学生的信息全部输入");
}
}
private bool Check()
{
if (string.IsNullOrEmpty(txtSno.Text.Trim()))
{
return false;
}
if (string.IsNullOrEmpty(txtSname.Text.Trim()))
{
return false;
}
if (string.IsNullOrEmpty(txtSex.Text.Trim()))
{
return false;
}
if (string.IsNullOrEmpty(txtSdept.Text.Trim()))
{
return false;
}
if (string.IsNullOrEmpty(txtSage.Text.Trim()))
{
return false;
}
return true;
}
//修改学生信息
private void ucBtnExt2_BtnClick(object sender, EventArgs e)
{
if (Check())
{
StudentInfo student = new StudentInfo();
student.Sno = txtSno.Text.Trim();
student.Sname = txtSname.Text.Trim();
student.Sage = Convert.ToInt32(txtSage.Text.Trim());
student.Ssex = txtSex.Text.Trim();
student.Sdept = txtSdept.Text.Trim();
string msg = bll.UpdateStudentInfo(student) ? "操作成功" : "操作失败";
MessageBox.Show(msg);
LoadStudentInfo();
}
else
{
MessageBox.Show("请将学生的信息全部输入");
}
}
//删除学生信息
private void ucBtnExt3_BtnClick(object sender, EventArgs e)
{
if (Check())
{
string msg = bll.DeleteStudentInfo(txtSno.Text.Trim()) ? "操作成功" : "操作失败";
MessageBox.Show(msg);
LoadStudentInfo();
}
else
{
MessageBox.Show("请选中要删除的行");
}
}
}
}
在网上看到了一个开源控件库HZH_Controls(请点开链接,给作者点个☆),界面外观确实比自带的控件美观。该图是作者文档里展示的,虽然是再进一步扩展后得到的,但是界面已经做的挺好了。
于是下载下来后,发现不怎么会用。加入作者建立的交流群里,询问了一下,才做到了展示数据这一步。然后发现,该控件库外观确实比自带的控件美观,但是,相应的属性和事件比较少,需要自己写。由于技术比较菜,折腾了一下午,也没做成想要的事件,于是又使用自带的DataGridView展示数据。(不过该控件库也没完全没发挥作用,下面几个按钮就是该控件库里的)。初步简单使用了一下,没发现什么大问题。一些功能还需要完善,因时间问题,就先写到这,毕竟这几天,该做什么,已经基本安排好了,时间比较紧,就先到这,等五一假期有空了,再完善。