znyc.recruitment.admin.api/Znyc.Recruitment.Admin.Commons/Core/DbContextCore/SqlServerDbContext.cs

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using Znyc.Recruitment.Admin.Commons.Extend;
using Znyc.Recruitment.Admin.Commons.Extensions;
using Znyc.Recruitment.Admin.Commons.Helpers;
using Znyc.Recruitment.Admin.Commons.IDbContext;
using Znyc.Recruitment.Admin.Commons.Pages;

namespace Znyc.Recruitment.Admin.Commons.DbContextCore
{
    /// <summary>
    ///     Sql Server数据库上下文
    /// </summary>
    public class SqlServerDbContext : BaseDbContext, ISqlServerDbContext
    {
        /// <summary>
        ///     批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities">数据实体集合</param>
        /// <param name="destinationTableName">如果为 null，则使用 TModel 名称作为 destinationTableName</param>
        /// <returns></returns>
        public override void BulkInsert<T>(IList<T> entities, string destinationTableName = null)
        {
            if (entities == null || !entities.Any())
            {
                return;
            }

            if (string.IsNullOrEmpty(destinationTableName))
            {
                string mappingTableName = typeof(T).GetCustomAttribute<TableAttribute>()?.Name;
                destinationTableName = string.IsNullOrEmpty(mappingTableName) ? typeof(T).Name : mappingTableName;
            }

            SqlBulkInsert(entities, destinationTableName);
        }

        /// <summary>
        ///     分页查询，SQL语句查询，返回指定输出对象集合
        /// </summary>
        /// <typeparam name="T">查询对象实体</typeparam>
        /// <typeparam name="TView">返回/输出实体</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="orderBys">排序条件</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="eachAction"></param>
        /// <returns></returns>
        public override PageResult<T> SqlQueryByPagination<T, TView>(string sql, string[] orderBys, int pageIndex,
            int pageSize,
            Action<TView> eachAction = null)
        {
            int total = SqlQuery<T, int>($"select count(1) from ({sql}) as s").FirstOrDefault();
            List<TView> jsonResults = SqlQuery<T, TView>(
                    $"select * from (select *,row_number() over (order by {string.Join(",", orderBys)}) as RowId from ({sql}) as s) as t where RowId between {pageSize * (pageIndex - 1) + 1} and {pageSize * pageIndex} order by {string.Join(",", orderBys)}")
                .ToList();
            if (eachAction != null)
            {
                jsonResults = jsonResults.Each(eachAction).ToList();
            }

            return new PageResult<T>
            {
                CurrentPage = pageIndex,
                ItemsPerPage = pageSize,
                TotalItems = total
            };
        }

        /// <summary>
        ///     分页查询，SQL语句查询，返回数据实体集合
        /// </summary>
        /// <typeparam name="T">查询对象实体</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="orderBys">排序条件</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="parameters">查询SQL参数</param>
        /// <returns></returns>
        public override PageResult<T> SqlQueryByPagination<T>(string sql, string[] orderBys, int pageIndex,
            int pageSize,
            params DbParameter[] parameters)
        {
            int total = (int)this.ExecuteScalar($"select count(1) from ({sql}) as s");
            List<T> jsonResults = GetDataTable(
                    $"select * from (select *,row_number() over (order by {string.Join(",", orderBys)}) as RowId from ({sql}) as s) as t where RowId between {pageSize * (pageIndex - 1) + 1} and {pageSize * pageIndex} order by {string.Join(",", orderBys)}")
                .ToList<T>();
            return new PageResult<T>
            {
                CurrentPage = pageIndex,
                ItemsPerPage = pageSize,
                TotalItems = total,
                Items = jsonResults
            };
        }

        /// <summary>
        ///     根据sql语句返回DataTable数据
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="cmdTimeout">执行超时时间，默认30毫秒</param>
        /// <param name="parameters">DbParameter[]参数</param>
        /// <returns></returns>
        public override DataTable GetDataTable(string sql, int cmdTimeout = 30, params DbParameter[] parameters)
        {
            return GetDataTables(sql, cmdTimeout, parameters).FirstOrDefault();
        }

        /// <summary>
        ///     根据sql语句返回List数据
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="cmdTimeout">执行超时时间，默认30毫秒</param>
        /// <param name="parameters">DbParameter[] 参数</param>
        /// <returns></returns>
        public override List<DataTable> GetDataTables(string sql, int cmdTimeout = 30, params DbParameter[] parameters)
        {
            List<DataTable> dts = new List<DataTable>();
            //TODO： connection 不能dispose 或者 用using，否则下次获取connection会报错提示“the connectionstring property has not been initialized。”
            DbConnection connection = Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            using (SqlCommand cmd = new SqlCommand(sql, (SqlConnection)connection))
            {
                cmd.CommandTimeout = cmdTimeout;
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    using (DataSet ds = new DataSet())
                    {
                        da.Fill(ds);
                        foreach (DataTable table in ds.Tables)
                        {
                            dts.Add(table);
                        }
                    }
                }
            }

            connection.Close();

            return dts;
        }

        /// <summary>
        ///     批量插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entities">数据实体集合</param>
        /// <param name="destinationTableName">如果为 null，则使用 TModel 名称作为 destinationTableName</param>
        /// <returns></returns>
        private void SqlBulkInsert<T>(IList<T> entities, string destinationTableName = null) where T : class
        {
            using (DataTable dt = entities.ToDataTable())
            {
                dt.TableName = destinationTableName;
                SqlConnection conn = (SqlConnection)Database.GetDbConnection();
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        SqlBulkCopy bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran)
                        {
                            BatchSize = entities.Count,
                            DestinationTableName = dt.TableName
                        };
                        GenerateColumnMappings<T>(bulk.ColumnMappings);
                        bulk.WriteToServerAsync(dt);
                        tran.Commit();
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        throw;
                    }
                }

                conn.Close();
            }
        }

        /// <summary>
        ///     字段与实体关系映射
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="mappings"></param>
        private void GenerateColumnMappings<T>(SqlBulkCopyColumnMappingCollection mappings)
            where T : class
        {
            PropertyInfo[] properties = typeof(T).GetProperties();
            foreach (PropertyInfo property in properties)
            {
                if (property.GetCustomAttributes<KeyAttribute>().Any())
                {
                    mappings.Add(new SqlBulkCopyColumnMapping(property.Name, typeof(T).Name + property.Name));
                }
                else
                {
                    mappings.Add(new SqlBulkCopyColumnMapping(property.Name, property.Name));
                }
            }
        }
    }
}