Dapper in .Net Core

白雲任去留發表於2021-12-31

一、前言

  關於什麼是Dapper(詳細入口),在此不做贅述;本文僅對Dapper在.Net Core中的使用作扼要說明,所陳程式碼以示例講解為主,乃拋磚引玉,開發者可根據自身需要進行擴充套件和調整;其中如有疏漏之處,望不吝斧正。

二、Dapper環境搭建

當前以.Net Core WebAPI或MVC專案為例,框架版本為.NET 5.0,相關NuGet包引用如下:

Install-Package Dapper

Install-Package Dapper.Contrib

Install-Package Dapper.SqlBuilder

Install-Package System.Data.SqlClient

其中Dapper.Contrib和Dapper.SqlBuilder為Dapper的擴充套件,當然,Dapper的擴充套件還有如Dapper.Rainbow等其他包,根據自身需要引用,對相關引用作下說明:

  • Dapper:不言而喻;
  • Dapper.Contrib:可使用物件進行資料表的增刪改查,免卻SQL語句的編寫;
  • Dapper.SqlBuilder:可以方便動態構建SQL語句,如Join、SELECT、Where、OrderBy等等;
  • System.Data.SqlClient:由於示例資料庫為Sql Server,如MySql則引用MySql.Data;


對於Dapper.Contrib實體配置選項,以Product類為例,作扼要說明如下:

[Table("Product")]
public class Product
{
    [Key]  
    public int Id { get; set; }
    public string Name{ get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public DateTime CreateTime { get; set; }
}

對於實體配置項,有如下幾個主要項:

  • Table:指定資料庫表名,可忽略;
  • Key:指定為自動增長主鍵;
  • ExplicitKey:指定非自動增長主鍵,如guid;
  • Computed:計算列屬性,Insert、Update操作將忽略此列;
  • Write:是否可寫入,true/false,如[Write(false)],false時Insert、Update操作將忽略此列,比如可擴充套件區域性類作資料表額外查詢欄位使用;

對於資料表物件實體,可結合T4模板生成即可。

三、Dapper封裝

  關於Dapper資料訪問,這裡參考Github上的某示例(入口:https://github.com/EloreTec/UnitOfWorkWithDapper),作修改調整封裝如下:

定義DapperDBContext類

 

Dapper in .Net Core
public abstract class DapperDBContext : IContext
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        private int? _commandTimeout = null;
        private readonly DapperDBContextOptions _options;

        public bool IsTransactionStarted { get; private set; }

        protected abstract IDbConnection CreateConnection(string connectionString);

        protected DapperDBContext(IOptions<DapperDBContextOptions> optionsAccessor)
        {
            _options = optionsAccessor.Value;

            _connection = CreateConnection(_options.Configuration);
            _connection.Open();

            DebugPrint("Connection started.");
        }

        #region Transaction

        public void BeginTransaction()
        {
            if (IsTransactionStarted)
                throw new InvalidOperationException("Transaction is already started.");

            _transaction = _connection.BeginTransaction();
            IsTransactionStarted = true;

            DebugPrint("Transaction started.");
        }

        public void Commit()
        {
            if (!IsTransactionStarted)
                throw new InvalidOperationException("No transaction started.");

            _transaction.Commit();
            _transaction = null;

            IsTransactionStarted = false;

            DebugPrint("Transaction committed.");
        }

        public void Rollback()
        {
            if (!IsTransactionStarted)
                throw new InvalidOperationException("No transaction started.");

            _transaction.Rollback();
            _transaction.Dispose();
            _transaction = null;

            IsTransactionStarted = false;

            DebugPrint("Transaction rollbacked and disposed.");
        }

        #endregion Transaction

        #region Dapper.Contrib.Extensions

        public async Task<T> GetAsync<T>(int id) where T : class, new()
        {
            return await _connection.GetAsync<T>(id, _transaction, _commandTimeout);
        }

        public async Task<T> GetAsync<T>(string id) where T : class, new()
        {
            return await _connection.GetAsync<T>(id, _transaction, _commandTimeout);
        }

        public async Task<IEnumerable<T>> GetAllAsync<T>() where T : class, new()
        {
            return await _connection.GetAllAsync<T>();
        }

        public long Insert<T>(T model) where T : class, new()
        {
            return _connection.Insert<T>(model, _transaction, _commandTimeout);
        }

        public async Task<int> InsertAsync<T>(T model) where T : class, new()
        {
            return await _connection.InsertAsync<T>(model, _transaction, _commandTimeout);
        }
        public bool Update<T>(T model) where T : class, new()
        {
            return _connection.Update<T>(model, _transaction, _commandTimeout);
        }

        public async Task<bool> UpdateAsync<T>(T model) where T : class, new()
        {
            return await _connection.UpdateAsync<T>(model, _transaction, _commandTimeout);          
        }

        public async Task<Page<T>> PageAsync<T>(long pageIndex, long pageSize, string sql, object param = null)
        {
            DapperPage.BuildPageQueries((pageIndex - 1) * pageSize, pageSize, sql, out string sqlCount, out string sqlPage);

            var result = new Page<T>
            {
                CurrentPage = pageIndex,
                ItemsPerPage = pageSize,
                TotalItems = await _connection.ExecuteScalarAsync<long>(sqlCount, param)
            };
            result.TotalPages = result.TotalItems / pageSize;

            if ((result.TotalItems % pageSize) != 0)
                result.TotalPages++;

            result.Items = await _connection.QueryAsync<T>(sqlPage, param);
            return result;
        }
      

        #endregion


        #region Dapper Execute & Query
      

        public int ExecuteScalar(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.ExecuteScalar<int>(sql, param, _transaction, _commandTimeout, commandType);
        }

        public async Task<int> ExecuteScalarAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.ExecuteScalarAsync<int>(sql, param, _transaction, _commandTimeout, commandType);
        }
        public int Execute(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.Execute(sql, param, _transaction, _commandTimeout, commandType);
        }

        public async Task<int> ExecuteAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, commandType);
        }

        public IEnumerable<T> Query<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.Query<T>(sql, param, _transaction, true, _commandTimeout, commandType);
        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
        }

        public T QueryFirstOrDefault<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.QueryFirstOrDefault<T>(sql, param, _transaction, _commandTimeout, commandType);
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryFirstOrDefaultAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
        }
        public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text)
        {
            return _connection.Query(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType);
        }

        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryAsync(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType);
        }

        public async Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryMultipleAsync(sql, param, _transaction, _commandTimeout, commandType);
        }

        #endregion Dapper Execute & Query

        public void Dispose()
        {
            if (IsTransactionStarted)
                Rollback();

            _connection.Close();
            _connection.Dispose();
            _connection = null;

            DebugPrint("Connection closed and disposed.");
        }

        private void DebugPrint(string message)
        {
#if DEBUG
            Debug.Print(">>> UnitOfWorkWithDapper - Thread {0}: {1}", Thread.CurrentThread.ManagedThreadId, message);
#endif
        }
    }
View Code

 

Dapper in .Net Core
public class DapperDBContextOptions : IOptions<DapperDBContextOptions>
    {
        public string Configuration { get; set; }

        DapperDBContextOptions IOptions<DapperDBContextOptions>.Value
        {
            get { return this; }
        }
    }

 public interface IContext : IDisposable
    {
        bool IsTransactionStarted { get; }

        void BeginTransaction();

        void Commit();

        void Rollback();
    }
View Code

 

以上程式碼涵蓋了Dapper訪問資料庫的基本操作,分同步和非同步,其中大部分不作贅述,著重說下分頁部分;

非同步分頁構建(PageAsync)

分頁這裡為方便呼叫,只需傳入要查詢的Sql語句(如:SELECT * FROM Table,必須帶Order BY)、頁索引、頁大小即可;

至於具體如何構建的,這裡參照某小型ORM工具PetaPoco,抽取相關程式碼如下,有興趣的同學也可以自行改造:

public class Page<T>
    {
        /// <summary>
        /// The current page number contained in this page of result set 
        /// </summary>
        public long CurrentPage { get; set; }

        /// <summary>
        /// The total number of pages in the full result set
        /// </summary>
        public long TotalPages { get; set; }

        /// <summary>
        /// The total number of records in the full result set
        /// </summary>
        public long TotalItems { get; set; }

        /// <summary>
        /// The number of items per page
        /// </summary>
        public long ItemsPerPage { get; set; }

        /// <summary>
        /// The actual records on this page
        /// </summary>
        public IEnumerable<T> Items { get; set; }
        //public List<T> Items { get; set; }
    }
    public class DapperPage
    {
        public static void BuildPageQueries(long skip, long take, string sql, out string sqlCount, out string sqlPage)
        {
            // Split the SQL
            if (!PagingHelper.SplitSQL(sql, out PagingHelper.SQLParts parts))
                throw new Exception("Unable to parse SQL statement for paged query");

            sqlPage = BuildPageSql.BuildPageQuery(skip, take, parts);
            sqlCount = parts.sqlCount;
        }
    }

    static class BuildPageSql
    {
        public static string BuildPageQuery(long skip, long take, PagingHelper.SQLParts parts)
        {
            parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1);
            if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved))
            {
                parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner";
            }
            var sqlPage = string.Format("SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) peta_rn, {1}) peta_paged WHERE peta_rn>{2} AND peta_rn<={3}",
                                    parts.sqlOrderBy ?? "ORDER BY (SELECT NULL)", parts.sqlSelectRemoved, skip, skip + take);
            //args = args.Concat(new object[] { skip, skip + take }).ToArray();

            return sqlPage;
        }

        //SqlServer 2012及以上
        public static string BuildPageQuery2(long skip, long take, PagingHelper.SQLParts parts)
        {
            parts.sqlSelectRemoved = PagingHelper.rxOrderBy.Replace(parts.sqlSelectRemoved, "", 1);
            if (PagingHelper.rxDistinct.IsMatch(parts.sqlSelectRemoved))
            {
                parts.sqlSelectRemoved = "peta_inner.* FROM (SELECT " + parts.sqlSelectRemoved + ") peta_inner";
            }    

            var sqlOrderBy = parts.sqlOrderBy ?? "ORDER BY (SELECT NULL)";
            var sqlPage = $"SELECT {parts.sqlSelectRemoved} {sqlOrderBy} OFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY";
            return sqlPage;
        }
    }

    static class PagingHelper
    {
        public struct SQLParts
        {
            public string sql;
            public string sqlCount;
            public string sqlSelectRemoved;
            public string sqlOrderBy;
        }

        public static bool SplitSQL(string sql, out SQLParts parts)
        {
            parts.sql = sql;
            parts.sqlSelectRemoved = null;
            parts.sqlCount = null;
            parts.sqlOrderBy = null;

            // Extract the columns from "SELECT <whatever> FROM"
            var m = rxColumns.Match(sql);
            if (!m.Success)
                return false;

            // Save column list and replace with COUNT(*)
            Group g = m.Groups[1];
            parts.sqlSelectRemoved = sql.Substring(g.Index);

            if (rxDistinct.IsMatch(parts.sqlSelectRemoved))
                parts.sqlCount = sql.Substring(0, g.Index) + "COUNT(" + m.Groups[1].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
            else
                parts.sqlCount = sql.Substring(0, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length);


            // Look for the last "ORDER BY <whatever>" clause not part of a ROW_NUMBER expression
            m = rxOrderBy.Match(parts.sqlCount);
            if (!m.Success)
            {
                parts.sqlOrderBy = null;
            }
            else
            {
                g = m.Groups[0];
                parts.sqlOrderBy = g.ToString();
                parts.sqlCount = parts.sqlCount.Substring(0, g.Index) + parts.sqlCount.Substring(g.Index + g.Length);
            }

            return true;
        }

        public static Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        public static Regex rxOrderBy = new Regex(@"\bORDER\s+BY\s+(?!.*?(?:\)|\s+)AS\s)(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.RightToLeft | RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
        public static Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
    }

 

對於構建分頁語句,分別示例BuildPageQuery和BuildPageQuery2,前者為通過ROW_NUMBER進行分頁(針對SqlServer2005、2008),後者通過OFFSET、FETCH分頁(針對SqlServer2012及以上版本),相關輔助操作類一覽便知,如果使用MySql資料庫,可酌情自行封裝; 

至於Where查詢的進一步封裝,有興趣的也可兌Dapper lamada查詢進行擴充套件。

 

定義工作單元與事務

Dapper in .Net Core
    public interface IUnitOfWork : IDisposable
    {
        void SaveChanges();
    }

    public interface IUnitOfWorkFactory
    {
        IUnitOfWork Create();
    }

public class UnitOfWork : IUnitOfWork
    {
        private readonly IContext _context;

        public UnitOfWork(IContext context)
        {
            _context = context;
            _context.BeginTransaction();
        }

        public void SaveChanges()
        {
            if (!_context.IsTransactionStarted)
                throw new InvalidOperationException("Transaction have already been commited or disposed.");

            _context.Commit();
        }

        public void Dispose()
        {
            if (_context.IsTransactionStarted)
                _context.Rollback();
        }
    }

public class DapperUnitOfWorkFactory : IUnitOfWorkFactory
    {
        private readonly DapperDBContext _context;

        public DapperUnitOfWorkFactory(DapperDBContext context)
        {
            _context = context;
        }

        public IUnitOfWork Create()
        {
            return new UnitOfWork(_context);
        }
    }
View Code

 

定義資料倉儲

Dapper in .Net Core
    #region Product
    public partial interface IProductRepository
    {
        Task<Product> GetAsync(int id);

        Task<IEnumerable<Product>> GetAllAsync();

        long Insert(Product model);

        Task<int> InsertAsync(Product model);

        bool Update(Product model);

        Task<bool> UpdateAsync(Product model);       

        int Count(string where, object param = null);

        Task<int> CountAsync(string where, object param = null);

        bool Exists(string where, object param = null);

        Task<bool> ExistsAsync(string where, object param = null);        

        Product FirstOrDefault(string where, object param = null);

        Task<Product> FirstOrDefaultAsync(string where, object param = null);

        T FirstOrDefault<T>(string sql, object param = null);

        Task<T> FirstOrDefaultAsync<T>(string sql, object param = null);

        IEnumerable<Product> Fetch(SqlBuilder where);

        Task<IEnumerable<Product>> FetchAsync(SqlBuilder where);

        IEnumerable<T> Fetch<T>(string sql, SqlBuilder where, bool orderBy = true);

        Task<IEnumerable<T>> FetchAsync<T>(string sql, SqlBuilder where, bool orderBy = true);

        Task<Page<Product>> PageAsync(long pageIndex, long pageSize, SqlBuilder builder);

        Task<Page<T>> PageAsync<T>(string sql, long pageIndex, long pageSize, SqlBuilder builder);

        Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null);
    }

    public partial class ProductRepository : IProductRepository
    {
        private readonly DapperDBContext _context;
        public ProductRepository(DapperDBContext context)
        {
            _context = context;
        }

        public async Task<Product> GetAsync(int id)
        { 
            return await _context.GetAsync<Product>(id);
        }

        public async Task<IEnumerable<Product>> GetAllAsync()
        {
            return await _context.GetAllAsync<Product>();
        }

        public long Insert(Product model)
        {
            return _context.Insert<Product>(model);
        }

        public async Task<int> InsertAsync(Product model)
        {
            return await _context.InsertAsync<Product>(model);
        }    

        public bool Update(Product model)
        { 
            return _context.Update<Product>(model);
        }    

        public async Task<bool> UpdateAsync(Product model)
        { 
            return await _context.UpdateAsync<Product>(model);
        }

        public int Count(string where, object param = null)
        {
            string strSql = $"SELECT COUNT(1) FROM Product {where}";
            return _context.ExecuteScalar(strSql, param);
        }

        public async Task<int> CountAsync(string where, object param = null)
        {
            string strSql = $"SELECT COUNT(1) FROM Product {where}";
            return await _context.ExecuteScalarAsync(strSql, param);
        }

        public bool Exists(string where, object param = null)
        {
            string strSql = $"SELECT TOP 1 1 FROM Product {where}";
            var count = _context.ExecuteScalar(strSql, param);
            return count > 0;
        }

        public async Task<bool> ExistsAsync(string where, object param = null)
        {
            string strSql = $"SELECT TOP 1 1 FROM Product {where}";
            var count = await _context.ExecuteScalarAsync(strSql, param);
            return count > 0;
        }

        public Product FirstOrDefault(string where, object param = null)
        {
            string strSql = $"SELECT TOP 1 * FROM Product {where}";
            return _context.QueryFirstOrDefault<Product>(strSql, param);
        }

        public async Task<Product> FirstOrDefaultAsync(string where, object param = null)
        {
            string strSql = $"SELECT TOP 1 * FROM Product {where}";
            return await _context.QueryFirstOrDefaultAsync<Product>(strSql, param);
        }

        public T FirstOrDefault<T>(string sql, object param = null)
        {
            return _context.QueryFirstOrDefault<T>(sql, param);
        }

        public async Task<T> FirstOrDefaultAsync<T>(string sql, object param = null)
        {
            return await _context.QueryFirstOrDefaultAsync<T>(sql, param);
        }

        public IEnumerable<Product> Fetch(SqlBuilder where)
        {
            var strSql = where.AddTemplate(@"SELECT * FROM Product /**where**/ /**orderby**/");
            return _context.Query<Product>(strSql.RawSql, strSql.Parameters);
        }

        public async Task<IEnumerable<Product>> FetchAsync(SqlBuilder where)
        {
            var strSql = where.AddTemplate(@"SELECT * FROM Product /**where**/ /**orderby**/");
            return await _context.QueryAsync<Product>(strSql.RawSql, strSql.Parameters);
        }

        public IEnumerable<T> Fetch<T>(string sql, SqlBuilder where, bool orderBy = true)
        {
            var _sql = orderBy ? $"{sql} /**where**/ /**orderby**/" : $"{sql} /**where**/";
            var strSql = where.AddTemplate(_sql);
            return _context.Query<T>(strSql.RawSql, strSql.Parameters);
        }

        public async Task<IEnumerable<T>> FetchAsync<T>(string sql, SqlBuilder where, bool orderBy = true)
        {
            var _sql = orderBy ? $"{sql} /**where**/ /**orderby**/" : $"{sql} /**where**/";
            var strSql = where.AddTemplate(_sql);
            return await _context.QueryAsync<T>(strSql.RawSql, strSql.Parameters);
        }

        public async Task<Page<Product>> PageAsync(long pageIndex, long pageSize, SqlBuilder builder)
        {         
            var strSql = "SELECT * FROM Product";   
            return await PageAsync<Product>(strSql, pageIndex, pageSize, builder);
        }

        public async Task<Page<T>> PageAsync<T>(string sql, long pageIndex, long pageSize, SqlBuilder builder)
        {
            var strSql = builder.AddTemplate($"{sql} /**where**/ /**orderby**/");
            return await _context.PageAsync<T>(pageIndex, pageSize, strSql.RawSql, strSql.Parameters);
        }

        public async Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null)
        {          
            return await _context.QueryMultipleAsync(sql, param);
        }
    }
    #endregion
View Code

根據自身需要進行調整或擴充套件,一般藉助T4模板生成

 

資料庫連線

通過Ioptions模式讀取配置檔案appsettings中連線字串

public class MyDBContext : DapperDBContext
    {
        public MyDBContext(IOptions<DapperDBContextOptions> optionsAccessor) : base(optionsAccessor)
        {
        }

        protected override IDbConnection CreateConnection(string connectionString)
        {
            IDbConnection conn = new SqlConnection(connectionString);
            return conn;
        }
    }

 

四、Dapper使用

Startup.cs注入並讀取資料庫連線字串

{
  "SQLConnString": "Data Source=(local);Initial Catalog=database;Persist Security Info=True;User ID=sa;Password=123456;MultipleActiveResultSets=True;",  
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

 

services.AddDapperDBContext<MyDBContext>(options =>
            {
                options.Configuration = Configuration["SQLConnString"];
            });

 

簡單示例WebAPI或Net Core MVC下的呼叫示例:

 

Dapper in .Net Core
public class ProductController : BaseController
{
    private readonly IProductRepository _productRepository;     
    

    public ProductController(
        IProductRepository productRepository
        
        )
    {

        _productRepository = productRepository;          
        
    }

    //商品列表
    [HttpGet]        
    public async Task<IActionResult> ProductList(DateTime? startDate, DateTime? endDate, int id = 1, int productStatus = 0, string keyword = "")
    {
        var model = new ProductModels();
        var builder = new Dapper.SqlBuilder();
        builder.Where("ProductStatus!=@ProductStatus", new { ProductStatus = productStatus });

        if (startDate.HasValue)
        {
            builder.Where("CreateTime>=@startDate", new { startDate = startDate.Value});
        }
        if (endDate.HasValue)
        {
            builder.Where("CreateTime<@endDate", new { endDate = endDate.Value.AddDays(1)});
        }           

        if (!string.IsNullOrWhiteSpace(keyword))
        {
            builder.Where("Name LIKE @keyword", new { keyword = $"%{StringHelper.ReplaceSql(keyword)}%" });
        }  

        builder.OrderBy("SortNum DESC,CreateTime DESC");

        var list = await _productRepository.PageAsync(id, PageSize, builder);
        

        model.ProductList = new PagedList<Product>(list.Items, id, PageSize, list.TotalItems);

        if (Request.IsAjaxRequest())
            return PartialView("_ProductList", model.ProductList);
        
        return View(model);
    }

    //新增商品
    [HttpPost] 
    public async Task<int> AddProduct(ProductModels model)
    {
        return await _productRepository.InsertAsync(model);
    }


}
View Code

 

Dapper in .Net Core
public partial interface IProductService
    { 
        Task<bool> AddProduct(Product productInfo, List<ProductStock> skuList);     

    }
    public class ProductService: IProductService
    {
        private readonly DapperDBContext _context;
        private readonly IUnitOfWorkFactory _uowFactory;

        public ProductService(DapperDBContext context, IUnitOfWorkFactory uowFactory)
        {
            _context = context;
            _uowFactory = uowFactory;
        }

        /// <summary>
        /// 新增商品
        /// </summary>
        /// <param name="productInfo"></param>
        /// <param name="skuList"></param>
        /// <returns></returns>
        public async Task<bool> AddProduct(Product productInfo, List<ProductStock> skuList)
        {
            var result = false;
            using (var uow = _uowFactory.Create())
            {
                //新增產品
                await _context.InsertAsync(productInfo);

                //新增Sku庫存售價
               
                //await _context.InsertAsync(skuList);

                uow.SaveChanges();
                result = true;
            }
            return result;
        }        

    }
View Code

 

相關文章