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

C# SqlSugar操作MySQL数据库实现增删改查

zgscwxd

996人浏览 · 2024-06-18 11:53:57

zgscwxd · 2024-06-18 11:53:57 发布

【小白专用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;
        }