using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace WebAPI.Dapper
{
    public class SqlHelper
    {
        private readonly string _connStr;
        public SqlHelper(string connStr)
        {
            _connStr = connStr;
        }
        IDbConnection GetConnection()
        {
            IDbConnection db = new SqlConnection(_connStr);
            db.Open();
            return db;
        }
        /// <summary>
        /// 获取一个值，param可以是SQL参数也可以是匿名对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public T GetValue<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = GetConnection())
            {
                return connection.ExecuteScalar<T>(sql, param, null, commandTimeout, commandType);
            }
        }
        /// <summary>
        /// 事务执行
        /// </summary>
        /// <param name="sql1"></param>
        /// <param name="sql2"></param>
        /// <param name="obj1"></param>
        /// <param name="obj2"></param>
        /// <returns></returns>
        public int Trans(string sql1, string sql2, object obj1, object obj2)
        {
            using (var connection = GetConnection())
            {
                IDbTransaction transaction = connection.BeginTransaction();
                try
                {
                    connection.Execute(sql1, obj1);
                    connection.Execute(sql2, obj2);
                    transaction.Commit();
                    return 1;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    return -1;
                }
            }
        }

        /// <summary>
        /// 获取一个数据模型实体类，param可以是SQL参数也可以是匿名对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public T GetModel<T>(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = GetConnection())
            {
                return connection.QueryFirstOrDefault<T>(sql, param, null, commandTimeout, commandType);
            }
        }
        public int GetExecuteScalar(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = GetConnection())
            {
                var result = connection.ExecuteScalar<int>(sql, param, null, commandTimeout, commandType);
                return result;
            }
        }

        /// <summary>
        /// 获取符合条件的所有数据模型实体类列表，param可以是SQL参数也可以是匿名对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public List<T> GetModelList<T>(string sql, object param = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) where T : class
        {
            using (var connection = GetConnection())
            {
                return connection.Query<T>(sql, param, null, buffered, commandTimeout, commandType).AsList();
            }
        }


        /// <summary>
        /// 获取符合条件的所有指定返回结果对象的列表(复合对象【如：1对多，1对1】)，param可以是SQL参数也可以是匿名对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="types"></param>
        /// <param name="map"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="buffered"></param>
        /// <param name="splitOn"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>

        public List<T> GetMultModelList<T>(string sql, Type[] types, Func<object[], T> map, object param = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = GetConnection())
            {
                return connection.Query<T>(sql, types, map, param, null, buffered, splitOn, commandTimeout, commandType).AsList();
            }
        }

        /// <summary>
        /// 执行SQL命令（CRUD），param可以是SQL参数也可以是要添加的实体类
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public int ExecuteCommand(string sql, object param = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            using (var connection = GetConnection())
            {

                int result = connection.Execute(sql, param, null, commandTimeout, commandType);
                return result;
            }
        }


        public bool ExecuteCommandByTrans(Action<IDbCommand> action)
        {
            using (var connection = GetConnection())
            {
                IDbTransaction transaction = connection.BeginTransaction();
                IDbCommand cmd = connection.CreateCommand();
                cmd.Transaction = transaction;
                try
                {
                    action.Invoke(cmd);
                    transaction.Commit();
                    return true;
                }
                catch
                {
                    transaction.Rollback();
                }
            }
            return false;
        }
    }
}