当前位置：首页 > web >正文

C#对SQLServer增删改查

web 2025/5/9 18:13:55

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);}}}
}