Sqlsugar入门使用

1.导入依赖

<PackageReference Include="SqlSugarCore" Version="5.1.4.199" />

2.配置SqlSugar启动服务

/// <summary>
/// SqlSugar 启动服务
/// </summary>
public static class SqlsugarSetup
{
    public static void AddSqlsugarSetup(this IServiceCollection services)
    {
        if (services == null) throw new ArgumentNullException(nameof(services));

        // 默认添加主数据库连接
        if (!string.IsNullOrEmpty(AppSettings.app("MainDB")))
        {
            MainDb.CurrentDbConnId = AppSettings.app("MainDB");
        }

        BaseDBConfig.MutiConnectionString.allDbs.ForEach(m =>
        {
            var config = new ConnectionConfig()
            {
                ConfigId = m.ConnId.ObjToString().ToLower(),
                ConnectionString = m.Connection,
                DbType = (DbType)m.DbType,
                IsAutoCloseConnection = true,
                MoreSettings = new ConnMoreSettings()
                {
                    IsAutoRemoveDataCache = true,
                    SqlServerCodeFirstNvarchar = true,
                },
                InitKeyType = InitKeyType.Attribute
            };
            if (SqlSugarConst.LogConfigId.ToLower().Equals(m.ConnId.ToLower()))
            {
                BaseDBConfig.LogConfig = config;
            }
            else
            {
                BaseDBConfig.ValidConfig.Add(config);
            }

            BaseDBConfig.AllConfigs.Add(config);
        });

        if (BaseDBConfig.LogConfig is null)
        {
            throw new ApplicationException("未配置Log库连接");
        }

        // SqlSugarScope是线程安全，可使用单例注入
        // 参考：https://www.donet5.com/Home/Doc?typeId=1181
        services.AddSingleton<ISqlSugarClient>(o =>
        {
            return new SqlSugarScope(BaseDBConfig.AllConfigs);
        });
    }
}

3.获取当前应用的连接数据对象(数据库实例)

public static class MainDb
{
    public static string CurrentDbConnId = "Main";
}

4.库配置

public class BaseDBConfig
{
    /// <summary>
    /// 所有库配置
    /// </summary>
    public static readonly List<ConnectionConfig> AllConfigs = new();

    /// <summary>
    /// 有效的库连接(除去Log库)
    /// </summary>
    public static List<ConnectionConfig> ValidConfig = new();

    public static ConnectionConfig MainConfig;
    public static ConnectionConfig LogConfig; //日志库

    public static bool IsMulti => ValidConfig.Count > 1;

    /* 之前的单库操作已经删除，如果想要之前的代码，可以查看我的GitHub的历史记录
     * 目前是多库操作，默认加载的是appsettings.json设置为true的第一个db连接。
     *
     * 优化配置连接
     * 老的配置方式,再多库和从库中有些冲突
     * 直接在单个配置中可以配置从库
     *
     * 新增故障转移方案
     * 增加主库备用连接,配置方式为ConfigId为主库的ConfigId+随便数字 只要不重复就好
     *
     * 主库在无法连接后会自动切换到备用链接
     */
    public static (List<MutiDBOperate> allDbs, List<MutiDBOperate> slaveDbs) MutiConnectionString => MutiInitConn();


    public static (List<MutiDBOperate>, List<MutiDBOperate>) MutiInitConn()
    {
        List<MutiDBOperate> listdatabase = AppSettings.app<MutiDBOperate>("DBS")
            .Where(i => i.Enabled).ToList();
        var mainDbId = AppSettings.app(new string[] { "MainDB" }).ToString();
        var mainDbModel = listdatabase.Single(d => d.ConnId == mainDbId);
        listdatabase.Remove(mainDbModel);
        listdatabase.Insert(0, mainDbModel);

        foreach (var i in listdatabase) SpecialDbString(i);
        return (listdatabase, mainDbModel.Slaves);
    }

    private static string DifDBConnOfSecurity(params string[] conn)
    {
        foreach (var item in conn)
        {
            try
            {
                if (File.Exists(item))
                {
                    return File.ReadAllText(item).Trim();
                }
            }
            catch (Exception)
            {
            }
        }

        return conn[conn.Length - 1];
    }

    /// <summary>
    /// 定制Db字符串
    /// 目的是保证安全：优先从本地txt文件获取，若没有文件则从appsettings.json中获取
    /// </summary>
    /// <param name="mutiDBOperate"></param>
    /// <returns></returns>
    private static MutiDBOperate SpecialDbString(MutiDBOperate mutiDBOperate)
    {
        if (mutiDBOperate.DbType == DataBaseType.Sqlite)
        {
            mutiDBOperate.Connection =
                $"DataSource=" + Path.Combine(Environment.CurrentDirectory, mutiDBOperate.Connection);
        }
        else if (mutiDBOperate.DbType == DataBaseType.SqlServer)
        {
            mutiDBOperate.Connection = DifDBConnOfSecurity(@"D:\my-file\dbCountPsw1_SqlserverConn.txt",
                mutiDBOperate.Connection);
        }
        else if (mutiDBOperate.DbType == DataBaseType.MySql)
        {
            mutiDBOperate.Connection =
                DifDBConnOfSecurity(@"D:\my-file\dbCountPsw1_MySqlConn.txt", mutiDBOperate.Connection);
        }
        else if (mutiDBOperate.DbType == DataBaseType.Oracle)
        {
            mutiDBOperate.Connection =
                DifDBConnOfSecurity(@"D:\my-file\dbCountPsw1_OracleConn.txt", mutiDBOperate.Connection);
        }

        return mutiDBOperate;
    }
}


public enum DataBaseType
{
    MySql = 0,
    SqlServer = 1,
    Sqlite = 2,
    Oracle = 3,
    PostgreSQL = 4,
    Dm = 5,
    Kdbndp = 6,
}

public class MutiDBOperate
{
    /// <summary>
    /// 连接启用开关
    /// </summary>
    public bool Enabled { get; set; }

    /// <summary>
    /// 连接ID
    /// </summary>
    public string ConnId { get; set; }

    /// <summary>
    /// 从库执行级别，越大越先执行
    /// </summary>
    public int HitRate { get; set; }

    /// <summary>
    /// 连接字符串
    /// </summary>
    public string Connection { get; set; }

    /// <summary>
    /// 数据库类型
    /// </summary>
    public DataBaseType DbType { get; set; }

    /// <summary>
    /// 从库
    /// </summary>
    public List<MutiDBOperate> Slaves { get; set; }
}

5.默认Log数据库标识

public class SqlSugarConst
{
    /// <summary>
    /// 默认Log数据库标识
    /// </summary>
    public const string LogConfigId = "Log";
}

6.数据转换器

/// <summary>
/// 数据转换器
/// </summary>
public static class UtilConvert
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static int ObjToInt(this object thisValue)
    {
        int reval = 0;
        if (thisValue == null) return 0;
        if (thisValue != DBNull.Value && int.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return reval;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <param name="errorValue"></param>
    /// <returns></returns>
    public static int ObjToInt(this object thisValue, int errorValue)
    {
        int reval = 0;
        if (thisValue != null && thisValue != DBNull.Value && int.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return errorValue;
    }

    public static long ObjToLong(this object thisValue)
    {
        long reval = 0;
        if (thisValue == null) return 0;
        if (thisValue != DBNull.Value && long.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return reval;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static double ObjToMoney(this object thisValue)
    {
        double reval = 0;
        if (thisValue != null && thisValue != DBNull.Value && double.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return 0;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <param name="errorValue"></param>
    /// <returns></returns>
    public static double ObjToMoney(this object thisValue, double errorValue)
    {
        double reval = 0;
        if (thisValue != null && thisValue != DBNull.Value && double.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return errorValue;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static string ObjToString(this object thisValue)
    {
        if (thisValue != null) return thisValue.ToString().Trim();
        return "";
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static bool IsNotEmptyOrNull(this object thisValue)
    {
        return thisValue.ObjToString() != "" && thisValue.ObjToString() != "undefined" &&
               thisValue.ObjToString() != "null";
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <param name="errorValue"></param>
    /// <returns></returns>
    public static string ObjToString(this object thisValue, string errorValue)
    {
        if (thisValue != null) return thisValue.ToString().Trim();
        return errorValue;
    }

    public static bool IsNullOrEmpty(this object thisValue) => thisValue == null || thisValue == DBNull.Value ||
                                                               string.IsNullOrWhiteSpace(thisValue.ToString());

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static decimal ObjToDecimal(this object thisValue)
    {
        decimal reval = 0;
        if (thisValue != null && thisValue != DBNull.Value && decimal.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return 0;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <param name="errorValue"></param>
    /// <returns></returns>
    public static decimal ObjToDecimal(this object thisValue, decimal errorValue)
    {
        decimal reval = 0;
        if (thisValue != null && thisValue != DBNull.Value && decimal.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return errorValue;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static DateTime ObjToDate(this object thisValue)
    {
        DateTime reval = DateTime.MinValue;
        if (thisValue != null && thisValue != DBNull.Value && DateTime.TryParse(thisValue.ToString(), out reval))
        {
            reval = Convert.ToDateTime(thisValue);
        }
        else
        {
            //时间戳转为时间
            var seconds = thisValue.ObjToLong();
            if (seconds > 0)
            {
                var startTime = TimeZoneInfo.ConvertTime(new DateTime(1970, 1, 1), TimeZoneInfo.Local);
                reval = startTime.AddSeconds(Convert.ToDouble(thisValue));
            }
        }

        return reval;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <param name="errorValue"></param>
    /// <returns></returns>
    public static DateTime ObjToDate(this object thisValue, DateTime errorValue)
    {
        DateTime reval = DateTime.MinValue;
        if (thisValue != null && thisValue != DBNull.Value && DateTime.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return errorValue;
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static bool ObjToBool(this object thisValue)
    {
        bool reval = false;
        if (thisValue != null && thisValue != DBNull.Value && bool.TryParse(thisValue.ToString(), out reval))
        {
            return reval;
        }

        return reval;
    }


    /// <summary>
    /// 获取当前时间的时间戳
    /// </summary>
    /// <param name="thisValue"></param>
    /// <returns></returns>
    public static string DateToTimeStamp(this DateTime thisValue)
    {
        TimeSpan ts = thisValue - new DateTime(1970, 1, 1, 0, 0, 0, 0);
        return Convert.ToInt64(ts.TotalSeconds).ToString();
    }

    public static object ChangeType(this object value, Type type)
    {
        if (value == null && type.IsGenericType) return Activator.CreateInstance(type);
        if (value == null) return null;
        if (type == value.GetType()) return value;
        if (type.IsEnum)
        {
            if (value is string)
                return Enum.Parse(type, value as string);
            else
                return Enum.ToObject(type, value);
        }

        if (!type.IsInterface && type.IsGenericType)
        {
            Type innerType = type.GetGenericArguments()[0];
            object innerValue = value.ChangeType(innerType);
            return Activator.CreateInstance(type, new object[] { innerValue });
        }

        if (value is string && type == typeof(Guid)) return new Guid(value as string);
        if (value is string && type == typeof(Version)) return new Version(value as string);
        if (!(value is IConvertible)) return value;
        return Convert.ChangeType(value, type);
    }

    public static object ChangeTypeList(this object value, Type type)
    {
        if (value == null) return default;

        var gt = typeof(List<>).MakeGenericType(type);
        dynamic lis = Activator.CreateInstance(gt);

        var addMethod = gt.GetMethod("Add");
        string values = value.ToString();
        if (values != null && values.StartsWith("(") && values.EndsWith(")"))
        {
            string[] splits;
            if (values.Contains("\",\""))
            {
                splits = values.Remove(values.Length - 2, 2)
                    .Remove(0, 2)
                    .Split("\",\"");
            }
            else
            {
                splits = values.Remove(0, 1)
                    .Remove(values.Length - 2, 1)
                    .Split(",");
            }

            foreach (var split in splits)
            {
                var str = split;
                if (split.StartsWith("\"") && split.EndsWith("\""))
                {
                    str = split.Remove(0, 1)
                        .Remove(split.Length - 2, 1);
                }

                addMethod.Invoke(lis, new object[] { str.ChangeType(type) });
            }
        }

        return lis;
    }

    public static string ToJson(this object value)
    {
        return JsonConvert.SerializeObject(value);
    }

    public static bool AnyNoException<T>(this ICollection<T> source)
    {
        if (source == null) return false;

        return source.Any() && source.All(s => s != null);
    }
}

7.在Model层配置RootEntityTkey类

public class RootEntityTkey<Tkey> where Tkey : IEquatable<Tkey>
{
    /// <summary>
    /// ID
    /// 泛型主键Tkey
    /// </summary>
    [SugarColumn(IsNullable = false, IsPrimaryKey = true)]
    public Tkey Id { get; set; }
}

8.在Model层配置Role类

 /// <summary>
 /// 角色表
 /// </summary>
 public class Role : RootEntityTkey<long>
 {

     /// <summary>
     ///获取或设置是否禁用，逻辑上的删除，非物理删除
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public bool? IsDeleted { get; set; }
     /// <summary>
     /// 角色名
     /// </summary>
     [SugarColumn(Length = 50, IsNullable = true)]
     public string Name { get; set; }
     /// <summary>
     ///描述
     /// </summary>
     [SugarColumn(Length = 100, IsNullable = true)]
     public string Description { get; set; }
     /// <summary>
     ///排序
     /// </summary>
     public int OrderSort { get; set; }
     /// <summary>
     /// 自定义权限的部门ids
     /// </summary>
     [SugarColumn(Length = 500, IsNullable = true)]
     public string Dids { get; set; }
     /// <summary>
     /// 权限范围
     /// -1 无任何权限；1 自定义权限；2 本部门；3 本部门及以下；4 仅自己；9 全部；
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public int AuthorityScope { get; set; } = -1;
     /// <summary>
     /// 是否激活
     /// </summary>
     public bool Enabled { get; set; }
     /// <summary>
     /// 创建ID
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public long? CreateId { get; set; }
     /// <summary>
     /// 创建者
     /// </summary>
     [SugarColumn(Length = 50, IsNullable = true)]
     public string CreateBy { get; set; }
     /// <summary>
     /// 创建时间
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public DateTime? CreateTime { get; set; } = DateTime.Now;
     /// <summary>
     /// 修改ID
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public long? ModifyId { get; set; }
     /// <summary>
     /// 修改者
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public string ModifyBy { get; set; }
     /// <summary>
     /// 修改时间
     /// </summary>
     [SugarColumn(IsNullable = true)]
     public DateTime? ModifyTime { get; set; } = DateTime.Now;

 }

9.在program.cs文件中注册sqlsugar启动服务

// 添加sqlsugar
builder.Services.AddSqlsugarSetup();

10.BaseRepository类中通过sqlsugar对sqlite数据库进行操作（前提需要安装sqlite数据库）

public class BaseRepository<TEntity> : IBaseRepository<TEntity> where TEntity : class, new()
{
    private readonly ISqlSugarClient _dbBaset;

    public BaseRepository(ISqlSugarClient sqlSugarClient)
    {
        _dbBaset = sqlSugarClient;
    }

    public ISqlSugarClient Db => _dbBaset;

    public async Task<List<TEntity>> Query()
    {
        await Console.Out.WriteLineAsync(Db.GetHashCode().ToString());

        return await _dbBaset.Queryable<TEntity>().ToListAsync();
    }
}

11.在control层中使用

public IBaseService<Role, RoleVo> _baseService { get; set; }

public WeatherForecastController(ILogger<WeatherForecastController> logger,
    IMapper mapper,
    IBaseService<Role, RoleVo> baseService,
    IOptions<RedisOptions> redisOptions,
    ICaching caching)
{
    _logger = logger;
    _mapper = mapper;
    _baseService = baseService;
    _redisOptions = redisOptions;
    _caching = caching;
}


[HttpGet(Name = "GetWeatherForecast")]
public async Task<object> Get()
{
    var roleList = await _baseService.Query();

    return roleList;
}