数据库作业十八-使用visual studio连接sql server实现对一张表的增删改查

界面外观

先附上数据库中某一张表的数据

开始运行

先根据学生信息，抽象出相应的对象

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展示数据。(不过该控件库也没完全没发挥作用，下面几个按钮就是该控件库里的)。初步简单使用了一下，没发现什么大问题。一些功能还需要完善，因时间问题，就先写到这，毕竟这几天，该做什么，已经基本安排好了，时间比较紧，就先到这，等五一假期有空了，再完善。