C#对SQLServer增删改查

1.创建数据库

2.SqlServerHelper

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


namespace WindowsFormsApp1
{

    internal class SqlServerHelper
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        private string strconn = string.Empty;

        public SqlServerHelper(string conn)
        {
            //读取配置文件
            //strconn = ConfigurationManager.AppSettings["Conn"].ToString();
            //strconn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
            strconn = conn;
        }

        /// <summary>
        /// 执行增删改SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();
                return ExecuteNonQuery(conn, cmdText);
            }
        }

        /// <summary>
        /// 执行增删改SQL语句
        /// </summary>
        /// <param name="conn">SqlConnection</param>
        /// <param name="cmdText">SQL语句<</param>
        /// <returns></returns>
        public int ExecuteNonQuery(SqlConnection conn, string cmdText)
        {
            int res;
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.CommandType = CommandType.Text;
                res = cmd.ExecuteNonQuery();
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return res;
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();
                return ExecuteDataTable(conn, cmdText);
            }
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="conn">SqlConnection</param>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        private DataTable ExecuteDataTable(SqlConnection conn, string cmdText)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(dt);
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return dt;
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(strconn))
            {
                conn.Open();
                return ExecuteQuery(conn, cmdText);
            }
        }

        /// <summary>
        /// 执行查询SQL语句
        /// </summary>
        /// <param name="conn">SqlConnection</param>
        /// <param name="cmdText">SQL语句</param>
        /// <returns></returns>
        public DataTable ExecuteQuery(SqlConnection conn, string cmdText)
        {
            DataTable dt = new DataTable();
            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    dt.Load(sdr);
                    sdr.Close();
                    sdr.Dispose();
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return dt;
        }

    }
}

3.WinForm代码

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 WindowsFormsApp1
{
    public partial class Form1: Form
    {

        private SqlServerHelper SqlServerHelpers;

        public Form1()
        {
            InitializeComponent();
            string conn = "Server=LAPTOP-J0SVE7KK;Database=sql;Integrated Security=True;";
            SqlServerHelpers = new SqlServerHelper(conn);

        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            string sql = "SELECT * FROM Student";
            DataTable dataTable = SqlServerHelpers.ExecuteQuery(sql);
            dataGridView1.DataSource = dataTable;
          
        }

       /// <summary>
       /// 修改
       /// </summary>
       /// <param name="sender"></param>
       /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {

                // 正确的UPDATE语句
                string sql = "UPDATE Student SET Name='zhangsan' WHERE Name='张三'";

                // ExecuteNonQuery返回受影响的行数
                int affectedRows = SqlServerHelpers.ExecuteNonQuery(sql);

                // 显示操作结果
                if (affectedRows > 0)
                {
                    MessageBox.Show($"成功更新 {affectedRows} 条数据", "操作成功",
                                   MessageBoxButtons.OK, MessageBoxIcon.Information);

                    // 刷新数据显示
                    button1_Click(sender, e); // 调用查询按钮的点击事件重新加载数据
                }
                else
                {
                    MessageBox.Show("没有数据被更新，请检查条件", "提示",
                                  MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }           
        }


        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {
            // 正确的UPDATE语句
            string sql = "insert into Student values('王五','77','88','99')";

            // ExecuteNonQuery返回受影响的行数
            int affectedRows = SqlServerHelpers.ExecuteNonQuery(sql);

            // 显示操作结果
            if (affectedRows > 0)
            {
                MessageBox.Show($"成功更新 {affectedRows} 条数据", "操作成功",
                               MessageBoxButtons.OK, MessageBoxIcon.Information);

                // 刷新数据显示
                button1_Click(sender, e); // 调用查询按钮的点击事件重新加载数据
            }
            else
            {
                MessageBox.Show("没有数据被更新，请检查条件", "提示",
                              MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        /// <summary>
        ///删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
            // 正确的UPDATE语句
            string sql = "delete from Student where Name='王五'";

            // ExecuteNonQuery返回受影响的行数
            int affectedRows = SqlServerHelpers.ExecuteNonQuery(sql);

            // 显示操作结果
            if (affectedRows > 0)
            {
                MessageBox.Show($"成功更新 {affectedRows} 条数据", "操作成功",
                               MessageBoxButtons.OK, MessageBoxIcon.Information);

                // 刷新数据显示
                button1_Click(sender, e); // 调用查询按钮的点击事件重新加载数据
            }
            else
            {
                MessageBox.Show("没有数据被更新，请检查条件", "提示",
                              MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    }
}

5绑定表格数据源

数据源与数据库列名一致