using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Text;


namespace MetroGauges
{
	public class SQLiteHelper
	{
		private static string connectionString = string.Empty;
		private static string mapConfigSource = string.Empty;

        static SQLiteHelper()
        {
            string datasource = AppDomain.CurrentDomain.BaseDirectory + "Database\\metrogauges.db"; 
            connectionString = string.Format("Data Source={0};Version={1};password={2}", datasource, 3, "");
        }

		/// <summary>  
		/// 创建一个数据库文件。如果存在同名数据库文件，则会覆盖。  
		/// </summary>  
		/// <param name="dbName">数据库文件名。为null或空串时不创建。</param>  
		/// <param name="password">（可选）数据库密码，默认为空。</param>  
		/// <exception cref="Exception"></exception>  
		public static void CreateDB(string dbName)
		{
			if (!string.IsNullOrEmpty(dbName))
			{
				try { SQLiteConnection.CreateFile(dbName); }
				catch (Exception) { throw; }
			}
		}

		/// <summary>   
		/// 对SQLite数据库执行增删改操作，返回受影响的行数。   
		/// </summary>   
		/// <param name="sql">要执行的增删改的SQL语句。</param>   
		/// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>   
		/// <returns></returns>   
		/// <exception cref="Exception"></exception>  
		public static int ExecuteNonQuery(string sql,  params SQLiteParameter[] parameters)
		{
			int affectedRows = 0;
	
			using (SQLiteConnection connection = new SQLiteConnection(connectionString))
			{
				using (SQLiteCommand command = new SQLiteCommand(connection))
				{
					try
					{
						connection.Open();
						command.CommandText = sql;
						if (parameters.Length != 0)
						{
							command.Parameters.AddRange(parameters);
						}
						affectedRows = command.ExecuteNonQuery();
					}
					catch (Exception) { throw; }
				}
			}
			return affectedRows;
		}

        public static int ExecuteNonQuery(string sql)
        {
            int affectedRows = 0;

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(connection))
                {
                    try
                    {
                        connection.Open();
                        command.CommandText = sql;
                        affectedRows = command.ExecuteNonQuery();
                    }
                    catch (Exception ex) {
                        //throw;
                        LogManager.WriteLog("error", "ExecuteNonQuery:" + ex.Message);
                    }
                }
            }
            return affectedRows;
        }

        /// <summary>  
        /// 批量处理数据操作语句。  
        /// </summary>  
        /// <param name="list">SQL语句集合。</param>  
        /// <exception cref="Exception"></exception>  
        public static void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list, string mapconfigSource = null)
		{
		
			using (SQLiteConnection connection = new SQLiteConnection(connectionString))
			{
				try { connection.Open(); }
				catch { throw; }
				using (SQLiteTransaction tran = connection.BeginTransaction())
				{
					using (SQLiteCommand cmd = new SQLiteCommand(connection))
					{
						try
						{
							foreach (var item in list)
							{
								cmd.CommandText = item.Key;
								if (item.Value != null)
								{
									cmd.Parameters.AddRange(item.Value);
								}
								cmd.ExecuteNonQuery();
							}
							tran.Commit();
						}
						catch (Exception) { tran.Rollback(); throw; }
					}
				}
			}
		}

		/// <summary>  
		/// 执行查询语句，并返回第一个结果。  
		/// </summary>  
		/// <param name="sql">查询语句。</param>  
		/// <returns>查询结果。</returns>  
		/// <exception cref="Exception"></exception>  
		public static object ExecuteScalar(string sql,  params SQLiteParameter[] parameters)
		{
		
			using (SQLiteConnection connection = new SQLiteConnection(connectionString))
			{
				using (SQLiteCommand cmd = new SQLiteCommand(connection))
				{
					try
					{
						connection.Open();
						cmd.CommandText = sql;
						if (parameters.Length != 0)
						{
							cmd.Parameters.AddRange(parameters);
						}
						return cmd.ExecuteScalar();
					}
					catch (Exception) { throw; }
				}
			}
		}

		/// <summary>   
		/// 执行一个查询语句，返回一个包含查询结果的DataTable。   
		/// </summary>   
		/// <param name="sql">要执行的查询语句。</param>   
		/// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>   
		/// <returns></returns>   
		/// <exception cref="Exception"></exception>  
		public static DataTable ExecuteQuery(string sql, params SQLiteParameter[] parameters)
		{
			
			using (SQLiteConnection connection = new SQLiteConnection(connectionString))
			{
				using (SQLiteCommand command = new SQLiteCommand(sql, connection))
				{
					if (parameters.Length != 0)
					{
						command.Parameters.AddRange(parameters);
					}
					SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
					DataTable data = new DataTable();
					try { adapter.Fill(data); }
					catch (Exception ex) {

                        //throw;
                        LogManager.WriteLog("error", "ExecuteQuery:" + ex.Message);
                    }
					return data;
				}
			}
		}

        public static DataTable ExecuteQuery(string sql)
        {

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {
                    connection.Open();
                    command.CommandText = sql;
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
                    DataTable data = new DataTable();
                    try {
                        adapter.Fill(data);
                    }
                    catch (Exception ex) {
                        //throw;
                        LogManager.WriteLog("error", "ExecuteQuery:" + ex.Message);
                    }
                    return data;
                }
            }
        }

        /// <summary>   
        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例。   
        /// </summary>   
        /// <param name="sql">要执行的查询语句。</param>   
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准。</param>   
        /// <returns></returns>   
        /// <exception cref="Exception"></exception>  
        public static SQLiteDataReader ExecuteReader(string sql,  params SQLiteParameter[] parameters)
		{
			
			SQLiteConnection connection = new SQLiteConnection(connectionString);
			SQLiteCommand command = new SQLiteCommand(sql, connection);
			try
			{
				if (parameters.Length != 0)
				{
					command.Parameters.AddRange(parameters);
				}
                if(connection.State == ConnectionState.Closed)
				    connection.Open();

                SQLiteDataReader reader= command.ExecuteReader(CommandBehavior.CloseConnection);

                return reader;

            }
			catch (Exception) { throw; }
		}

		/// <summary>   
		/// 查询数据库中的所有数据类型信息。  
		/// </summary>   
		/// <returns></returns>   
		/// <exception cref="Exception"></exception>  
		public static DataTable GetSchema()
		{
			
			using (SQLiteConnection connection = new SQLiteConnection(connectionString))
			{
				try
				{
					connection.Open();
					return connection.GetSchema("TABLES");
				}
				catch (Exception) { throw; }
			}
		}


        /// <summary>  
        /// 填充对象列表：用DataTable填充实体类
        /// </summary>  
        public static ObservableCollection<T> FillModel<T>(DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return new ObservableCollection<T>();
            }
            ObservableCollection<T> modelList = new ObservableCollection<T>();
           
            foreach (DataRow dr in dt.Rows)
            {
                T model = (T)Activator.CreateInstance(typeof(T));
                //T model = new T();
                for (int i = 0; i < dr.Table.Columns.Count; i++)
                {
                    string name = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(dr.Table.Columns[i].ColumnName);

                    PropertyInfo propertyInfo = model.GetType().GetProperty(name);

                    try
                    {
                        if (propertyInfo != null && dr[i] != DBNull.Value)
                            propertyInfo.SetValue(model, dr[i], null);
                    }
                    catch (Exception ex)
                    {
                        //throw;
                        LogManager.WriteLog("error", "FillModel:" + ex.Message);
                    }
                    
                }

                modelList.Add(model);
            }
            return modelList;
        }

        /// <summary>  
        /// 填充对象：用DataRow填充实体类
        /// </summary>  
        public static  T FillModel<T>(DataRow dr)
        {
            if (dr == null)
            {
                return default(T);
            }

            T model = (T)Activator.CreateInstance(typeof(T));
            //T model = new T();

            for (int i = 0; i < dr.Table.Columns.Count; i++)
            {
                PropertyInfo propertyInfo = model.GetType().GetProperty(dr.Table.Columns[i].ColumnName);
                if (propertyInfo != null && dr[i] != DBNull.Value)
                    propertyInfo.SetValue(model, dr[i], null);
            }
            return model;
        }
    }
}