using Dapper;
using Dapper.Contrib.Extensions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace IStation.DataProvider
{
    /// <summary>
    /// 数据库访问基类
    /// </summary>
    public partial class BaseMsSqlDAL<T> where T : class, new()
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public BaseMsSqlDAL()
        {
            this.TableName = EntityHelper.GetTableName<T>();
            this.PrimaryKey = EntityHelper.GetPrimaryKey<T>();
        }

        /// <summary>
        /// 对象的表名
        /// </summary>
        public virtual string TableName { get; set; }

        /// <summary>
        /// 对象主键属性名称
        /// </summary>
        public virtual string PrimaryKey { get; set; }

        /// <summary>
        /// 数据库连接
        /// </summary>
        protected virtual IDbConnection Connection
        {
            get
            {
                var connection = new SqlConnection(ConfigHelper.Db4Scada);
                connection.Open();
                return connection;
            }
        }

        #region Insert

        /// <summary>
        /// 插入指定对象到数据库中
        /// </summary>
        /// <param name="entity">指定的对象</param>
        /// <returns></returns>
        public virtual long Insert(T entity)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.Insert(entity);
            }
        }

        /// <summary>
        /// 插入指定对象集合到数据库中
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public virtual bool Inserts(IEnumerable<T> list)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            using (IDbTransaction trans = dbConnection.BeginTransaction())
            {
                result = dbConnection.Insert(list, trans) > 0;
                trans.Commit();
            }
            return result;
        }

        #endregion

        #region Update

        /// <summary>
        /// 更新对象属性到数据库中
        /// </summary>
        /// <param name="entity">指定的对象</param>
        /// <returns></returns>
        public virtual bool Update(T entity)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Update(entity);
            }
            return result;
        }

        /// <summary>
        /// 更新指定对象集合到数据库中
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public virtual bool Updates(IEnumerable<T> list)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            using (IDbTransaction trans = dbConnection.BeginTransaction())
            {
                result = dbConnection.Update(list, trans);
                trans.Commit();
            }
            return result;
        }

        #endregion

        #region Delete

        /// <summary>
        /// 从数据库中删除指定对象
        /// </summary>
        /// <param name="entity">指定的对象</param>
        /// <returns></returns>
        public virtual bool Delete(T entity)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Delete(entity);
            }
            return result;
        }

        /// <summary>
        /// 从数据库中删除指定对象集合
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public virtual bool Deletes(IEnumerable<T> list)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            using (IDbTransaction trans = dbConnection.BeginTransaction())
            {
                result = dbConnection.Delete(list, trans);
                trans.Commit();
            }
            return result;
        }

        /// <summary>
        /// 根据指定对象的ID,从数据库中删除指定对象
        /// </summary>
        /// <param name="id">对象的ID</param>
        /// <returns></returns>
        public virtual bool DeleteByID(long id)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                string strSql = string.Format("DELETE FROM {0} WHERE {1} = @id", TableName, PrimaryKey);
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                result = dbConnection.Execute(strSql, parameters) > 0;
            }
            return result;
        }

        /// <summary>
        /// 通过id列表删除
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public virtual bool DeleteByIds(IEnumerable<long> ids)
        {
            using (IDbConnection dbConnection = Connection)
            {
                var strSql = string.Format("DELETE FROM {0} WHERE {1} IN @ids", TableName, PrimaryKey);
                var parameters = new DynamicParameters();
                parameters.Add("@ids", ids);
                var result = dbConnection.Execute(strSql, parameters) > 0 ? true : false;
                return result;
            }
        }

        /// <summary>
        /// 从数据库中删除所有对象
        /// </summary>
        /// <returns></returns>
        public virtual bool DeleteAll()
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.DeleteAll<T>();
            }
            return result;
        }

        #endregion

        #region Query
        /// <summary>
        /// 返回数据库所有的对象集合
        /// </summary>
        /// <returns></returns>
        public virtual IEnumerable<T> GetAll()
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.GetAll<T>();
            }
        }

        /// <summary>
        /// 查询数据库,返回指定ID的对象
        /// </summary>
        /// <param name="id">主键的值</param>
        /// <returns></returns>
        public virtual T GetByID(long id)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.Get<T>(id);
            }
        }


        /// <summary>
        /// 通拓id列表获取
        /// </summary>
        /// <returns></returns>
        public virtual IEnumerable<T> GetByIds(IEnumerable<long> ids)
        {
            using (IDbConnection dbConnection = Connection)
            {
                var strSql = string.Format("SELECT * FROM {0} WHERE {1} IN @ids", TableName, PrimaryKey);
                var parameters = new DynamicParameters();
                parameters.Add("@ids", ids);
                return dbConnection.Query<T>(strSql, parameters);
            }
        }
        #endregion


    }
}