C# web api 连接数据库 mysql

一、依赖

1、SqlSugarCore

2、MySqlConnector

二、数据

1、表

CREATE TABLE `tb_person` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `age` int DEFAULT NULL,
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `is_delete` tinyint DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb3;

2、基础

using SqlSugar;
using System.Text.Json.Serialization;
namespace WebApi.Entities
{
    public class BaseEntity
    {
        /// <summary>
        /// 主键
        /// </summary>
        [SugarColumn(IsPrimaryKey =true, IsIdentity =true)]
        public int Id { get; set; }

        /// <summary>
        /// 创建数据
        /// </summary>
        [JsonIgnore]
        public DateTime CreateTime { get; set; }

        /// <summary>
        /// 更新时间
        /// </summary>
        [JsonIgnore]
        public DateTime UpdateTime { get; set; }

        /// <summary>
        /// 逻辑删除
        /// </summary>
        [JsonIgnore]
        [SugarColumn(DefaultValue ="0")]
        public bool IsDelete { get; set; }
    }
}

3、对象

using SqlSugar;

namespace WebApi.Entities
{
    [SugarTable("tb_person")]
    public class Person : BaseEntity
    {
        /// <summary>
        /// 姓名
        /// </summary>
        public string? Name { get; set; }
        /// <summary>
        /// 年龄
        /// </summary>
        public int Age { get; set; }
    }
}

三、配置

1、数据库信息放在配置文件 appsettings.json

{
    "ConnectionStrings": {
        "MySqlDb": "server=ip;Database=数据库;Uid=账号;Pwd=密码;"
    },
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*"
}

2、Programes.cs

#region 数据库
builder.Services.AddSingleton<ISqlSugarClient>(s => 
{
    string? connStr = builder.Configuration.GetConnectionString("MySqlDb");
    SqlSugarScope db = new SqlSugarScope(new ConnectionConfig 
    { 
        ConnectionString= connStr,
        DbType = DbType.MySqlConnector,
        IsAutoCloseConnection = true,
        ConfigureExternalServices = new ConfigureExternalServices()
        {
            EntityService = (property, column) =>
            {
                // 属性名转化成下划线
                column.DbColumnName = UtilMethods.ToUnderLine(column.DbColumnName);
            }
        }
    });

    db.QueryFilter.AddTableFilter<BaseEntity>(it => it.IsDelete == false);

    db.Aop.DataExecuting = (oldValue, entityInfo) => 
    {
        if (entityInfo.OperationType == DataFilterType.InsertByObject)
        {
            if (entityInfo.PropertyName == "CreateTime" || entityInfo.PropertyName == "UpdateTime")
            {
                entityInfo.SetValue(DateTime.Now);
            }

            if (entityInfo.PropertyName == "IsDelete")
            {
                entityInfo.SetValue(false);
            }
        }
        if (entityInfo.OperationType == DataFilterType.UpdateByObject) 
        {
            if (entityInfo.PropertyName=="UpdateTime")
            {
                entityInfo.SetValue(DateTime.Now);
            }
        }
    };

    // Sql打印日志
    db.Aop.OnLogExecuting = (sql, pars) =>
    {
        Console.WriteLine($"【SQL】: {sql}");
    };

    return db;
});
#endregion 

四、使用

_db.Insertable(person).ExecuteCommandAsync();