【小白专用已验证24.6.18】C# SqlSugar操作MySQL数据库实现增删改查

【小白专用24.6.18】C# SqlSugar：连接数据库实现简单的，增、删、改、查-CSDN博客

SqlSugar .Net ORM 5.X 官网、文档、教程 - SqlSugar 5x - .NET果糖网

SqlSugar项目创建

通过NuGet包管理器搜索SqlSugar（MySql还要安装MySql.Data、Newtonsoft.Json）包并安装

1.NuGet安装sqlsugar

新建一个C#的桌面应用程序，起名为SqlSugarDemo，然后鼠标右键选择引用，选择管理NuGet程序包，然后点击浏览后输入sqlsugar查找，找到直接点击安装。

所用.NET框架如果是.NET Framework则选择SqlSugar

所用.NET框架如果是.NET Core则选择SqlSugarCore

安装完后点击已安装，并且点开右边的引用后也可以看到SqlSugar就已经安装好了，如下图

2.实体类用法

在写查询代码之前，还要增加一个类，作为映射，数据库中tp_auth_rule表：

项目右键——添加——类，id为主键并设置为自增

在Id属性上加[SugarColumn(IsPrimaryKey = true, IsIdentity = true)]是因为在数据库中Id是主键并且自增，需要与数据库中一致。

//如果实体类名称和表名不一致可以加上SugarTable特性指定表名

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqlSugarDemo
{[SugarTable("tp_auth_rule")][Serializable]public class tp_auth_rule{/// <summary>/// 编号/// <summary>[SugarColumn(ColumnName = "id", IsPrimaryKey = true, IsIdentity = true)]public int id {get; set; }/// <summary>/// 编码/// <summary>[SugarColumn(ColumnName = "code")]public string code {get; set; }/// <summary>/// 父ID/// <summary>[SugarColumn(ColumnName = "pid")]public int pid {get; set; }/// <summary>/// 控制器/方法/// <summary>[SugarColumn(ColumnName = "name")]public string name {get; set; }/// <summary>/// 权限名称/// <summary>[SugarColumn(ColumnName = "title")]public string title {get; set; }/// <summary>/// type/// <summary>[SugarColumn(ColumnName = "type")]public int type {get; set; }/// <summary>/// 菜单状态/// <summary>[SugarColumn(ColumnName = "status")]public int status {get; set; }/// <summary>/// conditions/// <summary>[SugarColumn(ColumnName = "conditions")]public string conditions {get; set; }/// <summary>/// 排序/// <summary>[SugarColumn(ColumnName = "sort")]public int sort {get; set; }/// <summary>/// 验证权限/// <summary>[SugarColumn(ColumnName = "auth_open")]public int? auth_open {get; set; }/// <summary>/// 图标名称/// <summary>[SugarColumn(ColumnName = "icon")]public string icon {get; set; }/// <summary>/// 创建时间/// <summary>[SugarColumn(ColumnName = "create_time")]public int? create_time {get; set; }/// <summary>/// 更新时间/// <summary>[SugarColumn(ColumnName = "update_time")]public int update_time {get; set; }/// <summary>/// 参数/// <summary>[SugarColumn(ColumnName = "param")]public string param {get; set; }}
}

3.SqlSugar的简单用法

新建一个SqlConnection.cs

在这里，我们来完成SqlSugar连接SQLServer数据库的基本操作。语法上，其实和使用Microsoft.Data.SqlClient很相似，首先创建一个实例，

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;namespace SqlSugarDemo
{/// <summary>/// SqlSugar连接到MySQL数据库/// </summary>public class MySqlConnection{private static string strConnectionString = string.Empty;#region 数据库访问对象/// <summary>/// 数据库访问对象/// </summary>private static SqlSugarClient db = null;/// <summary>/// 初始化SqlSugarClient/// </summary>/// <returns>返回SqlSugarClient对象</returns>public static SqlSugarClient GetInstance(){if (strConnectionString == string.Empty){//strConnectionString = "Data Source=192.168.4.61;Initial Catalog=ReportServer;User ID=sa;Password=16";strConnectionString = "server = localhost; port = 3306; user = root; password = a6; database = worch; Convert Zero Datetime=True; Allow User Variables=True; AllowLoadLocalInfile=true";}db = new SqlSugarClient(new ConnectionConfig(){ConnectionString = strConnectionString,DbType = SqlSugar.DbType.MySql,//DbType.SqlServer,IsAutoCloseConnection = true});return db;}#endregion/// <summary>/// 查询出tp_auth_rule表中的所有数据，映射到StudentInfo，然后作为list返回/// </summary>/// <returns></returns>public static List<tp_auth_rule> Query(){var db = GetInstance();return db.Queryable<tp_auth_rule>().ToList();}public static void Insert(tp_auth_rule student){var db = GetInstance();db.Insertable<tp_auth_rule>(student).ExecuteCommand();}public static bool Delete(tp_auth_rule student){var db = GetInstance();db.Deleteable<tp_auth_rule>(student).ExecuteCommand();return true;}}
}

SqlSugarClient需要传入参数，我们以第一个为例，需要ConnectionConfig类型的参数

ConnectionConfig如下

功能的演示

查询示例

        private void btnQuery_Click(object sender, EventArgs e){var tp_auth_rulelist = MySqlConnection.Query();dataGridView1.DataSource = tp_auth_rulelist;}