using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Tools;

namespace VueWebCoreApi.Quartz
{
    public static class DapperHelper
    {
        // 配置文件读取，建议放到appsettings.json
        private static readonly string _connectionString = AppSetting.GetAppSetting("DBServer");
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        public static IDbConnection GetConnection()
        {
            var conn = new SqlConnection(_connectionString);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            return conn;
        }

        /// <summary>
        /// 执行查询返回DataTable
        /// </summary>
        public static DataTable SelectTable(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                var reader = conn.ExecuteReader(sql, param);
                var dt = new DataTable();
                dt.Load(reader);
                return dt;
            }
        }

        /// <summary>
        /// 执行增删改
        /// </summary>
        public static int Execute(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                return conn.Execute(sql, param);
            }
        }

        /// <summary>
        /// 异步执行增删改
        /// </summary>
        public static async Task<int> ExecuteAsync(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                return await conn.ExecuteAsync(sql, param);
            }
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        public static bool DoTransaction(List<(string Sql, object Param)> list)
        {
            using (var conn = GetConnection())
            {
                using (var tran = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (var item in list)
                        {
                            conn.Execute(item.Sql, item.Param, tran);
                        }
                        tran.Commit();
                        return true;
                    }
                    catch
                    {
                        tran.Rollback();
                        return false;
                    }
                }
            }
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        public static List<T> Query<T>(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                return conn.Query<T>(sql, param).ToList();
            }
        }

        /// <summary>
        /// 异步查询列表
        /// </summary>
        public static async Task<List<T>> QueryAsync<T>(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                var result = await conn.QueryAsync<T>(sql, param);
                return result.ToList();
            }
        }

        /// <summary>
        /// 查询单个对象
        /// </summary>
        public static T QueryFirstOrDefault<T>(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                return conn.QueryFirstOrDefault<T>(sql, param);
            }
        }

        /// <summary>
        /// 异步查询单个对象
        /// </summary>
        public static async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null)
        {
            using (var conn = GetConnection())
            {
                return await conn.QueryFirstOrDefaultAsync<T>(sql, param);
            }
        }
    }
}