using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Text;

namespace DateBaseConnection
{
    public class ConnectionPool
    {

        private static ConnectionPool cpool = null;//池管理对象
        private static Object objlock = typeof(ConnectionPool);//池管理对象实例
        private int size = 10;//池中连接数
        private int useCount = 0;//已经使用的连接数
        private ArrayList pool = null;//连接保存的集合
        private Dictionary<string, SqlConnection> poolist = new Dictionary<string, SqlConnection>();
        private String ConnectionStr = "";//连接字符串

        public ConnectionPool()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
            ConnectionStr = "server=127.0.0.1;database=Chloe;uid=sa;pwd=a1b2c3d4;Pooling=true;Max Pool Size=40000;Min Pool Size=0;";
            size = 100;
            pool = new ArrayList();
        }

        public SqlConnection getConn()
        {
            SqlConnection conn = new SqlConnection(ConnectionStr);
            return conn;
        }

        public static ConnectionPool getPools()
        {
            return new ConnectionPool();
        }
        //创建获取连接池对象
        public static ConnectionPool getPool()
        {
            lock (objlock)
            {
                if (cpool == null)
                {
                    cpool = new ConnectionPool();
                }
                return cpool;
            }
        }
        //获取池中的连接
        public SqlConnection getConnection(string key)
        {
            lock (poolist)
            {
                SqlConnection tmp = null;
                if (poolist.ContainsKey(key))
                {
                    tmp = poolist[key];
                    poolist.Remove(key);
                    //不成功
                    if (!isUserful(tmp))
                    {
                        //可用的连接数据已去掉一个
                        useCount--;
                        tmp = getConnection(key);
                    }
                }
                else
                {
                    //可使用的连接小于连接数量
                    if (useCount < size)
                    {
                        try

                        {
                            //创建连接
                            SqlConnection conn = new SqlConnection(ConnectionStr);
                            conn.Open();
                            useCount++;
                            tmp = conn;
                        }
                        catch (Exception e)
                        {
                        }
                    }
                }
                return tmp;
            }
        }
        //关闭连接,加连接回到池中
        public void closeConnection(string key, SqlConnection con)
        {
            lock (poolist)
            {
                if (con != null)
                {
                    if (poolist.ContainsKey(key))
                    {
                        poolist[key] = con;
                    }
                    else
                    {
                        poolist.Add(key, con);
                    }

                }
            }
        }

        /// <summary>
        /// 关闭所有连接
        /// </summary>
        public void Close(string key)
        {
            lock (poolist)
            {
                if (poolist.ContainsKey(key))
                {
                    SqlConnection con = poolist[key];
                    con.Close();
                    con.Dispose();
                }
            }
        }

        //目的保证所创连接成功,测试池中连接
        private bool isUserful(SqlConnection con)
        {
            //主要用于不同用户
            bool result = true;
            if (con != null)
            {
                string sql = "select 1";//随便执行对数据库操作
                SqlCommand cmd = new SqlCommand(sql, con);
                try
                {


                    cmd.ExecuteScalar().ToString();
                }
                catch
                {
                    result = false;
                }

            }
            return result;
        }
        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="StrSql"></param>
        /// <returns></returns>
        public DataTable GetDataReader(string StrSql)//数据查询 
        {
            SqlConnection SqlDrConn = new SqlConnection(ConnectionStr);
            //当连接处于打开状态时关闭,然后再打开,避免有时候数据不能及时更新 
            if (SqlDrConn.State == ConnectionState.Open)
            {
                SqlDrConn.Close();
            }
            try
            {
                SqlDrConn.Open();
                SqlCommand SqlCmd = new SqlCommand(StrSql, SqlDrConn);
                SqlDataReader SqlDr = SqlCmd.ExecuteReader();
                if (SqlDr.HasRows)
                {
                    DataTable dt = new DataTable();
                    //读取SqlDataReader里的内容 
                    dt.Load(SqlDr);
                    //关闭对象和连接 
                    SqlDr.Close();
                    SqlDrConn.Close();
                    return dt;
                }
                return null;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                SqlDrConn.Close();
            }
        }
    }


    public static class test
    {


        public static void TestGet1()
        {
            for (int i = 1; i <= 2; ++i)
            {
                Stopwatch sw = new Stopwatch();
                sw.Start();
                var dt = ConnectionPool.getPool().GetDataReader("Select * from Users");
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("ID:" + i + "，方法1姓名：" + dr[1]);
                }
                sw.Stop();
                Console.WriteLine("Without Pooling, first connection elapsed {0} ms", sw.ElapsedMilliseconds);
                sw.Reset();

                sw.Start();

                dt = ConnectionPool.getPool().GetDataReader("Select * from City");
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("ID:" + i + "，方法1名称：" + dr[1]);
                }
                sw.Stop();
                Console.WriteLine("Without Pooling, average connection elapsed {0} ms", sw.ElapsedMilliseconds / 100);
                dt = ConnectionPool.getPool().GetDataReader("Select * from Province");
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("ID:" + i + "，方法1震惊：" + dr[1]);
                }
            }
        }
        public static void TestGet2()
        {
            for (int i = 1; i <= 2; ++i)
            {
                var dt = ConnectionPool.getPool().GetDataReader("Select * from Users");
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("ID:" + i + "，方法2姓名：" + dr[1]);
                }
                dt = ConnectionPool.getPool().GetDataReader("Select * from City");
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("ID:" + i + "，方法2名称：" + dr[1]);
                }
                dt = ConnectionPool.getPool().GetDataReader("Select * from Province");
                foreach (DataRow dr in dt.Rows)
                {
                    Console.WriteLine("ID:" + i + "，方法2震惊：" + dr[1]);
                }
            }
        }

        public static void Gettest1()
        {
            //调用
            SqlConnection conn = null;
            string strkey = Guid.NewGuid().ToString();
            for (int i = 1; i <= 10; ++i)
            {
                //获取连接
                conn = ConnectionPool.getPool().getConnection(strkey);
                try
                {
                    //数据操作
                    SqlCommand cmd = new SqlCommand("Select * from Users", conn);
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine("ID:" + i + "，1姓名：" + dr[1]);
                    }
                    dr.Close();

                    cmd = new SqlCommand("Select * from Province", conn);
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine("ID:" + i + "，1名称：" + dr[1]);
                    }
                    dr.Close();

                    //将连接添加回连接池中
                    ConnectionPool.getPool().closeConnection(strkey, conn);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("\n异常信息:\n{0}", ex.Message);
                    break;
                }
            }
            ConnectionPool.getPool().Close(strkey);
        }

        public static void Gettest2()
        {
            //调用
            SqlConnection conn = null;
            string strkey = Guid.NewGuid().ToString();
            for (int i = 11; i <= 20; ++i)
            {
                //获取连接
                conn = ConnectionPool.getPool().getConnection(strkey);
                try
                {
                    //数据操作
                    SqlCommand cmd = new SqlCommand("Select * from Users", conn);
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine("ID:" + i + "，2姓名：" + dr[1]);
                    }
                    dr.Close();

                    cmd = new SqlCommand("Select * from Province", conn);
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine("ID:" + i + "，2名称：" + dr[1]);
                    }
                    dr.Close();

                    //将连接添加回连接池中
                    ConnectionPool.getPool().closeConnection(strkey, conn);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("\n异常信息:\n{0}", ex.Message);
                    break;
                }
            }
            ConnectionPool.getPool().Close(strkey);

        }


        public static void Gettest3()
        {
            List<DataTable> collection = new List<DataTable>();
            try
            {
                for (int i = 20001; i <= 500000; i++)
                {
                    var conns = ConnectionPool.getPools();
                    var str = conns.getConn();
                    string strsql = @"INSERT INTO [Chloe].[dbo].[Users]
           ([Name]
           ,[Gender]
           ,[Age]
           ,[CityId]
           ,[OpTime])
     VALUES
           ('jjg" + i + @"'
           ," + i + 1 + @"
           ," + i + 2 + @"
           ," + i + 3 + @"
           ,'" + DateTime.Now + @"')";

                    str.Open();
                    SqlCommand SqlCmd = new SqlCommand(strsql, str);
                    var num = SqlCmd.ExecuteNonQuery();
                    Console.WriteLine("ID:" + i + "，保存状态：" + num);

                }

                for (int i = 1; i <= 200000; ++i)
                {
                    var conns = ConnectionPool.getPools();
                    var str = conns.getConn();
                    if (str.State != ConnectionState.Open)
                    {
                        str.Open();
                        SqlCommand SqlCmd = new SqlCommand("Select * from Users", str);
                        SqlDataReader SqlDr = SqlCmd.ExecuteReader();
                        if (SqlDr.HasRows)
                        {
                            DataTable dt = new DataTable();
                            //读取SqlDataReader里的内容 
                            dt.Load(SqlDr);
                            collection.Add(dt);
                        }
                    }

                    Console.WriteLine("ID:" + i + "，打开数据库状态：" + str.State);

                }

               
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
            Console.WriteLine("总共创建了对象：" + collection.Count);
            Console.Write("请按任意键继续");
            Console.ReadLine();

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        public static void ExecuteDataReader(string sql)
        {

        }

    }
}