using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data.Common;
using ConsoleApplication22.Model;
using System.Reflection;

namespace ConsoleApplication22
{
    class Program
    {
        static void Main(string[] args)
        {
            ReadAsyncDemo();
            Console.ReadLine();
        }
       
        static async void ReadAsyncDemo()
        {
            string selectSQL = "select * from country";
            IList<Country> countryList = await MySqlRead2Async<Country>(selectSQL);
        }

        static string MySqlConnectionString = ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
        static async void MySqlReadAsync(string readSQL,Dictionary<string,object> parametersDic=null)
        {
            using (MySqlConnection conn = GetMySqlConnection())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
            
                using (MySqlCommand cmd = new MySqlCommand(readSQL, conn))
                {
                    using (DbDataReader dataReader = await cmd.ExecuteReaderAsync())
                    {
                        StringBuilder selectBuilder = new StringBuilder();
                        while (await dataReader.ReadAsync())
                        {                            
                            for(int i=0;i<dataReader.FieldCount;i++)
                            {
                                selectBuilder.Append(dataReader[i]+"\t");
                            }
                            selectBuilder.AppendLine();                                                       
                        }
                        Console.WriteLine(selectBuilder.ToString());
                    }
                }
            } 
        }

        static async Task<IList<T>> MySqlRead2Async<T>(string selectSQL,Dictionary<string,object> parametersDic=null)where T:class
        {
            IList<T> dataList = new List<T>();
            using (MySqlConnection conn = GetMySqlConnection())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                using(MySqlCommand selectCmd=new MySqlCommand(selectSQL, conn))
                {
                    using (DbDataReader dataReaderAsync = await selectCmd.ExecuteReaderAsync())
                    {
                        if (dataReaderAsync.HasRows)
                        {
                            DataTable dt = new DataTable();
                            dt.Load(dataReaderAsync);
                            dataList = dt.ToDataList<T>();
                        }
                    }
                }
            }
            return dataList;               
        }

        static async Task<int> MySqlWriteAsync(string writeSQL,Dictionary<string,object> parametersDic=null)
        {
            int executeResult = -1;
            using (MySqlConnection conn = GetMySqlConnection())
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (MySqlCommand cmd = new MySqlCommand(writeSQL, conn))
                {
                    using (MySqlTransaction myTrans = await conn.BeginTransactionAsync())
                    {
                        try
                        {
                            if (parametersDic != null && parametersDic.Any())
                            {
                                foreach (var pDic in parametersDic)
                                {
                                    cmd.Parameters.AddWithValue(pDic.Key, pDic.Value);
                                }
                            }

                            cmd.Transaction = myTrans;
                            executeResult = await cmd.ExecuteNonQueryAsync();
                            myTrans.Commit();
                        }
                        catch (Exception ex)
                        {
                            myTrans.Rollback();
                            Console.WriteLine(ex.Message);
                        }
                    }
                }
            }
            return executeResult;            
        }

        static MySqlConnection GetMySqlConnection()
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = MySqlConnectionString;
            return conn;
        }
    }

    static class ExtendClass
    {
        public static List<T> ToDataList<T>(this DataTable dt)
        {
            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }
                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }
    }
}