SQL Server 自学之路8 winform与数据库的增删改查

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQL_Server
{
    public class sqlTable
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Class { get; set; }
    }
    class SQL
    {
        public SqlConnection sqlConnection;
        public DataSet dataSet;
        public SqlCommand sqlCommand;
        /// <summary>
        /// 打开数据库
        /// </summary>
        public void sqlOpen()
        {
            string str = "Server = DESKTOP-OJBF5S6\\SQLEXPRESS ; Database = 脚本操作数据库1 ; Trusted_Connection = SSPI ; ";
            sqlConnection = new SqlConnection(str);
            sqlConnection.Open();
        }
        /// <summary>
        /// 查询数据库数据
        /// </summary>
        public void sqlInquire()
        {
            sqlOpen();
            if (sqlConnection.State == ConnectionState.Open)
            {
                sqlCommand = new SqlCommand("select * from 脚本数据表学生成绩", sqlConnection);
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
                dataSet = new DataSet();
                sqlDataAdapter.Fill(dataSet);
            }
        }
        /// <summary>
        /// 关闭数据库
        /// </summary>
        public void sqlClose()
        {
            if (sqlConnection.State == ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }
        /// <summary>
        /// 增加一行数据
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Name"></param>
        /// <param name="Age"></param>
        /// <param name="Class"></param>
        /// <returns></returns>
        public string sqlAdd(int ID, string Name, int Age, string Class)
        {
            sqlOpen();
            string sql = string.Format("insert into 脚本数据表学生成绩 (ID,Name,Age,Class) values ( {0}, '{1}',  {2} , '{3}')", ID, Name, Age, Class);
            sqlCommand = new SqlCommand(sql, sqlConnection);
            int num = sqlCommand.ExecuteNonQuery();
            if (num > 0)
            {
                return "添加成功";
            }
            else
            {
                return "添加失败";
            }
        }
        /// <summary>
        /// 删除一行数据
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public string sqlDelete(int ID)
        {
            sqlOpen();
            string sql = string.Format("delete from 脚本数据表学生成绩 where ID={0}", ID);
            sqlCommand = new SqlCommand(sql, sqlConnection);
            int num = sqlCommand.ExecuteNonQuery();
            if (num > 0)
            {
                return "删除成功";
            }
            else
            {
                return "删除失败";
            }
        }
        /// <summary>
        /// 修改原有的数据
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Name"></param>
        /// <param name="Age"></param>
        /// <param name="Class"></param>
        /// <returns></returns>
        public string sqlModify(int ID, string Name, int Age, string Class)
        {
            sqlOpen();
            string sql = string.Format("update 脚本数据表学生成绩 set Name = '{1}',Age = {2},Class = '{3}'where ID = {0} ", ID, Name, Age, Class);
            sqlCommand = new SqlCommand(sql, sqlConnection);
            int num = sqlCommand.ExecuteNonQuery();
            if (num > 0)
            {
                return "更改成功";
            }
            else
            {
                return "更改失败";
            }
        }
    }
}

数据库数据显示在表格控件中…

private void SQL_Form_Load(object sender, EventArgs e)
        {
            List<sqlTable> list = new List<sqlTable>();
            cmbID.Items.Clear();
            sql.sqlInquire();
            SqlDataReader sqlDataReader = sql.sqlCommand.ExecuteReader();
            if (sqlDataReader.HasRows)
            {
                while (sqlDataReader.Read())
                {
                    sqlTable table = new sqlTable();
                    table.ID = sqlDataReader.GetInt32(0);
                    table.Name = sqlDataReader.GetString(1);
                    table.Age = sqlDataReader.GetInt32(2);
                    table.Class = sqlDataReader.GetString(3);
                    list.Add(table);
                    cmbID.Items.Add(table.ID);
                }
                sqlDataReader.Close();
                cmbID.SelectedIndex = 0;
            }
            this.dgv_SQL.DataSource = list;
        }