ORM之SqlSugar简单示例

示例结构

 还有一种不需要拷贝X86/X64 下的SQLite.Interop.dll 以及System.Data.SQLite.dll 。直接安装Nuget包System.Data.SQLite，SqlSugar

下面给出示例代码，安装编码框架可扩展

IDal接口定义

namespace ORMRepository
{
    /// <summary>
    /// 数据库访问接口
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IDal<T>
    {
        /// <summary>
        /// 通过id获取数据
        /// </summary>
        /// <returns></returns>
        Task<T> GetByIdAsync(string id);
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        Task<List<T>> GetAllAsync();
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        Task<bool> InsertAsync(T t);
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        Task<bool> UpdateAsync(T t);
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        Task<bool> DeleteAsync(T t);
    }
}

namespace ORMRepository
{
    internal class DbConnectionFactory
    {
        internal static string ConnectionString;
        public static SqlSugarClient GetDbConnection()
        {
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                DbType = DbType.Sqlite,
                ConnectionString = ConnectionString,
                InitKeyType = InitKeyType.Attribute,
                IsAutoCloseConnection = true,
            });
            
            //SqlSugar默认会创建
            //if (!File.Exists(DbFileFullName))
            //{
            //db.DbMaintenance.CreateDatabase();
            //}
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //保留日志
                Debug.WriteLine(sql);
            };
            return db;
        }
    }
}

namespace ORMRepository
{
    /// <summary>
    /// 访问数据模板
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    public abstract class BaseDalTemplate<TEntity> where TEntity : class, new()
    {
        public abstract Task<TEntity> GetByIdAsync(string id);
        /// <summary>
        /// 管理队列锁
        /// </summary>
        private readonly SemaphoreSlim _parallelExecuteLock;
        protected BaseDalTemplate()
        {
            try
            {
                _parallelExecuteLock = new SemaphoreSlim(1);
                using (var client = DbConnectionFactory.GetDbConnection())
                {
                    //代码先行
                    client.CodeFirst.InitTables<TEntity>();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
        }

        /// <summary>
        /// 根据lambda表达式获取实体
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public async Task<List<TEntity>> GetByExpressionAsync(Expression<Func<TEntity, bool>> expression)
        {
            try
            {
                await _parallelExecuteLock.WaitAsync();
                using (var client = DbConnectionFactory.GetDbConnection())
                {
                    return await client.Queryable<TEntity>().Where(expression).ToListAsync();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
            finally
            {
                _parallelExecuteLock.Release();
            }
            return new List<TEntity>();
        }

        /// <summary>
        /// 获取所有实体
        /// </summary>
        /// <returns></returns>
        public async Task<List<TEntity>> GetAllAsync()
        {
            try
            {
                await _parallelExecuteLock.WaitAsync();
                using (var client = DbConnectionFactory.GetDbConnection())
                {
                    return await client.Queryable<TEntity>().ToListAsync();
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
            finally
            {
                _parallelExecuteLock.Release();
            }
            return new List<TEntity>();
        }

        /// <summary>
        /// 插入实体
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<bool> InsertAsync(TEntity t)
        {
            try
            {
                await _parallelExecuteLock.WaitAsync();
                using (var client = DbConnectionFactory.GetDbConnection())
                {
                    var executeCommand = await client.Insertable(t).ExecuteCommandAsync();
                    return executeCommand > 0;
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
            finally
            {
                _parallelExecuteLock.Release();
            }
            return false;
        }

        /// <summary>
        /// 更新实体
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<bool> UpdateAsync(TEntity t)
        {
            try
            {
                await _parallelExecuteLock.WaitAsync();
                using (var client = DbConnectionFactory.GetDbConnection())
                {
                    var executeCommand = await client.Updateable(t).ExecuteCommandAsync();
                    return executeCommand > 0;
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
            finally
            {
                _parallelExecuteLock.Release();
            }
            return false;
        }

        /// <summary>
        /// 删除实体
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<bool> DeleteAsync(TEntity t)
        {
            try
            {
                await _parallelExecuteLock.WaitAsync();
                using (var client = DbConnectionFactory.GetDbConnection())
                {
                    var executeCommand = await client.Deleteable(t).ExecuteCommandAsync();
                    return executeCommand > 0;
                }
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex);
            }
            finally
            {
                _parallelExecuteLock.Release();
            }
            return false;
        }
    }
}

namespace ORMRepository.Repository.DB
{
    [SugarTable("TB_TestInfo")]
    internal class TestInfoEntity
    {
        [SugarColumn(IsPrimaryKey = true)]
        public string CommandId { get; set; }
        [SugarColumn(IsNullable = true)]
        public string DownloadUrl { get; set; }

        [SugarColumn(IsNullable = true)]
        public string Md5 { get; set; }
        public DateTime CreateTime { get; set; } = DateTime.Now;
        public int TotalSize { get; set; }
        public byte Status { get; set; }

        //[SugarColumn(ColumnDataType ="boolean")]
        public bool CanOverlay { get; set; }
    }
}

namespace ORMRepository.Repository.DAL
{
    internal class TestInfoDal : BaseDalTemplate<TestInfoEntity>, IDal<TestInfoEntity>
    {
        public override async Task<TestInfoEntity> GetByIdAsync(string id)
        {
            return (await GetByExpressionAsync(obj => obj.CommandId == id)).FirstOrDefault();
        }
    }
}

示例调用

using ORMRepository.Repository.DAL;
using ORMRepository.Repository.DB;
using System;
using System.IO;
using System.Threading.Tasks;
using System.Windows;

namespace ORMRepository
{
    /// <summary>
    /// App.xaml 的交互逻辑
    /// </summary>
    public partial class App : Application
    {
        /// <summary>
        /// 文件推送
        /// </summary>
        internal static Lazy<TestInfoDal> TestInfoDal;
        public App()
        {
            DbConnectionFactory.ConnectionString = $"DataSource={Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"ORMRepository.sqlite")}";
            TestInfoDal = new Lazy<TestInfoDal>();
            TestAsync();
        }

        public async Task TestAsync()
        {
            var testInfoDal = TestInfoDal.Value;
            var testInfoEntities = await testInfoDal.GetAllAsync();
            var testInfoEntity = new TestInfoEntity
            {
                CommandId = Guid.NewGuid().ToString()
            };
            await testInfoDal.InsertAsync(testInfoEntity);
        }
    }
}