using CB.Entity;
using Dapper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CB.Common
{
    public class DapperHelper
    {
        class Nested
        {
            internal static readonly string connString = ConfigurationManager.ConnectionStrings["CB55128"].ToString();
        }

        #region 属性



        /// <summary>
        /// 获取 数据库连接串
        /// </summary>
        private IDbConnection Connection
        {
            get
            {
                var dbconnection = new SqlConnection(Nested.connString);
                if (dbconnection.State == ConnectionState.Closed)
                {
                    dbconnection.Open();
                }
                return dbconnection;
            }

        }

        #endregion 属性

        /// <summary>
        /// 新增sql
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="para">参数化</param>
        public void Insert(string sql, object para = null)
        {

            using (var db = Connection)
            {
                db.Execute(sql, para);
            }
        }

        public IEnumerable<T> FindList<T>(string sql, object para=null)
        {

            using (var db = Connection)
            {
                return db.Query<T>(sql, para);
            }
        }
    }

    public class VisitIPDAL
    {
        private static readonly DapperHelper db = new DapperHelper();
        /// <summary>
        /// 拦截器日志
        /// </summary>
        /// <param name="url"></param>
        /// <param name="ip"></param>
        public static void AddActionFilterLog(string url, string ip)
        {
            var sql = @"
INSERT INTO VisitIP(IP,Url,Time)
VALUES (@IP,@Url,@Time)
";
            var para = new SqlParameter[] {
                new SqlParameter(@"IP",ip),
                new SqlParameter(@"Url",url),
                new SqlParameter(@"Time",DateTime.Now),
            };
            db.Insert(sql, new { IP = ip, Url = url, Time = DateTime.Now });
        }

        public static List<string> GetProhibitIP()
        {
            var sql = @"
SELECT Id,IP FROM ProhibitIP
";
            return db.FindList<ProhibitIPEntity>(sql).ToList().Select(p => p.IP).ToList();
        }
    }
}