using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;

namespace JiangSu.cs
{
    public class SQLiteHelper
    {
        private static string _connStr = null;

        private static bool _isLoadDriver = false;

        /// <summary>
        /// ConnectionString样例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(_connStr))
                {
                    //_connStr = string.Format("Data Source={0};Version={1};", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\XiAn.sqlite"), 3);
                    _connStr = string.Format("Data Source={0};Integrated Security=True;Max Pool Size=64", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "App_Data\\js.db"));
                }

                return _connStr;
            }
        }

        private static void LoadDrivers(SQLiteConnection conn)
        {
            if (!_isLoadDriver)
            {
                string spatialitePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\sqlite");
                Environment.SetEnvironmentVariable("PATH", spatialitePath + ";" + Environment.GetEnvironmentVariable("PATH"));
                LogOut.Info(spatialitePath);

                _isLoadDriver = true;
            }

            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            conn.LoadExtension("mod_spatialite.dll");
        }

        /// <summary> 
        /// 查询数据库中的所有数据类型信息。
        /// </summary> 
        public DataTable GetSchema()
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                return conn.GetSchema("TABLES");
            }
        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, params SQLiteParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
        }

        public static DataSet ExecuteQuery(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    DataSet ds = new DataSet();
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }

                    return ds;
                }
            }
        }

        public static DataTable GetDataTable(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                //LoadDrivers(conn);

                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    DataTable dt = new DataTable();
                    using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }

                    return dt;
                }
            }
        }

        public static int ExecuteNonQuery(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static SQLiteDataReader ExecuteReader(string cmdText, params SQLiteParameter[] parameters)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnectionString);
            using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
            {
                PrepareCommand(cmd, conn, parameters);

                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public static object ExecuteScalar(string cmdText, params SQLiteParameter[] parameters)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(cmdText, conn))
                {
                    PrepareCommand(cmd, conn, parameters);

                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 批量处理数据操作语句。
        /// </summary>
        public void ExecuteNonQueryBatch(List<KeyValuePair<string, SQLiteParameter[]>> list)
        {
            using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                using (SQLiteTransaction tran = conn.BeginTransaction())
                {
                    using (SQLiteCommand cmd = new SQLiteCommand(conn))
                    {
                        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 ex)
                        {
                            tran.Rollback();
                            throw ex;
                        }
                    }
                }
            }
        }
    }
}